Derived metrics (D-DERIVE)

Where: a Metric's custom_extensions. Payload key: derive, a JSON object with a type discriminant — plus the window key's base field (M2, below). Default (absent): the metric is self-contained, no structure metadata. Supported forms: M1 ships filter + compose; M2 adds window.base and compose-with-window-members; 1.10 adds lod (D-LOD, documented with the rest of D-LOD). reagg and cross-dataset filter predicates are reserved and reject not_implemented with a hint naming their replacement or milestone. A metric re-aggregated at a coarser grain, such as a share of the total or of a parent group, is written today as a compose over a denominator: a fixed / exclude level of detail for any metric, or a whole-partition window for one that adds up across the grouping removed. See Shares of a total.

Declares how a metric derives from other metrics of the same model — structure Ossie expressions cannot carry. Two degradation tiers: on the flatten tier (filter, compose over leaves/filter members) the metric's expression is a legal, semantically equivalent Ossie aggregate — basic mode computes it and gets the same numbers, and the engine enforces the equivalence at compile time (derive_expression_mismatch); on the stage tier (window.base, compose with window members) no flat expression can express the semantics, so the expression is only the nearest approximation and basic mode legitimately diverges (with the ignored-extension warning). Either way, treat the expression as tool-maintained output, not something to edit by hand. When it does not match, the derive_expression_mismatch error carries the expansion as expected_expression (validate's JSON output), ready to be written in place by a tool — or pasted by hand.

metrics:
  - name: net_revenue
    expression:
      dialects: [{ dialect: ANSI_SQL, expression: "COALESCE(SUM(orders.amount), 0) - COALESCE(SUM(refunds.ref_amount), 0)" }]
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.4", "derive": {"type": "compose", "expr": "revenue - total_refunds", "fill": 0}}'
  # Reuses net_revenue; `expression` is the expansion, so a change to
  # net_revenue's declaration breaks this metric loudly.
  - name: net_revenue_per_order
    expression:
      dialects: [{ dialect: ANSI_SQL, expression: "(COALESCE(SUM(orders.amount), 0) - COALESCE(SUM(refunds.ref_amount), 0)) / COUNT(orders.order_id)" }]
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.4", "derive": {"type": "compose", "expr": "net_revenue / order_count"}}'

Filter, compose and lod

• filter — {"type":"filter","base":<metric>,"where":<predicate>}: a dimensional-filter variant of base. The predicate folds into the aggregate argument (CASE WHEN pred THEN arg END; COUNT(*) and a count of a primary-key column fold to THEN 1, while a count of any other column keeps it, since THEN 1 would also count the rows where it is NULL), never a WHERE — non-matching time buckets keep existing (value NULL), which is what makes the variant safe under ROWS-framed windows. The predicate reads the base's own dataset, or — since 1.12, S-ATTR-3′ — one dimension that dataset reaches by a unique many→one path (where: "customers.premier_customer = TRUE" on a metric over orders; a count of the primary key then keeps its column so the aggregate still names its home); when base is guarded already, the predicate may read that same dimension (the two guards nest) but not another; any other cross-dataset predicate is not_implemented until M4. base is a plain non-derived single aggregate. The engine records the subset relation (subset_of) in metric metadata.
• compose — {"type":"compose","expr":<arithmetic>,"fill":<n>}: scalar arithmetic over metric names ("revenue - total_refunds"). Identifiers resolve strictly to metric names — a qualified or unknown name is an error with candidates, never a silent column read. Aggregate calls are rejected (composition never re-aggregates). fill replaces a member's NULL after branch alignment, before the arithmetic (the cross-fact "missing bucket poisons the sum" fix). A filled division of two members is cast to DOUBLE and its filled divisor wrapped in NULLIF(…, 0), so a missing denominator reads NULL rather than a division error; the plain authored arithmetic still passes the equivalence check, and the guarded form is what runs. A compose with no time_dimension of its own buckets each member by the member's: its declared time_dimension, else its dataset's primary time — never another dataset's date. The engine detects linear combinations (Σ cᵢ·metricᵢ), records members + coefficients + the leaf-measure decomposition, and fixes the conformed dimension set (dimensions every member branch reaches, under the unique-join-path rule, plus metric_time when the members share one time axis or each resolves its own — S-TIME-5) at compile time — dosi list exposes it, and a query grouping outside it gets unconformed_dimension naming the member that misses the dimension. Members sharing no dimension warn compose_no_conformed_dimensions at compile time. Attribution metadata is labeled exact only for linear composes over SUM/COUNT-tier measures (a filtered COUNT(DISTINCT) overlaps its complement — never exact).
• lod — {"type":"lod","expr":<level of detail>} declares an include, exclude or fixed metric. Arithmetic over these metrics uses compose. The core expression carries the required nested aggregate representation; basic mode fails closed. See the unified LOD declarations and expression rules.

