Proposal: metric additivity (additive / semi-additive / non-additive) in the core spec

[harrydevforlife]

Problem

In the current core spec (core-spec/spec.md), a metric is only name, expression, description, datatype, ai_context and custom_extensions. There is no way to declare how a metric may be aggregated across dimensions.

For fully additive metrics (SUM(revenue)) and clearly non-additive ones (COUNT(DISTINCT user_id), ratios) a consumer can often infer behaviour from the expression. But semi-additive metrics cannot be inferred: SUM(balance), inventory levels, or daily-active snapshots are summable across users/regions but must not be summed across time (you want last / first / average over the period instead). Today the only options are vendor-specific custom_extensions or ai_context hints, neither of which is portable or enforceable by query engines.

Proposal

Add optional, portable additivity metadata to the Metric object, e.g.:

- name: account_balance
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: SUM(accounts.balance)
  additivity: semi_additive   # additive | semi_additive | non_additive
  non_additive_dimensions:
    - field: accounts.snapshot_date
      window: last            # last | first | min | max | avg

Prior art: dbt/MetricFlow non_additive_dimension (with window_choice and window_groupings), Cube, SSAS semi-additive measures (LastNonEmpty, etc.).

Open questions

1. Should additivity be explicit, or derived from non_additive_dimensions being present?
2. What should the default be when omitted (unknown vs. additive)?
3. Should non-additive metrics declare the components needed for correct re-aggregation (e.g. numerator/denominator for ratios)?
4. How does this interact with is_time fields and time-grain handling?

Happy to help draft a PR if there is interest.

[justint-db]

Thanks! The expression language working group has discussed this a little bit, but not come to any conclusions. We'd definitely appreciate input here.

Another open question I'd be interested in is what happens when there is a conflict, e.g. the expression is a COUNT(DISTINCT), but it is marked as additive. Does that mean anything? Is it an error?

I've worked on Databricks' feature in this space ("window measures"). It takes the approach that the query engine analyzes the expression to determine additivity, rather than having it be separate metadata. This avoids the potential for a conflict, but requires a library that can parse the analyze Ossie expressions. (Maybe such a library should be part of the Ossie repository?)

[harrydevforlife]

Thanks, happy to discuss. I think inference and explicit metadata can complement each other rather than compete:

Inference covers most cases. SUM/COUNT are additive, and COUNT(DISTINCT), AVG, percentiles and ratios are non-additive. Since datasets already declare primary_key / unique_keys, an analyzer can even recognise COUNT(DISTINCT orders.order_id) as additive when order_id is unique.

Some things can't be inferred from the expression alone. Semi-additivity is the main one: SUM(accounts.balance) over a snapshot table is syntactically identical to SUM(orders.amount). The difference lives in the data, not the expression, unless the expression language gains a construct for it (e.g. last value over a time dimension). Opaque dialects like MAQL can't be analysed at all.

Proposed conflict rule: a declaration can only make the metric stricter than what's inferred.

Declared stricter than inferred (e.g. SUM marked semi-additive over snapshot_date): valid; it adds information. Declared looser than inferred (e.g. COUNT(DISTINCT user_id) marked additive with no unique key to justify it): a validation error, since it would produce silently wrong totals. Inference not possible (unparsed dialect): the declaration is authoritative.

That keeps the no-conflict property of the inference approach while allowing metadata for what the expression can't express.

+1 on a reference parser/analyzer living in the Ossie repo, at least for ANSI_SQL. It would make "additive by inference" a consistent, testable behaviour across implementations rather than something each engine interprets differently.