Design a Correction Workflow that Preserves Reproducibility While Supporting Authorized Restatements

Learning outcomes

A trustworthy warehouse must be able to explain not only today's answer but why yesterday's published answer changed. The final Chapter 11 lab runs a correction sequence from the accepted Chapter 10 state through a backdated dimension split, fact re-keying, a late sale, an authorized metric restatement, source deletion, and a privacy workflow—without deleting the evidence needed to reconstruct each checkpoint.

1. Acceptance sequence and controls

The sequence deliberately contains two kinds of correction. The backdated segment split changes dimension attribution but preserves 625 baseline GMV. The late O1000 line adds 75 GMV. The finance-authorized O1002 restatement subtracts 10 GMV, producing final current GMV 690. Delete/privacy workflows then leave sales measures unchanged.

2. Reproducibility hashes at each checkpoint

Each hash covers current fact revisions, complete customer-history rows, correction records, tombstones, and deletion manifests under a canonical JSON serialization. Store the hash with batch/release metadata if consumers need evidence that a snapshot is reproducible.

3. End-to-end deterministic acceptance script

conn = build_base_conn()assert fact_controls(conn) == (7,4,9,625,380,245)# 1) retroactive dimension correctionassert apply_backdated_segment(conn) == 'split_interval'changed = restate_dimension_keys(    conn,'D-CUST-001','2026-09-18T08:30:00','2026-09-19T00:00:00')assert len(changed) == 2assert fact_controls(conn) == (7,4,9,625,380,245)# 2) late fact resolves event-time versionsk = apply_late_fact(conn)assert conn.execute('SELECT segment FROM dim_customer_history WHERE customer_sk=?',(sk,)).fetchone()[0] == 'Growth'assert apply_late_fact(conn) == 'replay_ignored'assert fact_controls(conn) == (8,5,10,700,425,275)# 3) authorized metric restatementassert apply_metric_restatement(conn) == (200,190)assert apply_metric_restatement(conn) == 'replay_ignored'assert fact_controls(conn) == (8,5,10,690,425,265)# 4) lifecycle/privacy workflows do not silently change salesassert apply_source_delete(conn) == 'inactivated'assert apply_privacy_erasure(conn)[0] != apply_privacy_erasure.__name__  # illustrative call site; run once in real scriptassert fact_controls(conn) == (8,5,10,690,425,265)

The actual generated fixture executes the erasure exactly once and separately verifies replay returns replay_ignored; the compact lesson snippet emphasizes the sequence rather than duplicating the full helper implementation.

4. Audit surfaces answer different questions

5. Current state and historical state are both first-class

The current BI view should filter is_current=1 fact revisions. Reproducibility/debugging workflows may query superseded revisions by correction ID or recorded time. Do not expose revision mechanics accidentally to every analyst; provide governed current and audit views with explicit semantics.

-- current published salesSELECT * FROM fact_sales_revision WHERE is_current=1;-- audit trail for corrected O1002 line 1SELECT order_id,line_no,revision_no,extended_amount,is_current,correction_id,recorded_atFROM fact_sales_revisionWHERE order_id='O1002' AND line_no=1ORDER BY revision_no;

The audit query shows both 200 and 190 revisions, with only the corrected revision current.

6. Rollback means a new governed revision

If the O1002 correction is later found wrong, do not delete revision 2 and resurrect revision 1 invisibly. Create a new authorized correction (revision 3) that restores the approved value, record who authorized it and why, recompute affected aggregates, and publish a new state hash. This keeps the sequence of published truths reproducible.

7. Downstream impact and communication

Before publishing a restatement, identify dashboards, extracts, semantic metrics, aggregates, financial reports, ML features, caches, and external data products that consume the affected rows or attributes. A correction can preserve total GMV while changing segment attribution, or change GMV while leaving dimensions untouched. The notification should state which semantics changed, effective period, correction ID, before/after controls, and whether consumers need refresh or backfill.

8. Cleanup/reset

The mandatory lab is in-memory SQLite. Closing the process discards all tables; calling build_base_conn() recreates the accepted Chapter 10 baseline. No production data, credentials, or paid services are involved.

Knowledge check

Summary and next step

Chapter 11 now treats identity, lateness, deletion, and correction as explicit state transitions with evidence. Chapter 12 next moves upstream to source-system profiling, data contracts, lineage, and ingestion readiness so ambiguous or unstable source semantics are caught before they reach these correction workflows.

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.