Metric additivity

Metric additivity tells FeatureQL when an already summarized value can be combined again without changing its meaning. It catches mistakes such as summing daily inventory snapshots, averaging averages, or adding distinct counts from overlapping regions.

Numeric values are additive by default. Raw amounts, quantities, and pre-aggregated sums need no annotation. Declare only exceptions at their source boundary.

Start with ordinary aggregation

Write the metric you need:

DEFINE
    sales := ENTITY(),
    sale_id := INPUT(BIGINT#sales),
    sales_facts := INLINE_COLUMNS(
        sale_id BIGINT#sales BIND TO sale_id,
        country VARCHAR,
        amount BIGINT
        FROM CSV(
            sale_id,country,amount
            1,FR,10
            2,FR,20
            3,US,7
        )
    ),
    country := sales_facts[country],
    amount := sales_facts[amount]
RETURN
    country,
    revenue := SUM(amount) GROUP BY (country)
FOR
    sale_id := BIND_VALUES(ARRAY(1, 2, 3))
ORDER BY country
;

SUM() establishes an additive result at its output grain. Nothing is added to the query.

FeatureQL classifies aggregate operations in two groups:

• Restricted functions may not consume a NOT ADDITIVE value or collapse a listed ACROSS axis: SUM(), AVG(), and statistical cousins such as variance and correlation.
• COUNT(PER key) checks occurrence but does not consume NOT ADDITIVE. Bare COUNT() is an additive bag of rows whose unit is unlabelled (Unknown).
• Unrestricted functions may consume any contract: MIN(), MAX(), MAX_BY(), MIN_BY(), boolean and bitwise aggregates, COUNT(DISTINCT ...), ARRAY_AGG(), and STRING_AGG().

The operation and its result are separate questions. COUNT(DISTINCT conversion_rate) and ARRAY_AGG(conversion_rate) are legal. SUM(COUNT(DISTINCT ...)) is not. AVG() of a NOT ADDITIVE rate is rejected. Counts are additive later only when disjointness is proved.

V1 transfer for unkeyed idempotent functions (MIN(), MAX(), ANY_VALUE(), boolean and bitwise cousins):

• same grain, no axis removed: preserve the input contract;
• collapsing only ordinary axes of an additive input: the result is additive (SUM(MAX(line_amount)) does not require UNSAFE);
• collapsing a non-additive axis without a matching selector: globally NOT ADDITIVE (no laundering). MAX(DAILY_STOCK) that drops DAY_ID stays non-additive.

MAX_BY(value, key) and MIN_BY(value, key) drop only a matched non-additive axis. An unmatched selector cannot launder an unrelated prohibition.

Contracts travel with the value:

• through ROW, ARRAY, UNNEST, field extraction, and TRANSFORM() field passthrough, including a rename such as SELECT category AS cross_cat; a later SUM of that field is still checked;
• ARRAY_SUM(), ARRAY_AVG(), and ARRAY_PRODUCT() read the element contract and are restricted like their scalar cousins;
• one ARRAY slot cannot mix known contracts (ADDITIVE next to NOT ADDITIVE or DETERMINED BY); put different measures in named ROW fields;
• RELATED(SUM(...)), SUM(...) OVER (...), TRANSFORM(RETURN SUM(...)), VARIANT(), and MACRO() apply the same checks as a top-level SUM.

The LLM help page help('metric_additivity') lists the function-by-function rules.

Aggregates produce typed values

An aggregate result is an ordinary value with its own contract:

• SUM(x) GROUP BY (country, city) is a function of (country, city) and is additive;
• SUM(x) without GROUP BY is one global value;
• AVG(), variance, and correlation produce a rate; the result is NOT ADDITIVE;
• MIN() and MAX() that drop a non-additive axis stay NOT ADDITIVE, because the choice of snapshot differs per group;
• COUNT(DISTINCT x) GROUP BY y is additive across groups only when the compiler can prove that x determines y; otherwise two groups may share members, and the result is NOT ADDITIVE.

A second aggregate consumes the rows the first one produced, at the first one's grain:

DEFINE
    sales := ENTITY(),
    sale_id := INPUT(BIGINT#sales),
    s := INLINE_COLUMNS(
        sale_id BIGINT#sales BIND TO sale_id,
        country VARCHAR,
        city VARCHAR,
        revenue BIGINT
        FROM CSV(
            sale_id,country,city,revenue
            1,FR,Paris,100
            2,FR,Paris,120
            3,FR,Lyon,80
            4,ES,Madrid,200
        )
    ),
    country := s[country],
    city := s[city],
    city_revenue := SUM(s[revenue]) GROUP BY (country, city),
    avg_city_revenue := AVG(city_revenue) GROUP BY (country)
RETURN
    country,
    avg_city_revenue
FOR
    sale_id := BIND_VALUES(ARRAY(1, 2, 3, 4))
ORDER BY country
;

AVG_CITY_REVENUE averages one row per city, not one row per sale. Selecting CITY beside AVG_CITY_REVENUE would be rejected, because CITY is finer than the GROUP BY COUNTRY grain; see Composability and purity .

Keep additivity separate from other semantics

A numeric feature can be dimensionally correct and still produce the wrong aggregate. BIGINT@CUSTOMERS correctly describes a distinct-customer count, but two regional distinct counts may overlap. The unit is correct; summing the values is not.

Additivity is one of the independent properties introduced in Semantic modeling . See Units and measures for dimensional rules and Aggregation multiplicity when a value may repeat on the current row spine.

Declare a summarized source once

Put annotations on source columns inside EXTERNAL_COLUMNS, alongside entity types and BIND TO:

TABLES.AGG_CITY := EXTERNAL_COLUMNS(
    city_id          BIGINT#CITIES BIND TO CITY_ID,
    revenue          DECIMAL,
    conversion_rate  DOUBLE NOT ADDITIVE,
    FROM TABLE(analytics.agg_city)
),
CITY_REVENUE := TABLES.AGG_CITY[revenue],

revenue needs no declaration because it is additive by default. conversion_rate is a precomputed scalar that cannot be combined safely by a summing function, so it is NOT ADDITIVE. Extraction preserves each column's contract; do not repeat the annotation on the derived feature.

DEFINE
    cities := ENTITY(),
    city_id := INPUT(BIGINT#cities),
    agg_city := INLINE_COLUMNS(
        city_id BIGINT#cities BIND TO city_id,
        revenue BIGINT,
        conversion_rate DOUBLE NOT ADDITIVE
        FROM CSV(
            city_id,revenue,conversion_rate
            1,125,0.10
            2,84,0.20
        )
    ),
    city_revenue := agg_city[revenue]
RETURN
    total_revenue := SUM(city_revenue),
    max_conversion_rate := MAX(agg_city[conversion_rate])
FOR
    city_id := BIND_VALUES(ARRAY(1, 2))
;

The example sums additive revenue and applies idempotent MAX() to the non-additive rate. A summing function over conversion_rate would be rejected.

Use NOT ADDITIVE when no scalar summation is valid:

apr  DOUBLE NOT ADDITIVE

The value remains available for display, filtering, idempotent operations, and formulas at its existing grain.

Recompute non-additive metrics from their components

An average carries more information than its displayed scalar. The average of two group averages weights both groups equally, even when one group contains many more rows.

The same problem appears with rates and ratios. Do not sum or average the precomputed scalar. Sum its additive components and calculate the ratio at the target grain:

DEFINE
    cities := ENTITY(),
    orders := ENTITY(),
    city_id := INPUT(BIGINT#cities),
    agg_city := INLINE_COLUMNS(
        city_id BIGINT#cities BIND TO city_id,
        revenue DOUBLE@CURRENCY.USD,
        orders BIGINT@ORDERS
        FROM CSV(
            city_id,revenue,orders
            1,10.0,1
            2,20.0,4
        )
    ),
    city_revenue := agg_city[revenue],
    city_orders := agg_city[orders]
RETURN
    revenue_per_order := SUM(city_revenue) / SUM(city_orders)
FOR
    city_id := BIND_VALUES(ARRAY(1, 2))
;

The two city rates are 10 / 1 = 10 and 20 / 4 = 5. Averaging those rates would produce 7.5; summing the components produces the correct country rate, (10 + 20) / (1 + 4) = 6.

This pattern gives each underlying order the intended weight:

REVENUE_PER_ORDER :=
    SUM(CITY_REVENUE) / SUM(CITY_ORDERS)

FeatureQL deliberately does not hide numerator, denominator, (sum, count), or sketch state behind a scalar. If the components are unavailable, aggregate from detail instead.

Similarly:

• recompute AVG() from detail or SUM(total) / SUM(count);
• recompute COUNT(DISTINCT ...) from detail or an explicitly mergeable sketch;
• recompute percentiles and medians from detail;
• treat custom consolidations as new formulas at the target grain.

Mark semi-additive axes

Some metrics are additive across ordinary business dimensions but not across time. Inventory is the classic example: warehouse balances can be added for one day, but adding Monday's balance to Tuesday's balance double-counts stock.

Declare the non-additive axis:

PRODUCTS := ENTITY(),
DAYS := ENTITY(),
PRODUCT_DAY_KEY := INPUT(
    ROW(product_id BIGINT#PRODUCTS, day_id DATE#DAYS)
),

INVENTORY := EXTERNAL_COLUMNS(
    product_day_key ROW(
        product_id BIGINT#PRODUCTS,
        day_id DATE#DAYS
    ) BIND TO PRODUCT_DAY_KEY,
    daily_stock BIGINT
        NOT ADDITIVE ACROSS (product_day_key[day_id]),
    FROM TABLE(supply.inventory)
)

NOT ADDITIVE ACROSS (product_day_key[day_id]) states both halves: the value is not additive when the day field of the composite key collapses and is additive everywhere else. Filtering that field chooses which snapshots enter the query. Keeping it in the output grain is also safe.

Choose the snapshot explicitly when time disappears from the result:

DEFINE
    products := ENTITY(),
    days := ENTITY(),
    product_day_key := INPUT(ROW<product_id BIGINT#products, day_id BIGINT#days>),
    inventory := INLINE_COLUMNS(
        product_day_key ROW(product_id BIGINT#products, day_id BIGINT#days) BIND TO product_day_key,
        daily_stock BIGINT NOT ADDITIVE ACROSS (PRODUCT_DAY_KEY[DAY_ID])
        FROM JSON([
            {"product_day_key": {"product_id": 1, "day_id": 1}, "daily_stock": 10},
            {"product_day_key": {"product_id": 1, "day_id": 2}, "daily_stock": 12},
            {"product_day_key": {"product_id": 2, "day_id": 1}, "daily_stock": 4},
            {"product_day_key": {"product_id": 2, "day_id": 2}, "daily_stock": 5}
        ])
    ),
    product_id := product_day_key[product_id],
    day_id := product_day_key[day_id],
    daily_stock := inventory[daily_stock]
RETURN
    product_id,
    latest_stock := MAX_BY(daily_stock, day_id) GROUP BY (product_id)
FOR
    product_day_key := BIND_VALUES(
        ARRAY(ROW(1, 1), ROW(1, 2), ROW(2, 1), ROW(2, 2))
    )
;

In the runnable example, DAY_ID := PRODUCT_DAY_KEY[day_id] names the same axis used by the declaration. MAX_BY(DAILY_STOCK, DAY_ID) selects the value associated with the latest day for each product. FeatureQL validates the choice but does not silently inject LAST, MAX_BY(), or another selector. The inferred result no longer carries the resolved day prohibition and remains constrained by any other non-additive axes.

The other way to remove an axis safely is to fix it: a FILTER (WHERE day_id = ...) that compares the axis with a single value keeps only one snapshot per product, so nothing is added across days:

DEFINE
    snapshots := ENTITY(),
    snapshot_id := INPUT(BIGINT#snapshots),
    days := ENTITY(),
    s := INLINE_COLUMNS(
        snapshot_id BIGINT#snapshots BIND TO snapshot_id,
        product VARCHAR,
        day_id DATE#days,
        stock BIGINT NOT ADDITIVE ACROSS (DAY_ID)
        FROM CSV(
            snapshot_id,product,day_id,stock
            1,a,2024-02-01,10
            2,a,2024-02-02,12
            3,b,2024-02-01,5
            4,b,2024-02-02,7
        )
    ),
    stock := s[stock],
    day_id := s[day_id]
RETURN
    latest_total := SUM(stock) FILTER (WHERE day_id = DATE '2024-02-02')
FOR
    snapshot_id := BIND_VALUES(ARRAY(1, 2, 3, 4))
;

The compiler accepts an equality with a literal or a bound parameter. A comparison between two row-level values (day_id = ship_day) does not fix the axis, because the chosen day still varies per row.

A metric may forbid more than one axis:

value BIGINT NOT ADDITIVE ACROSS (day_id, scenario_id)

The axes form a set. FeatureQL does not invent an order for applying several selectors; write the target-grain formula explicitly.

ACROSS accepts a key feature such as DAY_ID or a source column inside EXTERNAL_COLUMNS. It names that key, not the entity itself, which avoids ambiguity when the same entity appears more than once:

order_date  DATE#DAYS,
ship_date   DATE#DAYS,
balance     DECIMAL NOT ADDITIVE ACROSS (ship_date),

The entity annotation is what lets the compiler connect a derived key such as MONTH(ship_date) back to the declared axis. A key without an annotation is still a valid axis, but only the key itself is recognised in a result grain.

A value may carry a determinant and an axis at the same time; the two facts are independent and may be written in either order:

daily_stock BIGINT DETERMINED BY (product_id, day_id) NOT ADDITIVE ACROSS (day_id)

When both are declared, each ACROSS axis must be one of the DETERMINED BY keys: the axis is a component of the value's identity. Naming a key outside the declared grain (for example a fiscal year derived from day_id) is rejected with SUMMARIZABILITY-AXIS-NOT-IN-GRAIN; spell the axis with the grain key instead.

Collapse is about coarsening, not about whether a related name is present. GROUP BY MONTH(day_id) collapses day_id: Monday is added to Tuesday. The declared axis survives only when the result grain keeps it at the declared granularity or finer — the key itself, or a compiler-traced ancestor such as hour_id when day_id := DATE_TRUNC(hour_id, 'day'). An unlinked grain such as week_id versus day_id is treated as collapse. Declare ACROSS on the grain you actually group by, or keep the declared key in the result.

The Supply inventory tutorial shows why balances are semi-additive.

Mark values repeated below their determinant

A denormalized fact may store a value on rows finer than the key that determines it:

TABLES.FCT_ORDER_LINES := EXTERNAL_COLUMNS(
    line_id      BIGINT#LINES BIND TO LINE_ID,
    order_id     BIGINT#ORDERS,
    line_amount  DECIMAL,
    order_total  DECIMAL DETERMINED BY (order_id),
    FROM TABLE(sales.fct_order_lines)
),

Summing order_total at line grain multiplies each total by its line count. DETERMINED BY rejects that aggregation but does not rewrite it. Choose one contribution per determinant key explicitly:

DEFINE
    lines := ENTITY(),
    orders := ENTITY(),
    line_id := INPUT(BIGINT#lines),
    order_lines := INLINE_COLUMNS(
        line_id BIGINT#lines BIND TO line_id,
        order_id BIGINT#orders,
        order_total BIGINT DETERMINED BY (ORDER_ID)
        FROM CSV(
            line_id,order_id,order_total
            1,10,30
            2,10,30
            3,20,20
        )
    ),
    order_id := order_lines[order_id],
    order_total := order_lines[order_total]
RETURN
    total := SUM(order_total)
    FILTER (WHERE order_lines[line_id] IS NOT NULL AND order_id IS NOT NULL) PER order_id
FOR
    line_id := BIND_VALUES(ARRAY(1, 2, 3))
;

The clause accepts composite keys such as DETERMINED BY (order_id, product_id).

Unlike NOT ADDITIVE, determinant evidence is testable. A CI check can count determinant keys that map to more than one declared value; any match disproves the declaration. Duplicate rows carrying the same value are valid and are why PER key is still required during aggregation.

BIND TO does not prove determinant or row uniqueness. A fact source may legally repeat a bound key.

A false DETERMINED BY plus PER key still contributes each distinct (key, value) pair — it does not pick an arbitrary row and does not scan the warehouse to prove uniqueness. Review the declaration; do not treat a green query as proof that the determinant is true. Use PER ROW when every current-bag occurrence should contribute.

Annotations attach only to EXTERNAL_COLUMNS columns and INPUT(...). A derived formula such as X := Y + Z NOT ADDITIVE is rejected. Extraction aliases inherit the source contract.

DETERMINED BY records metric evidence; the PER key operation belongs to Aggregation multiplicity . One cannot replace the other.

Understand UNKNOWN and unsafe operations

FeatureQL stays quiet while an unknown contract is merely displayed or filtered. It warns when a summing function consumes an UNKNOWN derived contract.

UNKNOWN is never a source default. It arises only in a derived feature when inference has no sound transfer rule or two contracts are genuinely irreconcilable. Functions such as POW(), SQRT(), and similar transforms stay UNKNOWN on purpose: V1 does not invent a transfer rule for them. The compiler warns if a summing function consumes that result; it does not silently treat the value as additive.

Inference uses the least-permissive known result whenever possible:

• MAX_BY(DAILY_STOCK, DAY_ID) removes the matched DAY_ID prohibition and preserves every other restriction;
• BALANCE + APR, where APR is NOT ADDITIVE, is known NOT ADDITIVE rather than UNKNOWN;
• unsupported formula inference becomes UNKNOWN and warns only when a summing function consumes it.

Three warnings cover the cases where a proof is missing rather than failed. They do not stop the query:

A query can carry SELECTION-ARBITRARY and still run. Persisting such a feature is rejected (PERSIST-TIE-SENSITIVE-ORDER): a persisted feature must stay a deterministic function of its inputs.

When a rejection message says NOT ADDITIVE, it also says why: the value is a rate (AVG()), a non-additive axis was collapsed by MIN()/MAX(), or two distinct counts may overlap. The reason tells you which recomputation to write.

When a summing operation is intentional despite a known restriction, attach UNSAFE to that operation:

FORCED_TOTAL := UNSAFE SUM(CITY_CONVERSION_RATE)

UNSAFE is a parser modifier on the ordinary aggregate, not an UNSAFE_SUM() function and not a reclassification of CITY_CONVERSION_RATE. It bypasses only the additivity violation at that operation. It does not replace PER, satisfy DETERMINED BY, or bypass type and unit checks.

The aggregate result and every downstream dependent remain permanently unsafe. The taint stays visible in SHOW FEATURES, DESCRIBE, and EXPLAIN — Tools in the query (DESCRIBE()), Statement tools (EXPLAIN).

Inspect and change a metric contract

Persisted features keep their computed metric contract. Request the fields when you need them:

SHOW FEATURES (
    COLUMNS (
        NAME,
        SUMMARIZABILITY_STATUS,
        ADDITIVITY,
        NON_ADDITIVE_AXES,
        DETERMINED_BY,
        SUMMARIZABILITY_UNSAFE
    )
)
WHERE NAME LIKE 'FM.FINANCE.%';

The fields separate provenance from meaning:

• SUMMARIZABILITY_STATUS is INFERRED, DECLARED, or UNKNOWN;
• ADDITIVITY is ADDITIVE, SEMI_ADDITIVE, NOT_ADDITIVE, or UNKNOWN;
• NON_ADDITIVE_AXES lists the keys named by ACROSS;
• DETERMINED_BY lists the determinant keys;
• SUMMARIZABILITY_UNSAFE records whether an UNSAFE aggregate exists in the feature's lineage.

Use EXPLAIN (FIELDS (FEATURES)) to inspect inferred contracts and understand where a known contract became UNKNOWN — EXPLAIN .

Replacing a persisted feature recomputes its dependent frontier. If changing an additive source to NOT ADDITIVE would invalidate a summing dependent, FeatureQL rejects the replacement atomically. A change that only touches the metric contract re-checks the dependents; it does not reject a dependent that still type-checks under the new contract. Preview the change with:

EXPLAIN (FIELDS (MUTATION_DRY_RUN))
CREATE OR REPLACE FEATURES AS
PERSIST
    FM.FINANCE.CITY_REVENUE :=
        INPUT(DOUBLE@CURRENCY.USD) NOT ADDITIVE
;

Metric annotations and the UNSAFE modifier are erased before SQL emission. They validate semantics; they do not add a SQL operation. PER, by contrast, changes row contribution and therefore may change generated SQL.

Reuse finer materializations safely

The same proof that validates summation can support aggregate-aware optimization.

Suppose REVENUE is materialized at a finer grain and a query requests it at a compatible coarser grain. A planner can reuse that materialization when it proves:

• the source is additive across the collapsed axes;
• no non-additive axis is removed;
• filters, variants, inclusion rules, units, freshness, and access policy are compatible;
• neither the candidate nor the query path is UNKNOWN or unsafe.

Pure feature formulas provide stable lineage for comparing the requested metric with available materializations. The metric contract proves that using the finer aggregate preserves meaning.

Filter provenance is outside the metric annotation syntax. A materialization built with a hidden predicate such as status = 'completed' is not eligible until a separate materialization signature proves that its canonical filter is compatible with the query.

Cross-entity hierarchy reuse is also deferred. V1 emits no warning merely because an ordinary aggregation groups through RELATED().

Additive sums are the first candidates for this optimization. Averages, ratios, distinct counts, percentiles, and custom metrics require recomputation from detail/components or deliberately stored mergeable state.

What V1 deliberately does not check

These boundaries are product rules, not unfinished enforcement:

• Unannotated numerics are additive. UNKNOWN is never a source default. Field access (row[field]) of an unstamped numeric is additive, same as INPUT. Review aggregate-looking warehouse columns and mark exceptions at the mapping. See the caution above.
• Grain is assumed, not scanned. DETERMINED BY is opt-in evidence. FeatureQL does not inspect warehouse rows for uniqueness. A false declaration plus PER key contributes each distinct (key, value) pair. BIND TO does not prove uniqueness either.
• Hybrid SQL is a language boundary. FEATUREQL(...) is the checked island. An outer SQL SUM over that result is raw SQL and is not re-checked. See Hybrid queries .
• JSON and string reconstruct a number, not a measure. CAST(... AS JSON), JSON_EXTRACT*(), and CAST(... AS DOUBLE) from text build a new unannotated numeric. They cannot carry a FeatureQL contract, just as they cannot carry # or @. Keep rollups in FeatureQL types; do not round-trip a metric through JSON to preserve meaning.
• Windows do not require PER. OVER (...) keeps the current rows (broadcast). Multiplicity is a separate question from additivity; window SUM of a NOT ADDITIVE rate is still rejected. See Aggregation multiplicity .
• One diagnostic per validation. FeatureQL fails fast on the first UserException. Independent faults in the same query are reported on later repairs, not collected into one list.
• Missing transfer is loud, not guessed. No V1 rule means UNKNOWN plus a warning at a restricted site — not a silent additive default. UNSAFE is the explicit override.

Choose the right declaration

Next

• Semantic modeling — see all semantic axes together
• Units and measures — describe dimensional meaning
• Aggregation multiplicity — control contribution per key or per row
• Composability and purity — keep definition grain and population stable when features are reused
• Grouping & Unnesting — change result granularity
• Supply inventory — build a semi-additive snapshot
• Financial consolidation — apply target-grain eliminations