Skip to main content

Semantic SQL

Synced from semantic-layer

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 writeIt means
FROM commercethe domain. It is never aliased and never joined.
MEASURE(revenue)a metric, by its bare name.
orders.regiona field, always qualified by its dataset (or written as a path)
timeeach metric's own time axis
GROUP BY ALLgroup 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 writeYou get
MEASURE(orders.revenue)UNKNOWN_METRIC, naming MEASURE(revenue). Metric names are never qualified.
SUM(orders.amount) in a semantic selectMEASURE_MISUSE. An aggregate over fields is a metric, so declare it in the model.
revenue where a field belongsMEASURE_MISUSE, naming MEASURE(revenue)
WHERE MEASURE(revenue) > 100MEASURE_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] NULL and CONTAINS(dataset.field, 'text'), joined by AND, OR and NOT (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, number or boolean. 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 BY or a window.
  • ?, $1, @x and :1 are refused. Parameters are always named.
  • A name the SQL uses with no value is UNBOUND_PARAMETER, and one declared but unused is UNKNOWN_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's row_limit and 50,000 rows. A LIMIT above that is LIMIT_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 own LIMIT n, the top n rows are the answer. OFFSET is 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_LONG above 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.