YAML model format

This guide explains the authoring format and semantics. Target support varies; see Support and limitations.

This guide explains how to author Semantic Hub semantic catalogs, datasets, metrics, and deployable semantic models in YAML. The JSON Schema reference describes the normative data format; this document explains how the properties work together and how the Semantic Hub compiler interprets them.

Semantic Hub serves the same governed semantic definitions to database semantic layers, BI semantic models, and AI agents through its MCP server. A model must therefore describe more than a reusable SQL expression: it must describe source grain, join cardinality, metric dependencies, time behavior, and the evaluation context required to produce a correct result.

1. Metamodel overview

Semantic Hub has four public YAML object types.

Supporting schemas provide reusable definitions:

A typical catalog is authored as separate files:

catalog.yaml
connections/
  warehouse.yaml
datasets/
  orders.yaml
  customers.yaml
  calendar.yaml
models/
  commerce.yaml

The catalog loader validates each file with its public schema, resolves cross-file references, and assembles a resolved catalog described by ResolvedCatalog.json.

2. Design principles

Semantic Hub YAML follows six rules.

1. Keys express uniqueness when needed. Root datasets may omit unique keys, even when they define metrics. Relationship unique endpoints and grain-dependent operations still require a suitable non-null key.
2. Relationships are contracts. Cardinality, key uniqueness, referential integrity, fanout edges, and association-dataset paths are modeled rather than inferred from SQL text.
3. Metrics are canonical. Metric types and their properties describe the intended operation independently of a target database or BI language.
4. Metric inputs keep their native grain. Cross-dataset ratios and derived calculations aggregate each input independently before combining results.
5. Evaluation context is orthogonal. Grain, query-filter inheritance, time defaults, null behavior, and result type may be applied to any compatible metric type.
6. Dialect expressions are bounded escape hatches. Row-level source expressions and documented compiler overrides may be dialect-specific; filter and metric composition semantics remain canonical.

3. Common object properties

Named catalog objects inherit the common properties below.

The authored name is both the human-readable object name and its stable identity among peers. Semantic Hub does not maintain a separate display label. It derives database-safe identifiers for generated SQL and deployments while semantic references continue to use the authored name. See Object naming for the complete naming, normalization, collision, and referencing rules.

User-facing semantic fields, hierarchies, and metrics can define a presentation object with hidden, folder, and format properties. The namespace makes their presentation-only role explicit. Metrics, fields, and datasets can define synonyms to improve search and natural-language matching by AI agents.

presentation:
  hidden: false
  folder: Revenue
  format: "$#,##0.00"

format is written in the canonical Excel custom-format subset. It is the single value that importers translate to and exporters translate from; there is no per-target format variant.

Semantic Hub is a component of MetaKarta. Use MetaKarta to assign and govern object owners, stewards, versions, certification state, lifecycle, lineage, policies, and other authoritative catalog metadata. Semantic Hub YAML should contain calculation semantics and only lightweight annotations needed during semantic compilation. An annotation can carry a local tag or deprecation hint, but it does not replace the governed record in MetaKarta.

For example, lightweight annotations may be authored as follows:

annotations:
  - name: tag
    value: finance
  - name: deprecated
    value: "true"
  - name: deprecation_message
    value: Use Net Revenue instead.

4. Catalogs and connections

The root catalog manifest names the catalog and selects the connection inherited by datasets and models that do not choose another connection.

name: commerce
definition: Governed commerce analytics
default_connection: warehouse

Connections are authored separately. Credentials should be supplied through the connection provider or secret-management mechanism, not embedded in YAML committed to source control.

Semantic Hub provides typed connections for Snowflake, PostgreSQL, DuckDB, Oracle, and Databricks. DuckDB is embedded, so its connection names a database file (or :memory:) and does not require a server:

name: local_analytics
database_type: DUCKDB
database: data/analytics.duckdb
schema: main

5. Datasets

A dataset is the grain-bearing semantic representation of one physical source object or source query.

name: orders
definition: One row per accepted customer order
table: analytics.fact_orders
connection: warehouse
schema: analytics
unique_keys:
  - columns: [order_id]

Because this dataset has one unique key, its omitted name receives the effective name default, and the key automatically becomes the dataset grain.

5.1 Source selection

Exactly one of table or query is required.

table identifies a physical table, view, file, worksheet, or equivalent object:

table: analytics.fact_orders

query provides equivalent query variants when a dataset cannot be represented by a single source object:

query:
  - dialect: GENERIC
    text: SELECT * FROM analytics.fact_orders WHERE is_test = false
  - dialect: SNOWFLAKE
    text: SELECT * FROM ANALYTICS.FACT_ORDERS WHERE NOT IS_TEST

The optional filter property defines a canonical predicate that becomes part of the dataset contract when specified. Unlike a query-time filter, consumers cannot remove it.

filter: "is_test = FALSE AND status <> 'CANCELLED'"

The value is authored in the Semantic Hub expression language, not in a source-database dialect. Semantic Hub validates it and compiles it for each target.

5.2 Atomic grain and unique keys

unique_keys declares column uniqueness contracts. It is optional for root datasets, including those that define metrics. A root designation does not waive key requirements when the dataset is also a relationship's unique endpoint. Chapter 6 defines null semantics, grain selection, default-key behavior, relationship eligibility, and validation requirements.

unique_keys:
  - name: order_line_pk
    nulls_allowed: false
    columns: [order_id, line_number]
grain_key: order_line_pk

grain_key identifies the dataset's atomic grain. It may be omitted when row identity is not needed. If you declare one key, it is selected automatically; an unnamed sole key is named default. If you declare several named keys, a key named default is selected when you omit grain_key. The selected key must set nulls_allowed: false. A declared key is a correctness contract, not descriptive metadata.

5.3 Joins

Joins are directional graph edges. The dataset containing joins is the implicit from dataset, and to.dataset is the dataset reached by that edge. Model scope follows these explicit outgoing edges from each root dataset.

The cardinality-specific mapping properties are:

Every join requires equal source and target arity, positional component correspondence, and compatible types. A ONE_TO_ONE or MANY_TO_ONE edge maps from_columns to the columns of to.key; a ONE_TO_MANY edge maps the columns of its named or defaulted from_key to to.columns. Every named or defaulted from_key and to.key must have nulls_allowed: false. Foreign-key columns may be nullable and then represent an absent or unknown relationship for that row. Table-backed datasets reference table columns; query-backed datasets reference columns returned by every query variant.

Supported cardinalities are:

A normal transaction-to-lookup dataset relationship is:

joins:
  - name: customer
    from_columns: [customer_id]
    to:
      dataset: customers
      key: customer_pk
    referential_integrity: NOT_GUARANTEED

join_type: MANY_TO_ONE is the default and can be omitted. For ONE_TO_ONE and MANY_TO_ONE, to.key can also be omitted; Semantic Hub then uses the target dataset's resolved grain_key. For ONE_TO_MANY, from_key can be omitted; Semantic Hub then uses the owning dataset's resolved grain_key.

The canonical published model materializes an omitted join name and every applicable omitted to.key or from_key. This prevents a later dataset-grain change from silently altering an already-published join.

referential_integrity: ALL_FROM_ROWS_MATCH may be used on ONE_TO_ONE and MANY_TO_ONE only when every populated source foreign-key tuple matches a target row. ALL_TO_ROWS_MATCH is the corresponding ONE_TO_MANY guarantee: every populated target foreign-key tuple matches a source row. It does not require every source row to have a child. The conservative default preserves unmatched rows.

A role-playing relationship uses a distinct join name:

joins:
  - name: order_date
    from_columns: [order_date]
    to:
      dataset: calendar
  - name: ship_date
    from_columns: [ship_date]
    to:
      dataset: calendar

Fields are then addressed through the relationship path, such as order_date.month and ship_date.month.

Bridge modeling as an ordinary dataset

A many-to-many business relationship is not a special join object. The association or bridge table is modeled as an ordinary dataset with its own table or query, grain, semantic fields when useful, and outgoing joins. The two physical relationships are two explicit graph edges:

parent endpoint -- ONE_TO_MANY --> association dataset -- MANY_TO_ONE --> member endpoint

For example, orders and promotions are connected through order_promotions.

# orders.yaml
name: orders
table: analytics.orders
unique_keys:
  - columns: [order_id]
joins:
  - name: promotion_links
    join_type: ONE_TO_MANY
    from_key: default
    to:
      dataset: order_promotions
      columns: [order_id]
    referential_integrity: ALL_TO_ROWS_MATCH

