How do you retro-fit a late-arriving dimension change into an SCD Type 2 chain that already has newer versions?

## Why the ordinary load path fails A Type 2 dimension load is normally written for a monotonic world. It finds the row flagged current, sets its end date to the day before the change, inserts a new row from the change date to the high sentinel, and marks that row current. Every step of that is wrong when the change is dated in the past. Suppose customer 88123 has two versions — Berlin until 31 March, Hamburg from 1 April, current. On 20 May a change dated **10 February** arrives: Berlin to Bremen. Run the ordinary path and you get: Hamburg end-dated 19 May, Bremen current from 20 May. The customer's genuinely current city has been retired in favour of a value that was superseded three months ago, and the February-to-March window still says Berlin. Two errors from one load. This is why late-arriving dimension handling is treated as a separate code path rather than a variation on the normal one, and why interviewers ask about it — it is a bug that a naive implementation ships and nobody notices until a report is questioned. ## The splice The correct operation is an insertion into the middle of an ordered chain. Three steps: **1. Find the covering version.** Not the current row — the row whose effective window contains the change date: ```sql SELECT customer_sk, effective_from, effective_to FROM dim_customer WHERE customer_id = 88123 AND DATE '2024-02-10' BETWEEN effective_from AND effective_to; ``` **2. Shorten it.** Set its `effective_to` to the day before the change date (or to the change date itself, if your convention uses exclusive end bounds — pick one convention and apply it everywhere). **3. Insert the missed version.** `effective_from` is the change date. `effective_to` is derived from where the *next* version begins, not hardcoded and not the high sentinel. The `is_current` flag is FALSE, because a later version exists. The result is a chain that is still contiguous, still gap-free, still has exactly one current row, and now has three versions instead of two. ## What must not change **The newest row's current flag.** Only one row per business key may be current, and it is the one with the latest `effective_from`. A retro-fit in the past never touches it. **Existing surrogate keys.** The shortened row keeps its key. Reissuing keys would orphan facts. **The chain's contiguity.** After the splice, every date between the earliest `effective_from` and the high sentinel must fall inside exactly one version. A gap means facts land nowhere; an overlap means a fact matches two versions and fans out, silently multiplying measures. ## Verify the chain, always Retro-fits are exactly the operation that produces overlaps, so make the check routine rather than occasional: ```sql SELECT a.customer_id FROM dim_customer a JOIN dim_customer b ON a.customer_id = b.customer_id AND a.customer_sk <> b.customer_sk AND a.effective_from <= b.effective_to AND b.effective_from <= a.effective_to; ``` Any row returned is an overlapping pair. A second check counts current rows per business key and expects exactly one. ## The fact-side consequence Carving a new window out of an existing one means facts that fall inside it are now attributed to a version that no longer covers their event date. Those rows still carry the shortened version's surrogate key and will report Berlin for a period when the customer was in Bremen. The dimension-side splice is only half the job; the fact rows in the carved window have to be repointed at the newly inserted surrogate key, and anything derived from them for that period has to be rebuilt. ## Two cases that need a decision, not a formula **The change predates every existing version.** There is no covering row. Either back-date the earliest version's `effective_from` to a sentinel and insert the new version ahead of it, or accept that the change is older than the dimension's known history and log it. Decide deliberately; do not let the load silently do nothing. **Several late changes arrive together, out of order among themselves.** Do not apply them one at a time through the same code path. Sort all pending changes for the business key by effective date, rebuild the chain for that key as an ordered sequence, and write it once. Applying out-of-order changes sequentially through a splice routine is how overlaps get created. ## Where the change itself comes from Detecting that a source row *is* a late change — comparing arriving attributes against the version that was in force on the source's own change timestamp — is the load's job, and it depends on the source actually supplying a reliable change date. When it does not, and all you have is a load timestamp, retro-fitting is guesswork: you cannot place a version in a chain without knowing when it started. That limitation is worth naming in an interview, because it is the reason many teams accept slightly-wrong history rather than build the machinery.