A late-arriving fact is loaded against the dimension's is_current row — what does that break?

## What a late-arriving fact is A fact is late-arriving when it reaches the warehouse materially after the event it describes. A payment settles on 15 March but the settlement file is delivered on 28 April; a mobile client buffers events offline for two weeks; a correction re-issues an order from last quarter. The dimension rows involved are all present — this is not the early-arriving case — but the dimension has *moved on* since the event. ## The failure The naive key assignment joins staging to the dimension on the business key and filters on the current flag. On the vast majority of days that is right, because most facts arrive within hours of their event and the current version is also the version that was in force. It is wrong on exactly the days you would most like it to be right. Concretely: customer 88123 was segment SMB until 31 March and Enterprise from 1 April. An order placed 15 March arrives on 28 April. Keyed on the current flag it takes the Enterprise version, so March revenue by segment is now overstated for Enterprise and understated for SMB. The customer's history in the dimension is perfectly correct; the fact simply refuses to use it. The second-order damage is worse than the number. A March report that returned one answer in April and a different one in May, with no visible change, destroys confidence in the mart. And because the fault only manifests on late batches, it survives testing on synthetic same-day data indefinitely. ## The correct assignment Resolve the surrogate key by the **event date carried on the fact**: ```sql SELECT s.order_id, d.customer_sk FROM stg_orders s JOIN dim_customer d ON d.customer_id = s.customer_id AND s.order_date BETWEEN d.effective_from AND d.effective_to; ``` The fact's own event grain supplies the date; the dimension's windows supply the version. The current flag plays no part. This is the whole reason for keeping versioned history — a Type 2 dimension whose facts are keyed on the current row is pure cost with no benefit. Which date is "the event date" is a modelling decision worth stating explicitly. For an order it is usually the order date, but for a settlement it might be the settlement date, and for a shipment the ship date. Pick the date that the business considers the moment the measurement belongs to, and use the same one consistently for every dimension on that fact. ## Guard the edges **Facts older than the dimension's earliest version.** If the source only began supplying customer master data in 2022 and a fact from 2021 arrives, the point-in-time lookup matches nothing. The row then either drops silently or, if the load is written defensively, lands on the unknown member — both are wrong for a fact whose customer is perfectly well known. The convention is to back-date the earliest version's start to a low sentinel such as `1900-01-01`, so every possible event date falls inside exactly one window. This is a modelling convention, not a claim that the attributes were true in 1900. **Facts newer than the dimension knows about.** Rare, but it happens when the fact feed is fresher than the dimension feed — this collapses into the early-arriving case, and the high sentinel end date on the current version covers it. ## When the current row is genuinely the right key Some questions really are "as is", not "as was": *report all historical orders under each customer's current sales territory*, so a re-org can be analysed without the old boundaries. That is a legitimate requirement. It is satisfied by carrying a **second** foreign key on the fact — one resolved as-of the event date, one resolved to the current version — not by replacing the first with the second. Presenting the as-is number as if it were history is the mistake; offering both, clearly named, is the design. ## Detection and monitoring Because the bug is invisible on timely data, measure lateness directly. Compare event date to load date per feed and watch the tail: if a meaningful share of rows arrive more than a day after their event, the assignment logic is load-bearing and should be reviewed. A structural test complements it — assert that no fact's event date falls outside the effective window of the dimension version it was assigned. That check fails immediately and unambiguously the first time someone reintroduces a current-flag join, which is more than can be said for any amount of eyeballing. ## The interaction with retro-fitted dimensions One more wrinkle: if a dimension version was itself spliced in late, facts assigned before the splice may now sit outside their assigned version's window even though the assignment was correct when it ran. The same structural test catches this, and it is the trigger for repointing those fact rows at the newly inserted version.