skip to content

When a metric definition changes, do you restate history in the fact table or apply it going forward?

level: seniorimportance: should knowfreq 46%

answer

  1. who already acted on the old numbers?
  2. internally consistent versus reproducible
  3. a step in the chart that isn't a business event
  4. as reported and as adjusted
  5. the delta table comes before the decision

basics

~20 s

It depends on whether previously published numbers must remain reproducible. Restating makes the whole series internally comparable but rewrites numbers the business already acted on. Applying it going forward preserves the record but creates a break in the series. Publishing both measures side by side avoids choosing.

solid answer

~50 s

Two regimes, and the choice is governance rather than engineering. **Restating** recomputes all history under the new rule: trends stay internally consistent and nobody has to remember a cutover date, but every previously published report and board pack now disagrees with the warehouse — unacceptable where numbers were filed, audited, or used to pay people. **Going forward only** keeps the historical record reproducible but puts a discontinuity into every year-over-year comparison, which is worse than useless if it is not clearly labelled. The mature answer is usually neither alone: publish both measures under distinct names (`revenue_reported` and `revenue_restated`) so the series can be viewed either way, keep an immutable "as reported" snapshot of what was published, and label the effective date of the definition in the catalog. Whichever you choose, communicate before you ship — a number moving without notice destroys trust faster than the wrong number does.

code

sql · 7 lines
sql
SELECT period_month,
       SUM(revenue_old_rule)                        AS as_reported,
       SUM(revenue_new_rule)                        AS restated,
       SUM(revenue_new_rule) - SUM(revenue_old_rule) AS delta_abs
FROM fct_revenue_dual_rule
GROUP BY period_month
ORDER BY period_month

go deeper

for a junior

Understand that changing how a metric is calculated can change numbers people already saw. Know the two options — recompute history, or start from a date — and that someone must be told either way.

for a middle

Explain the tradeoff concretely: internal comparability versus reproducibility of published reports, and how carrying both measures under distinct names sidesteps the choice.

for a senior

Demonstrate process: quantify the delta by period before deciding, check for filed or compensation-bearing numbers, keep an as-reported archive, and label the cutover where consumers will actually see it.

for a principal

Own metric governance — who is allowed to change a definition, what evidence and sign-off a restatement requires, and how the platform keeps historical outputs reproducible without freezing the model.

## What the question is really asking A metric definition change means the rows are the same but the business rule that turns them into a number moved: revenue now excludes intercompany sales, an active user now needs two sessions instead of one, churn now measures a 30-day rather than a 60-day window. The engineering is trivial — change the transformation. The decision is what happens to the numbers you already published. ## Option A — restate history Recompute all of history under the new rule so that the entire series reflects one definition. **In favour:** every comparison in every dashboard is apples-to-apples; nobody has to remember a cutover; a single definition lives in one place. When the old rule was simply *wrong* — a bug, a mis-joined dimension, a filter that let test accounts through — restating is usually the correct and honest answer. **Against:** last quarter's board pack no longer matches the warehouse. Anyone who reconciles a saved PDF against a live dashboard finds a discrepancy and reports a data incident. Where numbers were filed with a regulator, used to calculate commission, or committed to in a contract, silently rewriting them is not an option at all. ## Option B — apply it going forward Leave history alone and let the new rule take effect from a date. **In favour:** the historical record is preserved and reproducible; nothing anyone published stops being true. **Against:** a discontinuity is now baked into every trend. A year-over-year comparison spanning the cutover compares two different metrics, and unless the chart shows the break, users will read the definitional step-change as a business event. "Growth of 8%" that is actually a definition change is a serious failure of a data team. ## Option C — carry both, which is what good teams do The dichotomy is usually false. Materialise both measures for the overlapping period under distinct names, and let consumers choose: - `revenue_as_reported` — the rule in force at the time, never rewritten. - `revenue_current_definition` — every period recomputed under today's rule. This costs one extra column and buys you the ability to answer both "what did we say?" and "what would it have been?" without argument. Finance recognises this pattern immediately: it is the difference between *as reported* and *as adjusted*. A related device is an immutable snapshot of published outputs — an archive table holding the numbers exactly as they went out each period, written once and never recomputed. That archive, not the live mart, is what you reconcile a historical report against. ## Dimension history is a separate axis, and people conflate them Even with a fixed metric definition, a report can change because a *dimension attribute* changed — a customer moved region, a product was recategorised. Whether last year's report should show last year's region or today's region is a modelling decision about how the dimension keeps history and how the fact joins to it, and it is orthogonal to metric restatement. Interviewers respect a candidate who separates the two: "the definition of revenue changed" and "the customer's segment changed" produce the same symptom — last year's number moved — with entirely different remedies. ## The process around the decision 1. **Name an owner.** Metric definitions belong to a business owner, not to the person editing the transformation. 2. **Quantify the impact first.** Run both rules over the same history and produce a period-by-period delta before anyone approves anything. "It moves Q3 by 0.4%" and "it moves Q3 by 19%" are different conversations. 3. **Check for hard constraints.** Filed, audited, contractual or compensation-bearing numbers usually forbid restatement outright. 4. **Announce with a date and a delta table.** Put the effective date in the catalog next to the column definition, not only in a chat message that scrolls away. 5. **Label it in the BI layer.** A vertical marker on the chart at the cutover, or a footnote naming the definition version, prevents the discontinuity from being misread as business performance. ## The short version for an interview Say: it is a governance decision, not a pipeline decision; restate when the old rule was wrong and nothing external depends on the old numbers; go forward when the historical record must stand; publish both when you can afford it; and never move a number without telling the people who use it.

  • What makes restatement clearly the right call rather than a judgment call?
    When the old number was simply wrong — a bug, a bad join, test accounts leaking through a filter. Preserving a defective series has no value; you restate, publish the corrected history with a delta by period, and tell every consumer what moved and by how much. The judgment only appears when both definitions were defensible.
  • How do you stop a definition cutover from being misread as a business trend?
    Mark it in the presentation layer: a labelled break on the time axis at the effective date, a footnote naming the definition version, and, where possible, both series drawn over the overlap period. Publish a period-by-period delta so anyone can see how much of a change is definitional. A note in a chat channel is not a control.
  • Why keep an immutable archive of published numbers separate from the mart?
    Because the mart is recomputed and the archive is not. When someone reconciles a nine-month-old report against today's dashboard, the archive tells you exactly what was published and when, which turns an argument into a lookup. It also gives you the "as reported" side of any future restatement for free.

saying these in an interview costs you the question

  • Restates history without telling consumers the numbers moved
  • Treats it as a pipeline change rather than a governance decision
  • Assumes regulatory or filed numbers can be silently recomputed
  • Leaves a definition cutover unlabelled in dashboards
  • Confuses a metric redefinition with a dimension attribute change

context

resolving links…