Modeling Time, History, and Corrections

In an illustrative scenario, a finance team asks for last quarter’s revenue to be regenerated, expecting the same number they signed off in January. It comes back different. Nothing was corrupted: three orders were canceled in February with effect from December, a rate correction was applied retroactively, and two territory reassignments changed which region several accounts belong to. Every one of those changes was correct. The report is now also correct, and it disagrees with the one that was filed.

The system was built to answer “what is true,” and it was asked “what was true then” and “what did we believe then” — three different questions that need different structures.

Three clocks, not one

A timestamp column named created_at is usually hiding a question nobody asked: created in what sense? Analytical models generally need to distinguish three different times, and confusing any two of them produces a specific, recognizable class of wrong answer.

The Dataflow Model paper distinguishes event time, recorded by the event-producing system when the event occurs, from processing time, measured by the clock at the pipeline stage that handles it. Those clocks need not be synchronized.

The asymmetry between them is what makes the distinction load-bearing: event time for a given event essentially never changes, while processing time changes constantly for each event as it moves through the pipeline. The gap is not a defect to be engineered away either — communication delays, scheduling, time spent processing, and pipeline serialization produce a varying gap between those time domains.

The third clock comes from the business rather than the plumbing. An event can occur on one date, be recorded on another, and take effect on a third: a contract signed in March, entered in April, effective from January. Call this effective time — the period for which a fact is true as far as the business is concerned. Martin Fowler’s treatment of bitemporal history distinguishes actual history, representing the history with the information now available, from record history, retaining changes to what was known. Delayed and imperfect communication make both useful.

Clock	Answers	Can it change later?	Getting it wrong looks like
Event time	When did this happen in the world?	The event’s actual time does not; a recorded timestamp can be corrected — a wrong time zone or clock — as a tracked revision	Yesterday’s totals keep changing as late data arrives
Processing time	When was it handled at a given stage of the pipeline?	It has a different value at every stage and on every retry	Reprocessing moves events into different days
Effective time	When is this true, for business purposes?	Yes — corrections move it	Retroactive changes silently rewrite closed periods

Two practical rules follow. The first is to aggregate on the time axis the question is about, and keep that meaning fixed when data is reprocessed. A figure for a period — March revenue — usually belongs on event time, so that a re-run cannot move a sale into a different day. Whether something was in force belongs on effective time: the contract signed in March but effective from January counts as active in January. Measures of the pipeline itself, such as throughput or ingestion delay, properly use processing time. How a table is physically partitioned is a separate decision, driven by how it is loaded and pruned; what matters for correctness is that each aggregate uses the right axis and that re-running the pipeline on the same inputs, with the same interpretation of time, cannot change which period a record belongs to. Correcting a timestamp — fixing a time-zone error that put 00:30 UTC on the previous local day, say — can legitimately move an event to another period, but as a recorded revision that keeps the original value and says which periods it affected, not as a side effect of a re-run. The difference between event time and processing time is where this starts.

The second rule is that processing time does not tell you what you knew. Apache Beam’s glossary defines it as the real-world time at which an element is processed at some stage in a pipeline, so one event has as many processing times as it has stages and attempts, and keeping any one of them does not reconstruct what the system believed on a given date. That question needs a different timestamp, which Fowler calls record time: when the system recorded a particular version of a value. It only works together with the versions themselves — a new version is added rather than written over the old one — because answering “what did we report on 1 March” requires both the timestamp and the value that was current then. Keeping the time data first arrived is also worth doing, since it is what shows how late data really was. The bitemporal discussion below returns to record time.

Waiting for stragglers

Once event time and processing time are separated, a scheduling question appears: when is a day’s data complete enough to publish? For an open-ended source with no end-of-data signal, the honest answer is that you cannot know for certain, and the useful answer is a bound you choose. A delivery under an explicit contract — a file manifest and a completion marker — is different: whether everything in that delivery arrived can be checked, even if whether the source recorded everything that happened cannot.

Streaming systems formalize this as a watermark — in the Dataflow paper’s description, a lower bound, often heuristically established, on the event times that have been processed by the pipeline. The word heuristically is doing real work. The paper states in a footnote that for most real-world distributed datasets, the system lacks sufficient knowledge to establish a completely correct watermark, and gives a memorable reason: someone takes a mobile device into the wilderness, watches videos offline, and the events arrive whenever they reconnect. No amount of infrastructure predicts that.