# order_promotions.yaml
name: order_promotions
table: analytics.order_promotions
unique_keys:
  - columns: [order_id, promotion_id]
joins:
  - name: promotion
    from_columns: [promotion_id]
    to:
      dataset: promotions
      key: promotion_pk
    referential_integrity: ALL_FROM_ROWS_MATCH

# promotions.yaml
name: promotions
table: analytics.promotions
unique_keys:
  - name: promotion_pk
    columns: [promotion_id]
grain_key: promotion_pk

With orders as a model root, promotion_links reaches the association dataset and promotion_links.promotion reaches promotions. Excluding promotion_links removes the association dataset and that path's complete descendant subtree; excluding only promotion_links.promotion retains the association dataset but removes promotions and descendants reached through that edge.

The association dataset must declare a non-null grain key, normally the composite endpoint tuple or a surrogate association-row key. This gives validation and query planning a deterministic deduplication boundary even when the association dataset defines no metrics.

When a transaction dataset references a separate bridge-group or header dataset, the complete pattern is transaction MANY_TO_ONE group, group ONE_TO_MANY association, and association MANY_TO_ONE member. A normal bridge path therefore does not contain two consecutive ONE_TO_MANY edges unless the source data genuinely has that cardinality.

Reachability does not authorize flattening. Before a query crosses a ONE_TO_MANY edge, the compiler must preserve every metric's native grain through pre-aggregation, deduplication, an isolated CTE, or another proven fanout-safe strategy.

5.4 Fields

A field is the single canonical row-level semantic object. A field can identify, group, sort, filter, be returned without aggregation, or supply an aggregate input. The property or query operation that references the field determines its behavior; fields are not divided into semantic dimension and fact types.

fields:
  - name: order_id
    expression: order_id
    data_type: TEXT

  - name: order_date
    expression: order_date
    data_type: DATE
    time_type: DAYS

  - name: status
    expression: status
    data_type: TEXT
    synonyms: [order state]

  - name: revenue
    definition: Extended order-line revenue before tax
    expression: unit_price * quantity
    data_type: DECIMAL
    default_aggregation: SUM
    preferred_usage: FACT
    presentation:
      format: "$#,##0.00"

When a source column collides with a semantic field name, use SOURCE_COLUMN('c') to explicitly select the physical column. For example, define C1 with SOURCE_COLUMN('c') and C with C1; plain c would resolve to C because semantic names are case-insensitive. This agreed syntax awaits parser implementation. See the physical-column contract for argument, scope, and query restrictions.

expression is the field's authoritative RowExpression in the default GENERIC dialect, Semantic Hub's portable ANSI-SQL-like language. Most fields need only this property. Importers should translate native source expressions into expression or into canonical metrics whenever the calculation can be represented without losing semantics.

Datasets commonly contain more fields than a semantic model should expose. Mark fields that are needed only for internal definitions or intermediate computations with presentation.hidden: true. Hidden fields remain available within the semantic model but are omitted from its external interface and from supported presentation and authoring experiences.

fields:
  - name: extended_price_input
    expression: unit_price * quantity
    data_type: DECIMAL
    presentation:
      hidden: true

dialect_expressions contains only equivalent third-party variants used when exporting to the named database or BI-tool dialect. It never contains GENERIC; the canonical form belongs in expression. For example:

fields:
  - name: full_name
    expression: first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name
    dialect_expressions:
      - dialect: SNOWFLAKE
        text: CONCAT_WS(' ', first_name, middle_name, last_name)
    imported_expression:
      dialect: TABLEAU
      text: "[First Name] + ' ' + IFNULL([Middle Name] + ' ', '') + [Last Name]"

imported_expression records the original third-party text and dialect when an importer translates it to the canonical form. It is retained for history preservation and future forward-engineering; it does not override expression or provide an export override. An importer may therefore store both the translated expression and the unchanged imported_expression.

data_type is the portable semantic type. Fields and metric results share the primitive values DECIMAL, INTEGER, FLOAT, BOOLEAN, TEXT, DATE, DATETIME, and TIME. Leave a field's data_type out when you want the system to infer its type from the expression and available source metadata. Binary and database-specific type names are unsupported. Existing fields loaded by the SDK with data_type: UNDEFINED are saved without that property; new documents should omit it. GEOGRAPHIC is not a primitive data type; geographic meaning belongs in semantic_category. time_type identifies a field's temporal granularity. default_aggregation suggests how an authoring or presentation tool initially aggregates the field when no governed metric is selected. It neither defines a metric nor prevents the field from being grouped, filtered, returned, or aggregated another way. Governed aggregate metrics declare their canonical input through expression; a simple input expression may name either an authored field or a verified source column.

default_time_dimension selects the dataset time field inherited by time-aware metrics when their own schema permits a default. time_zone defines the IANA time zone applied before truncation or comparison. The existing property name retains the analytical term dimension because it describes the field's grouping function in time-aware queries.

5.4.1 Semantic member ordering

The schema now defines Field.order_by as a nonempty list of sort keys in precedence order. Each key names one field and has its own direction (default ASC) and null placement (default DATABASE_DEFAULT). For example, order_by: [{field: year_number}, {field: month_number}] orders by year, then month, provided both fields are declared. The single-object example below remains accepted as equivalent to a one-item list.

Each displayed value must determine the complete sort-key tuple. The restrictions below apply to every key. Descending query order reverses each key's direction while preserving key precedence and null placement. Consumers must honor every key or report unsupported composite ordering.

Availability: this is a schema-only update dated 2026-09-28. SDK and compiler/exporter support for the list form remains deferred and unqualified. Existing single-object declarations remain compatible.

Window calculations use a separate WindowSort definition: each item names exactly one metric or grouped field. A window's ordering list can mix both kinds, and each entry defaults to DESC and DATABASE_DEFAULT. It controls analytic evaluation, not final display order. The old schema helper name MetricSort remains a deprecated alias; the authored object shape is unchanged.

Field.order_by declares the stable business order of a field's values. It references another authored field relative to the ordered field's dataset and supplies an uppercase ASC or DESC direction plus FIRST, LAST, or DATABASE_DEFAULT null placement. This metadata is semantic rather than visual formatting, so database, BI, and query exporters can share it.

fields:
  - name: month_name
    expression: MONTH_NAME
    data_type: TEXT
    order_by:
      field: month_number
      direction: ASC
      nulls: LAST

  - name: month_number
    expression: MONTH_NUMBER
    data_type: INTEGER
    presentation:
      hidden: true

The declaration asserts that each displayed month_name has one corresponding month_number within the query grain. The canonical compiler groups by the sort key as a hidden projection when month_name is ordered, but never exposes it in the result. A sort key must not reference the same field or cross a one-to-many relationship. Conflicting source values violate the authored functional-dependency assertion and remain visible as separate groups; the compiler does not hide the problem with DISTINCT, MIN, or MAX.

The declared direction defines canonical ascending member order. Query direction: asc uses it as authored; query direction: desc reverses it. Declared null placement remains explicit in either direction. Semantic member ordering is applied only when the query orders the displayed field, and is not recursively inherited from the sort key's own order_by.

5.5 Preferred usage

preferred_usage is an optional UX and lossless-interchange hint. Its allowed values are DIMENSION and FACT. It records where an authoring tool should initially display a field or preserves a source format's original dimension/fact classification during a round trip.

fields:
  - name: quantity
    expression: quantity
    data_type: DECIMAL
    default_aggregation: SUM
    preferred_usage: FACT

  - name: order_status
    expression: status
    data_type: TEXT
    preferred_usage: DIMENSION

preferred_usage has no semantic authority. Binders, semantic validators, query planners, metric evaluators, generated query SQL, access-control logic, target-capability checks, and query results must remain identical when it is changed or omitted. In particular, it must not determine whether a field is groupable, filterable, aggregatable, temporal, additive, or valid in an expression. Consumers may ignore it.

Metadata importers and exporters are the sole exception. An importer may preserve a source dimension/fact classification in preferred_usage. A corresponding exporter may use that hint to restore the source classification or choose an equivalent target authoring section, so target metadata or semantic-layer DDL can differ only in that organizational annotation. The exported calculation and query behavior must remain equivalent, and any genuine target limitation must be validated independently of the hint.

The canonical format does not define role or roles. During migration, move every legacy dimensions and facts entry into fields, convert a legacy fact_ref value into the aggregate metric's expression, rename second_fact_ref to second_field_ref, and retain default_aggregation where present. An importer may set preferred_usage from the legacy collection or source format when round-trip organization matters, but ordinary authored models should omit it unless a concrete UX or interchange requirement exists. Validators reject the legacy collections and semantic role properties rather than silently assigning execution meaning to them.

