Skip to content

Grain and multi-fact query planning

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

This guide explains how Semantic Hub resolves atomic grain, join fanout, multi-fact calculations, level-of-detail evaluation, and ambiguous relationship paths when generating SQL for BI tools and AI agents. Here, multi-fact is the established warehouse term for metrics sourced from multiple atomic datasets; it does not refer to a separate row-level Fact object.

The complete YAML property reference is in YAML model format.

1. The grain problems

Join fanout

An order with a stored total of 100 may join to three order lines. A flattened query that computes SUM(order.total_amount) returns 300. The SQL is executable but semantically wrong.

Multi-fact calculations

A KPI may combine metrics whose facts have different atomic grains:

revenue per active customer
  = order-grain revenue
  / customer-grain active customers

Forcing both facts into one flattened rowset duplicates at least one input.

Diamond schemas

Two facts may reach a shared dimension through different paths. Orders and returns can both reach customer and calendar, producing a diamond-shaped relationship graph. A query must preserve each fact branch and combine only their aggregated results.

Grain shifting

Percent-of-total, percent-of-parent, and benchmark calculations intentionally evaluate one metric at a different grain from the query result.

Semi-additive values

Balances and inventory may be additive across accounts or products but not across time. Summing every daily snapshot produces a meaningless result.

2. Atomic dataset grain

Root datasets may define metrics without unique_keys. Simple aggregation and outgoing MANY_TO_ONE joins do not require a root key. A suitable non-null key is still required when a dataset is a relationship's unique endpoint or an operation needs row identity, such as fanout protection or key-based deduplication. grain_key selects the dataset's atomic grain. When exactly one key exists, grain_key may be omitted; an unnamed sole key receives the effective name default.

name: orders
table: analytics.fact_orders
unique_keys:
  - columns: [order_id]

The key declaration has three roles:

  1. It identifies source rows when a metric operation requires row identity.
  2. It supplies a validated unique target for relationships.
  3. It gives the compiler a deduplication boundary when a query crosses a fanout relationship.

The deployment validator should test the uniqueness and non-null contract of keys with nulls_allowed: false and the populated-tuple uniqueness contract of keys with nulls_allowed: true. If key validation fails, a production model with unknown_grain: ERROR must not deploy.

3. Relationship contracts

Relationships declare cardinality and referential integrity rather than leaving them implicit in join SQL.

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

Semantic Hub supports ONE_TO_ONE, MANY_TO_ONE, and ONE_TO_MANY joins. MANY_TO_ONE is the default. For a to-one edge, a missing to.key resolves to the target dataset's grain key. A ONE_TO_MANY edge instead maps the owning dataset's non-null from_key to non-unique foreign-key to.columns in the target dataset.

The conservative referential-integrity setting is NOT_GUARANTEED. It tells the compiler to preserve unmatched rows. ALL_FROM_ROWS_MATCH applies to to-one edges and guarantees that every populated source foreign key matches the target. ALL_TO_ROWS_MATCH applies to one-to-many edges and guarantees that every populated target foreign key matches the source; it does not require every source row to have children. Either stronger setting may enable optimizations only after the source contract is validated.

Many-to-many relationships through ordinary datasets

A bridge is modeled as an ordinary association dataset, not as nested metadata on a special join. The parent-to-association edge is ONE_TO_MANY; the association-to-member edge is MANY_TO_ONE.

# orders.yaml
name: 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
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

The association dataset's non-null grain provides a deduplication boundary. With orders as a root dataset, ordinary traversal reaches order_promotions at promotion_links and promotions at promotion_links.promotion; path exclusions can remove either edge and its descendants. The conceptual many-to-many relationship is derived from this explicit path rather than declared as a special direct join type.

This path is not permission to flatten endpoint facts. The compiler still aggregates each endpoint at its native grain before combining results. If a fact first joins a bridge-group dimension, the cardinalities are fact MANY_TO_ONE group, group ONE_TO_MANY association, and association MANY_TO_ONE member—not two one-to-many edges unless the data actually has that shape.

