Reference

SQL reference

DXData speaks ANSI SQL with Trino-compatible extensions: time travel, branch-aware identifiers, and full federation across registered sources. Start with SELECT — the other tabs roll in over the coming weeks.

Syntax

select.bnf

SELECT [ ALL | DISTINCT ] select_expr [, ...]
  FROM table_reference [, ...]
  [ WHERE condition ]
  [ GROUP BY { expr | GROUPING SETS | CUBE | ROLLUP } ]
  [ HAVING condition ]
  [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
  [ ORDER BY expr [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
  [ LIMIT count ] [ OFFSET start ]

Example 1 — filter and aggregate

A bread-and-butter rollup. Note the use of INTERVAL syntax and the cents-to-dollars conversion in the projection.

query.sql

-- Filter and aggregate
SELECT region,
       COUNT(*) AS orders,
       SUM(total_cents) / 100.0 AS revenue_usd
FROM lake.orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY region
ORDER BY revenue_usd DESC
LIMIT 20;

Example 2 — cross-catalog join

Join an Iceberg table in lake with a live Postgres table in analytics-pg. The planner pushes the id filter down to Postgres so only matching rows come back over the wire.

join.sql

-- Cross-catalog join (warehouse + Postgres source)
SELECT o.id,
       c.segment,
       o.total_cents
FROM lake.orders o
JOIN analytics-pg.public.customers c
  ON c.id = o.customer_id
WHERE o.status = 'paid';

Example 3 — time travel

FOR VERSION AS OF (and its sibling FOR TIMESTAMP AS OF) reads a historical snapshot. Combine with a branch reference to inspect a named experiment.

snapshot.sql

-- Time travel: query a past snapshot
SELECT *
FROM lake.orders
FOR VERSION AS OF 'snapshot-41'
WHERE order_date = DATE '2026-03-15';

Notes

Identifiers are case-insensitive unless quoted. Reserved words must be quoted with double quotes.
LIMIT runs after ORDER BY; combine them to get deterministic top-N.
Subqueries and CTEs (WITH) are fully supported; recursive CTEs are available for hierarchies.

SELECT [ ALL | DISTINCT ] select_expr [, ...] FROM table_reference [, ...] [ WHERE condition ] [ GROUP BY { expr | GROUPING SETS | CUBE | ROLLUP } ] [ HAVING condition ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY expr [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT count ] [ OFFSET start ]

-- Filter and aggregate SELECT region, COUNT(*) AS orders, SUM(total_cents) / 100.0 AS revenue_usd FROM lake.orders WHERE order_date >= CURRENT_DATE - INTERVAL '30' DAY GROUP BY region ORDER BY revenue_usd DESC LIMIT 20;

-- Cross-catalog join (warehouse + Postgres source) SELECT o.id, c.segment, o.total_cents FROM lake.orders o JOIN analytics-pg.public.customers c ON c.id = o.customer_id WHERE o.status = 'paid';