Join a fact to the version that was true then

Constraints

1. 1For each fact, choose the version whose valid_from is the latest one at or before the fact's timestamp.
2. 2A fact exactly on a version's start date belongs to that version, not the one before it.
3. 3A fact earlier than every version of its key gets None for each attribute — the customer had no tier yet, and using the earliest version invents one.
4. 4A fact whose key is not in the dimension at all gets nulls too, and still appears in the output.
5. 5A fact with no usable timestamp cannot be placed in time. It gets nulls rather than the newest version.
6. 6The dimension arrives in no particular order and may hold many versions per key.
7. 7Copy each attribute onto the fact, and also record which version was used under dimension_valid_from.
8. 8Keep the facts in their original order and do not modify the inputs.

Worked example

Order o1 was placed on 2026-02-14, when c1 was silver — the platinum version does not begin until 2026-03-01. Joining to the current row reports it as gold, the tier that began in May, and moves three months of revenue into a tier that did not exist yet. Order o2 falls exactly on 2026-03-01, the day platinum begins. It is platinum, because the interval is closed at the start and open at the end — the same convention that makes the versions tile the timeline without overlapping. Order o4 for c2 was placed on 2026-01-01, a month before that customer has any version at all. The tier is None. Reaching for the earliest version instead is the tempting fix and it fabricates history: it asserts the customer was free in January, which the dimension does not say.

What this tests

Point-in-time correctness — the reason a fact table is not simply joined to a dimension — and binary search over a sorted history. The leakage this prevents is the same one that ruins a machine-learning training set built from a warehouse.