Aggregations and additivity

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

This document defines the canonical behavior of every Semantic Hub Aggregation, the structural parameters accepted by AggregateDefinition, and the rules for distinctness, deduplication, rollup, semi-additivity, empty populations, and SQL-target coverage.

Metrics.json is authoritative for authored shape. Expression language defines parsed aggregate-call syntax, Metric evaluation and composition defines dependency staging, and Grain and multi-fact query planning defines physical compilation.

1. Aggregate definition contract

A named AGGREGATE metric and a MetricInput.inline_metric share AggregateDefinition:

expression: revenue
aggregation: SUM
aggregation_parameters: {}
null_handling:
  empty_result: AUTO
  null_result: "NULL"

The properties have separate roles:

An inline metric deliberately omits name, metric_type, presentation metadata, common grain and filter contexts, overrides, and nested metric inputs. Its containing MetricInput may add a local filter or offset.

2. Complete aggregation catalog

In the tables below, x is AggregateDefinition.expression, y is exactly one of aggregation_parameters.second_field_ref or .expression, and p is aggregation_parameters.percentile.

2.1 Additive, counting, and selection aggregates

Empty AUTO states the canonical function-specific result expected by Semantic Hub. A target mapping must preserve it even when native target behavior differs.

2.2 Statistical and percentile aggregates

percentile is inclusive from 0 through 1. 0.9 means the 90th percentile; 90 is invalid.

For every paired statistic, the two expressions are evaluated on the same source row population and at the same pairing grain. Rows for which either conditioned input is null do not form a pair. A compiler must reject inputs that require incompatible anchors or fanout paths.

2.3 Ordered list aggregation

The JSON Schema restricts delimiter and order_by to LIST_AGG. It permits omission of order_by, but the result is then nondeterministic. A validation policy intended for reproducible models should reject or warn on unordered LIST_AGG; caches and conformance fixtures must not treat an unordered result as stable.

Target-specific overflow limits, truncation syntax, collation, and text coercion are capabilities. Silent truncation is not a valid mapping.

3. Structural aggregation parameters

Semantic validation narrows AggregationParameters beyond basic JSON object shape.

Unknown or inapplicable parameters are semantic errors even where a generic JSON Schema property definition alone cannot express the complete function dispatch.

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

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

4. Value distinctness is not key deduplication

4.1 Value-distinct aggregates

COUNT_DISTINCT, SUM_DISTINCT, and AVERAGE_DISTINCT deduplicate by the evaluated x value. If two different entities produce the same value, that value contributes once.

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

If orders 101 and 102 both have shipping amount 10, the sum of distinct values contributes 10, not 20. This is correct only when value distinctness is the intended business definition.

4.2 Key-based deduplication

Use deduplicate_by_dimensions when a denormalized dataset repeats one logical entity-level value:

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

The logical evaluation is:

1. Bind the deduplication fields from the aggregate metric's dataset anchor.
2. Apply the dataset, security, intrinsic metric, input, and applicable query filters.
3. Group eligible rows by the composite deduplication key plus dimensions required by the metric's evaluation grain.
4. Verify that all contributing non-null expression values for one key agree.
5. Produce one logical input value per key.
6. Apply SUM or AVERAGE to those logical inputs.

Conflicting values for one key are an error. The compiler must not choose MIN, MAX, ANY_VALUE, or the first physical row as a repair.

4.3 Scope and null keys

The deduplication key is part of the metric's canonical identity. Its fields must resolve unambiguously and form a fanout-safe determinant for the logical input population. If nullable keys can collapse unrelated entities or target null-grouping behavior would change identity, validation fails unless the applicable key contract proves the intended semantics.

Deduplication is performed before the final aggregate and after filters that define the input population. Moving it before a metric's intrinsic filter or after a fanout-producing join can change results and is invalid.

5. Intrinsic filters and aggregate inputs

An AggregateMetric.filter belongs to one metric:

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

Conceptually:

SUM(CASE WHEN status = 'COMPLETED' THEN revenue ELSE NULL END)

The compiler uses function-specific conditioning:

The excluded-row null is an intermediate “does not contribute” marker. null_handling.null_result must not replace it before aggregation. The predicate may become a native target aggregate filter only when the dialect proves identical null, distinct, order, and pair semantics.

The predicate cannot become a shared query WHERE unless every affected branch has the same effective filter and the rewrite preserves sibling metrics.

6. Additivity and decomposability are different

aggregation_behavior describes whether a business measure can be combined across dimensions. Decomposability describes what intermediate state an aggregation requires. Neither substitutes for the other.

