Fields and paths in SQL
Source: docs/guide/09-fields-and-paths-in-sql.md at origin/main (3688d236), synced 2026-11-05. Do not edit this page here; change the source and run yarn sync:semantic.
A query names every field with its dataset, dataset.field, and the layer joins it from each
metric's anchor. When there is more than one way to reach that dataset, you write the way you mean
as a path. A path names the anchor, each link, and the field. This chapter uses the
logistics domain:
shipments ──first_carrier──▶ carriers ──carrier_category──▶ categories ◀─┐
──second_carrier─▶ carriers └─ parent_category
One route: the shorthand
When exactly one shortest route reaches a dataset, dataset.field is enough:
SELECT categories.category_name, MEASURE(category_count)
FROM logistics
GROUP BY ALL
Two links to one dataset
A shipment has two carriers, so carriers.carrier_name could mean either and is refused:
-- refused: AMBIGUOUS_REFERENCE
SELECT carriers.carrier_name, MEASURE(shipments)
FROM logistics
WHERE time >= :since AND time < :until
GROUP BY ALL
The error's routes lists the paths to write instead. Name the link, and both roles fit in one
query:
SELECT shipments.first_carrier.carrier_name AS outbound,
shipments.second_carrier.carrier_name AS return_carrier,
MEASURE(shipments)
FROM logistics
WHERE time >= :since AND time < :until
AND shipments.second_carrier.country = 'DE'
GROUP BY ALL
A dataset linked to itself
A category's parent is a category. The path walks the self link once for the parent, and twice for the grandparent:
SELECT categories.category_name,
categories.parent_category.category_name AS parent,
categories.parent_category.parent_category.category_name AS grandparent,
MEASURE(category_count)
FROM logistics
GROUP BY ALL
The rules
- A path starts with the anchor of every metric that reads it. A path from
shipmentscannot group a metric anchored oncategories(FIELD_NOT_REACHABLE). - Each link is a to-one relationship of the dataset before it, named by its name or by its single
key column (
shipments.first_carrier_id.carrier_name). An unknown link isUNKNOWN_RELATIONSHIP, with suggestions. - At most eight links and ten parts in all, each a plain name (
INVALID_PATHpast that). - A link the domain does not expose is
FIELD_NOT_REACHABLE, naming the link. - A path works wherever a field does: the select list,
WHERE,GROUP BY,ORDER BYand row selects. Outside a semantic select, in a CTE or the outer query, a name of three parts or more is refused (SQL_NOT_ALLOWED). Read the column by its alias there. - Every part is a model name, never a warehouse name.
main.orders.order_idin a semantic select isUNKNOWN_DATASET, not a table. - Spelling one field two ways in one select (
customers.segmentandorders.orders_to_customers.segment, the same route) isDUPLICATE_DIMENSION. Keep one.
Output column names
An unaliased field is output under its bare field name: orders.region is region. When two
fields in one select share a name, or a field is called time, each is output under its full
spelling with . replaced by _:
SELECT shipments.first_carrier.carrier_name, shipments.second_carrier.carrier_name, MEASURE(shipments)
FROM logistics
WHERE time >= :since AND time < :until
GROUP BY ALL
-- columns: shipments_first_carrier_carrier_name, shipments_second_carrier_carrier_name, shipments
ORDER BY may use those output names. An answer titles a field read through a link by the link
too, for example First carrier · Carrier name.
Finding the paths
Describe lists every field a metric can be broken down by, spelled as you would write it, paths included (Domains). The paths a mistyped field suggests are kept short on purpose. Describe is the full list.
Not every listing shows links. Over MCP:
query_describe_metriclists the metric's breakdown fields with their paths (shipments.second_carrier.carrier_name).model_inspect(model, dataset=...)lists a dataset's relationships: each link's name, its target and its key.query_describe_modelandquery_search_fieldsshow only a link's key column (shipments.first_carrier_id), never the link itself. Seeing<name>_idthere usually means a link you can walk.
A link's name says nothing about what the link means. first_carrier and second_carrier are two
roles, and only the model's descriptions, or the person who asked, can tell you which is the
outbound leg. When a question names a role the model does not, say which link you took.