Metric evaluation and composition

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

This document defines how Semantic Hub expands, validates, orders, aligns, and evaluates metric dependencies. It complements the authoring overview in YAML model format, the binding rules in Expression language, and the physical planning rules in Grain and multi-fact query planning.

The JSON Schemas remain authoritative for authored shape. This document is the semantic contract for valid composition. A compiler must reject a dependency it cannot evaluate without changing grain, population, time, null, or ordering semantics.

1. Metric values are staged results

A metric is not a SQL fragment. Its identity includes at least:

• the authored metric object or stable anonymous inline identity;
• its containing dataset and dataset atomic grain;
• its metric operator and bound dependencies;
• its intrinsic and inherited filter context;
• its evaluation grain and additivity behavior;
• its time, calendar, and time-zone context;
• its result type and null policies; and
• any target override selected after canonical planning.

Three grains must remain distinct:

Dataset qualification selects an object; it does not make two metric grains equal. A compiler proves alignment separately for every query.

2. Dependency DAG

2.1 Nodes and edges

The compiler expands selected metrics into a directed acyclic graph (DAG). A node is one of:

• a named metric;
• an anonymous inline_metric aggregate;
• an aggregate call normalized from a DERIVED.expression, such as SUM(line_amount);
• a row-expression dependency required by an aggregate node; or
• a compiler-introduced alignment, rollup, time, event, or presence-state node.

An authored edge records the property that introduced it, for example:

• DERIVED.expression metric reference;
• RATIO.numerator or RATIO.denominator;
• CUMULATIVE.base_metric;
• WINDOW.base_metric or a metric-valued order_by item;
• PERIOD_OVER_PERIOD.base_metric; or
• CONVERSION.base_metric or CONVERSION.conversion_metric.

Each bound edge retains the authored source span, reference identity, anchor and terminal datasets, relationship path, result type, native grain signature, filter and time context, and evaluation-stage requirements. SQL generation consumes these bound identities; it must not resolve authored names again.

2.2 Named and anonymous dependencies

A MetricInput selects exactly one of metric_ref or inline_metric. A named reference may select any metric object, subject to stage and grain validation. An inline metric is always an anonymous aggregate anchored to the containing metric's dataset. It has no authored namespace entry, but it receives a stable internal identity for diagnostics, lineage, common-subexpression detection, and cache planning.

An explicit aggregate call in a derived expression is normalized in the same way:

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

SUM(line_amount) becomes an anonymous aggregate dependency. The compiler evaluates it at the dataset's safe aggregate grain, aligns it with existing_margin, and only then performs addition.

2.3 Expansion and cycle detection

Expansion is deterministic:

1. Resolve the selected metric objects.
2. Parse and bind expressions in their declared contexts.
3. Normalize inline aggregates and aggregate calls to dependency nodes.
4. Add edges in authored property and source order.
5. Detect cycles before semantic planning.
6. Topologically order nodes with stable object identity as the final tie-breaker.

A cycle is invalid even if a target SQL engine could express recursive SQL. The diagnostic includes the complete ordered cycle and the property and source span for every edge.

- name: net_revenue
  metric_type: DERIVED
  expression: "gross_revenue - refunds"

- name: refunds
  metric_type: DERIVED
  expression: "net_revenue * refund_rate"

This definition fails before target selection because net_revenue -> refunds -> net_revenue is a metric dependency cycle.

3. Evaluation stages

Semantic evaluation uses ordered logical stages. A target may combine adjacent stages into one SQL block only when it proves equivalent behavior.

The stage number describes a logical boundary, not a mandatory CTE count. Filters, fixed grains, multiple facts, or a target limitation may require more physical wrappers.

Null policies owned by a dependency are applied at that dependency's semantic boundary before its value is consumed. Helper state needed to distinguish empty, missing, and null results may remain alongside the scalar value until a later boundary.

4. Legal nesting

4.1 General legality test

A dependency edge is legal only when all of the following hold:

1. The dependency can be completely evaluated without using a value from its consumer.
2. The consumer accepts the dependency's result kind and retained state.
3. The compiler can place the consumer operator after the dependency's required wrapper.
4. Every requested grouping and filtering dimension has an unambiguous, fanout-safe interpretation for each branch.
5. Any finer-grain broadcast or coarser-grain rollup is explicit and semantically valid.
6. Ordering, calendar, event identity, and null-state requirements survive the boundary.
7. The selected target can render the resulting plan or returns an unsupported-capability diagnostic.

A SQL subquery can make nested syntax executable, but it cannot by itself make the semantic edge legal.

4.2 Consumer preconditions