5.6 Levels and hierarchies

A level defines the fields that identify and describe one business member. A hierarchy orders levels from the broadest parent to the most detailed child, matching drill-down order in authoring UIs. Existing leaf-first SHL must be migrated explicitly; the compiler does not guess an order from level names or source data. Property names such as key_dimensions describe how the referenced fields operate in the hierarchy; they do not reference a separate dimension object type.

To migrate an older leaf-first hierarchy, reverse only its hierarchies[].levels list once; keep the named level definitions and their keys intact. Imported hierarchies already listed in UI drill-down order need no migration. Review each hierarchy's child-to-parent functional dependencies before deploying it.

Level-specific order_by_dimension is not part of the canonical contract. Put member ordering on the applicable value field with Field.order_by; this lets hierarchy consumers, standalone fields, and BI exporters use one definition.

levels:
  - name: city
    key_dimensions: [city_id]
    value_dimension: city_name
    secondary_dimensions: [state_name, country_name]

  - name: country
    key_dimensions: [country_id]
    value_dimension: country_name

hierarchies:
  - name: geography
    levels: [country, city]

6. Dataset unique-key contracts

Dataset unique keys describe row identity and the guarantees on which grain-aware aggregation and join planning depend. They are candidate-key contracts, not declarations of a table's physical relational PRIMARY KEY. A dataset may therefore declare several non-null unique keys while selecting one with grain_key as its semantic grain.

A key declaration asserts what is true of the rowset after filter has been applied, regardless of whether the source database physically enforces the constraint.

6.1 Key properties

Each entry in unique_keys has the following properties:

grain_key is a dataset property rather than a property of each key. It references one local non-null unique key and prevents the contradictory state in which several keys are marked as the default grain.

6.2 Non-null unique keys

With nulls_allowed: false, the contract covers every source row:

• every component is non-null in every row;
• no two rows have the same complete key tuple;
• the key may be selected by grain_key;
• the key may be referenced by from_key or to.key.

unique_keys:
  - name: order_line_key
    columns: [order_id, line_number]
    nulls_allowed: false
grain_key: order_line_key

This declaration is valid only if both components are populated for every row, and every (order_id, line_number) tuple occurs once. It does not claim that the source database declares those columns as its physical primary key.

6.3 Nullable unique keys

With nulls_allowed: true, only rows whose key components are all non-null participate in the uniqueness guarantee. A row containing any null component is permitted but is outside the key's identifiable domain. Multiple excluded rows may contain the same null pattern.

For a single-column alternate identifier:

unique_keys:
  - name: email_key
    columns: [email_address]
    nulls_allowed: true

For a composite alternate identifier:

unique_keys:
  - name: external_reference_key
    columns: [external_system, external_id]
    nulls_allowed: true

Conceptually, validation filters to rows where every key component IS NOT NULL, then checks the populated tuples for duplicates. Semantic Hub does not use null-safe equality to turn excluded tuples into identifiers.

Nullable unique keys cannot be selected by grain_key, from_key, or to.key.

6.4 Foreign keys and optional relationships

Foreign-key roles are declared by relationship columns such as joins[].from_columns; they are not entries in unique_keys merely because their values refer to another dataset. A nullable foreign key means that the relationship is absent or unknown for that source row. Standard equality joins leave such rows unmatched.

joins:
  - name: ship_date
    from_columns: [ship_date]
    to:
      dataset: calendar
      key: calendar_date_key
    referential_integrity: NOT_GUARANTEED

ship_date may be null on an unshipped order. The target calendar_date_key must reference a unique_keys entry with nulls_allowed: false. referential_integrity: ALL_FROM_ROWS_MATCH means that every fully populated source tuple matches a target row; it does not require nullable foreign-key tuples to be populated.

6.5 Keys with both unique and foreign roles

The same columns may be both a non-null unique key and a foreign key, as in a one-to-one extension dataset. The two roles remain independent: unique_keys asserts non-null uniqueness, while the join asserts the referenced dataset and relationship behavior.

unique_keys:
  - name: employee_profile_key
    columns: [employee_id]
    nulls_allowed: false
grain_key: employee_profile_key

joins:
  - name: employee
    join_type: ONE_TO_ONE
    from_columns: [employee_id]
    from_key: employee_profile_key
    to:
      dataset: employees
      key: employee_key

A nullable unique key may use columns that also act as an optional foreign key, but nulls_allowed: true makes that key ineligible as the unique endpoint of a Semantic Hub relationship.

6.6 Grain and default resolution

A root dataset may omit both unique_keys and grain_key, including when it defines metrics and has outgoing MANY_TO_ONE joins. No row identity is inferred or synthesized for such a dataset. Keys remain required on the unique side of relationships: the target of MANY_TO_ONE, both sides of ONE_TO_ONE, and the source of ONE_TO_MANY. Operations that require row identity, such as fanout protection or key-based deduplication, must reject a missing suitable key. This follows the optional logical-table key convention in Snowflake Semantic Views. For declared keys, Semantic Hub resolves grain as follows:

1. When exactly one unique key is declared, grain_key may be omitted and that key is selected automatically.
2. When that sole key also omits name, its effective name is default.
3. When two or more keys are declared, name each key explicitly. You may omit grain_key; a key named default will be selected. Without either an explicit selection or a default key, the dataset has no selected grain.
4. grain_key must resolve to a local key with nulls_allowed: false.
5. A join that omits to.key uses the target dataset's resolved grain key.

The common case is intentionally concise:

unique_keys:
  - columns: [customer_id]

Its canonical form is:

unique_keys:
  - name: default
    columns: [customer_id]
    nulls_allowed: false
grain_key: default

Complex datasets are explicit:

unique_keys:
  - name: surrogate
    columns: [customer_sk]
  - name: natural
    columns: [tenant_id, customer_id]
grain_key: surrogate

An omitted to.key uses the target's selected grain key: the explicit grain_key, otherwise its sole key, otherwise the key named default. An explicit unresolved key does not fall back. For MANY_TO_ONE and ONE_TO_ONE, provide source from_columns; target columns are taken from the selected key, so do not repeat them in to.columns. A ONE_TO_MANY join still requires target foreign-key to.columns. Publication materializes resolved key names so later changes cannot silently change an existing join.

Grain is evaluated after filter, when present. A filtered rowset may therefore satisfy a key contract even when the underlying unfiltered table does not, but the filter is part of that contract and cannot be removed at query time.

6.7 Relationship eligibility

Every key selected by grain_key, from_key, or to.key must resolve to a key with nulls_allowed: false. Standard equality joins cannot address null key tuples, and a join's unique side must identify participating rows deterministically. The to.columns of a ONE_TO_MANY edge are foreign-key columns rather than a unique key and therefore are not declared through unique_keys unless they independently satisfy a uniqueness contract.

A nullable unique key may be exposed as descriptive metadata or used for source-quality validation, but it cannot establish cardinality, authorize fanout-sensitive optimization, or replace the default grain.

6.8 Validation

JSON Schema validates property shape, requires explicit names for multi-key datasets, and permits the single-key shorthand. grain_key is optional; joins and operations that need row identity still require a suitable key. The catalog, publication, and deployment validators must additionally enforce cross-object and data-dependent rules:

1. Metrics alone do not require a unique key. Every relationship unique endpoint and every operation requiring row identity must have a suitable resolved key with nulls_allowed: false.
2. A single unnamed key receives the effective name default; an explicit key named default and an unnamed key cannot coexist.
3. Every multi-key dataset has unique explicit key names. An explicit grain_key must resolve locally; otherwise the sole key or a key named default selects grain.
4. For nulls_allowed: false, every component is non-null, and every complete tuple is unique.
5. For nulls_allowed: true, fully populated tuples are unique, and rows containing any null component are excluded from the duplicate test.
6. Every join key reference resolves to a key with nulls_allowed: false in the appropriate dataset.
7. For ONE_TO_ONE and MANY_TO_ONE, from_columns and the target key columns have the same arity, positional correspondence, and compatible types.
8. For ONE_TO_MANY, the source from_key columns and target to.columns have the same arity, positional correspondence, and compatible types.
9. Every referenced column is exposed by the applicable dataset's table or query result.
10. Every association dataset used in a many-to-many path has a resolved grain key with nulls_allowed: false.
11. Join names are unique within their owning dataset; path exclusions use join names so role-playing joins remain unambiguous.
12. Cardinality and direction-specific referential-integrity claims agree with observed source data when data validation is enabled.

