Migration & Schema-Change Testing

1 The Hook

A fictional NZ KiwiSaver provider, Rimu Wealth, moved its member records from an old system to a new platform over a weekend. The migration ran overnight, the counts looked sensible the next morning, and the new platform went live on the Monday. Members logged in, saw their balances, and everything appeared fine.

Weeks later, a member queried their balance during a complaint. It was short by a few hundred dollars. The investigation found that the old system stored balances with four decimal places of precision, but the new balance column had been defined with only two. The migration had silently rounded every member’s balance on the way across. Multiplied over tens of thousands of members, the provider was now holding records that did not add up to the money it actually had.

No row was lost. The row count matched exactly — every member came across, which is why the morning check passed. The corruption was inside the values, not in the count. A column-type change had quietly truncated data, and the only check anyone ran was “did we get the same number of rows?”. That question cannot see a value that changed.

Here is the lesson hidden in that story. A migration is the one operation where you deliberately rewrite the data you most need to keep, and often the old copy is decommissioned soon after. “The counts match” proves nobody was dropped; it says nothing about whether the values survived intact. Migration testing is the discipline of proving, before the old system is gone, that every row arrived and every value is unchanged — and that you can get back if it did not.

2 The Rule

3 The Analogy

4 Data Preservation and Reconciliation

Data preservation is the core promise of a migration: every row and every value comes across unchanged, unless a transformation was deliberately specified. You prove it with reconciliation — comparing source and target and asserting they agree. Reconciliation has two layers, and most teams only do the first.

Row-count reconciliation is the cheap, necessary first check. Count the rows in each source table, count them in the target, and assert they match (allowing for any rows a transformation was meant to add or remove, with that number stated in advance).

A row count catches dropped and duplicated rows. It is completely blind to Rimu Wealth’s defect, because every row was present — the values inside had changed.

Content reconciliation is the layer that catches the rounding. The standard technique is a checksum: compute a fingerprint of the actual data — a hash or a set of column aggregates — on the source, compute the same on the target, and assert they match. If a single balance was rounded, the fingerprint differs, and the migration fails.

Had Rimu Wealth run this, SUM(balance) on the target would have been lower than the source by the total of all the rounding — an immediate, unmissable fail. For stronger assurance, a row-by-row hash of the key columns catches changes that happen to cancel out in a sum. The principle is constant: reconcile content, not just count.

5 Backward Compatibility

A schema change rarely happens in isolation. The same database is read and written by application code, batch jobs, reports, and integrations. A change that is correct for the new feature can break every one of those if it is not backward compatible.

The dangerous changes are the destructive ones. Dropping a column breaks any query that still selects it — a nightly report, an integration with MBIE, a fallback code path that runs once a quarter. Renaming a column is a drop and an add in disguise: everything referring to the old name breaks at once. Narrowing a type — Rimu Wealth’s four-decimals-to-two — truncates data and can break writes that send the old precision. Tightening a constraint — adding NOT NULL to a column that already has NULLs, or a UNIQUE where duplicates exist — can fail on the existing data the moment it is applied.

The tester’s job is to enumerate every consumer of the changed table and confirm each still works. The safe pattern for risky changes is expand then contract: add the new column alongside the old, migrate code to use it, and only drop the old column once nothing reads it. That sequencing lets old and new coexist, which is what makes the next idea — zero-downtime — possible. A tester who only checks that the new feature works, and never checks the old report that still selects the dropped column, ships a backward-incompatible change.

6 Zero-Downtime Migrations

Many NZ systems cannot simply go offline for a migration — an internet banking platform, a Te Whatu Ora clinical system, a Waka Kotahi service. These run zero-downtime migrations, where the change is applied while the system stays live and serving real traffic. That introduces a window in which the schema is mid-change and both old and new code may be running at once.

The technique that makes this safe is the expand-then-contract pattern from the last section, run in phases:

• Expand: add the new structure (a new column, a new table) without removing the old. The schema now supports both shapes.
• Dual-write / backfill: the application writes to both old and new while a background job copies existing rows across. During this phase the two must stay in agreement.
• Switch reads: point the application at the new structure once it is fully populated and verified.
• Contract: only after everything reads the new structure, remove the old — the irreversible step, done last and deliberately.

For a tester, the new and hardest target is the in-between state. You must test that the system is correct at every phase, not just at the end — that during dual-write a row written the old way and the new way agree, that a read during the switch never sees a half-migrated record, and that the backfill does not collide with live writes. The defects in a zero-downtime migration live in the overlap, where old and new code touch the same data at the same time. Testing only the final state misses them entirely.

7 Rollback and the Point of No Return

The single most important question before any migration is: if this goes wrong, how do we get back? A migration that cannot be undone is a one-way door, and walking through it untested is how a bad weekend becomes a lost member’s balance with no clean source to restore from.

Rollback is not free, and not always possible. A purely additive change (adding a column) is easy to reverse. A destructive change (dropping a column, rewriting values in place) may have no way back once the old data is overwritten — the information needed to reverse it is simply gone. This is the point of no return: the moment after which the original data no longer exists to restore from.

So a tester insists on three things:

• A taken, verified backup before the migration runs — and tested that it actually restores, not just that the backup job reported success. An untested backup is a guess.
• A rollback procedure that has been rehearsed on a copy — run the migration, run the rollback, and reconcile that the data returned exactly to its starting state.
• A clear, agreed point of no return — everyone knows the moment after which rollback is no longer possible, so the decision to cross it is deliberate and the reconciliation that gates it is run first.