For example, MIN is distributive because partial minima can be merged with MIN. It is not additive in the ordinary business sense and must not be rolled up with SUM. COUNT_DISTINCT may represent an additive-looking concept such as customers, but scalar subgroup distinct counts are holistic and cannot be summed when customers overlap.

6.1 ADDITIVE

ADDITIVE declares that the metric can be combined across all applicable dimensions using its authorized rollup state and operation. It does not authorize raw-row fanout, arbitrary SUM, incompatible filter populations, or merging of scalar holistic results.

Typical candidates are sums and counts over disjoint populations. The compiler still proves disjointness and the correct merge operation.

6.2 NON_ADDITIVE

NON_ADDITIVE declares that precomputed scalar results cannot be combined across dimensions. Ratios, percentages, unit prices, medians, distinct counts, and other holistic results are common examples.

At a coarser grain the compiler must recompute from dependencies or sufficient state. If that state is unavailable, it rejects the rollup.

6.3 SEMI_ADDITIVE

SEMI_ADDITIVE means the metric combines normally across some dimensions but requires explicit selection across each declared entry in non_additive_dimensions.

- 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

The operation is “choose a boundary balance per account across time, then sum accounts,” not “sum every snapshot.”

SEMI_ADDITIVE requires at least one non_additive_dimensions entry. That array is illegal for ADDITIVE and NON_ADDITIVE.

7. Semi-additive selection

7.1 Selection modes

“Non-empty” is not the same as non-null. An existing position whose non-empty calculation returns null remains distinguishable from an absent position. The selection uses the metric's retained presence state, not a blanket value IS NOT NULL test unless that is proven equivalent.

7.2 Grouping before selection

group_by_dimensions names the entities for which a boundary is chosen. For balances it is commonly account; for inventory it may be product and warehouse. Omitting an entity key means one boundary value is chosen for the entire current partition, which is rarely equivalent.

After boundary selection, the ordinary aggregate combines the selected entity values across compatible dimensions.

7.3 Ordering

First and last selection requires deterministic ordering. order_by normally includes the non-additive dimension and may include additional tie-breakers. If two eligible rows remain tied and the model does not establish functional uniqueness, validation fails or returns a configured nondeterminism warning; a physical row order is never inferred.

7.4 Multiple non-additive dimensions

Every declared rule is a semantic restriction. The compiler must derive a jointly valid selection plan. Because the schema does not assign procedural order to the array, a result that changes when rules are applied in a different sequence is ambiguous and must be rejected rather than choosing array order.

7.5 Interaction with time filling

Missing-period filling occurs according to the metric's time context. ZERO creates a business zero only when explicitly declared; it must not silently turn a missing snapshot into an observed zero balance. Boundary selection and time filling must preserve the distinction required by FIRST_NON_EMPTY and LAST_NON_EMPTY.

8. Empty, all-null, and non-empty results

An aggregate result carries at least two conceptual facts: whether any row contributed, and the aggregate value. These states are distinct:

empty_result: AUTO preserves the cataloged function behavior: count operations return zero; other current aggregations return null. A literal fallback must type-check against result_type, and reserved ZERO is numeric-only.

Policy application is:

1. evaluate row expressions without applying the metric's final fallback;
2. condition inputs with the intrinsic metric predicate;
3. perform key deduplication when declared;
4. execute the aggregate and retain population state;
5. apply empty_result when no rows contributed; otherwise
6. apply null_result if the final aggregate value is null.

The plan may carry a row count, non-null count, paired count, or explicit presence flag beside the value. A scalar aggregate alone is not always sufficient.

9. Rollup and materialization state

The decomposability categories are:

Cache eligibility additionally requires identical grain, filter population, null behavior, ordering, collation, security context, and target semantics.

Invalid rollups include:

• averaging averages without counts;
• summing distinct counts or approximate scalar estimates;
• merging percentiles without complete values or a compatible sketch;
• concatenating partial lists without a preserved global order;
• summing fixed-grain values broadcast to finer output rows; and
• summing snapshots across a declared non-additive time dimension.

grain.rollup_aggregation: RECOMPUTE is the safe default. A declared scalar rollup is legal only when both additivity and required intermediate state prove equivalence.

10. Canonical syntax and normalization

Authors of structural metrics use the Aggregation enum values. Aggregate calls inside DERIVED.expression use standard GENERIC syntax and normalize to the same identities:

Accepted aliases do not create separate lineage, cache, dependency, or target-capability identities.

11. Target coverage contract

The typed SQL targets are Snowflake, PostgreSQL, DuckDB, Oracle, and Databricks. DuckDB has its own dialect and capability definition; it is not a PostgreSQL alias. OTHER or a generic connection is unsupported until an explicit dialect is added.

Canonical availability in Metrics.json does not promise that every target has a native function. For each aggregation and target, the versioned dialect capability definition reports one of:

The compiler must not silently substitute:

• sample for population statistics or the reverse;
• exact for approximate behavior when accuracy or resource semantics matter, or approximate for exact behavior;
• continuous for discrete percentiles;
• value distinctness for key deduplication;
• nondeterministic selection for deterministic first, last, mode, or list behavior;
• scalar approximate results for mergeable sketch state; or
• a target's convenient null or empty behavior for the canonical policy.

11.1 Capability surface

A target capability entry for an aggregation includes, as applicable:

• canonical aggregation identity and target/server version range;
• accepted input and result types, precision, and nullability;
• native function or exact rewrite strategy;
• parameter mapping and bounds;
• distinct and key-deduplication support;
• ordered-set, collation, null-order, and tie behavior;
• approximate algorithm, accuracy-hint interpretation, and sketch compatibility;
• partial-state and rollup support;
• empty, all-null, overflow, and insufficient-sample behavior; and
• whether filtered inputs use CASE, native FILTER, or another proven equivalent.

Model validation can report that a model is not deployable to one selected target even when it is valid canonically. Query compilation rechecks the capabilities actually required by the expanded dependency DAG.

11.2 Required target tests

Every aggregation feature requires:

1. a positive test for each target that advertises native, exact-rewrite, or applicable conditional support;
2. a negative test for each unsupported target or condition;
3. null, empty, all-null, and boundary-parameter fixtures;
4. filtered-input tests, including paired statistics where applicable;
5. rollup or explicit non-rollup tests based on decomposability;
6. deterministic order and tie fixtures for order-sensitive operations; and
7. result-type and precision assertions.

A dialect must not advertise support solely because representative SQL parses.

12. Validation failures

Semantic Hub rejects an aggregate definition or query for conditions including:

• missing expression for a non-COUNT_ROWS aggregation;
• an expression supplied to COUNT_ROWS;
• missing, out-of-range, or inapplicable percentile;
• a second input missing from, duplicated in, or supplied to the wrong aggregation;
• deduplicate_by_dimensions on a function other than SUM or AVERAGE;
• conflicting non-null values for one deduplication key;
• a paired statistic whose inputs do not share one row population and pairing grain;
• a non-Boolean intrinsic filter;
• a semi-additive declaration without a non-additive dimension;
• a non-additive-dimension rule on a non-semi-additive metric;
• nondeterministic first, last, or ordered-list behavior where deterministic output is required;
• a holistic or sketch scalar rolled up with an invalid operation;
• a fanout-producing relationship path that duplicates aggregate inputs; or
• an unsupported target function, type, parameter, ordering, accuracy, or null behavior.

Diagnostics include the metric or anonymous dependency identity, aggregation, property and source span, native grain, relevant population or rollup state, target when applicable, and a concrete remediation.

13. Authoring guidance

• Choose the aggregation that states the business operation; do not choose one merely because a target exposes a convenient function.
• Declare dataset grain and validate relationship cardinality before relying on aggregate results.
• Use ordinary SUM or AVERAGE plus deduplicate_by_dimensions for repeated entity-level values.
• Use value-distinct aggregates only when equality of values, not identity of entities, defines duplication.
• Treat ratios, percentages, distinct counts, percentiles, medians, and broadcast fixed-grain values as non-additive unless a stronger canonical state proves otherwise.
• Model snapshot and balance behavior with SEMI_ADDITIVE boundary selection, including entity grouping and deterministic order.
• Preserve AUTO empty semantics unless a business zero or other fallback is genuinely defined.
• Require ordered LIST_AGG and deterministic first/last behavior for reproducible output.
• Use a metric target override only as a documented last resort; it does not change canonical dependencies, grain, filters, additivity, or null behavior.

14. Conformance checklist

A conforming implementation demonstrates that:

1. All 26 Aggregation values are parsed, bound, normalized, and either rendered exactly or rejected per target capability.
2. Function-specific required and forbidden parameters are validated after JSON Schema shape validation.
3. Filter conditioning preserves count, distinct, ordered-set, paired-statistic, empty, and null semantics.
4. Value distinctness and key-based deduplication produce different, tested plans.
5. Conflicting values for a deduplication key fail instead of being selected arbitrarily.
6. Additivity, decomposability, grain safety, and target support remain independent proofs.
7. Semi-additive selection preserves entity grouping, deterministic ordering, and empty versus null state.
8. Materialized partial state is sufficient for every authorized rollup.
9. Snowflake, PostgreSQL, DuckDB, Oracle, and Databricks have explicit positive and unsupported-capability tests; DuckDB is tested as its own dialect.
10. Unsupported or unsafe aggregate behavior never falls back to approximate SQL or an unmodeled DISTINCT.