Picture a pipeline that ingests reference data your business prices against — sales-tax rate tables from the states you ship to, a supplier's price list, the feature-flag matrix that decides which plan gets which capability. Each source publishes on its own cadence: some annual, some quarterly, some monthly. We have run pipelines like this in production, and every one of them taught the same lesson.
The naive model is a lookup table: key, value, valid-from, valid-to. Refresh on schedule. Done.
That model is wrong in a subtle way that eventually corrupts your answers, and the reason it's wrong is the most interesting data-modeling lesson we know: the publishers revise the past.
Two kinds of time
A rate-table update doesn't just add next year's rates. Sometimes it corrects history — a jurisdiction amends a rate's effective date, a supplier reissues last quarter's price list with a correction, a flag that "always" applied to a plan turns out to have been mis-keyed since March. The world's timeline itself gets edited.
So a correct system has to track two independent timelines:
- Valid time — when a fact was true in the world. The rate for region X was 7.25% from January to June.
- Transaction time — when your system believed it. We learned this in March; before March we believed something else.
Track both and you can answer the question that audits and reprocessing actually require: "what did we believe on date D about date V?" An invoice issued in February was priced against February's knowledge. If April's publication retroactively changed January's rates, the February invoice was still correct at the time — and your database can prove it, because the old belief isn't overwritten. It's closed out with a transaction-time end and preserved.
One timeline gives you a lookup table. Two timelines give you an audit trail of your own knowledge.
Where it bites
The theory is elegant. Production adds teeth:
Retroactive slices collide. When a source publishes a historical backfill, you're inserting valid-time ranges underneath existing current beliefs. Our worst bug in this system was a comparator that couldn't tell "the current belief about this period" from "a historical slice of it" — revising history would occasionally collapse the wrong version and violate the no-overlap constraint. The fix was conceptually one line — current beliefs are the ones whose validity extends to infinity; compare within, never across — but finding it required actually holding the two-timeline model in your head.
Sources drift. A published spreadsheet is not an API contract. Fiscal-year publications arrive with renamed files, date-stamped suffixes, restructured directories; a supplier switches from CSV to XLSX without telling anyone. An ingest pipeline that matched last year's layout will silently find nothing — which is why every ingest ends with sanity gates: expected row counts per source, freshness canaries per source cadence, and hard failures when a "monthly" source hasn't produced anything new in ninety days. Silence is an error state; we've written about that pattern before.
Corrections must be new facts. The iron rule of the whole design: never UPDATE a belief in place. Close the old row in transaction time, insert the new one. The moment someone patches history directly, your audit trail has a hole exactly where an auditor will look.
Do you need this?
More often than people think. Bitemporality earns its complexity whenever three things are true: facts can be revised retroactively, decisions were made against old versions of the facts, and someone may later demand to know why a decision was made. Pricing and tax rules, obviously — but also compliance policies, insurance rates, commission schedules, sports odds, even feature flags in a regulated product.
If your domain has all three, a single-timeline table is a slow-motion data-loss incident: every refresh destroys the evidence of what you used to believe. You won't notice until the day someone asks "why did the system do that in February?" — and by then the answer is gone.
Model both timelines from day one. It's one extra pair of columns and a discipline. The alternative is explaining to an auditor that your database has amnesia.