Analysts want yesterday's numbers to still match after a late-arriving correction. What does the warehouse have to record?

Short answer

Late-arriving corrections need bitemporal facts: the business effective time and the warehouse knowledge time. Do not update the original fact in place; append a new version or ledger correction so reports can query with an as-of timestamp. Snapshot published figures as well, because reproducibility also depends on the report parameters and the exact value that was released.

Two analysts, one query, two answers, and both are right

An analyst ran the regional revenue report on Monday morning and pasted 4.12 million into a deck. Someone reruns the same saved query on Wednesday and gets 4.09 million. Nobody changed the query.

What happened on Tuesday is that a source system corrected a transaction dated Sunday: an amount was wrong, or a refund was posted late, or a currency conversion was rerun. Your pipeline loaded the correction and updated the fact row.

Now examine the two answers, because the framing decides the whole design. The Monday figure was correct on Monday. The Wednesday figure is correct now. Neither is a bug, and the disagreement is not a data-quality problem. It is a missing dimension in the model: the warehouse records when things happened and does not record when it found out. Without that second axis, no query over it is reproducible, because the rows underneath the query change while the query does not.

The two time axes, named

Every fact needs two timestamps, and confusing them is the source of most of the confusion in this area.

Effective time is when the thing was true in the world. The transaction occurred on Sunday. This is the axis every warehouse already has, and it is what "yesterday's numbers" means in business language.

Knowledge time is when this warehouse first recorded this version of this fact. The original amount was known from Sunday night; the correction became known on Tuesday afternoon. This is the axis that is usually missing, and it is the one that makes reproduction possible.

With both present, "yesterday's numbers" stops being ambiguous, because a report is now a query with two predicates rather than one: effective date equals Sunday, and knowledge time is at or before Monday 09:00. That query returns the same result in six months as it did on Monday, because you have stopped asking the warehouse what it believes and started asking what it believed at a moment you can name.

The vocabulary varies by shop - valid time and transaction time, business date and as-of date, effective and recorded. What matters is that both exist and that everyone knows which is which.

Facts get appended, never updated

The modelling consequence is a single rule with wide implications: a fact row is never updated in place.

A correction appends. There are two shapes, and the choice is mostly a matter of which query pattern you want to be cheap.

The versioned-row shape gives each fact row a knowledge-time validity range. When a correction arrives, you close the superseded row's range at the correction's knowledge time and insert the new version with a range starting there and open-ended. An as-of query filters for rows whose range contains the as-of instant. Reading is straightforward and writing touches two rows.

The ledger shape never modifies anything at all. A correction is posted as a reversal of the original amount plus a new entry for the corrected amount, each with its own knowledge timestamp, and any figure is the sum of everything known by the as-of instant. Writing is pure insert, reading is an aggregation, and you get an audit trail for free because the reversal is visible as a row rather than implied by a closed range.

The second is what double-entry accounting has done for centuries, and saying so is worth a sentence in an interview because it establishes that this is a settled pattern rather than a clever idea. A posted journal entry is not edited. It is reversed and reposted, the ledger only ever grows, and any balance is reproducible as of any date because nothing that contributed to it was ever removed.

The thing people leave out: snapshot what you published

A bitemporal model lets you reconstruct any past figure. That is not the same as knowing which figure you published, and the difference matters more than it seems.

Six months from now, somebody asks what number went into the March board deck. With a bitemporal model you can compute it, provided you also know the exact query that was run, the exact as-of instant it was run at, the filter parameters, and the code version of every transformation between the raw fact and the reported measure. Any one of those being unrecorded makes the reconstruction an argument rather than an answer.

So store the output as well as the inputs. When a figure is published, write a row recording the figure, the query or report identifier, the parameters, the as-of instant, and who published it. It is a trivially small table and it converts a forensic exercise into a lookup. Store the load metadata alongside: a run identifier per pipeline execution, and the watermark that run had reached in each source, so you can state what the warehouse could possibly have known at that point.

Then keep a restatement log a person can read. What changed, which effective period it affects, the magnitude of the change, why it happened, and which published figures it invalidates. A model that supports restatement but does not tell anybody a restatement occurred produces a specific unpleasant outcome: a number quietly changes and the first person to notice is the one whose report no longer reconciles with a colleague's.

What it costs, and when not to do it

Nothing here is free, so price it.

Storage grows with corrections rather than staying flat. If 2 per cent of rows are corrected per cycle and you retain every version, the table grows by that 2 per cent on top of new data, indefinitely. Not alarming, but it does mean a fact table's size is no longer a function of the business volume alone.

Every query gains a predicate, and an unindexed knowledge-time predicate makes each query scan versions it does not want. Partition on effective date, because that is what most queries filter on, and cluster or sort within the partition on knowledge time so an as-of filter prunes. Then decide whether the current view is a query with the as-of set to now or a maintained table holding only the latest version, because most consumers want the latest and should not have to reason about any of this. A view named for what it is - current facts - is the usual answer.

And there is a legitimate alternative that is not a modelling failure. Many finance functions rule that a published period is closed and never reopened; a correction to a closed period lands as an adjustment in the current period, with a note. That answer needs no bitemporality at all, because a published figure is immutable by policy rather than by query. Recognising that as a valid design, and asking which regime the business runs under before building anything, reads as considerably more senior than reaching for the model.

Type 2 on the dimension will not save you here

The commonest wrong answer is slowly changing dimensions, and it is wrong in an instructive way.

Type 2 versioning solves a real and adjacent problem: an attribute of an entity changed, so historical facts should join to the version of the entity that was current when the event occurred. A customer moved region, and last quarter's sales should still be counted in the old region.

That is a dimension problem. This question is a fact problem. The transaction amount itself was restated, and no amount of dimension versioning helps, because the measure is wrong rather than the attribute it is grouped by. You can have flawless type 2 dimensions and still be unable to reproduce Monday's total.

The two also compose, which is the fully general case and worth naming rather than building by default: a report is reproducible when the facts carry knowledge time and the dimension versions are selected as of the same instant. Getting one of the two right produces a number that is stable in one respect and drifts in another, which is harder to explain to an analyst than either extreme.

Record when you learned it as well as when it happened, and stop updating facts in place. A reproducible report is not a saved spreadsheet, it is a query carrying an as-of instant - and the figure you published belongs in a table of its own.

---

© 2026 Preptima. Originally published at preptima.com.