Window bases and window members

Since M2 a derived metric can also sit on, or read, a window stage:

• window.base — the window key (D-WINDOW) accepts "base": <metric>: the window computes over the named metric's series instead of this metric's own expression. The base may be a leaf, a filter metric, a ratio (1.9), a compose — whether its measures sit in one dataset or several — or a compose over another window's output (1.9). A cross-fact base's series only becomes a value after the branch merge, so the stage sits on the merge instead of on one aggregate. Rejected: naming a window metric directly (the inner window would have no stage of its own to sit on — wrap it in a compose, which gives it one). Metadata records derive_family: "window", the derive_base chain and the inherited subset_of. Windows over a filter base inherit the CASE-WHEN bucket preservation: buckets with no matching rows still exist, so ROWS frames stay calendar-correct and cumulative sums skip the NULLs.

A ratio or compose base is what makes "rank the markets by close rate", "this rate versus last month's" and "am I above the average rate" expressible — none of which a single aggregate can state.

Nested windows (1.9) go one step further: a window reading a compose over another window's output is a second level, lowered as one more SQL stage. The scorecard shape needs it — rank the markets within a brand, then weight the scores within a market — because the two comparisons partition in different directions and one OVER has one PARTITION BY. This is not the re-aggregation I-1 bans: the row count is unchanged, each level recomputes with the query, and the weighted value lands on every row rather than folding them away. Levels are capped at 3, and past the first one partition must be declared explicitly — the query_dimensions default reads clearly on a single stage, but stacked, "which dims, minus whose exclusions" is no longer answerable from one declaration. Note what the window does and does not average: a whole-partition avg over a ratio base is the unweighted mean of the per-row rates, which is not the pooled ratio of sums. Both are legitimate and they differ; the window is the one that says "mean of the rates". - compose with window members — a compose whose expr references window metrics runs the arithmetic over the window output ("cumulative AOV" = revenue_cum / order_count_cum: numerator and denominator each accumulate, then divide). Members may mix window metrics with leaves; all members share one window stage, so they must share a time axis (window members over different facts do when the facts' dates are paired by D-CONFORM). Attribution is always approximate and there is no leaf-measure decomposition — window output cannot be served from materialized aggregates. - filter members in compose — a compose member may be a filter metric ("new_order_count / order_count"); it composes on the flatten tier exactly like a leaf.

Composes inside composes

Compose members that are themselves composes are expanded inline at compile time: the compiled artifact is flat, exactly as if the inner arithmetic had been written out. That is what lets a formula be stated once and reused — and, because the outer's expression is checked against the expansion of the inner's declaration, editing the inner and leaving a stale copy in a consumer is a derive_expression_mismatch rather than a number nobody questions.

Four consequences worth knowing before you nest:

• The expression is the full expansion. Write out the whole flattened arithmetic, including the parentheses the nesting implies and the CAST(… AS DOUBLE) an inner bare-ratio member compiles to. It is tool-maintained output — regenerate it from the declaration rather than editing it by hand.
• fill pushes down to the leaves. Each leaf reference takes the innermost fill declared on its path, so an outer fill: 0 still repairs a missing branch inside a member that declared none. The decision is per reference: one leaf reached through two members that fill it differently keeps both.
• derive_members is the flattened leaf list on the flatten tier, with coefficients folded across the levels (quadrupled = doubled × 2 over doubled = revenue × 2 reports revenue at 4). Conformed dimensions intersect along the DAG, and unconformed_dimension therefore names the leaf that misses the dimension, not the intermediate compose. dosi lineage still draws the edge to the member you wrote.
• On the stage tier a compose member stays a member. A window anywhere in the tree puts the metric on the stage tier, and there inlining would be wrong: each member is its own series out of the window stage, so dissolving an intermediate compose into leaves would lose arithmetic the stage never projects (cum_aov / revenue would lose the division and become two window members over a leaf). derive_members is therefore the DIRECT members there, the planner computes each nested compose as a hidden stage output, and substitution runs innermost-first at lowering. The tier decision reads the whole tree, so a window on an intermediate compose stages everything above it too.
• Nesting is capped at 256 member references in one expansion — an inlined member is substituted at every occurrence, so referencing one twice doubles the expansion per level.

Cycles and metadata

Cycles among metric references are metric_reference_cycle with the full path. Metadata surfaces on every host (dosi list, REST, MCP, Python): derive_family, derive_base, derive_expr, subset_of, derive_members, leaf_measures, attribution, conformed_dimensions, and required_dimensions — the dims a query has to group by, gathered over the whole chain: a window's partition.exclude (window-extension.md#frozen-degrees) and the dims a keyed exclude or fixed names (D-LOD). requires_time_axis says whether the query also needs the time axis at a grain. A lod metric's derive_expr is its declaration in canonical spelling.