Validators should report the dataset, key name, offending columns, violated rule, and representative duplicate or null tuples without exposing sensitive values, in accordance with governance policy.

7. Dataset filters

The optional dataset filter is an intrinsic row predicate: it defines which source rows belong to the dataset before fields, keys, relationships, or metrics consume those rows. It is part of the dataset contract and cannot be removed by a query. This differs from a query where filter, which selects rows for one query, and from an aggregate or metric-input filter, which changes only the population of that metric or input.

Dataset filters use the explicit FilterExpression schema definition. They are authored as strings in the portable GENERIC Semantic Hub ANSI-SQL-like expression language and must bind and type-check to Boolean in the SDK. This guide sometimes calls such an expression canonical because it is the authoritative, target-independent definition from which target SQL is generated. There is no separate "canonical filter" category, so this section uses the more specific heading Dataset filters.

filter: "status = 'COMPLETED'"

List and range predicates use SQL-like syntax:

filter: "customer.region IN ('AMERICAS', 'EMEA')"

Boolean expressions use parentheses to make grouping explicit:

filter: >-
  status <> 'CANCELLED'
  AND (customer.segment = 'ENTERPRISE'
       OR customer.segment = 'MID_MARKET')

Supported operations include equality and ordering comparisons, IN, NOT IN, BETWEEN, LIKE, Boolean AND/OR/NOT, IS NULL/IS NOT NULL, and typed CASE expressions. See Expression language for the language basics.

7.1 Binding scope

A dataset filter is anchored at the filtered dataset. An identifier may terminate at either an authored row-level field or a verified column returned by the applicable dataset's table or query. It may resolve locally or through an explicit authored join path:

The terminal source column must be present in verified source metadata. The terminal field must resolve to an authored field object and may use its row-level expression. Metrics, aggregate operations, window results, and other post-row-phase values are invalid in a dataset filter.

Every non-terminal path component is an authored join name. A resolver must not substitute a dataset name, infer a shortest path, or fall back to an unrelated same-name object. The binder retains the resolved source-column or field identity and complete relationship path; target SQL generation does not resolve the authored text again.

Direct source-column access is useful for technical predicates that do not justify a public business field. Across a relationship it also couples the filter to the related dataset's physical contract, so authoring tools should prefer an authored field when one represents the intended business value. That preference is guidance rather than a different binding rule.

7.2 Scalar relationship paths

Every relationship traversed by a dataset filter must yield at most one related row for each row of the filtered dataset. ONE_TO_ONE and MANY_TO_ONE edges are scalar from the containing dataset and are therefore eligible. ONE_TO_MANY, many-to-many, cross, or otherwise multivalued traversal is invalid in an ordinary FilterExpression, even when the terminal name resolves successfully.

Optional scalar relationships affect nullability. When no related row exists, a related value is NULL; normal three-valued Boolean logic applies. For example, customer.region = 'EMEA' evaluates to unknown and excludes the row when customer is absent, while an author can test that case explicitly with customer.region IS NULL or an appropriate COALESCE expression.

7.3 Why multivalued predicates require explicit semantics

Suppose orders has a ONE_TO_MANY join named items. The following predicate is invalid:

filter: "items.returned = TRUE"

One order can have several item values, so the expression does not produce one unambiguous Boolean value per order row. It could mean any of the following:

• keep the order when any item was returned;
• keep the order only when every item was returned; or
• filter the item rows before a later item-grain calculation.

Those meanings have different results. Lowering the expression to an ordinary SQL join silently chooses none of them as a semantic contract and can multiply an order row once per matching item. That fanout can overcount measures such as order revenue. Adding an unmodeled DISTINCT is not a valid repair: it is applied at a chosen SQL projection grain, may collapse legitimately distinct semantic rows or equal measure inputs, and still does not state whether the intended quantifier was any, every, or something else.

The current expression language therefore rejects multivalued paths in RowExpression and FilterExpression. If relational predicates such as EXISTS or NOT EXISTS are added in a future language version, they must be explicit constructs with a defined anchor, correlation path, and quantifier. A compiler can then plan them as fanout-safe semi-joins or anti-joins without changing the dataset's atomic grain. Until such a construct is part of the schema and expression language, authors must model the many-side condition through an explicit fanout-safe dataset, field, or metric rather than relying on implicit join behavior.

8. Metrics

Semantic Hub has seven metric types.

Every metric also inherits the common metric properties described in section 8.

8.1 Metric references and grain

A MetricInput selects exactly one of metric_ref or inline_metric. A metric_ref may reference any named metric in the same dataset or a different dataset; it is not restricted to AggregateMetric. The referenced metric may have the same grain or a different grain from the metric being defined. A qualified reference such as orders.total_revenue selects the referenced metric; qualification does not require equal grain and does not by itself prove that the metrics can be combined.

inline_metric defines an anonymous aggregate scoped to that input. Its object omits name and metric_type: the containing property establishes its identity and aggregate kind. It contains aggregation, the canonical GENERIC expression required by every aggregation except COUNT_ROWS, optional aggregation_parameters for parameterized functions, and optional aggregate null_handling. AggregateDefinition is the shared schema contract for these properties and their function-specific constraints; both InlineMetric and AggregateMetric reference it. An inline metric cannot contain presentation metadata, dialect expressions, overrides, grain settings, or another metric input. The containing MetricInput.filter and offset apply to either a named or inline input.

The same rules apply to the metric leaves in a DERIVED expression and to metric references used for window ordering. The field name differs because those references are nodes in an expression or sort definition, but their resolution and grain semantics are identical.

It is useful to distinguish three grains:

For a composite metric, the containing dataset supplies the unqualified-reference namespace and applicable defaults. It does not coerce every referenced metric to the containing dataset's atomic grain. Each input keeps its own semantic definition and is aggregated independently before combination.

A different-grain reference is legal when all of the following are true:

1. The reference and any required relationship paths resolve unambiguously.
2. The referenced metric can be evaluated according to its own grain, filters, time context, and additivity behavior.
3. The consuming metric type permits the referenced metric's evaluation stage.
4. The compiler can derive an output alignment grain for the requested dimensions.
5. Any rollup or broadcast from the referenced grain is explicitly meaningful and cannot introduce fanout or double counting.

Common cases are:

The following same-dataset ratio intentionally combines query-grain revenue with a fixed regional total:

metrics:
  - name: regional_revenue
    metric_type: AGGREGATE
    expression: revenue
    aggregation: SUM
    grain:
      mode: FIXED
      include_dimensions: [customer.region]

  - name: product_share_of_region
    metric_type: RATIO
    numerator:
      metric_ref: total_revenue
    denominator:
      metric_ref: regional_revenue

For a query grouped by customer.region and product.category, the numerator is evaluated by region and category. The denominator is evaluated once per region and safely repeated across categories for division. That repeated denominator remains a non-additive intermediate value and must not later be summed.

The cross-dataset revenue_per_customer example in section 8.4 is also different-grain: orders are atomic by order, customers are atomic by customer, and both inputs can be aggregated independently by a shared dimension such as customer region before division.

Compatibility can depend on the query. A ratio between orders.total_revenue and support_tickets.average_resolution_hours may be valid as a grand-total scalar calculation. A request to group it by product.category must fail if the support-ticket input has no valid product path and does not explicitly define a fixed grain that permits intentional broadcast. The MCP server should therefore report the dimensions that are safe for each metric, and SQL generation should reject unsupported groupings with the incompatible reference and dimension in the diagnostic.

Definition-time validation resolves dependencies, declared grain dimensions, time dimensions, entity keys, and evaluation stages. Query-time validation additionally proves that the requested grouping and filtering dimensions can be aligned for every required input.

8.2 AGGREGATE

An aggregate metric applies one canonical aggregation to a RowExpression at the containing dataset's atomic grain. Referencing a single field uses the same property and syntax as a formula. Its expression, aggregation, aggregation_parameters, and aggregate-specific validation come from the same AggregateDefinition used by an inline metric.

metrics:
  - name: total_revenue
    metric_type: AGGREGATE
    expression: revenue
    aggregation: SUM
    result_type:
      data_type: DECIMAL
      precision: 18
      scale: 2
      currency: USD
    presentation:
      format: "$#,##0.00"

COUNT_ROWS deliberately has no source input:

  - name: order_count
    metric_type: AGGREGATE
    aggregation: COUNT_ROWS
    result_type:
      data_type: INTEGER
      unit: orders