The preconditions, rather than the referenced metric's type name alone, decide legality. For example, a derived revenue expression can be a cumulative base when it can be reevaluated over the frame, while a rank cannot be used as a conversion event population merely because it is numeric.

4.3 Wrapping later-stage dependencies

When a scalar DERIVED, RATIO, or WINDOW operation consumes a metric that already requires a time, conversion, or window stage, the dependency is materialized first and the consumer receives its final governed value. The compiler must introduce a new wrapper instead of placing one analytic operation directly inside another.

Recursive frame or comparison semantics are never inferred. A cumulative metric over another cumulative metric, a period comparison over a window result, or a window ordered by another window result is legal only when the compiler has an explicit operator rule that defines population, grain, ordering, and retained state. Otherwise it fails as an evaluation-stage conflict.

4.4 Common legal compositions

4.5 Always-invalid shapes

• A metric reference inside RowExpression or FilterExpression.
• A bare row field in DERIVED.expression, such as total_revenue + line_amount.
• A dependency cycle.
• A consumer that would require raw-row flattening of independent fact datasets.
• An implicit broadcast from a coarser dependency to a finer grain.
• A rollup of a non-additive dependency without recomputation or an authorized selection.
• A conversion input whose event identity cannot be recovered.
• An analytic dependency whose ordering or partition is undefined after alignment.
• A target rewrite that skips a dependency's intrinsic filter, grain, time, event, or null-policy wrapper.

5. Derived expressions

DERIVED.expression is an AggregationExpression. It may contain metric references, scalar literals, arithmetic, typed CASE, COALESCE, NULLIF, ABS, POWER, scalar minimum and maximum, negation, and explicit aggregate calls.

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

- name: non_negative_profit
  metric_type: DERIVED
  expression: >-
    CASE
      WHEN gross_profit < 0 THEN 0
      ELSE gross_profit
    END

Evaluation follows expression dependencies, not YAML declaration order. A target wrapper is required whenever one derived alias depends on another and the target cannot reuse a select-list alias safely.

Use DIVIDE(numerator, denominator[, alternate]) for operator-local zero-denominator behavior inside an expression. The raw / operator is strict and errors on zero. A derived metric's null_handling.null_result applies only to the final expression result; it does not rewrite intermediate nulls or divisions.

Type inference occurs before target compilation. Every CASE result branch must have a compatible type, numeric precision and scale must not be silently narrowed, and the final inferred type must be compatible with result_type when declared.

6. Ratio evaluation

A RATIO is structural composition, not shorthand for placing two expressions in one SQL select list.

- name: return_rate
  metric_type: RATIO
  numerator:
    metric_ref: returns.return_count
  denominator:
    metric_ref: orders.order_count
  null_handling:
    divide_by_zero: "NULL"

The compiler:

1. expands the numerator and denominator DAGs;
2. applies each input's own intrinsic filter and offset;
3. evaluates each branch at its native grain and compatible requested dimensions;
4. preserves groups present on only one side according to query semantics;
5. aligns branches only on common output dimensions;
6. applies structural division and divide_by_zero; and
7. applies the ratio's final null_result if the non-empty result is null.

A null denominator is not a zero denominator. Input metrics establish their own empty and null results before the ratio consumes them.

6.1 Same-fact ratio

Inputs that share a row population may use one physical aggregate block when their contexts are compatible:

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

The numerator filter is local to that input. It must not become a shared WHERE predicate that removes denominator rows.

6.2 Multi-fact ratio

Inputs on separate fact datasets remain separate through aggregation:

returns rows -> aggregate by requested compatible dimensions --+
                                                             +-> align -> divide
orders rows  -> aggregate by requested compatible dimensions --+

No relationship path authorizes raw fact-to-fact flattening. If a requested dimension cannot be resolved compatibly for both inputs, the query fails even when a grand-total ratio would be valid.

7. Grain changes, broadcast, and rollup

7.1 Fixed and relative evaluation grain

grain.mode: FIXED defines a metric grain independent of extra query dimensions. An empty fixed grain is a grand total. grain.mode: RELATIVE starts with query grain and applies its declared additions or exclusions. keep_only_dimensions creates the documented adaptive fixed intersection.

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

For output by region and category, regional_revenue can be repeated across categories only as an intentional broadcast. The repeated value is a non-additive intermediate and must not later be summed.

7.2 Broadcast

A broadcast from metric grain to a finer output grain is legal only when omitted dimensions are intentional under the metric's grain contract and the join back is many-to-one from each output row to one metric value. The plan explanation identifies the broadcast dimensions and why fanout is impossible.

