Join historical reconstruction to an explicit cutover waterline.
Parallel Run, Dual Loads, Historical Backfill, CDC Cutover, and Data Reconciliation
Run old and new pipelines in parallel, reconcile history, and cut over at a tested CDC waterline.
Learning outcomes
Separate historical backfill, ongoing CDC, and consumer cutover into explicit migration phases.
Use dual loads and parallel run to compare old/new outputs over the same source waterline.
Test duplicate delivery, retry, restart, late data, corrections, and deletes where applicable.
Define a cutover waterline and prevent gaps or double application at the handoff.
Reconcile atomic controls and history before consumer routing changes.
Chapter 29 begins from the governed AtlasMart state through
Chapter 28:
10 current paid lines, 8 orders, 12 units, 820 USD gross
revenue, 495 USD cost, and 325 USD gross profit, with source waterline 208. The sales fact
grain remains one paid order line;
gross_revenue_usd.v1 remains paid line amount in
USD under its governed filters and UTC time semantics. A
platform migration is not permission to redefine those
contracts.
The mandatory lab is free/local/synthetic and was executed with Python 3.13.5 and SQLite 3.46.1. SQLite supplies tables, views, transactions, UPSERT and query-plan evidence, but it does not implement stored procedures, cloud roles, CDC connectors, or managed-warehouse cost controls. Stored-procedure behavior and access roles are therefore represented as explicit metadata/policy fixtures, while SQL correctness and restart/idempotency behavior are executed locally. Time zone is UTC; no production credentials or personal data are used.
1. Problem frame: the new warehouse is correct yesterday—what about the handoff minute?
AtlasMart can rebuild six days of history and still fail migration if the old and new systems disagree during cutover. A migration therefore needs two dimensions of completeness: historical state and ongoing change state. Backfill reconstructs the past; CDC/incremental loading keeps the new target current; a waterline defines the boundary at which ownership changes.
2. Historical backfill is a controlled reconstruction
The Chapter 29 fixture backfills the governed legacy surface by
UTC order date, not the raw eleven-row table. Each date records
lines, orders, units, revenue, cost, and profit. The six-day
control set hashes to
240cc2c5a2ae0fdb58be216e6a560f3975f36cb7903ff2e340778e756508bb8a. The hash is convenient evidence that the exact fixture did
not change; it is not a substitute for understanding the
dimensions and measures being hashed.
| UTC date | Lines | Orders | Units | Revenue | Cost | Profit |
|---|---|---|---|---|---|---|
| 2026-09-17 | 1 | 1 | 1 | 80 | 50 | 30 |
| 2026-09-18 | 2 | 2 | 2 | 195 | 115 | 80 |
| 2026-09-19 | 1 | 1 | 1 | 195 | 100 | 95 |
| 2026-09-20 | 2 | 1 | 2 | 115 | 65 | 50 |
| 2026-09-21 | 2 | 1 | 4 | 125 | 85 | 40 |
| 2026-09-22 | 2 | 2 | 2 | 110 | 80 | 30 |
3. Dual load and parallel run are different ideas
Dual load means the same source changes are delivered to old and new pipelines during a transition. Parallel run means old and new consumer-visible outputs are both computed and compared. You can have dual load without meaningful parallel validation, and you can parallel-run from a common immutable source without literally duplicating ingestion. The migration record should state which mechanism is used and where duplication can occur.
4. Waterline 208: define the handoff explicitly
The migration starts with the new target reconstructed through source sequence 208. Before routing consumers, replay events at the boundary to prove idempotency. The fixture replays E207 and E208 twice. The first deliveries are recorded; the duplicate deliveries collide with the event-id primary key and are ignored. The final governed controls remain 820 / 495 / 325 USD.
def apply_event(conn, event_id, row): try: conn.execute("BEGIN") conn.execute( "INSERT INTO applied_events(event_id, source_seq) VALUES (?, ?)", (event_id, row["source_seq"]), ) conn.execute("""INSERT INTO sales_line VALUES (...) ON CONFLICT(order_id,line_no) DO UPDATE SET ...""", row["values"]) conn.commit() return "applied" except sqlite3.IntegrityError: conn.rollback() return "duplicate_ignored"# Executed sequence:# E207 -> applied# E208 -> applied# E207 -> duplicate_ignored# E208 -> duplicate_ignored5. Cutover state machine
DISCOVER / INVENTORY -> BACKFILL history through sequence 208 -> DUAL-LOAD or shared immutable input -> PARALLEL-RUN old + new -> RECONCILE atomic + daily + metric + security + SLOs -> FREEZE cutover waterline = 208 -> DRAIN/replay <= 208 idempotently -> ROUTE consumers to modern -> OBSERVE smoke tests and SLOs -> success: continue observation / plan legacy retirement -> failure: route consumers back to legacy; retain evidence6. Controlled failure: cut over after row-count parity
Backfill eleven physical rows, see eleven rows on both sides, then switch the BI connection.
Diagnosis: both physical tables match yet the
governed metric is wrong because the new report includes
TEST900. Row parity cannot detect a semantic
divergence. It also says nothing about history by day, access
controls, or duplicate CDC replay.
Repair: reconcile at multiple surfaces: raw copy, accepted fact grain, daily history, governed metrics, event/watermark state, security behavior, and critical reports. Then cut over at a named waterline only after the acceptance gate passes.
7. Local lab: assert history and restart behavior
expected = { "controls": (10, 8, 12, 820, 495, 325), "waterline": 208, "history_sha256": "240cc2c5a2ae0fdb58be216e6a560f3975f36cb7903ff2e340778e756508bb8a", "cdc": ["applied", "applied", "duplicate_ignored", "duplicate_ignored"]}assert legacy_controls == expected["controls"]assert modern_controls == expected["controls"]assert legacy_daily == modern_dailyassert cdc_results == expected["cdc"]# A failed transaction must not advance the event ledger or expose a partial target.# Rerun the same boundary deliveries and confirm totals are unchanged.8. Late data, deletes, and corrections during migration
The fixture's boundary replay is intentionally small, but production cutover plans must reuse the Chapter 15/24 rules: stable event identity, source ordering/transaction semantics, delete/tombstone behavior, late-event policy, schema evolution, atomic watermark advancement, and replay tests. If the old system and new system resolve late facts to different historical dimension versions, the migration is semantically divergent even when total revenue matches.
9. Verification checklist
- Backfill start/end scope and source snapshot/waterline are recorded.
- Old/new history reconciles by the dimensions consumers actually use, not only overall totals.
- Boundary events are replayed and duplicates do not change state.
- Watermark/event ledger advancement is atomic with target writes.
- Rollback keeps a known-good route and source of truth available.
All destructive actions target only a disposable
atlasmart_ch29_lab directory. Never point these
commands at a production warehouse. Reset with
python -c "import shutil; shutil.rmtree('atlasmart_ch29_lab',
ignore_errors=True)"
and rerun the fixture from a clean directory.
10. Production judgment and bridge
Parallel run is expensive because it intentionally maintains two state surfaces, but that cost buys evidence. The observation window should be long enough to exercise critical business cycles and edge cases, not an arbitrary universal number. Next, the chapter focuses on semantic behavior itself: metric versions, filters, time windows, units, rounding, null rules, and compatibility across old and new platforms.
Knowledge check
What is the difference between backfill and CDC during migration?
Show answer
Backfill reconstructs bounded historical state; CDC/incremental loading carries ongoing changes. Both must meet at an explicit waterline without gaps or double application.
Does duplicate-safe CDC prove exactly-once end to end?
Show answer
No. It proves the fixture converges under repeated delivery using stable event IDs and atomic local transactions. End-to-end exactly-once requires evidence across every source, transport, processor, and sink boundary.
Why reconcile by day as well as total revenue?
Show answer
Two systems can share the same grand total while assigning facts to different dates or historical dimension versions, which breaks time-series and segmented analysis.
When is consumer cutover safe?
Show answer
Only after the defined acceptance gate passes at the chosen waterline and rollback remains executable; there is no universal observation duration or threshold.
Authoritative references
- SQLite — Transactions for the local atomicity model used in the executable harness.
- SQLite — UPSERT for the local idempotent replay example.
- SQLite — EXPLAIN QUERY PLAN for the local plan evidence; its output format is explicitly not a stable application API.
- Kimball Group — DW/BI resources for dimensional modeling, business process/grain discipline, and lifecycle-oriented warehouse delivery.
- Big Data Academy — Data Warehousing and Dimensional Modeling curriculum for this course's stable AtlasMart contracts and chapter sequence.