The reconciliation from earlier is what gates the point of no return. You do not decommission the old system, and you do not cross into the irreversible, until content reconciliation has proven the target matches the source. Rimu Wealth crossed that line on a row count alone — and once the old four-decimal data was gone, there was nothing left to restore the truncated balances from.

8 Building Migration Test Cases

A migration test case names what it preserves, reconciles source against target on content, and ties the result to the go/no-go decision on crossing the point of no return.

Here is a migration test case written to catch the exact Rimu Wealth defect:

Notice the shape: reconciliation is layered — count then content — so a passing count alone cannot let the migration through. The pass criterion explicitly gates the point of no return on content reconciliation, which is the exact control Rimu Wealth lacked. And rollback is a named, rehearsed prerequisite, not an afterthought. This is the difference between a migration you can defend to an auditor and one you hope worked.

9 Common Mistakes

10 Now You Try

Three graded exercises: spot the migration risks, fix the reconciliation, then build a migration test plan. Write your answer, run it for AI feedback, then compare to the model answer.

There are at least four real risks; any three well-explained earns full marks.

1. Backward compatibility — dropping the phone column in the same release breaks the monthly AML report and the marketing integration, both of which still SELECT phone. The AML report in particular is a compliance feed. Category: backward compatibility. Fix: expand-then-contract — keep phone until every consumer is migrated off it, drop it in a later release.

2. Data preservation / value corruption — splitting free-text phone into country_code and phone_number is a transformation that can silently mangle values (numbers with no country code, odd formats, extensions). Row counts will still match. Category: data preservation. Fix: content reconciliation — reconstruct phone from the two new columns and compare to the original per row.

3. Rollback / point of no return — the migration overwrites in place AND the old system is decommissioned the same evening on a row-count match alone. Once the original free-text phone is gone there is nothing to restore the split from if it went wrong, and there is no rehearsed rollback. Category: rollback. Fix: verified backup, rehearsed restore, and do not decommission until content reconciliation passes.

Bonus: zero-downtime overlap — the platform stays online, but there is no dual-write/expand plan, so reads during the in-place change can see a half-migrated table. Category: zero-downtime overlap.

The thread: row-count sign-off cannot see any of the value/compatibility damage. Reconcile content and keep a tested way back before decommissioning.

Why row-count alone missed it: a row count only proves the same NUMBER of prescriptions came across. Every row was present, so the count matched — but the dose_mg values inside some rows had changed. Count is completely blind to value corruption. This is the Rimu Wealth defect in a clinical context, where a corrupted dose is a patient-safety issue.

Content reconciliation — match aggregates AND a per-row hash:

-- aggregate smoke test (fast)
SELECT COUNT(*), SUM(dose_mg), MIN(dose_mg), MAX(dose_mg) FROM legacy.prescription;
SELECT COUNT(*), SUM(dose_mg), MIN(dose_mg), MAX(dose_mg) FROM new.prescription;
-- every figure must be identical

-- the real gate: per-row hash of the key columns
-- source set of hashes
SELECT prescription_id, HASH(prescription_id, dose_mg, drug_code) FROM legacy.prescription;
-- target set of hashes — must match the source set row for row
SELECT prescription_id, HASH(prescription_id, dose_mg, drug_code) FROM new.prescription;
-- assertion: the two sets of (id, hash) are identical; any prescription_id
-- whose hash differs is a corrupted row. PASS = zero differing rows.

Why a SUM alone is still not enough: a sum can hide offsetting errors — one dose corrupted up by 5 mg and another down by 5 mg leave SUM(dose_mg) unchanged, so the aggregate passes while two rows are wrong. The per-row hash catches changes a sum would cancel out. Use the aggregate as a quick smoke test and the row-level hash as the actual pass gate.

MIG-01 | Verifies: no rows lost or duplicated | Acceptance criteria: COUNT(*) target equals COUNT(*) source for every table (plus any pre-stated transformation delta); 0 difference | Evidence required: source and target counts per table; the run ID

MIG-02 | Verifies: values preserved exactly (content reconciliation) | Acceptance criteria: matching aggregates AND a per-row hash of key columns match source-to-target; 0 differing rows | Evidence required: source/target aggregates; hash comparison result listing any mismatched IDs

MIG-03 | Verifies: existing consumers still work after the change (backward compatibility) | Acceptance criteria: every report, batch, and integration that reads the account table runs successfully and returns identical results pre- and post-change | Evidence required: list of consumers; before/after run outputs for each

MIG-04 | Verifies: dual-write overlap is consistent | Acceptance criteria: during the expand/dual-write phase, a row written via old and new paths agrees; a read mid-switch never returns a half-migrated record; backfill does not overwrite a newer live write | Evidence required: dual-write comparison samples; concurrent-read test results; backfill-vs-live-write test log

MIG-05 | Verifies: rollback returns the data to its starting state | Acceptance criteria: on a copy, run migration then rollback; reconcile that counts and per-row hashes match the original pre-migration state exactly | Evidence required: pre-migration snapshot fingerprint; post-rollback fingerprint; they must be identical

Strong plans: each case is specific, measurable, names concrete evidence, and together they cover count (MIG-01), content (MIG-02), backward compatibility (MIG-03), the zero-downtime overlap (MIG-04), and rollback (MIG-05). Weak plans say "check the migration worked" five times — that is the difference being marked. Note that the point of no return must be gated on MIG-02 passing.

11 Self-Check

Click each question to reveal the answer.

12 Interview Prep

Real questions asked in NZ QA interviews for data-heavy roles. Read the model answers, then practise your own version.