Point-in-time joins

How do you match a record to the version of the truth that was current when it happened?

A join is normally a matter of equality. This order has customer 88213; find the customer row whose key is 88213; done. There is one match, and asking for it is the simplest thing in the language.

Now suppose the customer table holds four rows for that customer, one per era of their details. The equality still succeeds — four times. Which of the four is the right one? And notice that the question has no answer at all until you say when: the right row for an order placed in 2019 is not the right row for one placed last week. The join has stopped being about identity and become about identity at a moment.

Begin by writing down what each version row actually claims. It does not claim "this customer is in the South". It claims "from 15 March 2021 until further notice, this customer is in the South". Each row is an assertion with a window attached. If we make that window explicit as two columns — when it began, when it ceased — then the matching rule almost writes itself: take the row whose window contains the moment of the event.

So the join gains a condition. Match on the entity, and then require that the event's timestamp falls inside the version's interval. That is the whole of a point-in-time join — an equality plus a containment.

Which sounds trivial until you ask how the endpoints behave. Suppose one version ends on 15 March and the next begins on 15 March, and both intervals include their endpoints. An event on 15 March now matches both, and the join produces two rows where the data contains one order. Every downstream total is silently doubled. So the intervals must partition time rather than merely cover it, and the standard way is a half-open convention: the start moment belongs to the row, the end moment belongs to the next one. That single convention, applied everywhere, is what makes exactly one row match. It is a small rule with a large blast radius when it is applied inconsistently across tables.

There is a second endpoint problem. What is the end date of the version that is still current? Leaving it empty is honest, but the containment test must then special-case null in every query. The common alternative is a far-future sentinel date, keeping the test uniform at the cost of a fictional date in the data. Neither is obviously right; what matters is consistency, because a mixture guarantees rows that silently fail to match.

Now the harder case, and the reason this technique earns a name of its own. Suppose the event arrives late — an order from June, loaded in September. The instinct is to join it to the customer as they are, because that row is right there and flagged current. But the order happened in June, and joining to today's row rewrites June's history using September's facts. So the join must use the event's time, never the load time and never now. This is the discipline machine-learning feature stores enforce, where a value not yet knowable at the event's timestamp leaks the future into training data and yields a model that tests well and fails in production.

And here a genuine complication rather than a tidy resolution. Two notions of time are in play and they can disagree: when the fact was true in the world — the customer moved in March — and when the system came to know it, in May. Store only the first, and a backdated correction rewrites what your reports say about March, which is right for analysis and wrong for an audit that must reproduce what was reported at the time. Storing both — a bitemporal model — lets you ask either question, but every query then carries two interval conditions. Plenty of systems reasonably decide they need only one.

Three refinements.

First, the misconception that a point-in-time join is a performance problem to be optimised away. It is a correctness rule. A join to the current row is not a faster version of the same answer; it is a different and usually wrong answer, and it is wrong in the direction that flatters — reports look tidier and models score better, because both have been handed information that did not exist yet.

Second, this technique depends entirely on the versioning underneath it. If the description table was updated in place, no interval exists and no point-in-time join is possible — the past is simply unavailable, and no query can recover it. Versioning creates the material; the join is what reads it correctly.

Third, in warehouses the join is often done once rather than at read time: as the fact is loaded, the correct version's key is resolved and stored on the fact row, so later queries are plain equality joins. That is the same rule applied earlier, and it moves the risk rather than removing it — get the resolution wrong at load, and every later query inherits the error invisibly.

• What a bitemporal model costs in practice, and when only one time dimension is genuinely needed.
• Why feature stores treat this join as the main defence against leaking future information into a model.
• Whether resolving the version key at load time is worth the loss of the ability to re-derive it.

Every term the collection defines is gathered in the glossary.