4. Dataset-scoped base metrics

An AGGREGATE metric is anchored to the dataset containing it.

fields:
  - name: revenue
    expression: total_amount

metrics:
  - name: total_revenue
    metric_type: AGGREGATE
    expression: revenue
    aggregation: SUM

The metric is not a floating SQL fragment. Its semantic signature includes:

  • Dataset: orders
  • Atomic grain: order_pk
  • Aggregation: SUM
  • Intrinsic filter, if any
  • Additivity behavior
  • Result type and null policy

MCP discovery should expose this signature so an AI agent can select compatible dimensions and understand why generated SQL uses particular subqueries.

5. Fanout prevention

When a parent-grain metric is requested with dimensions that require a one-to-many traversal, the compiler must not aggregate the parent value after flattening the child rows.

Depending on the relationship graph and target dialect, it can use one of these strategies:

  1. Aggregate the metric in a parent-grain CTE, aggregate the child dimensions separately, and join the results.
  2. Deduplicate at the declared parent unique key before applying the metric aggregation.
  3. Use a target-specific symmetric aggregate only when its correctness conditions are proven.

The chosen strategy must preserve results before and after adding a dimension across the fanout edge.

6. Multi-fact and diamond traversal

Consider orders and returns sharing customer and calendar dimensions. A requested return-rate metric is:

The two metric_ref values are intentionally at different atomic grains: one return per return event and one order per order event. This is valid because the compiler evaluates both metrics independently and can align their results by shared customer and calendar dimensions. A qualified reference identifies the input dataset; it does not require the input to have the same grain as the metric being defined. See Metric references and grain for the complete legality and validation rules.

name: return_rate
metric_type: RATIO
numerator:
  metric_ref: returns.total_return_amount
denominator:
  metric_ref: orders.total_revenue
null_handling:
  divide_by_zero: "NULL"

For a query grouped by customer region and month, the compiler should produce this logical plan:

orders branch
  orders -> customer -> calendar
  aggregate total_revenue by region, month

returns branch
  returns -> customer -> calendar
  aggregate total_return_amount by region, month

combine
  full or policy-selected join on region, month
  divide aggregated return amount by aggregated revenue

Representative SQL is:

WITH revenue_by_grain AS (
  SELECT c.region, d.month, SUM(o.total_amount) AS total_revenue
  FROM orders o
  LEFT JOIN customers c ON o.customer_id = c.customer_id
  LEFT JOIN calendar d ON o.order_date = d.date
  GROUP BY c.region, d.month
),
returns_by_grain AS (
  SELECT c.region, d.month, SUM(r.return_amount) AS total_return_amount
  FROM returns r
  LEFT JOIN customers c ON r.customer_id = c.customer_id
  LEFT JOIN calendar d ON r.return_date = d.date
  GROUP BY c.region, d.month
)
SELECT
  COALESCE(rv.region, rr.region) AS region,
  COALESCE(rv.month, rr.month) AS month,
  rr.total_return_amount / NULLIF(rv.total_revenue, 0) AS return_rate
FROM revenue_by_grain rv
FULL OUTER JOIN returns_by_grain rr
  ON rv.region = rr.region
 AND rv.month = rr.month;

The exact join policy can depend on query semantics, but neither fact branch is flattened into the other.

Multi-root query filters

A shared-dimension query filter applies independently to each fact branch before aggregation. For F1 and F2 sharing D12, filtering D12 should restrict both roots. If only F2 reaches D2, adding a global D2 filter is inapplicable to F1 and must reject the query unless an explicit authored filter-context rule removes that predicate from F1's effective context. It must not silently become F2-local.

