Semantic query
A source with metricflow: <dir> carries a bundle: datasets (tables with
calculations and relationships), entities (keys and attributes) and
constellations (metrics conformed across datasets). A query that names one of
them is compiled into the warehouse's SQL before it runs; everything else is
ordinary SQL on the same connection. A source that sets required: true under
metricflow: allows no ordinary SQL: a query reading a table the bundle does
not declare is refused with ERR-METRICFLOW-OUTSIDE-BUNDLE.
{{#if_tool describe_semantics}}## Discover before writing
Call {{ s_describe_semantics }} without a name first: every dataset, entity
and constellation with its description and how many fields it has. Then call
it again with the name of the one or two objects the question is about to
get their calculations, relationships, attributes, roles and metrics, or
with search and the question's words to find the object or field that
means them, including the namespaces and pages the overview only counts. A
page holds a saved query, and a derived dataset shows its SQL: read one that
fits, then run or adapt that SQL, keeping its filters, when a question needs a
different cut. Use
those names exactly. A name that is not in the listing does not exist,
however natural it reads.
{{/if_tool}}
The grammar
SELECT account.industry AS industry, -- relationship.attribute
DATE_TRUNC(created_at, MONTH) AS month, -- a plain column, in BigQuery's argument order
AGG(invoiced_total) AS invoiced_total, -- a declared calculation
AGG(collection_rate) AS collection_rate -- a ratio calculation, never a SUM
FROM invoices -- the dataset's bare name
WHERE account.country = 'US'
GROUP BY 1, 2
ORDER BY 1, 2
AGG(<calculation>) aggregates with the expression the bundle declares. A
ratio such as collection_rate is paid_total / invoiced_total inside the
compiler; AGG(collection_rate) is right, AVG(collection_rate) is wrong.
account.industry follows the account relationship to that entity's
attribute. Chain them: document.owner.plan. The join is declared
many-to-one, so the aggregate never fans out.
FROM <constellation> reads metrics from several datasets at once, each
aggregated in its own branch and joined on the conformed roles. Group by the
roles (month, account.industry) and read metrics with AGG().
WHERE, GROUP BY ALL, ORDER BY, LIMIT, CTEs and board variables all
work. {{ filter('account.industry', industry) }} applies a dropdown through
a relationship path, on a dataset or a constellation.
Don't
COUNT(*), SUM(amount) or AVG(x) on a dataset or constellation: the
bundle already declares the aggregate; use AGG(<name>).
GROUP BY a raw column of a joined table or a hand-written JOIN: name the
path (account.industry) and let the compiler join.
- The physical location of a bundle object:
FROM invoices, never
FROM `proj`.dataset.invoices. The warehouse path names the raw table,
which the bundle does not declare, so the query runs as plain SQL, or is
refused when the source requires its bundle.
- An output alias that repeats the dataset's name (
AGG(ticket_count) AS tickets FROM tickets): BigQuery resolves the compiled GROUP BY against
the select list first and rejects it. Alias the calculation by its own
name.
Write the warehouse's SQL
A query without dialect: is read in the source's dialect, so the functions
around the semantic names are the warehouse's: BigQuery takes
DATE_TRUNC(created_at, MONTH), Postgres and DuckDB take
DATE_TRUNC('month', created_at). A compile error names the unknown
calculation, attribute or table; fix the name, do not fall back to plain SQL.
1---2name: semantic-query3kind: pattern4description: >5 Write a board query against a source that carries a MetricFlow bundle6 (`metricflow:` on the source): `AGG()` over declared calculations and7 metrics, `relationship.attribute` paths instead of joins, constellations as8 tables, and the pitfalls that make a semantic query fail to compile or fail9 in the warehouse. Use whenever a query reads a dataset or constellation the10 bundle declares.11metadata:12 source: https://skillmd.com/skills/dbt-labs/semantic-query13 author: fivetran14---1516# Semantic query1718A source with `metricflow: <dir>` carries a bundle: datasets (tables with19calculations and relationships), entities (keys and attributes) and20constellations (metrics conformed across datasets). A query that names one of21them is compiled into the warehouse's SQL before it runs; everything else is22ordinary SQL on the same connection. A source that sets `required: true` under23`metricflow:` allows no ordinary SQL: a query reading a table the bundle does24not declare is refused with `ERR-METRICFLOW-OUTSIDE-BUNDLE`.2526{{#if_tool describe_semantics}}## Discover before writing2728Call `{{ s_describe_semantics }}` without a name first: every dataset, entity29and constellation with its description and how many fields it has. Then call30it again with the `name` of the one or two objects the question is about to31get their calculations, relationships, attributes, roles and metrics, or32with `search` and the question's words to find the object or field that33means them, including the namespaces and pages the overview only counts. A34page holds a saved query, and a derived dataset shows its SQL: read one that35fits, then run or adapt that SQL, keeping its filters, when a question needs a36different cut. Use37those names exactly. A name that is not in the listing does not exist,38however natural it reads.39{{/if_tool}}40## The grammar4142```sql43SELECT account.industry AS industry, -- relationship.attribute44 DATE_TRUNC(created_at, MONTH) AS month, -- a plain column, in BigQuery's argument order45 AGG(invoiced_total) AS invoiced_total, -- a declared calculation46 AGG(collection_rate) AS collection_rate -- a ratio calculation, never a SUM47FROM invoices -- the dataset's bare name48WHERE account.country = 'US'49GROUP BY 1, 250ORDER BY 1, 251```5253- `AGG(<calculation>)` aggregates with the expression the bundle declares. A54 ratio such as `collection_rate` is `paid_total / invoiced_total` inside the55 compiler; `AGG(collection_rate)` is right, `AVG(collection_rate)` is wrong.56- `account.industry` follows the `account` relationship to that entity's57 attribute. Chain them: `document.owner.plan`. The join is declared58 many-to-one, so the aggregate never fans out.59- `FROM <constellation>` reads metrics from several datasets at once, each60 aggregated in its own branch and joined on the conformed roles. Group by the61 roles (`month`, `account.industry`) and read metrics with `AGG()`.62- `WHERE`, `GROUP BY ALL`, `ORDER BY`, `LIMIT`, CTEs and board variables all63 work. `{{ filter('account.industry', industry) }}` applies a dropdown through64 a relationship path, on a dataset or a constellation.6566## Don't6768- `COUNT(*)`, `SUM(amount)` or `AVG(x)` on a dataset or constellation: the69 bundle already declares the aggregate; use `AGG(<name>)`.70- `GROUP BY` a raw column of a joined table or a hand-written `JOIN`: name the71 path (`account.industry`) and let the compiler join.72- The physical location of a bundle object: `FROM invoices`, never73 `` FROM `proj`.dataset.invoices ``. The warehouse path names the raw table,74 which the bundle does not declare, so the query runs as plain SQL, or is75 refused when the source requires its bundle.76- An output alias that repeats the dataset's name (`AGG(ticket_count) AS77 tickets FROM tickets`): BigQuery resolves the compiled `GROUP BY` against78 the select list first and rejects it. Alias the calculation by its own79 name.8081## Write the warehouse's SQL8283A query without `dialect:` is read in the source's dialect, so the functions84around the semantic names are the warehouse's: BigQuery takes85`DATE_TRUNC(created_at, MONTH)`, Postgres and DuckDB take86`DATE_TRUNC('month', created_at)`. A compile error names the unknown87calculation, attribute or table; fix the name, do not fall back to plain SQL.