Chapter 11 · Surrogate Keys, Late-Arriving Facts/Dimensions, Deletes, Restatements, and History Corrections
Source Deletes, Soft Deletes, Inactivation, GDPR Erasure, and Analytical Auditability
Model source deletion, soft delete/inactivation, and a controlled privacy-erasure example with tombstones and deletion manifests while separating technical patterns from legal policy.
Learning outcomes
AtlasMart receives two different deletion signals. CRM reports that C002 was deleted from the operational source; separately, an authorized privacy workflow targets C004's direct identifiers. Treating both signals as “DELETE the warehouse row” would erase historical meaning, break fact references, or fail to remove data from other state surfaces.
Distinguish source hard delete, soft delete/inactivation, warehouse tombstone, and privacy-erasure request.
Preserve historical analytical evidence for a source deletion by applying an explicit inactivation policy rather than silently deleting dimension history.
Use a deletion manifest to record scope and verification without retaining the erased clear-text value in the manifest.
Explain why GDPR erasure is not absolute and why legal/controller policy must determine required scope and exceptions.
Test the difference between intended logical deletion and effective deletion across dimensions, identity maps, facts, exports, backups, and other copies.
Chapter 11 preserves all accepted AtlasMart contracts from Chapters 01–10. Before this chapter, current sales contain seven paid order-line facts, four paid orders, nine units, 625 paid GMV, 380 cost-at-sale, and 245 gross profit. Customer history uses Chapter 07's half-open business-effective intervals; Chapter 08 special dimensions, Chapter 09 bridges, and Chapter 10 fact-shape contracts remain valid. Chapter 11 adds versioned fact revisions and correction/audit surfaces without changing the business grain. A backdated C001 segment correction affects historical attribution, one late order line increases the current sales population, and one authorized O1002 amount correction changes a published metric under an explicit restatement record.
The mandatory lab is synthetic, local, and disposable. It uses
Python's standard-library sqlite3 module and
SHA-256 hashes only as reproducibility evidence. The lab
proves local key-resolution, revision, temporal, and audit
mechanics; it does not prove distributed exactly-once
delivery, legal compliance, production deletion completeness,
or cloud-warehouse behavior. Privacy/erasure requirements
depend on applicable law, controller policy, purpose,
retention obligations, backups/exports, and other state
surfaces.
The C004 erasure is a synthetic engineering example, not a legal prescription. Whether pseudonymous facts may remain, what constitutes personal data, what exceptions apply, and how backups/exports must be handled are policy- and jurisdiction-specific questions.
1. “Delete” is not one warehouse operation
| Signal | AtlasMart technical response | Historical effect | Governance note |
|---|---|---|---|
| Source record disappears | write tombstone + Type 2 inactive version | prior dimension versions and facts remain | source absence is not automatically a privacy request |
| Soft delete/inactivation | new lifecycle state effective from business time | history preserved | safe only if business semantics define inactive |
| Authorized privacy erasure example | remove direct identifiers + identity-map link; record hashes/manifest | facts remain linked to pseudonymous durable ID in this lab | controller/legal policy must decide if this scope is sufficient |
| Blind hard delete | DELETE dimension row | may break foreign keys or erase historical context | not an acceptable default |
The warehouse must name which signal it received and which state surfaces the policy covers.
2. Source delete becomes a tombstone plus inactivation
INSERT INTO source_tombstone( tombstone_id,source_system,source_customer_id,durable_customer_id, observed_at,business_effective_at,reason,payload_hash) VALUES (...);-- close the current C002 version at business-effective delete time-- insert a new Type 2 row with lifecycle_status='inactive'-- do NOT delete prior versions or historical fact rowsThe tombstone proves the source delete was observed. The inactive Type 2 row tells analytical consumers that the customer ceased to be active from the stated business time. Those are separate evidence surfaces.
3. Deliberately wrong approach: hard-delete referenced history
With foreign keys enabled, deleting a customer version referenced by a fact raises an integrity error in the local fixture. Disabling constraints would make the delete “succeed” while creating orphaned facts. Neither behavior is a privacy strategy.
-- This is intentionally unsafe:DELETE FROM dim_customer_historyWHERE customer_sk = :referenced_customer_sk;-- With referential integrity enabled, the lab rejects it.-- Without constraints, facts can become orphaned and historical context disappears.4. Privacy-erasure example with a manifest
The synthetic C004 workflow removes the CRM identity-map link
and replaces direct customer-name/source-ID fields in every C004
dimension version with [ERASED]. It records only
SHA-256 before/after evidence plus scope metadata in
deletion_manifest; it does not copy the erased
clear-text name into the manifest.
before_hash = customer_private_hash(conn, 'D-CUST-004')conn.execute("""UPDATE dim_customer_history SET source_customer_id='[ERASED]', customer_name='[ERASED]', privacy_state='erased' WHERE durable_customer_id='D-CUST-004'""")conn.execute("DELETE FROM identity_map WHERE durable_customer_id='D-CUST-004'")after_hash = customer_private_hash(conn, 'D-CUST-004')# deletion_manifest stores scope/status/hashes, not the erased clear-text valueSales totals remain unchanged in this teaching policy. That does not mean retaining pseudonymous transaction facts is universally lawful; determine that from the applicable purpose, legal basis, retention duties, identification risk, and controller policy.
5. GDPR boundary: erasure has conditions and exceptions
The European Commission describes a right to erasure in certain circumstances and also notes exceptions, including cases where processing is necessary for legal obligations or legal claims. Therefore an engineering team should not translate “GDPR” into one unconditional SQL deletion recipe. Define the applicable policy with legal/privacy stakeholders and test every in-scope copy: raw/staging data, warehouse dimensions/facts, semantic extracts, caches, exports, logs, backups, and downstream products.
6. Control totals after deletion workflows
| Stage | Current lines | Orders | Units | GMV | Cost | Gross profit |
|---|---|---|---|---|---|---|
| Accepted Chapter 10 baseline | 7 | 4 | 9 | 625 | 380 | 245 |
| After backdated dimension split + key restatement | 7 | 4 | 9 | 625 | 380 | 245 |
| After late O1000 line arrives | 8 | 5 | 10 | 700 | 425 | 275 |
| After authorized O1002 amount restatement | 8 | 5 | 10 | 690 | 425 | 265 |
| After source-delete + privacy workflow | 8 | 5 | 10 | 690 | 425 | 265 |
The source deletion and the lab's identifier-erasure workflow do not change current sales totals. Their acceptance criteria concern state, access, identity links, and audit evidence—not revenue arithmetic.
Knowledge check
Check your understanding
- Why is a source-system delete not automatically a privacy-erasure request?
- What does the tombstone prove?
- Why does the lab not hard-delete historical customer rows?
- Why does the deletion manifest store hashes instead of the erased clear-text identifier?
- Does this lab define GDPR compliance?
Review the answers
1. It reports source lifecycle state; privacy obligations require separate policy/legal context and may have different scope.
2. That a specific source delete signal was observed with source identity, times, reason, and payload hash.
3. That would remove analytical context and can violate referential integrity; the source-delete policy uses Type 2 inactivation instead.
4. The manifest should verify that a transformation occurred without reintroducing the value it was intended to remove.
5. No. It demonstrates auditable technical mechanics; applicable law and controller policy determine required scope, exceptions, retention, and verification.
Summary and next step
Deletion semantics must be named, scoped, and verified across state surfaces. Lesson 5 combines late data, historical correction, source deletion, privacy handling, and metric restatement into one reproducible correction workflow.
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.