Modeling Time: Three Clocks, History, and Corrections

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 cancelled 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. This article is about those structures: which clocks a model has to carry, how dimensions keep or discard history, what to do when data arrives late in either direction, and how to correct the past without destroying the record of what you previously said. The examples are invented unless attributed.

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 first two come from stream processing, where the distinction is unavoidable and has been stated precisely. The Dataflow Model paper defines event time as “the time at which the event itself actually occurred, i.e. a record of system clock time (for whatever system generated the event) at the time of occurrence,” and processing time as “the time at which an event is observed at any given point during processing within the pipeline, i.e. the current time according to the system clock.” The paper is explicit that it makes no assumptions about clock synchronization between the two.

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 what the paper calls “an inherent and dynamically changing amount of skew between the two 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 frames the pairing well, describing actual history as what “records what history should be given perfect transmission of information,” while record history “captures how our knowledge of history changes.” The reason both are needed is that “communication is neither perfect nor instantaneous.”

Clock	Answers	Can it change later?	Getting it wrong looks like
Event time	When did this happen in the world?	No — it is a property of the event	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 cannot change which period a record belongs to. 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? The honest answer is that you cannot know, and the useful answer is a bound you choose.

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 will change; a figure for last month probably will not. Publishing that boundary — how far back the window reaches, and therefore at what age a number becomes stable — turns an unpredictable annoyance into a documented property.

What you cannot do is choose a lateness bound that never excludes anything. Every bound eventually drops 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, because it attributes December’s activity to March. 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 mature model over one business process usually carries more than one shape. 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 and therefore cannot say where the pipeline stood last Tuesday.

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 almost everything encountered in practice.

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	One prior value only	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 exactly right for fixing a misspelled name, and exactly wrong for a sales region.

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, and “a new primary surrogate key is assigned and used as a foreign key in all fact tables from the moment of the update until a subsequent change creates a new dimension key.” 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, will fan out. 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 require opposite handling.

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 is only possible at all if the dimension kept its history — which is the return on having chosen Type 2 earlier.

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 in a queue makes the pipeline stateful and the totals wrong in a different way. 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.

The placeholder pattern is worth adopting with one addition of my own: make the placeholders 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 dated to the correction	Yes — nothing was changed	Regulated reporting, and anywhere the prior figure was acted on

The third row is the accounting profession’s answer, arrived at long before data warehouses, and its logic transfers directly: a published number that someone relied on is itself a fact about the past. Where regulators, contracts, or auditors are involved, appending is usually not a preference.

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, which is a restatement by another name.

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 whether anyone will ever have to defend a past statement: a figure that was filed, contracted on, or paid against needs both axes, and a dashboard of last week’s traffic does not.

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?

Answering yes 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 must have kept the versions that were current then, which is the Type 2 decision. 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.

Which is why the check is empirical rather than architectural. Pick a report from six months ago, reproduce it from current data, and compare. Where the numbers differ, every difference should be explainable by a known correction — and the ones that are not explainable are the findings. 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 cheapest remedy is usually not bitemporal modeling everywhere. It is keeping an immutable record of what was published — the figures as filed, with their date — alongside the reproducible model, which is the same instinct as an audit log. Reconstructing the past is expensive; recording it as you go is not.

Questions that reveal the wrong choice

Question	If the answer is unclear
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” cannot be reconstructed or explained
How late can data arrive and still be counted, and what happens beyond that?	Records are dropped silently, or attributed to the wrong period
Which dimension attributes are Type 1 and which Type 2, and who decided?	History is destroyed for attributes whose history mattered
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?	A join on the natural key alone fans out every fact joined to it
How many unresolved placeholder dimension rows exist right now?	An “Unknown” bucket grows and nobody is accountable for it
When a period is restated, how do consumers find out?	People hold two numbers for the same quarter and trust neither
When did anyone last reproduce an old report from current data?	Reconstructability is an assumption, not a property

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