Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

Bitemporal modeling and time travel

Why does a fact need two time axes, and why is time travel not a substitute for them?

Bitemporal modeling

A fact has two independent time coordinates:

  • Valid time: when it was true in the world.
  • Transaction time: when the system believed it (when the system recorded it).

They are independent because corrections exist. On Friday you can learn that something was true on Monday. On Saturday you can learn that it was not. Two independent variables cannot be stored on one axis. A store that keeps one timestamp per row is lossy, and the loss happens at write time, irreversibly. No later mechanism can bring it back.

Each axis alone answers half of the questions:

  • Valid time only: answers "what was true on Monday?" but not "what did we report on Tuesday?". So nothing is reproducible and nothing is auditable.
  • Transaction time only: answers "what did we know on Tuesday?" but a backdated correction looks the same as a brand-new fact.

The mechanics

A row is a rectangle in two-dimensional time: it covers a span of and a span of . A query is a point or a slice in that plane. A correction is not an update. It is the insertion of a new rectangle that overlaps the old one along valid time and is later in transaction time. So the table has four temporal columns and queries put conditions on both axes.

The old row is not deleted. Its transaction span is simply closed on 10 March, and a new row begins on 10 March.

A correction is a new rectanglevalid time × transaction time

Now both questions can be answered:

-- What rate was true on 5 March, as we believed it on 5 March?   -> 20
SELECT rate FROM courier_rate
WHERE courier = 'Ravi'
  AND valid_from <= DATE '2025-03-05' AND DATE '2025-03-05' < valid_to
  AND tx_from    <= DATE '2025-03-05' AND DATE '2025-03-05' < tx_to;

-- Same valid date, as we believe it on 12 March?                 -> 25
-- (use DATE '2025-03-12' in the two tx_ conditions)

With one updated_at column, the answer to "what did you believe on 10 March?" was impossible, which is what the auditor found.

A common misunderstanding to correct

A great deal of data warehousing treats Type-2 SCD as if it were bitemporal. It then cannot reproduce last quarter's report after a source system backdates a correction.

The costs are real:

  • Storage grows with the correction rate, not the change rate.
  • Every join becomes an interval-overlap join instead of an equality join. This does not hash well (a hash join needs one exact key to look up).

The cost is structural. The test for whether you need bitemporality: will anyone ever ask why a number differs from what you told them last month? If yes, you need it.

Time travel and versioned reads

Time travel means reading a table as it was at an earlier moment. It is the transaction-time axis, implemented in the storage layer instead of in the data model. Because of that it is nearly free: it re-uses the snapshot metadata the system already keeps. It is with the snapshot kept after its last reader has finished.

is strictly weaker than bitemporality, and the two are confused often enough to be worth naming:

  • Time travel is versioned per table, not per fact.
  • It works only along transaction time.

It answers "what did this table say at 14:00?" It cannot answer "what did we believe about courier X as of a business date, given later corrections?"

Its costs land on cleanup

  • The retention horizon multiplies the number of stored files and the size of the metadata tree.
  • It blocks deletion: a file is not deleted while an older snapshot still refers to it. So for a right to be forgotten request (a legal duty to delete a person's data), a system must either expire its history or rewrite it.
  • One detail rarely mentioned: time travel by timestamp turns a commit time into a snapshot id, and the commit time is a wall-clock time on the writer machine. A query that spans a clock adjustment can resolve to a surprising snapshot.

A typical query looks like SELECT * FROM deliveries TIMESTAMP AS OF '2025-03-10 09:00:00' (the exact syntax differs between systems).