Drill 101 — The Migration With No Dry Run

Theme: Migrations & Releases · Format: 20-min drill · Difficulty: core

The story

A retail bank's core-ledger team has a tidy piece of housekeeping on the sprint board. The accounts table stores available_balance and posted_balance as NUMERIC(18,4) — exact fixed-point decimal — and an ORM upgrade wants those columns as double precision to match its new default mapping. The ticket reads like a one-liner: ALTER TABLE accounts ALTER COLUMN available_balance TYPE double precision USING available_balance::double precision. It runs in seconds against staging's ten thousand seeded rows, every value round-trips, the diff is clean, and the change is approved.

It ships for the first time ever against production inside the Saturday 02:00 maintenance window. Four hundred million account rows. The ALTER runs, the cast succeeds for every single row, the migration reports zero errors and zero warnings, and the window closes green at 02:40. Monday's dashboards are green too — balances render, payments post, statements generate. Nobody is looking for a problem, because nothing reports one.

Three weeks later, at month-end close, an accountant in Finance opens the reconciliation pack and the general ledger does not tie to the sub-ledger. The gap is small — a few hundred dollars — but it is not zero, and in a bank a few hundred dollars that no journal entry explains is a fire. The difference is smeared across roughly two percent of accounts, each off by a fraction of a cent, in balances that were never touched by a transaction since the migration weekend.

⏸ Stop here and ask the room: the migration cast every row with zero errors and the dashboards stayed green for three weeks. So what exactly got corrupted, and why did nothing catch it?

Run it — facilitator script

Timing for a 20-minute session: 2 min story · 10 min staged questions · 3 min reveal · 5 min transfer.

The cast succeeded on every row — so where did the corruption come from?

Listen for: double precision is IEEE-754 binary floating point and cannot exactly represent most decimal cents (0.10, 0.29, …). NUMERIC(18,4) could; the cast quietly rounds the values it cannot represent, so a subset of balances drift by a sub-cent. A successful cast is not a lossless cast — the type conversion is silent precision loss, not an error.
Weak signal: "the migration must have had a bug" or "some rows failed." No row failed; the corruption is the conversion working exactly as floating point is defined to work. If the room hunts for an exception in the logs, they've missed the mechanism.

Staging ran clean on ten thousand rows. Why didn't that prove anything?

Listen for: staging held synthetic, tidy data — small round numbers that happen to be float-representable — not production's real distribution of odd fractional balances at scale. The migration was never rehearsed on production-shaped data, so the exact input mix that triggers the drift never flowed through. Ten thousand seeded rows validate syntax; four hundred million real rows are the only thing that validates mechanics.
Weak signal: "staging passed, so it was tested." Push back: tested against what data? A green run on data that can't exhibit the failure is not evidence, it's theater.

Given it's already in production, how do you even find the two percent that changed?

Listen for: you can't, cheaply, after the fact — the old exact values are gone unless a pre-migration snapshot or the source column was retained. This is why verification has to be built into the cutover: a transaction you can roll back, a row-by-row old-vs-new validation before commit, a shadow read comparing both columns on live traffic, and a row-count-plus-checksum reconciliation. The evidence has to be captured while the old value still exists.
Weak signal: "we'll write a script to fix the bad rows." Fix them to what? Without the pre-migration values there is nothing to restore to — the answer is a restore from backup, and prevention next time, not a clever UPDATE.

The reveal

The fix is not "cast more carefully" — there is no careful way to squeeze exact decimal into a binary float. The team restores the affected column from the pre-migration backup, reverts the type back to NUMERIC(18,4), and re-runs the ORM upgrade with an explicit fixed-point mapping instead of double precision. Then they add what the first migration never had: a rehearsal against a restored production snapshot, a transactional run with row-by-row validation that rolls back on any diff, a shadow read comparing old and new for a week after cutover, and a row-count and checksum reconciliation that must read exactly zero before the old column is dropped. The corrupted-row count on the re-run is zero.

Why this happens — the mechanics

The technical root is a type mismatch that the database is happy to perform. Postgres will cast NUMERIC to double precision on request, and for values that binary floating point can represent exactly the round-trip is lossless — which is precisely why it slips through. The migration author sees a clean run and a green window and reasonably concludes it worked; the two percent of rows whose decimal value has no exact binary representation are rounded to the nearest representable double, and rounding is not an error condition. There is no exception, no warning, no failed row — the corruption is the operation succeeding. Money is the one domain where this is unforgivable, and it is exactly why "never store money in a float" is a rule; a migration is just the sneakiest way to break it.

The organizational root is the seam between "the migration ran" and "the migration was correct," and it is bridged only by data. Staging existed, a review happened, a dry run happened — but the dry run was against ten thousand synthetic rows chosen for convenience, not against the production distribution that actually exhibits the failure. A rehearsal on data that cannot express the bug is indistinguishable from a rehearsal that passes, and both feel like diligence. The first time the migration meets production-shaped data is the first time it can possibly fail — and that first time is in production, at 02:00, with four hundred million rows and no old value retained to compare against.

