Bitemporal entities, or: what did we know and when did we know it

A register change has two dates and they are never the same. Storing one of them is the most common modelling mistake in reference data, and it is invisible until someone runs an audit.

Two axes

Valid time is when the fact became true in the world: a company was renamed on 2 April 2019. Transaction time is when we learned it: the gazette printed on 5 April and we ingested at 09:12 UTC that morning.

Collapse them and you get a database that can answer “what is true now” and nothing else. Every historical question it answers afterwards is a reconstruction that quietly assumes we always knew what we know today.

The question that breaks single-axis stores

A compliance team is reconstructing a decision made on 1 March 2019. The right question is not “what was true on 1 March”, it is “what could our analyst have seen on 1 March”. If a filing was published on 5 April, it was not available on 1 March, and an audit that treats it as available is judging the analyst by information they could not have had.

# what the register now says was true then
$ curl "…/v1/entities/ent_01JR8K3F5T2QW9?as_of=2019-03-01"

# what was actually knowable then
$ curl "…/v1/entities/ent_01JR8K3F5T2QW9?as_of=2019-03-01&knowledge_as_of=2019-03-01"

Storage, and why it is cheaper than it sounds

We store attribute changes, not snapshots. entity_history is append-only with a valid-time interval and an ingested_at, and current state is a materialised projection over it. 3.1B rows for 418.6M entities — roughly seven changes per entity across seventeen years, which is what a register actually produces.

The projection is what makes reads fast. The history is what makes them honest. Losing either one is not a trade worth making.

Corrections are not updates

When a court amends a 2017 filing in 2024, the world did not change in 2024 — our knowledge did. That is a new transaction-time row against an old valid-time interval. Under today's knowledge, the 2017 read now shows the corrected value; under 2017 knowledge it still shows what was published then, which is the whole point.

What we got wrong first

• We originally derived valid time from the publication date. Off by up to 11 days in Germany, and the error was systematically in one direction.
• We stored corrections as updates for the first eight months. Rebuilding that history from raw documents took three weeks and was only possible because we had kept them.
• We exposed as_of before knowledge_as_of, and watched several customers build audit workflows on the wrong axis. Both are now in the point-in-time guide, in that order.