Batch platforms face the same problem without the vocabulary, and usually solve it by reprocessing a trailing window — recomputing the last several days on every run so that late data gets picked up. That works, and it has a consequence worth stating to consumers rather than discovering with them: recent periods are provisional. A figure for yesterday may still change; one for last month is less likely to. Publishing that boundary — how far back automatic reprocessing reaches — turns an unpredictable annoyance into a documented property. It is not the same as saying a number is final: a figure outside the window can still change through a cancellation processed later or a targeted backfill, so say too when a period is formally closed and how revisions after that are announced.

For open-ended sources, what you cannot do is choose a lateness bound guaranteed never to exclude anything. A finite bound carries the risk of dropping something, so the design question is what happens to data that arrives outside it. Silently discarding is the common default and the worst one. Counting it into the current period is cheap, and wrong when the report is about when activity happened, because it attributes December’s activity to March — though some reporting rules deliberately book late items in the period they are recorded, as the section on corrections discusses. Reopening the old period is correct and expensive. Whichever you choose, choose it deliberately, and log the exclusions so that the size of the problem is measurable rather than theoretical.

Which table shape answers “as of”

Before dimensions, one structural point about the facts themselves. A table of events answers “what happened between these dates” natively, by filtering on event time. It answers “what was the state on this date” only by rebuilding that state: starting from the beginning if nothing else exists, or from a trusted opening balance or snapshot, and applying every change after it. Martin Fowler’s description of event sourcing makes the same point — state can be rebuilt by replaying events, and a snapshot lets the replay start from a recent point instead of from nothing. The rebuild is correct as long as the change history is complete from the starting point. Its costs are that every such question repeats the computation, and that a missing change makes the answer quietly wrong.

That is why a model over one business process often carries more than one shape, chosen by the questions asked of it. A transaction-grained table holds the events. A periodic snapshot holds the state at regular intervals, with a row for every entity in every period whether or not anything happened, so a question about a quiet account on a quiet day has something to read. An accumulating snapshot holds one row per process instance, updated as it advances, so the lags between milestones are subtraction — at the price that the row carries current state. Its milestone dates still show some past states — picked on 4 March and shipped on 6 March means picked but not yet shipped on 5 March — but repeated steps, attributes updated in place, and what was known at the time are gone.

Read as time questions, they divide cleanly: events for “what happened,” periodic snapshots for “what was it then,” accumulating snapshots for “how long does this take and where is it stuck.” Getting the second answer from the first is possible, but it repeats a rebuild for every question and fails quietly when a change is missing; a periodic snapshot does that work once and turns a state question into a simple, cheap read.

Dimensions that change

Facts are usually about a moment. Descriptive context is not: a customer moves, a product is recategorized, an account is reassigned to a different sales region. Whether your model can answer “what region was this account in when the sale happened” depends on a choice made when the dimension was designed.

The slowly changing dimension techniques name the options, and the first four cover the cases most designs need.

Type	What happens on change	History	Fits when
0 — Retain original	Nothing; the value never changes	Not applicable	The attribute is defined as original — an original credit score, a durable identifier
1 — Overwrite	The old value is replaced	Lost	Correcting an error, or history is deliberately not tracked
2 — Add row	A new dimension row with a new surrogate key	Preserved	Facts must stay attached to the context that was current when they occurred
3 — Add column	A new attribute holds the prior value; the main one is overwritten	A limited number of prior or alternate values — one in the usual example	Users need to report on both an old and new grouping — used relatively infrequently

Kimball’s own framing of Type 1 is blunt and worth quoting because teams choose it by default without noticing what they are choosing: “Type 1 destroys the history of a particular field.” Reports that group or filter on that field will change — retroactively, for every period, including ones already published. That is right for fixing a misspelled name, and wrong for a sales region when the question is where a sale was made at the time — though a report that deliberately restates all past sales under today’s territories wants exactly the current value.

Type 2 is the one that makes historical attribution work, and the mechanism is worth understanding rather than treating as magic. On a change, a new row is added with the updated values, with a new surrogate key. New fact rows use that key until the next change creates another version. Facts written before the change keep pointing at the old row; facts written after point at the new one. A minimum of three columns supports this — a row effective date, a row expiration date, and a current row indicator.