The deepest trap is that a migration mutates the very thing you would use to check it. Once the ALTER commits, the exact pre-migration balances are gone; the dashboards read the new column and show it faithfully, so "correct-looking" and "same as before" have quietly diverged with no artifact that reveals the gap. Detection therefore cannot be a dashboard after the fact — it has to be verification built into the cutover, while both the old and new values still exist side by side. A migration is an experiment; an experiment that overwrites its own control group before measuring has not finished, it has destroyed its evidence.

Diagrams

Problem — how the failure unfolds:

sequenceDiagram
    autonumber
    participant M as Migration
    participant DB as Ledger DB
    participant AP as Banking app
    participant RP as Month-end recon
    Note over M: staging passed on 10k<br/>tidy synthetic rows
    M->>DB: ALTER balance to<br/>double precision
    DB-->>M: 400M rows cast<br/>zero errors, zero warnings
    Note over DB: 2% of cents not<br/>float-representable, rounded
    AP->>DB: read and post balances<br/>nothing looks wrong
    Note over AP,DB: dashboards green<br/>for three weeks
    RP--xDB: GL vs sub-ledger tie-out<br/>fails by a few hundred
    Note over M,RP: found by an accountant<br/>not a monitor

Solution — where the guardrails go:

flowchart TD
    A["Schema migration ready"] --> G1["Rehearse on restored<br/>prod snapshot (full copy)"]
    G1 -.-> AL["Any row diff &gt; 0<br/>blocks the cutover"]
    A --> B["Run inside one<br/>transaction (BEGIN)"]
    B --> G2["In-txn validation of<br/>old vs new, every row"]
    G2 -.-> AL2["Any diff triggers<br/>ROLLBACK, never COMMIT"]
    B --> C["Cut over to new column"]
    C --> G3["Shadow read (compare<br/>old + new on live traffic)"]
    G3 -.-> AL
    C --> G4["Row-count + checksum<br/>reconciliation (sum, hash)"]
    G4 -.-> AL

What good looks like

Rehearse against a restored production snapshot, not synthetic staging — the CI/pre-prod migration job runs on a full restored copy of prod (or a representative partition of hundreds of millions of rows), and the pass criterion is a row-level diff of zero between old and re-derived values. A green run on ten thousand seeded rows is explicitly not a sign-off.
Wrap the migration in one transaction with in-transaction validation — BEGIN, alter/backfill, then a validation query comparing every migrated row to its source; any nonzero diff → ROLLBACK, and COMMIT only fires when the diff is exactly zero. A migration whose validation can't run inside the transaction gets redesigned into steps that can.
Shadow-read the cutover for a fixed window — keep the source column (or a snapshot) alongside the new one and, for at least one week post-cutover, compare old vs new on live reads for a sampled or full stream; page on the first mismatch, target zero. This catches drift the offline rehearsal's data mix missed.
Row-count and checksum reconciliation as the drop gate — before and after must match exactly: identical COUNT(*), identical SUM() of every money column, and an aggregate hash (e.g. md5(string_agg(...))) per partition. Any nonzero delta blocks the cutover and blocks dropping the old column; tolerance is zero, not "close enough."
Never store money in a float, and assert it in the schema — money columns are NUMERIC/DECIMAL with an explicit scale, guarded by a schema test in CI that fails the build if any monetary column resolves to real/double precision.
Retain the pre-migration value until reconciliation is clean — the old column or a snapshot survives until row-count, checksum, and shadow-read all read zero for the full window; only then is the destructive drop scheduled, as its own later release.

The lesson

A migration you have never run against production-shaped data is not a plan, it is a bet — and a type cast that succeeds is not the same as a type cast that is correct. Rehearse on a restored snapshot, run inside a transaction that validates and rolls back on any diff, shadow-read the cutover, and reconcile row-count and checksum to exactly zero before you drop the old value.

Named concepts: rehearse on production-shaped data, transactional migration + validation, shadow / dual-read verification, row-count & checksum reconciliation.

Real-world echoes

Ariane 5, Flight 501 (1996). A 64-bit floating-point value was converted to a 16-bit signed integer in inertial-reference software carried over from Ariane 4. The conversion had never been exercised against Ariane 5's actual flight profile, whose larger values overflowed the 16-bit type; the conversion "succeeded" on the old rocket's data range and destroyed the new one on its first real run — 37 seconds after launch. The same shape as this drill: a numeric type conversion that is fine on the old system's data and silently, catastrophically wrong on real production-shaped inputs it was never rehearsed against.

Transfer

Which migration on your roadmap is scheduled to run against production data it has never actually touched? "It passed in staging" is not an answer unless staging held a restored copy of production — name the migration, and name the data its rehearsal ran on.

Make it a ticket: for the next schema or type migration on the board, make "rehearsed against a restored production snapshot with a row-level diff of zero" a required, checked cutover gate, and add a post-cutover row-count-plus-checksum reconciliation job that must read zero before the old column may be dropped.

Drill 56 — The Lock at 10 A.M. — the same "seconds on staging rows, disaster on production scale" blindness; that drill is the lock-duration failure mode, this one is the silent-corruption failure mode of the same untested ALTER.
Drill 54 — The Big Bang — a migration that overwrites its own control group before measuring; here it is one column, there it is a whole system.
Drill 8 — Backfill That Rewrote History (Theme 2 — Data Disasters): the reconciliation rhyme — a data mutation that looks clean and surfaces weeks later, catchable only by diffing against the values it overwrote. </content> </invoke>