Chapter 11 · Surrogate Keys, Late-Arriving Facts/Dimensions, Deletes, Restatements, and History Corrections
Late-Arriving Facts: Resolve Dimension Version as of Event Time Without Assigning Current History Incorrectly
Load a deliberately late AtlasMart sale and prove that its customer surrogate key is resolved from business event time rather than the current dimension row at load time.
Learning outcomes
Order O1000 happened at 15:00 on September 18 but
does not reach the warehouse until September 22. By load time,
C001's current segment is Mid-Market. Historical truth depends
on the customer's version at the event time, not the
newest row available when the loader runs.
Define a late-arriving fact in terms of delayed delivery relative to its dimensional context.
Resolve the correct dimension surrogate key with a half-open event-time as-of join.
Contrast correct event-time resolution with the wrong “join to current row” shortcut.
Prove replay/idempotency through a source-event registry and fixed business grain.
Reconcile before/after controls so a late fact changes metrics only by its own atomic measures.
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.
In this lab, ingestion time is audit metadata. It does not replace business event time for historical dimension lookup.
1. The wrong row is easy to find—and wrong historically
After the backdated segment correction in Lesson 3's scenario, C001 has a Growth version covering September 18 from 08:30 until midnight September 19, followed by a current Mid-Market version. O1000 occurred at 15:00 on September 18, so the correct segment is Growth even though Mid-Market is current when the row arrives on September 22.
SELECT customer_sk,segmentFROM dim_customer_historyWHERE durable_customer_id = 'D-CUST-001' AND effective_from <= '2026-09-18T15:00:00' AND '2026-09-18T15:00:00' < effective_to;-- Wrong shortcut:SELECT customer_sk,segmentFROM dim_customer_historyWHERE durable_customer_id='D-CUST-001' AND is_current=1;The first query resolves the Growth version (surrogate key 9 in this deterministic fixture); the current-row shortcut resolves Mid-Market and misclassifies historical revenue.
2. Insert the late fact without inventing history
def load_late_sale(conn, sale, first_seen_at): order_id,line_no,event_id,event_ts,durable,product,qty,amount,cost = sale if conn.execute( 'SELECT 1 FROM fact_event_registry WHERE source_event_id=?', (event_id,) ).fetchone(): return 'replay_ignored' customer_sk = resolve_customer_sk(conn, durable, event_ts) conn.execute('INSERT INTO fact_event_registry VALUES (?,?,?,?)', (event_id,order_id,line_no,first_seen_at)) # insert revision 1 at the original business event time ...
The registry makes duplicate delivery observable. The business
key remains (order_id,line_no); the event registry
does not redefine grain.
3. Observable control delta
| 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 late line adds exactly 1 line, 1 order, 1 unit, 75 GMV, 45 cost, and 30 gross profit. If any other control changes, the load has done more than ingest the late measurement event.
4. Deliberately wrong approach: bind every late row to current history
If O1000 is linked to C001's current Mid-Market surrogate key, a report “GMV by segment as-of sale time” moves 75 from Growth to Mid-Market. Total GMV still equals 700, so a total-only reconciliation would miss the error. Historical correctness therefore needs both control totals and dimensional-attribution tests.
5. Late dimension versus late fact
A late fact arrives after the relevant dimension history already exists; resolve the history that was effective at the event time. A late dimension means the descriptive context itself is missing or arrives retroactively. That can require a placeholder/inferred member, a Type 2 split, and possibly restating already-loaded fact foreign keys. The two cases share temporal reasoning but are not the same workflow.
6. Freshness and observability
Record both event_ts and
first_seen_at. Their difference is ingestion
lateness. Alerting on lateness distributions helps detect source
or pipeline degradation, but a small lag is not automatically
“better” if the source sends incomplete or unordered events.
Correctness and freshness are separate SLO dimensions.
Knowledge check
Check your understanding
- Which timestamp selects the customer version for O1000?
- Why can total GMV reconcile while the late-fact load is still wrong?
- What does the source-event registry prove?
- How does a late dimension differ from a late fact?
- Why keep both event and ingestion timestamps?
Review the answers
1. The business event timestamp, 2026-09-18T15:00:00, under the stated history policy.
2. A wrong current-version join can preserve the numeric total but attribute the amount to the wrong historical segment.
3. It proves duplicate suppression for the local fixture; it does not prove end-to-end exactly-once delivery.
4. A late fact is delayed measurement data; a late dimension is delayed or retroactively corrected descriptive context and may require placeholder completion or fact re-keying.
5. They support historical lookup and observable freshness/lateness without conflating the two semantics.
Summary and next step
Late facts must bind to the dimension version that was valid when the business event occurred. Lesson 3 handles the harder case where the dimension history itself arrives late and splits an interval that has already been used by facts.
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.