SCD/Incremental/CDC Tests for History, Late Data, Deletes, Idempotency, and Restart

Learning outcomes

1. The realistic problem: history can be wrong even when current totals match

AtlasMart’s C001 customer history contains SMB, Growth, then Mid-Market versions. A late fact with event time 2026-09-18T15:00Z arrives after C001 is currently Mid-Market. If the loader resolves “current customer row,” the fact still contributes the correct 75 USD to total revenue but lands in the wrong historical segment. Aggregate control totals alone cannot detect this semantic error.

2. SCD tests: interval shape first

SELECT COUNT(*) AS overlapping_pairsFROM dim_customer_history aJOIN dim_customer_history b  ON a.customer_id = b.customer_id AND a.customer_sk < b.customer_skWHERE a.effective_from < COALESCE(b.effective_to,'9999-12-31T00:00:00Z')  AND b.effective_from < COALESCE(a.effective_to,'9999-12-31T00:00:00Z');

The expected count is 0. The negative fixture moves the Mid-Market start backward to 2026-09-18T20:00Z, overlapping the Growth interval until midnight. The detector must fail before the repair. Half-open intervals remain the Chapter 7 convention: effective_from ≤ event_time < effective_to.

3. Boundary cases must be explicit

A test suite that exercises only “middle of interval” timestamps can miss off-by-one/boundary bugs that systematically misclassify facts at change instants.

4. Controlled failure: current-row lookup for a late fact

late_fact_current_lookup_is_wrong  observed current segment: Mid-Market  event-time segment:        Growth  expected repair:           resolve as of 2026-09-18T15:00:00Zlate_fact_event_time_lookup PASS -> Growth

The negative test deliberately proves the unsafe approach differs from the correct result. “Current row” is convenient but violates historical reconstruction.

5. CDC restart test: watermark must not lead the committed target

BEGIN  upsert target row for source_seq = 208  insert delivery ledger row for source_seq = 208  update committed watermark to 208COMMIT# on any exception before COMMIT:ROLLBACK

The target write, delivery ledger, and watermark are one transaction in this local fixture. A production source/target boundary may require a different transactional mechanism, but the invariant remains: do not acknowledge/advance progress past data that is not durably represented in the target.

6. Executed crash → restart → duplicate delivery trace

before watermark        207attempt 1              rolled_back (simulated crash)target rows for E208   0watermark after crash  207attempt 2              appliedduplicate redelivery   duplicate_ignoredtarget rows for E208   1final watermark        208

This is convergence under at-least-once delivery, not magical exactly-once transport. The pipeline obtains an exactly-once effect for this event because stable identity, transactional state, and deduplication cooperate.

7. Deletes and corrections need tests, not assumptions

Earlier chapters established tombstone/inactivation and revision semantics. A CDC test suite should therefore include insert, update/correction, delete/tombstone, duplicate delivery, late delivery, and out-of-order delivery fixtures. The correct expected result depends on source guarantees: transaction/sequence ordering, before/after images, key reuse, and whether deletes are physical, soft, or absent. A test cannot infer missing source semantics safely.

8. Idempotency is observed through repeated state, not a function name

Calling a task upsert_sales does not prove idempotency. Replay the same event/batch and compare target keys, control totals, history intervals, ledger rows, and checksum. A non-idempotent “increment quantity” update may succeed twice and corrupt measures without creating duplicate keys.

9. History controls and aggregate controls are complementary

The canonical 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit can remain correct while the late C001 fact is assigned to Mid-Market instead of Growth. Therefore acceptance needs both quantitative reconciliation and semantic/history assertions. Conversely, perfectly shaped SCD intervals do not prove total revenue is complete. Layer the tests by failure mode.

10. Restart/replay test matrix

11. Production judgment and bridge

Correctness: history tests protect event-time semantics that totals cannot. Ordering: only assert guarantees your source provides. Schema evolution: CDC payload changes require contract/version tests before replay. Throughput: test batch/window strategies at representative scale, but never advance watermarks early for speed. Security: event logs/before-images can contain sensitive values. Rollback: preserve raw/ledger evidence so a bad transformation can be corrected and replayed. Lesson 4 reconciles the resulting target to trusted source/metric controls.

12. Verification checklist

1. Confirm the valid SCD fixture has zero overlaps and the bad fixture produces at least one overlap.
2. Confirm the late C001 fact resolves to Growth, not current Mid-Market.
3. Confirm the simulated crash leaves watermark 207 and no E208 target row.
4. Confirm retry commits one E208 effect and duplicate redelivery is ignored.
5. Confirm replay tests compare business state, not only task exit status.

Knowledge check

Authoritative references

• Python — unittestStandard-library support for deterministic fixtures, assertions, setup/teardown, and automated test suites.
• SQLite — CREATE TABLE / constraintsAuthoritative syntax and semantics for NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and table constraints used in the local lab.
• SQLite — Foreign Key SupportExplains foreign-key behavior and the need to enable enforcement explicitly in SQLite connections.
• SQLite — TransactionsUsed to demonstrate that target writes, delivery ledger, and watermark advancement must commit atomically for safe restart.
• SQLite — UPSERTReference for conflict-aware idempotent target application in the CDC fixture.
• Python — hashlibUsed to fingerprint deterministic test evidence rather than compare unstable timestamps or environment-specific logs.

13. Lab cleanup/reset

The restart fixture is in memory. Closing the process discards target, ledger, and watermark state. Rerun to reproduce the crash/retry trace deterministically.