Late-Arriving Dimension Changes, Backdated SCD2 Splits, and Historical Restatement Policies

Learning outcomes

AtlasMart learns on September 22 that C001 should have been in segment Growth starting September 18 at 08:30, before the already-loaded O1001 sale. This is not a new “current” change. It is a retroactive correction to historical dimension context, so the warehouse must split the affected Type 2 interval and evaluate fact foreign keys inside that interval.

1. Split the interval, do not overlap it

Before correction, C001 is SMB from January 1 through September 19, then Mid-Market. The correction inserts Growth at 08:30 on September 18, producing three non-overlapping intervals: SMB before 08:30, Growth until September 19, then Mid-Market.

-- conceptual result after correction-- [2026-01-01 00:00, 2026-09-18 08:30)  SMB-- [2026-09-18 08:30, 2026-09-19 00:00)  Growth-- [2026-09-19 00:00, 9999-12-31 00:00) Growth? NO -> existing Mid-Market row remains-- Integrity rules:-- every interval has effective_from < effective_to-- adjacent intervals do not overlap-- exactly one row is_current = 1 per durable customer

The correction modifies the historical interval that contained the effective timestamp; it does not replace the later Mid-Market business change.

2. Identify the facts whose keys became stale

SELECT order_id,line_no,event_ts,customer_skFROM fact_sales_revisionWHERE is_current=1  AND durable_customer_id='D-CUST-001'  AND event_ts >= '2026-09-18T08:30:00'  AND event_ts <  '2026-09-19T00:00:00';-- O1001 line 1 and line 2 are in the corrected interval.-- Their measures do not change; only customer_sk must be re-resolved.

The executable fixture finds exactly 2 already-loaded fact rows in this interval. Each receives a new fact revision with the correct customer surrogate key; the superseded revision remains queryable for audit.

3. Restatement by new revision, not silent update

The lab marks the old fact revision non-current and inserts revision 2 with the same order-line grain, event timestamp, quantity, amount, cost, and source event ID, but the corrected customer surrogate key. A correction ledger records before/after hashes and the authorizing role.

BEGIN;-- locate current fact revision-- resolve corrected customer_sk from event_tsUPDATE fact_sales_revisionSET is_current=0WHERE fact_revision_sk=:old_revision;INSERT INTO fact_sales_revision (...)SELECT ..., revision_no+1, 1, 'CORR-DIM-C001-20260918', :applied_atFROM fact_sales_revisionWHERE fact_revision_sk=:old_revision;INSERT INTO correction_ledger (...) VALUES (...before_hash..., ...after_hash...);COMMIT;

Production systems may implement this with different SQL/transaction primitives. The invariant is versioned, attributable change—not a specific vendor MERGE syntax.

4. Totals must remain invariant

After the dimension split and two key restatements, sales controls are still 7 lines, 4 orders, 9 units, 625 GMV, 380 cost, and 245 gross profit. The correction changes historical slicing, not measurement values.

5. Deliberately wrong approaches

Wrong 1: append a backdated Type 2 row without closing the covering interval; as-of joins can then return multiple customer rows. Wrong 2: change only the dimension and leave affected facts pointing at the obsolete surrogate key; totals reconcile but historical attribution remains wrong. Wrong 3: rewrite old fact foreign keys in place and destroy evidence of what was previously published.

6. Restatement scope and downstream impact

Before applying a retroactive dimension correction, identify affected facts, aggregates, semantic metrics, extracts, caches, and reports. Some systems materialize segment-level aggregates; they may need rebuild even when atomic amounts do not change. Publish a correction batch ID so consumers can trace why historical slices moved.

Knowledge check

Summary and next step

Retroactive dimension history is a governed restatement, not a routine append. Lesson 4 turns to source deletion and privacy erasure, where “delete” can mean very different things operationally and legally.

Authoritative references

• Kimball Group — Dimension Surrogate Keys — Why warehouse dimensions need warehouse-controlled surrogate keys rather than relying only on operational natural keys.
• Kimball Group — Natural, Durable, and Supernatural Keys — Reference for persistent durable identity distinct from mutable/reused natural keys and from Type 2 version surrogate keys.
• Kimball Group — Late Arriving Fact — Late facts must resolve dimension keys that were effective when the measurement event occurred.
• Kimball Group — Late Arriving Dimension — Placeholder/late-dimension handling and retroactive Type 2 changes that may require fact restatement.
• Kimball Group — Dimensional Modeling Techniques — Authoritative technique index for surrogate keys, late-arriving facts/dimensions, SCDs, and related dimensional patterns.
• European Commission — Information for individuals — Official overview of GDPR data-subject rights, including the right to erasure and its limits.
• European Commission — Dealing with requests from individuals — Official guidance explaining that erasure applies in certain cases and has exceptions; technical deletion scope must follow the applicable controller/legal policy.
• SQLite — CREATE TABLE — Constraint semantics used by the deterministic local fixture.
• SQLite — Partial Indexes — Used by the lab to enforce one current customer version and one current fact revision per business key.