An explicitly F2-scoped D2 filter leaves F1 subject only to the shared D12 filter. Metric-local and metric-input filters can represent metric-definition scope; query-time branch scope requires a future explicit contract. Cross-fact existence propagation through F2 is a distinct, deferred operation. Preserve Boolean structure, exact dimension roles, independent aggregation and value-state policies.

The normative details, applicability matrix and certification requirements are in Multi-root filter ownership is specified in docs/development/contracts/queries/query-sql-generation.md, section 3.3.1. This contract applies to SQL and Snowflake Semantic View lowering. Phase 3.4 supports shared to-one multi-root filters with explicit applicability checks; see docs/development/implementation/query-compiler/compiler/phase34-shared-filters.md for current evidence and limitations for the bounded SQL implementation and focused native Snowflake comparisons.

7. Qualified paths and role-playing dimensions

When a dataset has multiple relationships to the same target, relationship names disambiguate the path:

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

The dimensions are referenced as order_date.month and ship_date.month.

Multi-hop references use every path segment, for example customer.nation.region. If an unqualified dimension can be reached through more than one valid path, validation must fail instead of choosing an arbitrary path.

8. Level-of-detail grain context

LOD is modeled as the common grain context rather than a separate metric type.

A fixed regional result is:

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

An empty fixed grain produces a grand total. A relative grain inherits query dimensions and then adds or removes specified dimensions:

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

When the metric grain is a subset of query dimensions, the compiler can broadcast the result through a many-to-one join or an equivalent window operation. When intermediate results must be summarized to a coarser query grain, rollup_aggregation controls the operation. RECOMPUTE is the safe default for non-additive metrics.

9. Filter context

An intrinsic metric filter narrows the rows that define a metric. filter_context changes which query filters the metric inherits.

name: revenue_without_color_filter
metric_type: AGGREGATE
expression: revenue
aggregation: SUM
filter_context:
  mode: RELATIVE
  exclude_dimensions: [product.color]

This requires a separate filter-context CTE when the user filters by product color. It must not be compiled as a CASE inside the same aggregate because that would leave the query-level filter in effect.

Grain and filter context can be combined. The compiler groups metrics that share identical effective grain and filter contexts into the same isolated CTE.

10. Semi-additive metrics

Semi-additivity is declared on an aggregate metric:

name: ending_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

The logical evaluation is:

  1. Partition source rows by account.
  2. Select the last non-empty balance across snapshot date for each account.
  3. Aggregate the selected balances across compatible dimensions.

This behavior must be preserved in generated caches and final rollups.

11. Validation and failure behavior

Shape validation is performed by JSON Schema. Catalog validation additionally checks source and cross-object semantics.

Production defaults are:

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

Semantic Hub must reject a query or deployment when:

  • An operation requires atomic row identity but the dataset has no suitable non-null key.
  • A declared key is not unique.
  • A relationship cardinality claim is contradicted by validated source data.
  • A many-to-many business relationship is not represented by an ordinary association dataset with a non-null grain and explicit ONE_TO_MANY then MANY_TO_ONE edges.
  • A requested dimension path is ambiguous.
  • Two metric inputs have no compatible grouping dimensions.
  • A fixed-grain result would join back with fanout.
  • A non-additive metric would be naively summed.
  • Metric dependencies form a cycle or violate evaluation-stage rules.

Warnings may be allowed during exploration, but they must be returned by MCP discovery and SQL explanation rather than hidden.

12. Explainability contract

For a generated metric query, the compiler should be able to explain:

  • Each selected metric's native dataset and atomic grain
  • The relationship path used for every dimension
  • Cardinality and referential-integrity assumptions
  • Why each pre-aggregation or isolated CTE was created
  • Intrinsic and inherited filters
  • Grain and filter-context changes
  • Time expansion, calendar alignment, and missing-period filling
  • Non-additive selection and rollup behavior
  • Final result grain and result type
  • Any dialect override

This explanation is essential for AI agents. SQL execution proves only syntactic and database validity; the grain explanation provides evidence of semantic correctness.