What You'll Master Here
valid time (when it was true in the world) and transaction time (when we recorded it). Model the ones your questions need.
Chapter 11 kept history for one dimension attribute. This chapter is about the thing underneath that: time is two independent clocks, not one. Valid time is when a fact was true in the world; transaction time is when your database believed it. Most "the report changed" mysteries are a confusion between them.
Climb the ladder below. Each rung adds one column pair to the same land-registry title, and the four questions beside it turn from unanswerable to answerable. It opens on rung 1 — valid time only — because that is the rung most warehouses actually ship, and rung 2 is where a confident, wrong answer appears.
Two clocks: valid time (when it was true in the world) and transaction time (when we recorded it). Model the ones your questions need.
Auditing, compliance, and any "as of" reporting depend on modeling time correctly. The difference between "what was true" and "what we knew" is invisible until a regulator or a restated number forces it, and by then the data must already support it.
- valid time
- When a fact was true in the real world.
- transaction time
- When the database recorded (or learned) the fact.
- bitemporal
- Tracking both valid time and transaction time together.
- event sourcing
- Storing an append-only log of events as the source of truth, deriving state from it.
| proprietor | valid_from | valid_to |
|---|---|---|
| Bellhaven Farms | 2019-05-14 | 2026-04-01 |
| Carrick Land Co | 2026-04-01 | 9999-12-31 |
- Who is the registered proprietor of ARD-4471 today?YESThe open-ended row: Carrick Land Co.
- Who owned ARD-4471 on 20 March 2026?YESBellhaven — 20 March falls inside its range. This is the rung most warehouses stop at.
- Who did the register name when a lender searched it on 20 May 2026?NOThe table holds the best current account of the past, not the account it gave on 20 May.
- A buyer relied on that 20 May search. Was the answer they were given right?NOThe old answer was overwritten by the rectification, so there is nothing left to check it against.
Treating "keeping history" as a single, vague goal. You cannot answer "what did we believe last quarter?" without distinguishing valid from transaction time.
Distinguish when something was true from when you recorded it.
Choose a temporal pattern from the questions you must answer.
Make history queryable with explicit date ranges or event logs.
One title deed runs through it: a transfer that completed on 12 March, was registered on 2 May, and was rectified on 8 July. Every widget that names ARD-4471 is showing you the same five rows from a different angle.
Temporal modeling distinguishes valid time (what was true) from transaction time (what we knew) and provides patterns, effective dating, bitemporal, snapshots, event sourcing, to make history queryable.