Orientation
What You'll Master Here
Slowly changing dimensions taught you to keep history for one attribute. This chapter generalizes that into the full discipline of modeling time: not just "what changed," but the two independent kinds of time every serious data system must distinguish, and the patterns for recording them.
You will learn valid time vs transaction time, bitemporal modeling (tracking both at once), periodic snapshots as an alternative to effective dating, and event sourcing, where an append-only log of events is the source of truth. Together these answer the hardest historical questions: not only "what was true?" but "what did we believe, and when?"
Each pattern is shown with concrete tables and queries, because temporal modeling is exactly where vague intentions ("we keep history") fall apart, and precise structures (date ranges, two timelines, event logs) are what actually make the past queryable.
Why it matters
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.
Core mental model
Two clocks: valid time (when it was true in the world) and transaction time (when we recorded it). Model the ones your questions need.
- 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.
Common mistake
Treating "keeping history" as a single, vague goal.
You cannot answer "what did we believe last quarter?" without distinguishing valid from transaction time.
Better habit
- 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.
Time is two dimensions, not one. Valid time is about reality; transaction time is about your knowledge. Many "the report changed" mysteries are really a confusion between the two.
Follow the same NY→CA move from Chapter 11, now through valid time, transaction time, snapshots, and events, to see how each pattern records it.
Practice prompts
- Define valid time and transaction time in one sentence each.
- Give a question that needs transaction time, not just valid time.
Remember this
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.