Two consequences follow that surprise people later. The dimension’s primary key has to be generalized beyond the natural or durable key, because one member now has several rows — so customer_id is no longer unique in the customer dimension. A query that joins on it alone, with no condition on which version was in effect, fans out for every member that has more than one version — one row per matching version — and the members with a single version so far look fine until their next change. Facts normally carry the surrogate key of the version that applied when they occurred; where a join has to start from the natural key instead — loading a late arriving fact is the documented case — it must also match the event’s time against each version’s effective date range, so that exactly one version is found. And the choice is per attribute rather than per table: a dimension commonly has Type 1 attributes for corrections and Type 2 attributes for things whose history matters, which means someone has to decide, attribute by attribute, whether a change is a correction or an event.

That last question is the actual design work, and it is not a technical one. “The customer moved” and “we had the wrong address” produce identical data and usually call for different handling — though the choice also depends on which view reports need, current or as-at, and on whether an audit requires the earlier value to stay explainable.

When data arrives out of order

Lateness cuts both ways, and the two directions have different prescriptions.

A late arriving fact is a measurement that shows up long after the event, when “the most current dimensional context for new fact rows does not match the incoming row.” The naive load attaches it to today’s dimension rows, which quietly credits the sale to whoever owns the territory now. The prescription is to search the relevant dimensions and find the dimension keys that were effective when the late arriving measurement event occurred. This is the same operation as a point-in-time join, and it needs the context as it was at the event’s time from somewhere. A Type 2 dimension is the usual source — the return on having chosen it earlier — but a retained source history or dated snapshots can serve too; only when that history exists nowhere is correct attribution impossible. How late the fact is does not decide the matter either: a sale at 09:59:59 that arrives at 10:00:01, after a change at 10:00, needs the same treatment.

A late arriving dimension is the opposite: facts arrive “minutes, hours, days, or weeks before the associated dimension context,” so there is nothing to point at. Dropping the fact loses a real measurement. Holding it until the dimension arrives is a legitimate choice when the publication deadline allows — a batch can wait in staging and be published complete — but it makes the pipeline stateful, needs a timeout, and makes a figure published in the meantime incomplete whenever its stated period and scope were meant to include the held facts — a report cut off before they occurred is unaffected, and a report that should include them is short whether or not the gap is flagged, so the flag tells consumers the figure is provisional rather than making it complete. Where facts have to be visible immediately, the documented approach is to insert a placeholder dimension row carrying the unresolved natural keys as attributes and generic unknown values for the descriptive columns, then update those placeholders with Type 1 overwrites when the real data arrives.

Make unresolved placeholder rows findable. A row whose description reads “Unknown” is indistinguishable from a resolved one unless something marks it, and unresolved placeholders that are never revisited become permanent holes in the data that reports quietly aggregate into an “Unknown” bucket nobody investigates. A flag and a count on the dashboard is enough.

The harder case, and the documented one, is when a dimension change turns out to have been retroactive — the attribute changed weeks ago and you are only learning now. Then a new row is inserted in the dimension table and the associated fact rows must be restated. That is a genuine rewrite of history, which is the subject of the next section.

Correcting the past without erasing it

Corrections are inevitable. What varies is whether the previous answer survives, and that determines whether anyone can explain the discrepancy in the opening example.

Approach	What it does	Can you reproduce the old report?	Suits
Overwrite	Replaces the wrong value	No	Genuine errors in unpublished data
Restate	Rewrites the affected rows and republishes the period	Only if a copy was kept	Material corrections where the record must reflect reality
Append an adjustment	Adds an offsetting entry that identifies the original event, its effective period, and when it was recorded	Only if queries can apply the original report’s record cutoff, or the published figures were kept — no existing row changes, but an entry effective in January still changes January’s total	Anywhere the prior figure was acted on and the correction must be traceable

The third row borrows the accounting habit of recording corrections as new entries, and its logic transfers directly: a published number that someone relied on is itself a fact about the past. How a store records a correction and which period a report assigns it to are separate decisions, though. Storage can be append-only while the report restates the earlier period; under IFRS, for example, material prior-period errors are generally corrected by restating the comparative figures for the period in which they occurred. Which treatment applies depends on the accounting framework, the contract, and the kind of correction.

Where a restatement is right, the cost is that consumers now receive two different answers to the same question at different times, which is manageable only if it is announced. Attaching a version to the published dataset and telling consumers when a period was restated is the difference between a controlled revision and a credibility problem. The same applies to a backfill that changes figures already published, which is then a restatement by another name; a backfill that fills a new table’s history for the first time changes nothing anyone has seen.

