Semantic SQL
Source: docs/guide/08-semantic-sql.md at origin/main (3688d236), synced 2026-11-05. Do not edit this page here; change the source and run yarn sync:semantic.
Semantic SQL is how you ask the layer a question. It is SQL whose table is a domain, whose columns are the domain's fields, and whose aggregates are its metrics. The layer reads your statement, checks it, works out the joins and filters each metric needs, and writes the warehouse SQL itself. Your text is never run as written.
SELECT orders.region,
DATE_TRUNC('week', time) AS week,
MEASURE(revenue) AS revenue,
MEASURE(aov) AS aov
FROM commerce
WHERE time >= :since AND time < :until
AND orders.channel IN ('web', 'app')
GROUP BY ALL
The shape of a statement
A semantic select is a SELECT … FROM <domain>. It is the only place model names mean
anything:
| You write | It means |
|---|---|
FROM commerce | the domain. It is never aliased and never joined. |
MEASURE(revenue) | a metric, by its bare name. |
orders.region | a field, always qualified by its dataset (or written as a path) |
time | each metric's own time axis |
GROUP BY ALL | group by every selected field and time bucket |
A select with no MEASURE is a row select: it returns the rows themselves, or with DISTINCT
(or GROUP BY and no MEASURE) the distinct combinations, which is how you list a field's values:
SELECT DISTINCT orders.region, orders.channel
FROM commerce
WHERE time >= :since AND time < :until
Everything outside the semantic selects is ordinary SQL over their results: CTEs, joins between
them, window functions, arithmetic, HAVING, QUALIFY, ORDER BY, LIMIT
(Composing queries).
Measures
MEASURE(metric) takes exactly one bare metric name. Some mistakes and what they return:
| You write | You get |
|---|---|
MEASURE(orders.revenue) | UNKNOWN_METRIC, naming MEASURE(revenue). Metric names are never qualified. |
SUM(orders.amount) in a semantic select | MEASURE_MISUSE. An aggregate over fields is a metric, so declare it in the model. |
revenue where a field belongs | MEASURE_MISUSE, naming MEASURE(revenue) |
WHERE MEASURE(revenue) > 100 | MEASURE_MISUSE. Filter measures in HAVING. |
SELECT orders.region, MEASURE(revenue) AS revenue
FROM commerce
WHERE time >= :since AND time < :until
GROUP BY ALL
HAVING MEASURE(revenue) > 1000
Fields
A field is dataset.field. The layer joins it from each metric's anchor along the one shortest
route of links. When two routes tie, it is refused and you write a path instead
(Fields and paths in SQL). An unqualified name is UNKNOWN_FIELD,
and the error suggests the qualified one. Names are case-insensitive: orders.Region is
orders.region.
WHERE
WHERE holds two kinds of condition, joined by AND:
- The window: one lower and one upper bound on
time,time >= a AND time < b(Time). - Filters on fields:
=,<>,<,<=,>,>=,IN,BETWEEN,IS [NOT] NULLandCONTAINS(dataset.field, 'text'), joined byAND,ORandNOT(Filters). A boolean field alone means= true.
A filter's value is a literal or a parameter, checked against the field's type. A value that is an
expression or another column is FILTER_NOT_PUSHABLE: compute it over the results in a CTE.
Parameters
Write :name where a value goes, and declare it in the request's params:
{
"sql": "SELECT MEASURE(revenue) FROM commerce WHERE time >= :since AND time < :until AND orders.region = :region",
"params": {
"since": {"type": "timestamp", "value": "2026-09-01T00:00:00Z"},
"until": {"type": "timestamp", "value": "2026-10-01T00:00:00Z"},
"region": "EU"
}
}
- A type is
timestamp,date,string,numberorboolean. A bare JSON value is typed by JSON, so a time must be declared with its type. - A parameter is bound as a typed value, never pasted into the text, so it cannot inject SQL. It
may stand only where a value belongs, never as a name, a grain, an alias, or inside
ORDER BY,GROUP BYor a window. ?,$1,@xand:1are refused. Parameters are always named.- A name the SQL uses with no value is
UNBOUND_PARAMETER, and one declared but unused isUNKNOWN_PARAMETER.
A saved query is SQL plus its declared parameters. It keeps working when a metric or the domain is renamed (Names and changes).
Plan or execute
A request is mode: execute (the default), which runs it, or mode: plan, which checks, routes and
prices it and runs nothing. A plan returns its problems inline as valid: false rather than as an
HTTP error, and shows the exact SQL an execute would send. Over MCP, query_compile plans and
query_run executes.
Limits
- Rows. An answer may have at most the smallest of your
LIMIT, the domain'srow_limitand 50,000 rows. ALIMITabove that isLIMIT_EXCEEDED. When an answer would be larger than a limit you did not write, it is refused (RESULT_TOO_LARGE) rather than cut, since a cut answer would read as the whole one. Under your ownLIMIT n, the topnrows are the answer.OFFSETis refused, because there is no paging. - Time. A warehouse statement runs under the deployment's timeout. A request may ask for more
with
timeout_seconds, up to a maximum (TIMEOUT_TOO_LONGabove it). A statement past its timeout is cancelled (WAREHOUSE_TIMEOUT). - Cost. On a metered warehouse, a statement priced above the byte cap is
QUERY_TOO_EXPENSIVE, and consent does not lift it. Narrow the window or the filters, or group coarser. - Size. At most 20,000 characters, one statement, and 8 semantic selects.
One connection per statement
A statement runs on the one connection its datasets live on. A statement that reads datasets on
two connections is CROSS_CONNECTION_UNSUPPORTED, naming each dataset and its connection. Ask
each connection's part in its own statement.