Engineering Atlas › Problem Solving & Incidents (AI era)

Data Corruption and Bad Migrations

12 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Why data incidents are special

A bad deploy that returns errors is visible within minutes and fixed by rolling back. A bad deploy that writes subtly wrong values, for example prices in cents stored as dollars or a timezone shift on dates, returns success, passes health checks and keeps writing. By the time someone notices, the wrong values have been read by reports, copied into the warehouse, emailed to customers and included in last night's backup. Google's SRE book makes the point that what people really want is not backups but restores: the ability to get correct data back (Google SRE book). It also notes that replication copies corruption and accidental deletes to every replica, so a replica is not a backup.

Detect

Corruption is found by checks, not by errors. Invariants: totals that must balance, counts that must match between two systems, fields that must be within a range. Reconciliation jobs that compare your data with an independent source (the payment provider's report, the inventory system). Checksums and row counts before and after migrations. Customer reports are a detection method too, but the slowest one.

Contain

Stop the writer first: roll back the code, turn off the flag, pause the backfill or the consumer. Consider stopping downstream propagation as well: pause the export to the warehouse, hold outbound emails or invoices generated from the affected data. Note the time containment happened; it closes the corruption window.

Scope

Find exactly what changed. Good sources: an audit or history table (each row's previous values with timestamps), change data capture streams (Debezium reading the database's log and publishing every change), application logs with entity IDs and the deploy version that wrote them, and comparing current data with a point-in-time copy. The output is a list of affected keys, fields and the window between the first bad write and containment.

Repair

Three options, in order of preference:

  1. Recompute from a source of truth if one exists (re-derive totals from a ledger, re-fetch from the provider).
  2. Surgical restore: restore a point-in-time copy from just before the first bad write into a separate instance, read the old values for affected keys, and patch them into production. Rows created during the window may need deleting or fixing. Rows changed by legitimate writers after the window are conflicts for a human to resolve.
  3. Full restore of the whole database to before the incident, discarding everything written since. Simple but destructive to all legitimate work after that point; acceptable only if little happened since or nothing else is possible.

Always run the repair as a reviewed script, first as a dry run that reports what it would change, on a copy, with counts that match expectations.

Prevent

Make bad migrations hard and recoverable. Expand and contract: add new columns or tables, write to both, backfill, switch reads, then remove the old, so each step is reversible. Backfills in small batches, with a dry run, a sample check and a kill switch. Keep history: audit tables or CDC retained for at least as long as it takes to notice problems. Google describes three layers of defence: soft deletion (commonly a 15 to 60 day delay before data is really removed), tiered backups, and out-of-band validators that catch inconsistencies early, which its Gmail validators do within 24 hours (Google SRE book). Stripe's pattern for moving data is four steps: dual write plus backfill, switch reads, switch writes, then remove the old data, with GitHub's Scientist library comparing old and new reads before each switch (Stripe).

The same shape everywhere

This is the multi-step consistency and trust-but-verify shapes together: data copied into many places must be repaired in all of them, and only independent checks catch silent errors. Finance teams do the same with journal corrections, and supply chains with recalls: contain, trace where the bad batch went, repair, then fix the process.

Down the stack

Layer 1The contract

A system that handles important data promises: invariants checked continuously with alerts; history sufficient to reconstruct any row's previous values for N days; point-in-time recovery to any second within the retention window; and a tested procedure to repair a subset of rows without discarding legitimate later writes.

Layer 2The mechanism

Point-in-time recovery restores a base backup and replays the write-ahead log to a chosen time, producing a copy of the database as it was then. The target must be after the base backup ended, the WAL sequence must be unbroken from the backup's start, and each recovery starts a new timeline (PostgreSQL docs). Change data capture tools read the same log (Postgres logical decoding, MySQL binlog) and emit a stream of row changes with before and after values. A repair job joins three things by primary key: the list of affected keys from the scope step, the old values from the point-in-time copy, and the current production values. For each key it decides: restore the old value, delete a row created during the window, or flag a conflict because the row was changed again after containment.

Layer 3The machine

Not all corruption is logical. Storage can return wrong bytes: firmware bugs, bit flips in memory without ECC, torn writes when power fails in the middle of an 8 KB database page written as two 4 KB disk sectors. Databases defend with checksums: Postgres page checksums (on by default for new clusters since PostgreSQL 18, where initdb --no-data-checksums opts out; older versions needed initdb --data-checksums; an existing cluster can be switched offline with pg_checksums; release notes, PostgreSQL docs) store a 16-bit checksum in each page header and verify it on read, and full-page writes put a complete page image into the WAL after each checkpoint so a torn page can be rebuilt. File systems such as ZFS checksum every block. A checksum failure is a hardware or software bug signal, and the repair path is a replica or backup copy of the page, not the application.

Why it's built this way

  1. Wrong data looks like right data, so it must be detected by independent checks.
  2. It spreads to copies and backups over time, so speed of containment matters.
  3. Later legitimate writes are valuable, so repairs should be surgical rather than wholesale.
  4. Surgical repair needs to know old values, which requires history or point-in-time copies.
  5. Migrations and backfills are the most common cause, so they are made reversible and gradual.

Diagram

flowchart LR
  B["Bad writer starts"] --> D["Detected by invariant or report"]
  D --> C["Contain: stop writer and downstream exports"]
  C --> S["Scope: keys, fields, window from CDC or audit"]
  S --> P["Restore point-in-time copy to side instance"]
  P --> R["Repair plan: restore, delete or conflict"]
  R --> DR["Dry run, counts check, review"]
  DR --> A["Apply to production and downstream"]

Worked example

A backfill job is meant to convert a price column from dollars (decimal) to cents (integer) by writing price_cents = price * 100. A bug multiplies by 10,000 for products in one currency with three decimal places. It runs from 02:00 to 02:40 and touches 180,000 of 4 million products. At 07:15, a customer complains about a 4,800 dollar phone case. The storefront reads price_cents, so for five hours 180,000 products showed prices 100 times too high, and 23 orders were placed at those prices.

Containment at 07:30: the storefront switches back to reading the old price column with a flag, and the warehouse export is paused. Scope: the backfill logged batch IDs and product IDs; CDC confirms 180,000 rows changed between 02:00 and 02:40 by the backfill user. Of these, 1,200 were edited again by merchants after 02:40.

Repair: no point-in-time restore is needed because the old price column is the source of truth. A script recomputes price_cents correctly for the 178,800 untouched rows. The 1,200 rows merchants edited are recomputed from their new price values, because merchants edited price through the admin, which still wrote the old column. The 23 orders are refunded the difference with an apology. The dry run reports 180,000 updates and zero conflicts, a sample of 500 is checked by hand, and the patch runs in batches of 5,000. The fixes afterwards: backfills must run a dry run on a production copy with a value distribution check (no price should increase by more than 10 times), and the read switch happens only after an invariant check passes.

In the wild

Trade-offs

Repair approachStrengthsWeaknessesChoose when
Recompute from source of truthExact, keeps later writesNeeds a reliable sourceDerived data, ledgers, external records
Surgical restore from point-in-time copyKeeps legitimate later writesEffort, conflict handlingMost corruption of primary data
Full point-in-time restoreSimple and completeLoses all later writesCorruption everywhere, little activity since
Fix forward manuallyNo restore neededError-prone at scaleA handful of rows

Pitfalls in production

Leadership lens

Check yourself

  1. Why is a full point-in-time restore often the wrong first choice?

    Show answer

    It discards all legitimate writes made after the restore point, such as orders and edits, turning a partial problem into a total loss of recent work.

  2. What is a conflict in a repair plan, and why not overwrite it?

    Show answer

    A row that was legitimately changed again after the corruption window; overwriting it with the pre-incident value would destroy a valid later change.

  3. How does expand and contract make migrations recoverable?

    Show answer

    Old and new structures coexist while data is copied and verified, so you can switch back to the old one at any step before the final removal.

  4. What do Postgres page checksums protect against, and what do they not?

    Show answer

    They detect bytes corrupted by storage or memory between write and read; they do nothing about logically wrong values written correctly by buggy code.

Sources and further study

Primary

Systems docs

Books

Other curricula

Free resources

Open in the interactive app: practice, quiz, mock interview →