Module 6: Migrating Data Without Downtime
Reconciling the discrepancies
Overview
The parallel-run of lesson 5 did its job: it found 2 discrepancies and blocked the read-switch. But finding isn't fixing. A discrepancy report is a diagnosis, not a cure; the number doesn't drop on its own. This lesson is the cure: the reconciliation, the work of investigating each discrepancy, understanding its cause, applying the right fix, and comparing again —repeating until the number reaches zero—.
The key to reconciliation is that each type of discrepancy has its own fix, and that fix is deduced from the cause, not the symptom. An absent row (MISSING_IN_NEW) almost always comes from the backfill —it skipped a row—, and the fix is to re-run the backfill, which you already know is idempotent, so doing it is safe and only covers what's missing. A value difference (VALUE_MISMATCH) almost always comes from the ACL —it translated a field wrong— or the dual-write —it lost a write—, and the fix is to correct the cause and re-migrate the affected row. Reconciling well is, above all, classifying well: the parallel-run already gave you the type of each discrepancy; the reconciliation uses it to apply the corresponding fix.
And there's a distinction that separates a superficial reconciliation from a good one: fixing the symptom (this row) versus fixing the cause (why this row —and maybe others— came out wrong). If webcam was missing because the backfill excluded the products without stock, re-copying webcam by hand covers that row, but leaves the filter broken: the next time you run the backfill, or if there are other products without stock, the problem reappears. Truly reconciling is fixing the filter (the cause) and then re-running the backfill, which now does cover webcam and all the others without stock. The lesson insists on this because it's the difference between a migration that converges to zero and one that plays whack-a-mole, covering symptoms while the cause keeps generating new ones.
Connection with the module. This lesson closes the cycle lesson 5 opened: parallel_run detects, reconcile fixes, parallel_run checks again —and so on until zero—. That zero, sustained over time, is the precondition of the read-switch of lesson 7. The fixes this lesson uses are pieces you already know: re-running the backfill (lesson 4, idempotent) and correcting the ACL (the anti-corruption layer of module 5). Notice the boundary: here we reconcile 2 rows by hand to see the mechanics; at scale, reconciling thousands of discrepancies involves automated reconciliation tools and repair queues —from the Data Engineering ecosystem—. The principle —classify by type, fix the cause, re-compare— is the same at any scale.
An analogy: closing the auditor's to-do list
The auditor of lesson 5 handed you their report, and it doesn't say "all wrong" or "all fine": it says exactly two things, each with its type. First: "the account 'Webcam' is missing from the new ledger". Second: "the account 'Keyboard' appears in both, but with a different name: the old says 'Mech Kbd' and the new says 'mech kbd'". Now it's your turn, the accountant, to close each item —and the way to close each one depends on which type it is—.
The item of the missing account. "Webcam" isn't in the new ledger. Why? You investigate and discover the cause: when you copied the old archive to the new, your assistant had the instruction "don't copy the accounts of out-of-stock products", and "Webcam" was out of stock. The correct fix isn't just to copy "Webcam" by hand —that would cover this account but leave the wrong instruction—; it's to correct the instruction ("copy all the accounts, out of stock or not") and run the copy again. Since your copy process only adds what's missing (never duplicates or overwrites what's already copied), running it again is safe: this time it includes "Webcam" and any other out-of-stock account that had also been skipped. A fix that covers the cause, not just the symptom.
The item of the different name. "Keyboard" is in both ledgers, but the new wrote it in lowercase. Why? The cause is your translator —the person who passes the names from the old format to the new—: by mistake, it was lowering all the names to lowercase. The fix is to correct the translator (to respect the uppercase) and re-translate that account with the corrected rule. If the translator made that mistake with more accounts, correcting it fixes them all at once.
And then, the auditor reviews again. Here's what makes the process reliable: after closing the two items, you don't declare victory on your own. You ask the auditor to compare the two ledgers again. If their new report says "zero differences", then —and only then— the ledgers match. If they still found something (maybe your fix uncovered another difference, or you didn't cover the cause well), you keep reconciling. The cycle fix → re-audit repeats until the report comes out clean. And a single clean audit isn't quite enough: you want to see it match several days in a row, because the system is alive and keeps receiving movements; a sustained match tells you it wasn't a coincidence.
That's reconciliation: closing each item according to its type, fixing the cause and not just the symptom, and comparing again until the number is zero —sustained—. Let's close the two items, executed.
Worked example: reconciling the two discrepancies to zero
We start from the exact state lesson 5 left: the new_db with the two defects —webcam absent and kbd-mech with the title in lowercase—. We run the parallel_run, get the 2 discrepancies with their type, and reconcile each one according to its type: MISSING_IN_NEW is fixed by re-running the backfill (idempotent); VALUE_MISMATCH is fixed with the corrected ACL re-migrating the row. At the end, we run the parallel_run again to check the zero.
# --- The fixed ACL: translates the title well (without lowering it to lowercase). ---
def to_new_model(old_row):
return {
"id": old_row["sku"],
"title": old_row["name"],
"price_usd": old_row["price_cents"] / 100,
"in_stock": old_row["stock"] > 0,
}
def canon_old(row):
return (row["sku"], row["name"], row["price_cents"], row["stock"] > 0)
def canon_new(row):
return (row["id"], row["title"], round(row["price_usd"] * 100), row["in_stock"])
def parallel_run(old_db, new_db):
disc = []
for sku in sorted(old_db):
if sku not in new_db:
disc.append((sku, "MISSING_IN_NEW"))
elif canon_old(old_db[sku]) != canon_new(new_db[sku]):
disc.append((sku, "VALUE_MISMATCH"))
return disc
# --- idempotent backfill: fills ONLY what's missing in the new. ---
def backfill(old_db, new_db):
copied = 0
for sku, old_row in old_db.items():
if sku not in new_db:
new_db[sku] = to_new_model(old_row)
copied += 1
return copied
# --- The state parallel_run left in L5: the new with two defects. ---
old_db = {
"ssd-1tb": {"sku": "ssd-1tb", "name": "SSD 1TB", "price_cents": 8499, "stock": 12},
"usb-c-hub": {"sku": "usb-c-hub", "name": "USB-C Hub", "price_cents": 3499, "stock": 40},
"webcam": {"sku": "webcam", "name": "Webcam HD", "price_cents": 5999, "stock": 0},
"hdmi-cable": {"sku": "hdmi-cable", "name": "HDMI 2m", "price_cents": 1299, "stock": 30},
"kbd-mech": {"sku": "kbd-mech", "name": "Mech Kbd", "price_cents": 7999, "stock": 5},
"mouse-pro": {"sku": "mouse-pro", "name": "Mouse Pro", "price_cents": 2499, "stock": 8},
}
new_db = {sku: to_new_model(row) for sku, row in old_db.items()}
del new_db["webcam"] # defect 1: backfill skipped the row
new_db["kbd-mech"]["title"] = "mech kbd" # defect 2: old ACL lowered to lowercase
# --- BEFORE ---
disc = parallel_run(old_db, new_db)
print("Before reconciling:")
print(f" discrepancies = {len(disc)}")
for sku, kind in disc:
print(f" {sku:<10} {kind}")
# --- RECONCILIATION, discrepancy by discrepancy, by its type. ---
print("\nReconciliation:")
for sku, kind in disc:
if kind == "MISSING_IN_NEW":
copied = backfill(old_db, new_db) # re-run backfill
print(f" {sku:<10} MISSING_IN_NEW -> re-ran backfill (+{copied} row)")
elif kind == "VALUE_MISMATCH":
new_db[sku] = to_new_model(old_db[sku]) # fixed ACL re-migrates
print(f" {sku:<10} VALUE_MISMATCH -> ACL fixed + re-migrated the row")
# --- AFTER ---
disc = parallel_run(old_db, new_db)
print("\nAfter reconciling:")
print(f" discrepancies = {len(disc)} ({'none' if not disc else disc})")
verdict = "read_switch AUTHORIZED" if len(disc) == 0 else "read_switch BLOCKED"
print(f" verdict = {verdict}")
print("\n Reconciled 2 -> 0. The re-run backfill is idempotent (only")
print(" touched what was missing); the ACL fix was applied by re-migrating the row.")
print(" A green and SUSTAINED parallel_run is the condition for the read_switch (L7).")What to expect. When you run the file, the output is exactly this:
Before reconciling:
discrepancies = 2
kbd-mech VALUE_MISMATCH
webcam MISSING_IN_NEW
Reconciliation:
kbd-mech VALUE_MISMATCH -> ACL fixed + re-migrated the row
webcam MISSING_IN_NEW -> re-ran backfill (+1 row)
After reconciling:
discrepancies = 0 (none)
verdict = read_switch AUTHORIZED
Reconciled 2 -> 0. The re-run backfill is idempotent (only
touched what was missing); the ACL fix was applied by re-migrating the row.
A green and SUSTAINED parallel_run is the condition for the read_switch (L7).
Read the three parts in order, because they're the complete reconciliation cycle.
Before: the parallel-run confirms the 2 discrepancies we already knew, with their type: kbd-mech is VALUE_MISMATCH, webcam is MISSING_IN_NEW. This is the starting diagnosis.
Reconciliation: each discrepancy is closed according to its type, and notice that the fix is different for each one:
kbd-mech(VALUE_MISMATCH) → ACL fixed + re-migrated the row. The cause was the translator (the ACL was lowering the title to lowercase). The fix has two parts: correcting the ACL (in the code,to_new_modelno longer lowers to lowercase) and re-migrating the affected row (new_db["kbd-mech"] = to_new_model(old_db["kbd-mech"])), which translates it again with the already-corrected ACL. Now the title in the new matches the old.webcam(MISSING_IN_NEW) → re-ran the backfill (+1 row). The cause was that the row was missing. The fix is to re-run the backfill, which —being idempotent and insert-if-absent— copies only what's missing: it found thatwebcamwasn't there and copied it (+1 row), without touching any of the ones that were already fine. It neither overwrote them nor duplicated them.
After: the parallel-run runs again and now discrepancies = 0. The verdict changes from BLOCKED to read_switch AUTHORIZED. The two rows that lied now match the old, and the new, finally, tells the truth in all 6 rows.
The deep point is in the last line of the output: "a green and SUSTAINED parallel_run is the condition for the read_switch". The zero we just achieved is necessary but, in a live system, a single zero isn't enough: the dual-write keeps bringing writes, and a new ACL bug could appear tomorrow. That's why the read-switch (lesson 7) isn't triggered by the first zero, but by a zero that holds across several runs —the evidence that the new not only matched once by chance, but stays matched while the system operates—.
Deep dive: fixing the cause, not the symptom, and the cycle to zero
Each type of discrepancy maps to a fix, and the map is the value of the report.
discrepancy type typical cause fix
───────────────────── ────────────────────────── ───────────────────────────
MISSING_IN_NEW backfill skipped the row re-run backfill (idempotent)
VALUE_MISMATCH ACL translated a field wrong fix ACL + re-migrate the row
(same type) dual_write lost a write re-migrate the row from the old
EXTRA_IN_NEW dual_write wrote extra delete from new / investigate
That the parallel-run classifies isn't cosmetic: the type is the clue to what to repair. A MISSING_IN_NEW sends you to the backfill; a VALUE_MISMATCH sends you to the ACL or the dual-write. Without the classification, each discrepancy would be a mystery to investigate from scratch; with it, the fix almost writes itself.
Why re-running the backfill is safe (and why that matters). The fix for MISSING_IN_NEW is to re-run the whole backfill, not to "copy the missing row by hand". And you can do it whole precisely because the backfill is idempotent (lesson 4): re-running it doesn't overwrite the rows that are already fine or duplicate them; it only covers the absent ones. This turns the reconciliation of missing rows into something trivial and robust: you don't need to surgically identify which rows are missing and copy them one by one (fragile, error-prone); you relaunch the backfill and it finds and covers everything that's missing. The idempotence of lesson 4 is what makes the reconciliation of lesson 6 cheap.
Fix the cause, not the symptom. Here's the difference between reconciling well and playing whack-a-mole. webcam was missing because the backfill excluded the products with stock 0 (you'll see it as an explicit bug in the project, lesson 8). You have two ways to "fix it":
- Symptom: copy
webcamby hand to the new. Thewebcamdiscrepancy disappears... but the filter stays broken. If there are other products without stock, they're still missing; and if you re-run the backfill, it excludes them again. You covered a hole while the hole-generator stays on. - Cause: fix the backfill's filter (so it doesn't exclude by stock) and re-run the backfill. Now
webcamand any other product without stock are covered, and the future backfill no longer excludes them. You fixed the hole-generator.
The parallel-run helps you notice the difference: if you fix only the symptom of webcam but the cause affects more rows, the next parallel-run will keep reporting the others. A discrepancy number that drops but doesn't reach zero, run after run, is the signature of fixing symptoms. The reconciliation converges to zero only when you attack causes.
The cycle, and why "sustained". Reconciliation isn't a step, it's a cycle:
flowchart LR
P["parallel_run"] -->|discrepancies > 0| R["reconcile<br/>(by type, attacking the cause)"]
R --> P
P -->|discrepancies = 0<br/>sustained over time| S["read_switch<br/>(lesson 7)"]
You run the parallel-run, reconcile what it finds, and run the parallel-run again. Each round should lower the number; if it doesn't drop, you're fixing symptoms. When it reaches zero, you still don't cross immediately to the read-switch: in a live system, you keep running the parallel-run for a while (several runs, several days) to confirm the zero holds. A zero that holds while the dual-write brings new writes is the proof that the new is not only complete today, but stays correct —that neither the ACL nor the dual-write are introducing new errors—. That sustained zero, not the first zero, is the key to the read-switch.
Common mistakes
Fixing the symptom instead of the cause. What happens: the parallel-run reports that webcam is missing, and the team copies webcam by hand to the new, marks the discrepancy as resolved, and moves on —without fixing the backfill's filter that excluded it—. Why it happens: copying a row is fast and lowers the counter immediately; investigating why it was missing is slower. How to spot it: the discrepancies reappear —the same ones or others of the same pattern— in later runs of the parallel-run, or every time the backfill is re-run. The number drops but bounces, never stabilizing at zero. How to fix it: for each discrepancy, ask why it happened before fixing it, and fix that cause. If webcam was missing because of a filter, fix the filter (and re-run the backfill, which covers webcam and all its out-of-stock siblings). Fixing the cause is slower per discrepancy, but it's the only thing that makes the cycle converge to zero; fixing symptoms leaves it oscillating forever.
Reconciling by changing the comparison instead of the data. What happens: faced with a capitalization VALUE_MISMATCH, the team modifies the canonical to ignore uppercase/lowercase, the discrepancy "disappears", and they declare it reconciled —without having touched the bad datum—. Why it happens: loosening the comparison lowers the counter without fixing anything, and it's faster than correcting the ACL. How to spot it: the number of discrepancies drops right after touching the canonical (the yardstick), not the data; the read-switch passes but the users see lowercase titles. How to fix it: reconciling is fixing the data (or the cause that generates it wrong), not the metric that evaluates it. If the capitalization really matters for your domain, fix it in the ACL and re-migrate; if it really doesn't matter, then normalizing it in the canonical is legitimate —but that's a conscious design decision, not a trick to lower the counter—. The right question is "is this datum right?", not "how do I make the comparison not flag it?".
Crossing to the read-switch with the first zero, without sustaining it. What happens: the parallel-run reaches zero once, and the team triggers the read-switch immediately —but it was a chance zero, and the next day an ACL bug reintroduces discrepancies, now on the new that already serves reads—. Why it happens: reaching zero feels like the goal, and waiting "a little longer just to confirm" seems like wasted time. How to spot it: the read-switch happened right after the first zero, without a period of continuous green parallel-run to back it up; shortly after, discrepancies appear in the new already in production. How to fix it: demand a sustained zero —the green parallel-run across several runs and a period of time, with the dual-write bringing live writes—. A single zero proves the new is complete at that instant; a zero that holds proves it stays complete while the system operates, which is what you really need before trusting it with the reads. Lesson 7 makes this distinction the door of the read-switch.
Exercises
Exercise 1 — Match type and fix. For each discrepancy, say the correct fix and why that one and not another: (a) mouse-pro → MISSING_IN_NEW; (b) ssd-1tb → VALUE_MISMATCH with detail 8499 != 8999 (the price); (c) hdmi-cable → VALUE_MISMATCH with detail in the title, and you discover that many titles are wrong.
See solution
(a) mouse-pro → MISSING_IN_NEW → re-run the backfill. The row is missing from the new; the typical cause is that the backfill skipped it. Re-running the backfill (idempotent) covers it without touching anything else. First, it's worth investigating why it skipped it (a filter?, a failed batch?) and fixing that cause, so it doesn't repeat.
(b) ssd-1tb → VALUE_MISMATCH (8499 != 8999) → re-migrate the row from the old. The row is in both, but the price differs: the old has 8499 (fresh) and the new 8999 (old). This smells of a write the dual-write didn't reflect in the new (a partial failure), or a backfill that overwrote the fresh value. The fix is to re-migrate the row from the old (new_db["ssd-1tb"] = to_new_model(old_db["ssd-1tb"])), which copies the current value. If the pattern repeats, investigate why the dual-write loses writes.
(c) hdmi-cable → VALUE_MISMATCH in the title, and many more wrong → fix the ACL (the cause) and re-migrate all the affected rows. That many titles are wrong points to a systematic cause: the ACL. Fixing hdmi-cable by hand would cover one of many. The correct fix is to fix the ACL and re-migrate all the rows with the wrong title (or, simpler, re-migrate all the rows from the old with the corrected ACL). A systematic cause is fixed at the cause, not row by row.
Exercise 2 — Symptom or cause. webcam was missing because the backfill excluded the products with stock 0. A colleague proposes: "I'll copy webcam to the new by hand and that's it, the discrepancy disappears". (a) What problem does that fix have? (b) What would happen if there are 200 products with stock 0? (c) What's the fix by the cause, and why is re-running the backfill afterward safe?
See solution
(a) It fixes the symptom (webcam) but not the cause (the filter that excludes stock 0). The filter stays broken: the next time the backfill is run, it will exclude the products without stock again, and if webcam drops back to stock 0 in a re-backfill, or if new products without stock appear, the problem returns. You covered a hole with the hole-generator on.
(b) If there are 200 products with stock 0, copying webcam by hand leaves 199 still missing. The parallel-run would report them all as MISSING_IN_NEW, and you'd have to copy 200 rows by hand, one by one —tedious and prone to forgetting some—. The symptom wasn't one row, it was two hundred; the cause was one: the filter.
(c) The fix by the cause is to fix the backfill's filter (so it no longer excludes by stock) and re-run the backfill. This covers webcam and the other 199 in one shot, and leaves the future backfill correct. Re-running the backfill afterward is safe because it's idempotent and insert-if-absent (lesson 4): it copies only the absent rows (the 200 without stock that were missing) and doesn't overwrite or duplicate the ones that were already fine. A single run fixes the 200 without risk to the rest.
Exercise 3 — The zero that doesn't hold. You run the parallel-run five days in a row and get: day 1: 2 discrepancies; day 2 (after reconciling): 0; day 3: 3; day 4 (after reconciling): 0; day 5: 2. (a) What does this pattern tell you? (b) Should you do the read-switch on day 2, when you reached zero for the first time? (c) What do you have to investigate before you can trust a zero?
See solution
(a) That the discrepancies reappear after each reconciliation: you reach zero, but the next day they come out again. That means something keeps generating discrepancies in the live system —very probably the dual-write or the ACL introducing errors in the new writes—. You're not facing a fixed set of historical errors you fix once; you're facing an active source that produces new errors. Reconciling covers them, but the source replaces them.
(b) No. The zero of day 2 is a chance zero: it matched at that instant, but the next day 3 new discrepancies appeared. If you had done the read-switch on day 2, those 3 discrepancies of day 3 would now be on the new that already serves reads —bad data served to users—. A single zero doesn't prove the new stays correct; it proves it was for a moment.
(c) You have to investigate why they reappear: is the dual-write losing writes (partial failures)? does the ACL translate a certain type of new write wrong? Until you find and fix that active source, any zero will be temporary. Only when the parallel-run stays at zero several runs in a row —without reappearing— do you have evidence that the source is off and the new stays correct. That sustained zero, not the first zero, is what authorizes the read-switch.
Summary and next step
In this lesson you turned the parallel-run's diagnosis into a cure: the reconciliation. You saw, with the auditor's to-do list closed account by account according to its type, that each discrepancy has its fix —the missing row (MISSING_IN_NEW) is covered by re-running the idempotent backfill; the value difference (VALUE_MISMATCH) is fixed by correcting the ACL and re-migrating the row—, and that the fix is deduced from the cause, not the symptom. You executed it: the 2 discrepancies of lesson 5 reconciled 2 → 0, with the verdict going from BLOCKED to AUTHORIZED. And you saw two ideas that separate a serious reconciliation from a superficial one: fixing the cause (not playing whack-a-mole with the symptoms) and demanding a sustained zero (not the first chance zero).
Before moving on you should be able to: map each type of discrepancy to its fix; explain why re-running the whole backfill is safe (idempotence); distinguish fixing the cause from fixing the symptom and recognize the signature of fixing symptoms (the number that bounces without reaching zero); and argue why a sustained zero —not just one— is the precondition of the read-switch.
Lesson 7 uses that sustained zero as the key to the final cut: the read-switch and turning off the dual-write. You're going to move the reads to the new (with the old still warm as a backup net), and only after turn off the dual-write and retire the old —in that exact order—. You'll see, executed, why the order matters: if you turned off the dual-write before moving the reads, the old would go stale while the reads still come out of it. And you'll see the mistake that leaves migrations half-finished forever: forgetting to turn off the dual-write, leaving the system writing for eternity to a store nobody reads anymore. The green zero gave you permission; lesson 7 executes the cut.
Resources
- Martin Fowler, "Patterns of Legacy Displacement" — martinfowler.com/articles/patterns-legacy-displacement. The living article on running the old implementation and the new in parallel and comparing; the parallel run as a named pattern is Sam Newman's (Monolith to Microservices). The framework of reconciliation begins here: investigating and resolving the differences the comparison reveals before trusting the new. In English.
- Sam Newman, Monolith to Microservices (O'Reilly, 2019), ch. 4, "Decomposing the Database" — the section on verifying and reconciling the data between the old store and the new during the migration, and the typical causes of the differences. In English.
- Pramod Sadalage and Martin Fowler, Refactoring Databases: Evolutionary Database Design (Addison-Wesley, 2006) — the treatment of data migrations as steps that preserve the information and that are verified and corrected before advancing, with emphasis on attacking the cause of an incorrect migration. In English.
- Stripe Engineering, "Online migrations at scale" — stripe.com/blog/online-migrations. The production account includes the phase of comparing and correcting discrepancies between the old table and the new before the cut, and the importance of sustaining the match over time. In English.