SQL queries

COALESCE: replace missing values#

COALESCE returns the first value that is not NULL. Use it to provide an appropriate default for missing values. An empty string is not NULL, so it is not automatically replaced.

SELECT coalesce(CAST(NULL AS varchar), 'unbekannt') AS status;

Aggregation: summarize rows#

An aggregation combines multiple rows, for example with SUM, COUNT or AVG. GROUP BY creates groups. This example calculates revenue of 30 for Nord and 7 for Süd; the input data is contained in the query.

SELECT region, sum(umsatz) AS umsatz
FROM (VALUES ('Nord', 10), ('Nord', 20), ('Süd', 7)) AS t(region, umsatz)
GROUP BY region
ORDER BY region;

Regexp: check text patterns#

Regular expressions describe text patterns. In Trino, regexp_like checks whether a pattern occurs in a string. Use ^ and $ to match the entire string. The example checks an identifier made of two uppercase letters and three digits.

SELECT regexp_like('AB123', '^[A-Z]{2}[0-9]{3}$') AS gueltig;

What is a data type?#

A data type determines the values a column can hold and the operations that apply. VARCHAR stores text, BIGINT whole numbers and DECIMAL numbers with defined precision and scale. Trino uses REAL or DOUBLE for floating-point numbers. Consider rounding effects when choosing a float type; monetary amounts are commonly modeled as DECIMAL.

DDL, schemas and SQL comments#

DDL stands for Data Definition Language: statements that define data structures, such as CREATE TABLE or ALTER TABLE. A schema groups tables within a catalog. SQL comments explain a query: -- comments to the end of the line, while /* ... */ marks a block. Review structure-changing statements before confirming them.

AI functions and Snowflake SQL#

Administrators choose the SQL model separately: local or API-backed. ai_summarize, ai_classify, ai_translate and ai_extract process text in queries. External models receive the submitted content; permissions, quotas and audit still apply. The “Snowflake SQL” switch translates supported syntax; review the result before adopting it.

SELECT ai_summarize(description) AS summary
FROM iceberg.service.tickets
LIMIT 5;

Table streams#

A stream exposes net changes since its stored read position. SELECT reads changes without advancing that position; processing them in a write statement advances it. UPDATE can appear as a deleted old row and an inserted new row. A stream can become stale when its starting snapshot is no longer retained.

Finding objects#

Addressing is three-part: catalog, schema, table. A fresh installation's catalog is called iceberg; alongside it sit Trino's own catalogs system, jmx and memory, plus the sample data in tpch and tpcds.

SHOW CATALOGS;
SHOW SCHEMAS FROM iceberg;
SHOW TABLES FROM iceberg.produkte;
DESCRIBE iceberg.produkte.produkte;

Querying#

From here on it is ordinary SQL. The example below works on the table from the data browser — article number, description, category, net price, VAT rate and stock.

SELECT
    kategorie,
    count(*)                                        AS artikel,
    round(avg(preis_netto_eur * (1 + mwst_prozent / 100.0)), 2) AS brutto_schnitt,
    sum(lagerbestand)                               AS bestand
FROM iceberg.produkte.produkte
WHERE lagerbestand > 0
GROUP BY kategorie
ORDER BY brutto_schnitt DESC;

Travelling back in time#

Iceberg does not rewrite rows; it writes new snapshots, and Nessie keeps them. Any table can therefore be queried as it looked at an earlier point in time — no backup, no copy.

-- Which states exist?
SELECT snapshot_id, committed_at, operation
FROM iceberg.produkte."produkte$snapshots"
ORDER BY committed_at DESC;

-- Yesterday morning
SELECT count(*) AS zeilen
FROM iceberg.produkte.produkte
FOR TIMESTAMP AS OF TIMESTAMP '2026-09-03 08:00:00 Europe/Berlin';

-- Or one particular snapshot
SELECT *
FROM iceberg.produkte.produkte
FOR VERSION AS OF 8623201004411246578
LIMIT 20;

Besides $snapshots there are further metadata tables of the same shape: $history shows the sequence of states, $files the data files behind the current one, $partitions their distribution. The quotes are required — without them Trino does not read the dollar sign as part of the name.

Creating a view#

Views are the intended way to make an analysis reusable — and the only way to get one into the BI feed: the feed accepts no SQL from the caller, only the name of an object. Whatever Tableau should compute is therefore stored here as a view first.

CREATE VIEW iceberg.produkte.v_bestandswert AS
SELECT
    artikelnummer,
    bezeichnung,
    kategorie,
    lagerbestand,
    preis_netto_eur,
    lagerbestand * preis_netto_eur AS bestandswert_netto
FROM iceberg.produkte.produkte;

Concurrency and quotas#

The Compose stack applies S, M and L workload classes from your user role. Additional queries queue when the class reaches its concurrency limit. Daily quotas for compute time and data read may also apply. Class limits are documented under Operating Compose.

What has its own route#

• Metrics and semantic views: maintained under KI & Semantik → Kennzahlen and queried by name.
• Bulk loading: that is what the five ingestion paths are for, with an assistant instead of hand-written INSERTs.
• The catalog itself: the installation brings it along; it is not created in SQL.