Fowler’s worked example shows why keeping both time axes is what makes any of this coherent. Payroll processes Sally in late February at 6,000 a month. In mid-March, HR reveals that a raise to 6,500 took effect from 15 February. In early April, the raise amount is corrected again, to 6,400. Actual history changes retroactively each time; record history is append-only, so it remains possible to say both what her salary was on 20 February and what the system believed on 1 March. Only one of those explains the February payslip.

Carrying both axes is real cost — more columns, more careful queries, more explaining — so it is worth applying selectively. The test is what will have to be answered later. Proving what was published needs only the published output kept. Re-running a particular report needs its inputs, definitions, and dimension versions kept. Answering arbitrary questions about what was true and what was known on any date needs both axes. A figure that was filed or paid against usually needs at least the first; a traffic dashboard may need none — unless it becomes evidence in an SLA dispute or an experiment has to be reproduced.

Can you reconstruct what you said?

All of the above converges on one capability that is easy to assume and rarely tested: given a date, can the platform reproduce the numbers it was reporting on that date?

Recomputing a past report as it was, from the model itself, requires several things to have been true at once, and the failure of any one of them is enough. Each fact must carry the time axis its report is defined on — usually event time, effective time where the question is what was in force — so that a re-run cannot move it to a different period. The platform must have kept what it knew: earlier versions of changed values, each with its record time. The dimensions, or some retained record of them, must have kept the versions that were current then — the Type 2 decision is the usual way. Nothing must have been overwritten in place without a record. And the report definition itself must be recoverable, since a metric whose SQL changed in June will produce a different answer for May regardless of how well the data was preserved — a point that tends to be forgotten because it lives in code rather than in the warehouse. Keeping the published output, its input snapshot, and the query version is a narrower alternative that reproduces particular reports without any of this.

Which is why the check is empirical rather than architectural, and there are two different checks. Reproducing a report means rebuilding it with the data, definitions, dimension versions, and cutoff as of its date, and comparing the result with what was published; any difference is a finding. Recomputing it from current data and explaining each difference by a known correction is a useful reconciliation too, but it does not show that the past can be reproduced — if the old inputs are gone, every difference can be explained and reproduction still be impossible. This is the same discipline as any reconciliation, applied to your own past output, and running it once tends to be more informative than a year of assuming.

Where the platform cannot do this and needs to, the remedy is often not bitemporal modeling everywhere. Keeping an immutable record of what was published — the figures as filed, with their date — alongside the model is narrower and usually simpler, the same instinct as an audit log, though its cost depends on how much is kept, for how long, and how it must be accessed. Recording the past as you go is generally easier than reconstructing it afterwards.

Questions that reveal the wrong choice

Question	If the answer is unclear, check whether
For each timestamp column, which of the three clocks is it?	aggregates mix “when it happened” with “when we loaded it”
Does each aggregate use the time axis its question is about, and does reprocessing keep it there?	a re-run moves records between periods, or a figure answers a different question than the one asked
Are earlier versions of changed values kept, with the time each was recorded?	“what did we report then” can still be reconstructed or explained
How late can data arrive and still be counted, and what happens beyond that?	records are being dropped silently or attributed to the wrong period — the exclusion log would show it
Which dimension attributes are Type 1 and which Type 2, and who decided?	history is being overwritten for attributes whose history matters
Do queries find the right version of a Type 2 dimension — by the version’s surrogate key, or by the natural key plus its effective date range?	any join uses the natural key alone; facts for members with several versions are then multiplied, and members with one version so far are exposed at their next change
How many unresolved placeholder dimension rows exist right now?	an “Unknown” bucket is growing with nobody accountable for it
When a period is restated, how do consumers find out?	people are holding two numbers for the same quarter and trusting neither
When did anyone last reproduce an old report as it was published?	reconstructability has ever been tested, or is only assumed

References

• Tyler Akidau et al., The Dataflow Model: A Practical Approach to Balancing Correctness, Latency, and Cost in Massive-Scale, Unbounded, Out-of-Order Data Processing, Proceedings of the VLDB Endowment 8(12), 2015
• Martin Fowler, Bitemporal History, April 2021
• Ralph Kimball, Slowly Changing Dimensions, August 2008
• Kimball Group, Type 2: Add New Row (and the companion pages for Types 0, 1, and 3)
• Kimball Group, Late Arriving Facts
• Kimball Group, Late Arriving Dimensions
• Kimball Group, Accumulating Snapshot Fact Tables
• Apache Beam, Beam glossary
• Martin Fowler, Event Sourcing
• IFRS Foundation, IAS 8 overview