Your nightly SCD Type 2 snapshot load did not run for three days — how do you recover the history?

## First: establish what is actually recoverable A snapshot-based Type 2 load records the source's state at the moments it ran. If it did not run, those states were never observed, and no amount of clever SQL invents them. So the first move is not to write a repair script — it is to find out what the source can still replay: - **A change log, CDC stream, or transaction log with sufficient retention** — full recovery is possible. Replay the missed changes in commit order and insert one version per change, each backdated to its own change time. - **The source keeps its own history or audit table** — usually full recovery too, subject to what that table tracks. - **Only current state** — partial recovery. You will produce at most one new version per changed key, and any intermediate state during the outage is permanently lost. - **Immutable daily extracts already landed in storage** — often overlooked, and often the answer: the files for the missed days may still exist even though the transformation did not run. Process them in date order and the history is exact. Say this out loud in an interview. Candidates who jump straight to "rerun the job" have skipped the only question that determines what is possible. ## Why rerunning the daily job three times does nothing A correctly built load is idempotent (it compares before writing). Point it at today's snapshot three times and the first pass versions the changed keys; the second and third find matching hashes and write nothing. You get exactly one version per changed key, dated at whatever the run supplied — which is the same result as running it once, plus the illusion of having replayed three days. If instead the load is *not* idempotent, three runs give you three identical versions, which is worse. Neither outcome recovers anything. The only version of "just rerun it" that works is replaying three *distinct* inputs — three archived extracts or three CDC windows — with the batch date parameter set to each day in turn, in order. ## Inserting a backdated version is not a plain insert When you do have per-day inputs, inserting a version *between* existing versions is more than an `INSERT`. For each affected key you must: 1. place the new version's start at its real change time; 2. re-end-date the version that previously covered that instant so its window closes where the new one begins; 3. recompute which version is open and carries the current flag — if a later version already exists, the backdated one is not current; 4. re-verify the invariants: no overlapping windows, no gaps, exactly one open row per key. Doing this key by key with ad-hoc updates is where corruption creeps in. If the dimension is small enough, the safer route is often to rebuild it: replay the full ordered change history into a fresh table, validate the invariants, and swap. Rebuilding changes surrogate key assignment, so check what already references those keys before choosing it. ## The fact side of the gap A dimension gap rarely stays contained. Facts loaded during the outage looked up dimension versions that were stale — or, for members created during the gap, found nothing and were assigned a placeholder. After you repair the dimension, those fact rows still point at the wrong surrogate keys and need their lookups redone for the affected window. Whether that restatement is worth doing is a business call about how much the affected reports matter; either way it must be a deliberate decision rather than an oversight. ## Close the loop Two follow-ups separate a repair from an incident response. First, **document the gap** where consumers see it — a period during which a dimension's history is known to be coarser than usual is exactly the kind of caveat an analyst needs before trusting a trend. Second, **fix the detection**, because three days is a long time for a daily job to be silent: add a freshness assertion on the dimension's maximum effective-from date, alert on a missed run rather than on a failed one, and make sure the alert reaches someone. A job that fails loudly for three days is an ops problem; a job that stops silently is a design problem.