After retro-fitting a missed Type 2 dimension version, which existing fact rows must be repointed?

## Bounding the blast radius When a missed Type 2 version is spliced into a chain, exactly one window changes hands. Version 9041 covered 1900-01-01 to 2024-03-31; after the splice it covers 1900-01-01 to 2024-02-09, and the new version 9611 covers 2024-02-10 to 2024-03-31. Fact rows are affected if and only if all three of these hold: - they belong to the same business key, - their event date falls in 2024-02-10 to 2024-03-31, - they currently carry surrogate key 9041. Everything else is untouched. This precision matters, because the tempting alternative — rerun the whole fact load, or reassign keys across the fact table — is orders of magnitude more expensive and puts rows at risk that had nothing to do with the change. ```sql UPDATE fct_orders SET customer_sk = 9611 WHERE customer_sk = 9041 AND order_date BETWEEN DATE '2024-02-10' AND DATE '2024-03-31'; ``` In practice you drive this from a set of retro-fitted versions rather than literals, joining the fact table to the newly inserted rows on business key and date range. Either way it is one set-based statement over a bounded slice, not a row-by-row repair. ## What goes stale downstream Repointing a foreign key changes how those facts group. Everything computed from them for the affected period is now wrong until rebuilt: - **Aggregate and summary tables** covering the window — they must be rebuilt for that period, not just going forward. - **Extracts and files already delivered** to another team or a regulator — those cannot be rebuilt, only reissued or annotated. - **Cached dashboards and BI extracts**, which will otherwise keep serving the pre-restatement numbers for as long as their refresh cycle lasts, producing the confusing state where two dashboards disagree. A restatement that fixes the base fact table and leaves its dependants alone is worse than doing nothing, because the mart is now internally inconsistent and there is no single wrong number to point at. ## The decision that is not technical The update is easy. The question an interviewer is really probing is what you do about a period the business has already closed. **Restate silently.** The numbers simply change between one run and the next. Technically the most accurate state, and the fastest way to lose the finance team's confidence permanently — a report that quietly disagrees with the copy someone printed last month is indistinguishable from a broken report. **Restate with disclosure.** Change the numbers, and record what changed: which business keys, which window, which run, how much the affected measures moved. Publish that record alongside the mart, and mark the restated period visibly. This is usually the right default for analytical marts. **Freeze and adjust forward.** For periods under formal close, do not touch history at all. Leave the fact rows on their original keys and book the correction as an explicit adjustment in the current open period. This is standard accounting discipline and it is often mandated rather than chosen; the warehouse's job is to reflect it rather than to out-argue it. The honest answer in an interview names all three and says the choice belongs to the data's owner, not to the pipeline — while insisting that whichever is chosen must be written down and applied consistently, because inconsistency between subject areas is what actually confuses consumers. ## Keep the restatement auditable Whatever the policy, capture the event. A restatement log — which dimension versions were retro-fitted, which fact windows were repointed, when, by which run, and how many rows moved — is what turns "the March number changed" from a mystery into a two-minute lookup. Snapshotting the affected measure totals before and after is cheap and makes the impact quantifiable rather than anecdotal. ## Guard the result The same structural test that catches a current-flag join catches a half-finished restatement: every fact's event date must fall inside the effective window of the dimension version it points at. Run it after the repoint. Any survivor means a fact was left on the shortened version despite falling in the carved window — usually because the update's date bounds and the splice's date bounds disagreed by a day, which is exactly the kind of off-by-one that inclusive-versus-exclusive end-date conventions produce. ## When not to bother If the retro-fitted attribute is not used by any consumer for that period — a descriptive field nobody groups by — the repoint is still correct but the urgency is low, and batching it with the next scheduled rebuild is a defensible call. Judgement about which attributes are load-bearing for which reports is part of the answer; treating every retro-fit as an emergency is as much a failure of proportion as ignoring them all.