An unqualified coarser metric with no fixed-grain contract is not implicitly broadcast.

7.3 Rollup

When a fixed-grain result must be summarized to a coarser query grain, grain.rollup_aggregation controls the operation:

rollup_aggregation does not override aggregation_behavior, turn a holistic scalar into mergeable state, or repair fact fanout.

7.4 Recompute versus combine

Recomputation returns to the metric's dependencies or sufficient partial state. Combination operates on already calculated metric values. They are equivalent only for operations whose decomposability, population, filters, and null behavior permit that equivalence.

For example, summing regional distinct-customer counts does not produce a global distinct-customer count. A global result must reevaluate the distinct input or merge compatible sketches; summing scalar estimates is invalid.

8. Common-subexpression reuse and caches

Two dependency nodes can share physical work only when their canonical identities match in every semantically relevant property. At minimum this includes bound input identity, aggregation or operator, parameters, grain, intrinsic and inherited filters, time and calendar context, additivity, ordering, result and null policies, security context, and target-capability version.

Textual equality is insufficient. Conversely, normalized aliases such as AVG(x) and structural AVERAGE identify the same canonical aggregation when every other property matches.

A cache may substitute for a dependency only when it contains the exact-grain value or the sufficient distributive, algebraic, holistic, or sketch state needed by the remaining plan. Scalar cache values must not be treated as universally rollup-safe.

9. Null, empty, missing, and division state

Composition preserves distinct semantic states:

A branch may carry a value plus a presence count, population flag, or sketch state. Alignment must not collapse “group absent” into “group present with null value.” Applying COALESCE(value, 0) indiscriminately is invalid.

Policy order is:

1. evaluate dependencies using their own policies;
2. perform the current aggregate, frame, event, comparison, or window operation;
3. apply structural zero-denominator handling at its division node;
4. apply empty_result when no rows contributed; and
5. otherwise apply final null_result.

10. Definition-time and query-time validation

Definition-time validation proves:

• schema shape and mutually exclusive input alternatives;
• reference identity, expression phase, type, and nullability;
• DAG acyclicity and operator preconditions;
• declared grain, filter, time, partition, ordering, and event dimensions;
• intrinsic two-input population compatibility; and
• canonical target-independent semantics.

Query-time validation additionally proves:

• every selected, filtered, hidden, and ordered dimension is compatible with each dependency branch;
• a common output alignment grain exists;
• required broadcasts and rollups are authorized;
• fact branches remain isolated through aggregation;
• semi-additive and time operators retain their populations; and
• the chosen target has every required capability.

A metric can therefore be valid in the catalog but invalid for one query grain. The diagnostic identifies the metric edge, incompatible dimension, native grains, and failed operator precondition.

11. Error cases and diagnostics

Normal author and target failures return structured diagnostics rather than approximate SQL.

Diagnostic ordering is deterministic: validation phase, owning object identity, property/source location, then stable code.

12. Plan explanation contract

For every selected metric, an explain result should expose:

• the expanded dependency DAG and stable node identities;
• each node's metric type, operator, native dataset, atomic grain, and evaluation grain;
• the stage and physical wrapper in which each node executes;
• intrinsic, input, inherited, excluded, and security filters;
• branch isolation and relationship paths;
• output alignment keys and unmatched-group handling;
• intentional broadcasts, recomputations, and rollups;
• semi-additive selection, time expansion, period alignment, event matching, and window ordering;
• helper state retained for empty, missing, null, or sketch semantics;
• the final result grain and type; and
• target capabilities and any selected override.

Executable SQL is not sufficient evidence of semantic correctness. The explanation must make the grain and stage proof inspectable.

13. Conformance checklist

A conforming implementation demonstrates that:

1. Selected metrics expand into a deterministic, acyclic, identity-bound DAG.
2. Row, aggregate, composite, time/event, and window stages remain separate unless an equivalence proof permits fusion.
3. Every consumer validates the preconditions of each dependency result.
4. Explicit aggregate calls in derived expressions become ordinary grain-anchored dependencies.
5. Same-fact and multi-fact ratios preserve input populations and apply division only after alignment.
6. Fixed-grain broadcast is intentional and cannot become a later additive rollup.
7. Non-additive results recompute or use an authorized selection instead of being naively summed.
8. Empty, missing, null, and zero-denominator states survive composition to their owning policy boundary.
9. Unsupported nesting, grain, event, order, or target behavior produces a structured diagnostic.
10. SQL and diagnostic output are deterministic for the same bound catalog, query, and target-capability version.

See Aggregations and additivity for base aggregate parameters, decomposability, semi-additive selection, and target coverage.