A filtered aggregate uses the same type:

  - name: completed_revenue
    metric_type: AGGREGATE
    expression: revenue
    aggregation: SUM
    filter: "status = 'COMPLETED'"

AggregateMetric.filter is intrinsic to that metric. The compiler conditionally supplies values to the aggregate with a function-specific CASE; it does not place the predicate in the query-level WHERE, where it would incorrectly remove rows needed by sibling metrics. For example, the metric above compiles conceptually as SUM(CASE WHEN status = 'COMPLETED' THEN revenue ELSE NULL END). Counts, distinct aggregates, ordered-set functions, and two-input statistics use equivalent function-specific conditional inputs, with both statistical inputs conditioned by the same predicate.

Formulas use that same canonical expression property:

metrics:
  - name: total_margin
    metric_type: AGGREGATE
    expression: COALESCE(revenue, 0) - COALESCE(cost, 0)
    aggregation: SUM

Every non-COUNT_ROWS aggregate has exactly one canonical expression input in the default GENERIC language. COUNT_ROWS has no input expression. Aggregate metrics and their aggregation parameters cannot define dialect_expressions; when canonical compilation is impossible or a target compiler is defective, use the metric-level override as the explicit last resort.

Aggregation functions

The schema supports additive, distinct, statistical, ordered-set, list, correlation, covariance, and regression aggregations. Common functions include SUM, AVERAGE, MIN, MAX, COUNT_ROWS, COUNT_NON_NULL, COUNT_DISTINCT, APPROX_COUNT_DISTINCT, MEDIAN, MODE, and ANY_VALUE. The structural names are deliberately explicit; the GENERIC parser accepts standard spellings such as AVG(expr), COUNT(expr), COUNT(*), STDDEV(expr), and VARIANCE(expr) and normalizes them to AVERAGE, COUNT_NON_NULL, COUNT_ROWS, STDDEV_SAMP, and VAR_SAMP respectively.

Percentiles require a canonical percentile parameter:

  - name: order_value_p90
    metric_type: AGGREGATE
    expression: revenue
    aggregation: PERCENTILE_CONT
    aggregation_parameters:
      percentile: 0.9

Here expression: revenue is the ordered value. A SQL target renders the definition as PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY revenue). The GENERIC parser also accepts that standard ordered-set syntax when importing or normalizing an aggregation expression.

SUM_DISTINCT and AVERAGE_DISTINCT use SQL value-distinct semantics. Equal input values contribute once regardless of which source rows produced them:

  - name: sum_of_distinct_shipping_values
    metric_type: AGGREGATE
    expression: shipping_amount
    aggregation: SUM_DISTINCT

Key-based deduplication is a different operation. Use deduplicate_by_dimensions with ordinary SUM or AVERAGE when dimensions identify one logical input:

  - name: shipping_revenue
    metric_type: AGGREGATE
    expression: shipping_amount
    aggregation: SUM
    aggregation_parameters:
      deduplicate_by_dimensions: [order_id]

This protects repeated order-level values in a denormalized order-line dataset without incorrectly collapsing two different orders that happen to have the same shipping amount. The compiler must validate that one deduplication key does not map to conflicting input values. Existing models using SUM_DISTINCT or AVERAGE_DISTINCT with distinct_dimensions migrate to SUM or AVERAGE with deduplicate_by_dimensions.

The portability and decomposability classifications for every aggregation are documented in OSI (Ossie) and Semantic Hub mapping. OSI-required functions form the portable core, approximate functions are recommended, and the remaining functions are Semantic Hub extensions. Cache planning uses the decomposability category—distributive, algebraic, holistic, or sketch-based—together with grain, filters, ordering, and target capabilities; AggregationBehavior alone is not a materialization-safety classification.

Two-input statistics use second_field_ref or the canonical GENERIC aggregation_parameters.expression for their second input:

  - name: price_quantity_correlation
    metric_type: AGGREGATE
    expression: unit_price
    aggregation: CORRELATION
    aggregation_parameters:
      expression: COALESCE(quantity, 0) * package_size

8.3 DERIVED

A derived metric uses an authored AggregationExpression string over metrics, scalar literals, and optional explicit aggregate calls over row fields. The string is the persisted source; Semantic Hub parses and binds it into a typed canonical intermediate representation before target compilation.

  - name: gross_profit
    metric_type: DERIVED
    expression: "total_revenue - total_cost"

Parentheses make nested evaluation explicit and portable:

  - name: adjusted_profit
    metric_type: DERIVED
    expression: "gross_profit * 0.95"

Supported operations include arithmetic, power, minimum/maximum, typed CASE, COALESCE, NULLIF, absolute value, and negation. Metric references may be qualified, such as returns.total_return_amount. Semantic validation ensures that every direct reference is a metric and that row-level fields occur only beneath an explicit aggregate call.

Use canonical DIVIDE(numerator, denominator[, alternate]) when an authored expression needs division-specific fallback behavior. The two-argument form returns NULL for a zero denominator; the optional third argument supplies the zero-denominator result. This is operator-local and remains unambiguous when one expression contains several divisions. A derived metric's null_handling.null_result applies only to the final expression result.

A row field may occur only inside an explicit aggregate call:

  - name: adjusted_margin
    metric_type: DERIVED
    expression: existing_margin + SUM(line_amount)

The compiler normalizes SUM(line_amount) into a grain-anchored aggregate dependency before metric evaluation. It validates the row input exactly like an aggregate metric, evaluates it independently, aligns it with existing_margin, and only then performs the addition. A bare existing_margin + line_amount is invalid because it mixes aggregation and row phases without an aggregation boundary.

8.4 RATIO

A ratio names its numerator and denominator. The compiler evaluates both inputs at their native dataset grains and joins their aggregated results on compatible requested dimensions before division.

  - name: revenue_per_customer
    metric_type: RATIO
    numerator:
      metric_ref: orders.total_revenue
    denominator:
      metric_ref: customers.active_customer_count
    null_handling:
      divide_by_zero: "NULL"

Filters and time offsets may be applied independently to either input:

  - name: completed_revenue_share
    metric_type: RATIO
    numerator:
      metric_ref: total_revenue
      filter: "status = 'COMPLETED'"
    denominator:
      metric_ref: total_revenue

The default divide-by-zero result is NULL, not zero. Use ZERO, ERROR, or ALTERNATE only when that behavior is part of the business definition.

An importer can preserve embedded source measures as anonymous canonical inputs. For example, Power BI DIVIDE(SUM(a), SUM(b)) can be represented as:

  - name: a_per_b
    metric_type: RATIO
    numerator:
      inline_metric:
        expression: a
        aggregation: SUM
    denominator:
      inline_metric:
        expression: b
        aggregation: SUM
    imported_expression:
      dialect: POWER_BI
      text: DIVIDE(SUM(a), SUM(b))

The compiler normalizes each inline_metric into an anonymous grain-anchored dependency. It receives a stable internal identity for diagnostics, lineage, common-subexpression detection, and automatic cache planning, but it does not enter the authored metric namespace. Promote it to a named metric when it must be reused, governed, referenced directly, or selected explicitly in a cache definition.

8.5 CUMULATIVE

Cumulative metrics describe aggregation over a time or row frame. They are distinct from single-row window functions.

A running total is:

  - name: cumulative_revenue
    metric_type: CUMULATIVE
    base_metric:
      metric_ref: total_revenue
    time_dimension: order_date
    frame:
      frame_type: ALL
    accumulation: SUM

A rolling seven-day distinct-user metric should re-evaluate its base metric across the expanded event interval rather than sum daily distinct counts:

  - name: weekly_active_customers
    metric_type: CUMULATIVE
    base_metric:
      metric_ref: active_customer_count
    time_dimension: activity_date
    frame:
      frame_type: TRAILING
      value: 7
      unit: DAY
    accumulation: REEVALUATE_BASE

Period-to-date metrics use TO_DATE:

  - name: revenue_ytd
    metric_type: CUMULATIVE
    base_metric:
      metric_ref: total_revenue
    time_dimension: order_date
    frame:
      frame_type: TO_DATE
      grain_to_date: YEAR
    period_aggregation: LAST_VALUE

period_aggregation controls how a cumulative series is shown at a coarser requested time grain.

8.6 WINDOW

Window metrics return one analytic result per output row. order_by is required to make results deterministic.

  - name: product_revenue_rank
    metric_type: WINDOW
    function: DENSE_RANK
    partition_by_dimensions: [order_date.year]
    order_by:
      - metric: total_revenue
        direction: DESC

