Reproducing last quarter's number after the definition changed
Why does re-running last quarter's report give a different number, and how do I explain it?
요약
- A metric that returns a different value than it did last quarter is usually correct in both cases, because the definition changed between them.
- Storing metric definitions as versioned snapshots means an old number can be recomputed under the rules that were live when it was published.
- Without versioning, the only way to explain a moved number is archaeology through commit history and people's memories, which is why the answer is usually that nobody explains it.
- The question to ask of any analytics system is not whether it is accurate today, but whether it can reproduce what it said six months ago.
In April the board pack said gross margin was 41.2 percent. In August somebody re-runs it and gets 39.8. Same quarter, same question, different answer.
Nothing has been corrupted. In May a finance analyst noticed that intercompany transfers were being counted in revenue for one subsidiary, filed a fix, and everyone agreed it was a fix. The 39.8 is a better number. It is also not the number in the board pack, and now there is a meeting about it.
The meeting goes one of two ways, and the difference is entirely architectural.
The bad version
Somebody opens the dbt repo and starts reading commit history. Somebody else searches Slack for when the change was discussed. A third person tries to remember whether the April run happened before or after the migration. Four hours later the group has a plausible story and no proof, and the number in the board pack remains unexplained in any durable way.
The reason this is hard is that the definition is stored as current state. The repo tells you what gross_margin means today. It does not tell you what it meant on 14 April, unless you can find the commit that was live then and reconstruct the whole dependency graph as of that date — which is theoretically possible and practically never done.
The good version
Every build of the analysis model freezes a version snapshot. Each published number carries the version it was computed under. So the April figure is not just 41.2; it is 41.2, model v41, and v41 is still there.
Now the question is answerable mechanically:
- Recompute the April question under v41. You get 41.2. The old number is reproduced exactly, which means it was not a mistake and nobody needs to be defensive.
- Recompute it under today’s v58. You get 39.8.
- Diff v41 against v58, filtered to definitions this metric depends on. One change comes back:
revenuegained anexclude intercompanyclause on 6 May, owned by finance, with a rationale attached.
The entire explanation is: the April number was right under the definition we had, the definition improved in May, and the 1.4 point difference is exactly that improvement. Ten minutes, and you can show your work.
What has to be versioned
Not just the SQL. Four things move, and all four change the answer:
Metric definitions. The obvious one. What counts as revenue, which statuses count as active, how a rate is computed.
Analysis rules. The suppression threshold, the stratification requirements, the seasonality conventions. These are knowledge objects and they need versions for the same reason — a report that suppressed a segment under a threshold of 20 reads as inconsistent next to one that suppressed under 30, unless you can show the threshold moved and when.
Source bindings. Which physical table a logical entity resolves to. Warehouse migrations quietly change this and it is the hardest to reconstruct after the fact, because the old table may not exist any more.
The document set. If a number was corroborated against a contract, and the contract was superseded in June, then the April answer was correct against the April document. Documents need effective dates too.
The cost of getting this wrong
It is not primarily an accuracy cost. Both numbers were right. The cost lands in three other places.
Trust, non-linearly. A number that moves without explanation reads as unreliable, whatever the truth. People do not conclude “the definition improved”. They conclude “the system gives different answers on different days”, and after that they check everything by hand — which removes the entire value of having the system.
Audit exposure. In a regulated context, “we cannot reproduce the figure we published two quarters ago” is a finding. Not a discussion, a finding. Reproducibility is frequently the actual requirement, ahead of accuracy.
Institutional memory. Every unexplained discrepancy gets resolved by someone senior remembering the context. That knowledge is not written anywhere, so it leaves when they do, and the next person re-litigates the same 1.4 points from scratch.
What to ask
For any analytics or AI system you are evaluating, the diagnostic question is not “how accurate is it” — everyone answers yes to that, and it is unfalsifiable in a demo. It is:
Show me a number this system produced six months ago, then reproduce it exactly, then show me what changed between then and now.
A system that can do that has versioned definitions and is recording which version each answer used. A system that cannot will tell you the current number is correct, which is true, and entirely beside the point.
The reason we build it this way is not elegance. It is that the first time a CFO asks why a figure moved, the answer determines whether the tool is still in use next quarter.