Write a Reversible Data Migration

Deliver the migration, the rollback, and the checks together — validated on a small sample first

You are a backend engineer responsible for a production data migration. Given the schema and the intended change below, produce three things: 1) the migration script — batched, re-runnable, with a hard batch limit so no long transaction locks the table; 2) the rollback script — state which steps are irreversible and how to verify the data after rolling back; 3) verification SQL — run at each checkpoint (after backfill, before cutover, before rollback), with expected results and a pass threshold. Name the step most likely to lose data before you show any script. Do not assume a maintenance window.

[schema / intended change / data volume]

How to use

Run it against a shadow table or 1% of the data first; validate both the re-run and the rollback before you book a release window.

Good for

You need to add a NOT NULL column to a ten-million-row orders table and backfill it, with a maintenance window measured in minutes.
You are renaming or splitting a table — an irreversible step that needs a rollback path and a data reconciliation plan written down before execution.

Not for

You are only adding a nullable column with no backfill: one DDL statement is enough, and a migration script is unnecessary.
The field's meaning is still being argued about or the schema changes daily: settle the model first; any script written now will be rewritten.

Example

Input

-- orders: ~12M rows, MySQL 8.0
ALTER TABLE orders ADD COLUMN region_code VARCHAR(8) NOT NULL DEFAULT '';
UPDATE orders SET region_code = ...;

What you get

The full-table UPDATE holds locks for the entire backfill and blows the window; rewrite it as batches of 5,000 keyed by primary key, and make each batch re-runnable. The rollback is not symmetric — dropping the column discards the backfilled data, so run the `region_code = ''` count after every batch and record a baseline before rollback. State the expected counts explicitly instead of saying 'check the data'.

Common mistakes

Trying the full-table UPDATE on a small staging database first: it finishes instantly there and tells you nothing about lock time on the real table.
Checking that the migration succeeded but never testing the rollback — the moment you need it, the dropped column takes the backfilled data with it.

Try tweaking it like this

Add 'assume the maintenance window is five minutes' and it will reach for batching plus dual writes instead of one giant UPDATE.
Ask 'if it fails halfway, which step do I resume from?' and scripts that cannot be re-run get caught in review.