Lag and lead return a base metric from another row:

  - name: previous_month_revenue
    metric_type: WINDOW
    function: LAG
    base_metric:
      metric_ref: total_revenue
    partition_by_dimensions: [customer.region]
    order_by:
      - field: order_date.month
        direction: ASC
    offset: 1
    default_value: 0

Framed running totals over grouped query rows use function: SUM with an optional cumulative row frame (frame_type: ALL, include_current: true):

  - name: running_product_revenue
    metric_type: WINDOW
    function: SUM
    base_metric:
      metric_ref: total_revenue
    partition_by_dimensions: [customer.region]
    order_by:
      - field: product.category
        direction: ASC
    frame:
      frame_type: ALL

NTILE requires buckets. LAG, LEAD, FIRST_VALUE, LAST_VALUE, and SUM require base_metric (SUM requires a numeric base metric). frame is permitted only when function is SUM; when omitted on SUM, it defaults to frame_type: ALL and include_current: true (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Use CUMULATIVE for calendar or time-series accumulation requiring a temporal time_dimension, and WINDOW with function: SUM for row-framed running totals ordered by arbitrary dimensions or metrics over grouped query rows.

8.7 PERIOD_OVER_PERIOD

Period-over-period metrics align a metric with another calendar period and perform one canonical comparison.

Learn the properties step by step in PERIOD_OVER_PERIOD metrics with TPC-H, with illustrated examples and logical mappings for SQL, Power BI, and Tableau.

  - name: revenue_yoy_growth
    metric_type: PERIOD_OVER_PERIOD
    base_metric:
      metric_ref: total_revenue
    time_dimension: order_date
    offset:
      value: -1
      unit: YEAR
      alignment: CALENDAR
    comparison: PERCENT_CHANGE
    comparison_grain: MONTH
    complete_periods_only: true

Comparison modes are:

Offsets cannot be zero. Negative values look backward; positive values look forward.

complete_periods_only requests proof that both periods are complete. Current period-comparison routes diagnose this option when they cannot provide that proof; see the target support section.

8.8 CONVERSION

Conversion metrics match base events to conversion events for the same entity within a time window. They must not be modeled as ordinary ratios because event matching and attribution occur before aggregation.

  - name: visit_to_purchase_rate
    metric_type: CONVERSION
    base_metric:
      metric_ref: visits.visit_count
    conversion_metric:
      metric_ref: purchases.purchase_count
    entity_dimensions: [user_id]
    base_time_dimension: visits.occurred_at
    conversion_time_dimension: purchases.occurred_at
    window:
      value: 7
      unit: DAY
    calculation: CONVERSION_RATE
    attribution: NEAREST
    constant_dimensions: [marketing_campaign]

constant_dimensions requires the matched events to share the same dimension value. Attribution can select the first, last, nearest, or any qualifying base event.

9. Common metric properties

9.1 Result type and display format

result_type defines the portable data contract; presentation.format defines display.

result_type:
  data_type: DECIMAL
  precision: 18
  scale: 4
  unit: percent
presentation:
  format: "0.00%"

Supported result types are decimal, integer, float, Boolean, text, date, datetime, and time. nullable applies to every result type and defaults to true. Decimal metrics require precision and scale; these properties are prohibited for every other result type, and semantic validation must ensure that scale does not exceed precision. Numeric results (DECIMAL, INTEGER, and FLOAT) may declare a non-empty business unit. Numeric monetary results may additionally declare an ISO 4217 currency. unit and currency are prohibited for Boolean, text, date, datetime, and time results.

Canonical display format

presentation.format is an Excel custom format string. Excel syntax is the common denominator of the supported BI targets: Power BI formatString, Analysis Services, and Looker value_format use it verbatim, Tableau custom number formats use the same section and placeholder model with different date tokens, and Databricks metric-view formats map onto a structured subset. Snowflake semantic views and Oracle analytic views carry no display format, so nothing is exported to them and nothing is imported from them.

Semantic Hub accepts only the portable subset below. Common.json#/$defs/PresentationFormat enforces it with a regular expression so a catalog fails schema validation rather than exporting a format that one target cannot render.

Excluded from the canonical subset because most targets cannot render them: color names such as [Red], conditional sections such as [>100], and locale tags such as [$-409]. An importer that meets one of these drops the non-portable element, translates the remainder into format, and reports the simplification; when no portable translation exists it omits format and reports the source format as unsupported. There is no per-target format property: one canonical value serves every target.

The regular expression is a lexical contract. It does not decide whether m denotes month or minute, and it does not check that a numeric pattern is applied to a numeric value; the SDK validates format applicability against the field data_type or the metric result_type, and the target renderer applies its own conventions. Currency and unit are data, not characters: declare them in result_type.currency and result_type.unit so exporters can emit them structurally, and use the format string only for the visual pattern.

- name: gross_margin_pct
  metric_type: RATIO
  numerator: { metric_ref: gross_profit }
  denominator: { metric_ref: total_revenue }
  result_type: { data_type: DECIMAL, precision: 9, scale: 4, unit: percent }
  presentation:
    format: "0.0%;(0.0%);-"

Exporters translate format to the target syntax. An exporter must report a format it cannot translate rather than emit a different one silently.

9.2 Null handling

Null handling is defined by the state being handled, not as one unrestricted policy object for every metric type.

An aggregate can distinguish empty input from a non-empty set whose values are all NULL:

null_handling:
  empty_result: AUTO
  null_result: "NULL"

AUTO preserves function-specific empty behavior, such as zero for COUNT_ROWS and SQL-compatible NULL for SUM. A literal fallback must type-check against result_type; ZERO is valid only for numeric results. Do not use a zero fallback merely to make charts look complete—a zero must have the intended business meaning.

Quote the reserved "NULL" policy token in YAML. An unquoted NULL may be parsed as the YAML null scalar rather than the policy name.

A ratio has no independent empty input: its numerator and denominator metrics establish their own empty results. It adds structural division handling:

null_handling:
  null_result: "NULL"
  divide_by_zero: ALTERNATE
  alternate_value: 0

alternate_value is valid only with divide_by_zero: ALTERNATE. A NULL denominator follows normal null propagation and then null_result; it is not a zero denominator.

Policies execute at defined semantic boundaries:

1. Evaluate row expressions and metric dependencies using their own policies.
2. Apply intrinsic filters and perform the aggregate, frame, event, comparison, or window calculation.
3. Apply structural divide_by_zero at the division operation.
4. If there were no contributing rows, apply empty_result; this state does not fall through to null_result.
5. Otherwise, apply null_result to the final calculated value.

AggregateMetric.filter compiles excluded rows to ELSE NULL inside the aggregate input. Those intermediate NULLs are never replaced by null_result; doing so before aggregation would change COUNT, AVERAGE, distinct, and statistical semantics. To distinguish empty input, all-NULL input, missing aligned groups, and calculated NULL reliably, a planner may need to carry row-presence or count state alongside a partial metric value.

For migration from the former common superset, retain null_result on any named metric; retain empty_result only on aggregate, cumulative, or conversion metrics; and retain divide_by_zero only on structural ratio-like metrics. Rewrite division-specific behavior on a derived or aggregate expression with DIVIDE. Move type-specific policies out of metric_defaults and onto the affected metrics.

9.3 Grain context

grain replaces a separate LOD metric type. It can be combined with compatible aggregate, ratio, or derived calculations.

A fixed regional value is:

grain:
  mode: FIXED
  include_dimensions: [customer.region]
  rollup_aggregation: RECOMPUTE

An empty fixed grain is a grand total:

grain:
  mode: FIXED

A relative grain removes or adds dimensions compared with the query:

grain:
  mode: RELATIVE
  exclude_dimensions: [product.subcategory]

keep_only_dimensions defines an adaptive fixed grain equal to the intersection between the listed dimensions and the query dimensions. It is valid only with FIXED and cannot be combined with include_dimensions. With RELATIVE, include_dimensions and exclude_dimensions may be used together to transform the inherited query grain.

rollup_aggregation controls what happens whenever an evaluation grain contains dimensions absent from a coarser query grain. This can occur with either mode—for example, a RELATIVE context can add a dimension—and RECOMPUTE is the safe default for non-additive metrics.

9.4 Filter context

An intrinsic filter defines which source rows belong to a metric. filter_context instead controls which filters supplied by the query are inherited.

Ignore all query filters:

filter_context:
  mode: FIXED

Ignore only product-color filters:

filter_context:
  mode: RELATIVE
  exclude_dimensions: [product.color]

Retain only time and region filters:

filter_context:
  mode: FIXED
  keep_only_dimensions: [order_date, customer.region]

A static include filter may be added after the inheritance policy is applied.

keep_only_dimensions is valid only with FIXED; exclude_dimensions is valid only with RELATIVE. Neither mode accepts both properties. A FIXED context without keep_only_dimensions inherits no query filters, while a RELATIVE context without exclude_dimensions inherits every query filter.

9.5 Time context

time_context:
  time_dimension: order_date
  calendar: corporate_fiscal
  time_zone: America/Los_Angeles
  fill_missing_periods: ZERO
  supported_granularities: [DAY, WEEK, MONTH, QUARTER, YEAR]

Metric settings override dataset settings, which override semantic-model defaults.

calendar is an authored-name reference to an entry in the containing semantic model's time_settings.calendars collection, not a field or physical table name. It selects the week, fiscal-period, time-spine, truncation, offset, period-to-date, and period-over-period alignment rules used by the metric. When omitted, time_settings.default_calendar is inherited. Authoring tools should populate this value from the model's calendar collection, and semantic validation must reject an unresolved name.

9.6 Semi-additive behavior

Semi-additivity is part of an AGGREGATE metric, not a separate metric type.

  - name: ending_account_balance
    metric_type: AGGREGATE
    expression: balance
    aggregation: SUM
    aggregation_behavior:
      additivity: SEMI_ADDITIVE
      non_additive_dimensions:
        - dimension: snapshot_date
          selection: LAST_NON_EMPTY
          group_by_dimensions: [account_id]
          order_by:
            - field: snapshot_date
              direction: ASC

This means: select the last non-empty balance for each account across time, then sum the selected account balances across other dimensions. FIRST_VALUE and LAST_VALUE include empty values; FIRST_NON_EMPTY and LAST_NON_EMPTY skip them.

9.7 Dialect overrides and imported expressions

Use override only as a last resort when the required metric cannot be represented canonically or when a target compiler has a known defect:

override:
  reason: Target database requires a proprietary sketch-union function
  dialect_expressions:
    - dialect: SNOWFLAKE
      text: HLL_ESTIMATE(HLL_COMBINE(customer_hll))

MetricOverride.dialect_expressions is the only place a metric may contain target-dialect expression text. It contains no canonical GENERIC expression and replaces generated SQL only for the named targets; the canonical structural metric and GENERIC expressions remain authoritative for dependencies, grain, type, and behavior.

imported_expression preserves the original third-party text for history and future forward-engineering. It never overrides the canonical runtime definition and is not selected as a target export override.

10. Canonical expression evaluation

Metric expressions form a dependency DAG. Semantic Hub must reject cycles and validate references before SQL generation.

Evaluation proceeds by stage:

1. Resolve source datasets, atomic grains, and relationship paths.
2. Apply source and intrinsic metric filters.
3. Aggregate each base input at its native grain and requested compatible dimensions.
4. Apply grain and filter-context isolation using window operations or separate CTEs.
5. Combine metric inputs from multiple datasets on common query dimensions.
6. Evaluate derived and ratio expressions.
7. Apply cumulative, period-over-period, conversion, and row-window stages as required.
8. Apply null policy, result type, and display metadata.

Metrics from later stages must not be nested in a way that skips their evaluation wrapper. The compiler should report the exact dependency edge and evaluation-stage conflict.

11. Grain-safe query planning

Dataset scope anchors every AGGREGATE metric to the dataset's resolved grain key. Join cardinality determines when the compiler may join before aggregation and when it must isolate an input dataset's grain.

For a ratio whose numerator and denominator come from different datasets, Semantic Hub should:

1. Determine the native dataset and atomic grain of each input.
2. Resolve requested dimensions independently for each input.
3. Aggregate each input in a separate CTE.
4. Join the CTEs only on dimensions common to both results.
5. Apply the ratio and null policy after the join.

The same principle applies to derived metrics spanning several metric-bearing datasets. A direct raw-row flattening between those datasets is never inferred merely because a join path exists.

Role-playing dimensions use explicit path segments. If more than one valid path exists and the reference is not qualified, validation must fail with an ambiguity error rather than choosing an arbitrary shortest path.

12. Semantic models

A semantic model selects one or more root datasets and everything reachable through their relationship graphs unless explicitly excluded.

name: commerce
root_datasets:
  - dataset: orders
    exclude_join_paths: [customer.sensitive_segment]
  - dataset: returns
    exclude_metrics: [internal_return_score]

Reachability follows only explicit outgoing joins edges. For each root, Semantic Hub adds the edge's to.dataset, then recursively follows that dataset's outgoing joins. ONE_TO_ONE, MANY_TO_ONE, and ONE_TO_MANY participate in scope traversal in exactly the same way; cardinality affects query planning, not whether the target dataset is in scope.

Each exclude_join_paths value is relative to its root and consists of dot-separated join names. Excluding a path removes the target reached by that edge and the complete descendant subtree reached through that path. It does not remove the same dataset when that dataset is reachable through another non-excluded path. Exclusions identify paths rather than dataset names because role-playing joins can reach the same dataset more than once.

An association table is therefore visible to traversal as an ordinary dataset node. For the bridge example in section 5.3, an orders root contributes order_promotions through promotion_links and contributes promotions through promotion_links.promotion. There is no hidden bridge edge or second reachability algorithm.

Traversal must detect cycles and must not expand the same path state indefinitely. Dataset reachability establishes model scope only; the compiler must still prove that each requested metric/dimension combination is safe across every fanout edge.

12.1 Time settings and calendars

time_settings:
  default_time_zone: America/Los_Angeles
  default_calendar: corporate_fiscal
  fill_missing_periods: SKIP
  calendars:
    - name: corporate_fiscal
      calendar_type: FISCAL
      week_start: MONDAY
      fiscal_year_start_month: 2
      fiscal_year_start_day: 1
      time_spine_dataset: calendar
      time_spine_dimension: date

A time spine supplies dense periods for cumulative and PoP calculations and for explicit missing-period filling.

Calendar names must be unique within time_settings.calendars. Both default_calendar and every metric time_context.calendar must resolve to one of those named entries.

12.2 Validation policy

validation:
  unknown_grain: ERROR
  unknown_cardinality: ERROR
  validate_unique_keys: true
  validate_referential_integrity: true

ERROR is recommended for production models. WARN is intended only for early exploration and should be visible in generated artifacts and MCP responses.

12.3 Metric defaults

metric_defaults:
  result_type:
    data_type: DECIMAL
    precision: 18
    scale: 2
  null_handling:
    null_result: "NULL"

Only the universally applicable final null_result can be a model-wide default. Empty-input, missing-value, and division policies are authored on compatible metrics because their meaning depends on the calculation. Defaults merge by property: a metric-local null_result overrides the model default, while a local empty_result or division policy does not erase an inherited null_result.

12.4 Caches and deployments

Declare caches in root_datasets[].caches so their field and metric paths have a clear starting dataset. For example:

name: commerce
caching:
  schedule: "0 2 * * *"
root_datasets:
  - dataset: orders
    caches:
      - name: revenue_by_region
        dimensions: [customer.region]
        metrics: [total_revenue]

Here total_revenue belongs to orders, while customer.region follows the customer join from orders. The example assumes those objects exist in your catalog. References cannot select excluded metrics or traverse excluded branches. Different join roles retain their distinct meaning even when they reach the same dataset.

A root can have several caches, and cache names must be unique within that root. A cache's schedule overrides the shared caching.schedule; omitting both specifies no refresh schedule. When a query combines independent roots, each branch can use its own compatible cache before the aggregated results are combined.

To migrate an older model, move each entry from caching.caches to its owning root's caches and make its references relative to that root. Keep the shared schedule in caching.schedule. If a cache combines independent roots, separate its branches rather than assigning it an arbitrary root.

This syntax describes the accepted authoring contract; SDK and compiler support for the new placement is pending. It does not establish cache materialization or refresh support for a target.

Caches select named dimensions and metrics for materialization. Cache definitions must preserve metric grain, filter semantics, and non-additive behavior; a cache is not valid merely because it contains similarly named columns. Inline metrics cannot be selected by authored reference, but the cache planner may materialize their normalized aggregate states as dependencies of a selected named metric.

deployments configures generated database semantic objects, Power BI models, and Tableau data sources. Target generators should report unsupported canonical features rather than silently replacing them with different behavior.

13. Reference qualification

References use dot-separated paths:

Object names cannot contain dots. Validators must distinguish qualified dataset names from relationship paths based on the property being resolved.

Dataset source/query output columns may be named directly only inside expression text or properties whose schema is ColumnReference. Authored fields overlay the row-expression namespace and may intentionally override a same-name column binding; inside the overriding field's own expression, the self-name resolves the inherited column, while all other expressions resolve the authored field. Join names are ordinary authored names—including source—but cannot collide with an effective row-expression name. See Object naming for the complete namespace and shadowing algorithm.

For a structural FieldReference, every non-final path component must resolve to an authored join and the final component must resolve to an authored field object in the reached dataset. There is no fallback from a missing field object to a same-name physical column. An identifier inside RowExpression, AggregationExpression, or FilterExpression uses the same explicit join traversal but may terminate at either an authored field or a verified source/query output column. Resolvers do not substitute dataset names for missing joins or infer an unmodeled shortest path. A path used in row-level expression text must also be scalar from its anchor and therefore cannot cross a ONE_TO_MANY edge.

Qualification controls name resolution only. A qualified metric_ref may have the same grain as the consuming metric, while an unqualified reference in the same dataset may have a different grain because of its own grain, time, or semi-additive behavior. Apply the compatibility rules in section 8.1 in both cases.

14. Validation requirements

JSON Schema validates object shape. The Semantic Hub catalog validator must additionally enforce cross-object semantics:

• Names are unique in their applicable scopes.
• Verified source/query output columns populate each dataset's row-expression namespace as column bindings; they do not become authored field objects.
• Every structural FieldReference resolves to an authored field object. A same-name source/query output column is not a fallback target.
• An authored field may override only its same-name inherited column under the deterministic self-binding rule; duplicate authored fields and all other shadowing are invalid.
• Join names are unique and do not collide with effective row-expression names under the same case-insensitive semantic comparison used by reference lookup (target identifier allocation is separate).
• Every multi-key dataset names all keys; explicit grain selection is optional. A sole key or a key named default resolves grain deterministically. An operation requiring row identity must have a suitable non-null resolved key.
• Declared unique keys obey their nulls_allowed uniqueness contract.
• Every join key reference resolves to a key with nulls_allowed: false.
• To-one from_columns/target-key mappings and one-to-many from_key/to.columns mappings have compatible lengths and types.
• Relationship cardinality and referential-integrity claims pass validation queries when enabled.
• Every many-to-many business relationship is represented by an ordinary association dataset with a non-null grain and explicit ONE_TO_MANY then MANY_TO_ONE edges.
• Every path component resolves to the object kind required at that position; qualified references are unambiguous and no relationship path is inferred implicitly.
• RowExpression resolves only scalar authored fields or verified source columns, AggregationExpression resolves metrics and permits those row values only inside explicit aggregate calls, and FilterExpression follows row-expression binding rules and produces Boolean.
• Row-level expression paths use only edges that yield at most one terminal value per anchor row.
• Every non-COUNT_ROWS aggregate metric defines exactly one canonical GENERIC expression; COUNT_ROWS defines none. Direct metric and aggregation-parameter dialect_expressions are invalid, and target-specific metric text is allowed only inside a documented MetricOverride.
• Standard aggregate-call aliases normalize to one structural aggregation identity before dependency, lineage, and cache analysis.
• SUM_DISTINCT and AVERAGE_DISTINCT deduplicate values; key-based deduplicate_by_dimensions is valid only for SUM and AVERAGE, and every key must determine one non-conflicting input value.
• AggregateMetric.filter conditionally supplies that metric's aggregate inputs through function-specific CASE logic and cannot be promoted to a query-level WHERE when doing so would filter sibling metrics.
• null_result applies only to a final non-empty calculation; it cannot rewrite intermediate NULLs introduced by AggregateMetric.filter or ordinary expression propagation.
• empty_result, division handling, and missing-period policies occur only on compatible metric types. A fallback value type-checks against result_type, and alternate_value occurs only with divide_by_zero: ALTERNATE.
• Period-over-period division handling is valid only for RATIO and PERCENT_CHANGE; conversion division handling is valid only for CONVERSION_RATE.
• Every MetricInput defines exactly one of metric_ref or inline_metric. A reference may target any named metric; an inline metric is anonymous, aggregate-only, and subject to the same aggregate-parameter rules as AggregateMetric.
• Metric dependency graphs are acyclic.
• Metric types are composed only across valid evaluation stages.
• Every metric input has a provable evaluation grain, and every requested grouping has a fanout-safe alignment across inputs.
• Coarser-grain metric values are broadcast only when an explicit grain context makes the broadcast intentional; broadcast values are never treated as additive inputs.
• Time-aware metrics resolve a temporal field and calendar.
• Changing or omitting preferred_usage cannot change semantic validation, planning, generated query SQL, permissions, calculations, or query results; only UX placement or lossless metadata classification may differ.
• Window ordering is deterministic.
• Aggregate-specific parameters match their aggregation function.
• Result precision and target-platform limits are compatible.
• Fixed-grain results can join back without fanout.
• Non-additive metrics are not naively summed during cache construction or final rollup.
• Cache construction honors the aggregation catalog's decomposability, order-sensitivity, and nondeterminism metadata in addition to grain and AggregationBehavior.

These are the rules authors must account for while modeling. YAML model validation requirements defines the required schema, loader, SDK, source-contract, query-time, target-validation, diagnostic, and conformance behavior.

15. MCP discovery and SQL generation

The Semantic Hub MCP server should expose the canonical model rather than only names and descriptions. Metric discovery should include:

• Metric type, dataset, and qualified name
• Definition, synonyms, and lightweight annotations
• Authoritative governance metadata resolved from MetaKarta, subject to the caller's permissions
• Native atomic grain and additivity
• Metric dependency list
• Referenced-metric evaluation grains and the dimensions on which inputs can be aligned safely
• Intrinsic filter summary and filter-context policy
• Required time field, calendar, and supported granularities
• Result type, unit, currency, and display format
• Fields that can safely group or filter the metric
• Any model-validation warnings

SQL generation should provide an optional explanation containing relationship paths, native-grain CTEs, pre-aggregation decisions, applied filters, time expansion, final rollup, and dialect overrides. This lets an AI agent verify semantic correctness rather than treating executable SQL as sufficient evidence.

See Grain and multi-fact query planning for the complete Query-to-SQL procedure and worked examples for every metric type.

16. Recommended companion documentation

17. Complete compact example

name: orders
definition: One row per accepted customer order
table: analytics.fact_orders
unique_keys:
  - columns: [order_id]

joins:
  - name: customer
    from_columns: [customer_id]
    to:
      dataset: customers

fields:
  - name: order_id
    expression: order_id
    data_type: TEXT
  - name: order_date
    expression: order_date
    data_type: DATE
    time_type: DAYS
  - name: status
    expression: status
    data_type: TEXT

  - name: revenue
    expression: total_amount
    data_type: DECIMAL
    default_aggregation: SUM
    preferred_usage: FACT

  - name: cost
    expression: total_cost
    data_type: DECIMAL

default_time_dimension: order_date
time_zone: UTC

metrics:
  - name: total_revenue
    metric_type: AGGREGATE
    expression: revenue
    aggregation: SUM
    result_type:
      data_type: DECIMAL
      precision: 18
      scale: 2
      currency: USD

  - name: completed_revenue
    metric_type: AGGREGATE
    expression: revenue
    aggregation: SUM
    filter: "status = 'COMPLETED'"

  - name: total_cost
    metric_type: AGGREGATE
    expression: cost
    aggregation: SUM

  - name: gross_profit
    metric_type: DERIVED
    expression: "total_revenue - total_cost"

  - name: revenue_per_order
    metric_type: RATIO
    numerator:
      metric_ref: total_revenue
    denominator:
      metric_ref: order_count

  - name: order_count
    metric_type: AGGREGATE
    aggregation: COUNT_ROWS

  - name: revenue_ytd
    metric_type: CUMULATIVE
    base_metric:
      metric_ref: total_revenue
    time_dimension: order_date
    frame:
      frame_type: TO_DATE
      grain_to_date: YEAR

  - name: revenue_yoy_growth
    metric_type: PERIOD_OVER_PERIOD
    base_metric:
      metric_ref: total_revenue
    time_dimension: order_date
    offset:
      value: -1
      unit: YEAR
    comparison: PERCENT_CHANGE

18. Known limitations

The canonical format deliberately omits the capabilities below. Importers must report a source construct that depends on one of them as an unsupported or approximated element rather than dropping it silently, and exporters must not invent target behavior for it. Where a lightweight record is useful, an importer may keep the original text in imported_expression or a note in annotations; neither carries runtime semantics.