Skip to main content

Fields and paths in SQL

Synced from semantic-layer

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

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 shipments cannot group a metric anchored on categories (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 is UNKNOWN_RELATIONSHIP, with suggestions.
  • At most eight links and ten parts in all, each a plain name (INVALID_PATH past 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 BY and 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_id in a semantic select is UNKNOWN_DATASET, not a table.
  • Spelling one field two ways in one select (customers.segment and orders.orders_to_customers.segment, the same route) is DUPLICATE_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_metric lists 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_model and query_search_fields show only a link's key column (shipments.first_carrier_id), never the link itself. Seeing <name>_id there 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.