skip to content

Should a derived metric live as a mart column or as a semantic-layer definition?

level: seniorimportance: should knowfreq 44%

answer

  1. can this number be summed safely?
  2. store the ingredients, define the recipe
  3. never average a stored rate
  4. freeze what must not change
  5. expensive logic materialised below reporting grain

basics

~20 s

Materialise additive components in the mart and define the metric over them in the semantic layer. Store a computed metric in a column only when it must be frozen, is too expensive to compute at read time, or is consumed by tools that cannot reach the layer.

solid answer

~50 s

The default is **components in the mart, definition in the layer**. Store the additive raw ingredients — gross amount, discount, refund, event counts — as columns, and express the metric as an aggregation over them. That keeps the metric sliceable by any dimension, and a change to the definition takes effect everywhere without a backfill. Materialise the computed value instead when one of three things is true: it must be **frozen** (a figure filed with regulators or agreed with finance cannot silently change when the definition improves), it is **too expensive** to recompute per query, or it is consumed by **tools that cannot reach the semantic layer**. The trap is ratios. A rate stored per row cannot be averaged up — `AVG(margin_rate)` weights a one-dollar order the same as a million-dollar one. Store the numerator and denominator and let the layer compute `SUM(n) / SUM(d)` at whatever grain the user asked for.

code

sql · 16 lines
sql
-- Store the additive ingredients, not the rate
CREATE TABLE mart_finance.fct_order_line (
  order_line_key  BIGINT        NOT NULL,
  order_date      DATE          NOT NULL,
  region          VARCHAR(40)   NOT NULL,
  gross_amount    DECIMAL(18,2) NOT NULL,
  margin_amount   DECIMAL(18,2) NOT NULL
);

-- Wrong: unweighted average of a stored rate
SELECT region, AVG(margin_amount / gross_amount) AS margin_rate
FROM mart_finance.fct_order_line GROUP BY region;

-- Right: ratio of sums, correct at any grain
SELECT region, SUM(margin_amount) / SUM(gross_amount) AS margin_rate
FROM mart_finance.fct_order_line GROUP BY region;

go deeper

for a junior

Know that a rate or percentage stored per row cannot simply be averaged to a higher level, and that storing the numerator and denominator separately is the safe pattern.

for a middle

Explain the default — additive components in the table, metric defined over them — and what changing a stored derived column costs: a code change plus a backfill of history.

for a senior

Argue the exceptions convincingly: frozen figures, expensive computations, consumers outside the layer, and logic the layer cannot express. Show the compromise of materialising below reporting grain.

for a principal

Own the policy: a stated rule for which metrics may be materialised and who approves it, so the mart does not accumulate forty computed columns that quietly disagree with the layer's definitions.

## The question behind the question Every derived metric has to be computed somewhere. The choice is whether the computation happens **when the table is built** (a stored column) or **when the question is asked** (a semantic-layer definition). Interviewers ask this because the wrong choice produces either wrong numbers or an unmaintainable model, and the reasoning generalises well beyond metrics. ## Default: additive components stored, metric defined Store in the mart the raw, additive quantities: `gross_amount`, `discount_amount`, `refund_amount`, `session_count`, `converted_count`. Define in the layer the thing people ask for: net revenue, margin rate, conversion rate. This default wins on three counts: - **Sliceability.** The layer aggregates the components at whatever grain the user picked — by region, by month, by product line, by all three — and the metric is correct at every one of them. A stored value is correct only at the grain it was computed for. - **Changeability.** When the definition improves, you edit one definition. A stored column requires a code change plus a backfill of history, and until the backfill lands, part of the table means one thing and part means another. - **Auditability.** The components are inspectable. If net revenue looks wrong, you can see which ingredient moved. ## The ratio trap This is the part candidates get wrong, and it is the most valuable thing to say out loud. A **ratio is not additive**: you cannot sum it, and averaging it is almost always wrong because it weights every row equally. ```text region gross margin margin_rate EMEA 1000 100 0.10 APAC 1 0.5 0.50 AVG(margin_rate) = 0.30 <- meaningless SUM(margin)/SUM(gross) = 0.1005 <- the actual rate ``` The one-dollar APAC row drags an averaged rate from 10% to 30%. Store `margin` and `gross` as columns; define `margin_rate` as `SUM(margin) / SUM(gross)` in the layer, and it is right at every grain automatically. Storing `margin_rate` per row is only safe if nobody ever aggregates it, which is not a property you can enforce. The same applies to averages, percentages, per-unit prices and any weighted figure. ## When to materialise the computed value anyway **It has to be frozen.** A number filed with a regulator, agreed with the auditor, or published in a board pack must not change when someone improves the definition next quarter. Materialise it with the definition version stamped alongside, and treat it as a record rather than a calculation. **Reproducing it at read time is expensive.** A metric involving a window over years of history, a sessionisation pass, or an attribution model is not something to recompute for every dashboard refresh. Compute it once in the pipeline and store the result — with the caveat below. **Consumers cannot reach the layer.** An exported extract, an external partner's feed, a legacy tool connecting straight to the database — those need the number in a column, because there is no layer in their path. **The logic is genuinely not expressible in the layer.** Multi-step procedural logic, statistical scoring, or anything requiring row-by-row state belongs in the transformation pipeline. The layer is for aggregation over declared joins, not for arbitrary computation. ## The compromise that usually applies Materialise the expensive intermediate at a grain **finer than anyone reports on**, and define the user-facing metric over it. An attribution model that is costly to run can emit an attributed-credit column per order line; the layer then sums that column by whatever dimensions the user picked. You pay the expensive computation once, and you keep the sliceability and the single definition. What you must avoid is materialising at the *reporting* grain and then discovering nobody can drill. ## The costs of over-materialising Every stored derived column is a definition frozen at build time. It needs a backfill to change, it can silently disagree with the layer's version of the same metric, and it multiplies as each request adds another column. A mart with forty computed columns and no clear rule about which is authoritative is exactly the mess a semantic layer was introduced to prevent. ## In an interview State the default, then the exceptions, then the ratio trap — that last one is the discriminator, because it is a correctness argument rather than a preference. If you are asked to decide for a specific metric, ask two questions first: is it additive, and does anyone need it frozen?

  • A mart stores conversion_rate per campaign per day. What breaks when someone rolls it up to the month?
    Averaging daily rates weights every day equally, so a day with ten sessions counts as much as a day with a million. The monthly figure will not match the true monthly conversion rate. The fix is to store session_count and converted_count and define the rate as SUM(converted) / SUM(sessions), which is correct at any grain the user picks.
  • How do you keep a regulatory figure reproducible when the metric definition later improves?
    Materialise the filed figure as a record, stamped with the definition version and the run date, and never recompute it. The live semantic-layer metric goes on improving for operational use. You then hold both — the number as filed and the number as currently defined — and can explain the difference, rather than having history silently restate itself.
  • An attribution model is too expensive to run per query. Where should it be computed?
    In the pipeline, but emitted at a grain finer than anything anyone reports on — attributed credit per order line, say — not at the reporting grain. The layer then sums that column by whichever dimensions are requested. You pay for the expensive logic once and still keep sliceability and a single user-facing definition.

saying these in an interview costs you the question

  • Stores a ratio per row and lets users average it
  • Materialises every derived metric to make dashboards fast
  • Assumes a stored column is correct at every aggregation grain
  • Forgets that changing a stored metric needs a history backfill
  • Puts frozen regulatory figures in an editable layer definition

context

resolving links…