The Backfill Nobody Scoped
Every backfill starts the same way. Something is wrong in the data, the fix is obvious, and the script to apply it is about fifteen lines.
The fifteen lines are real. What is missing is everything around them: how many rows this touches, what else reads those rows while you are rewriting them, what happens if it stops halfway, and how you put it back if the obvious fix turns out to be wrong.
A backfill is not a script. It is a bulk write to production, usually against the oldest and least understood data you own, usually written in a hurry by someone who just finished being annoyed about a bug.
Count the rows before you write the update
The first question is not how to fix the rows. It is how many there are.
The answer changes the entire approach. Four hundred rows is an afternoon and a spreadsheet. Forty million is a project with batching, a rate limit, progress tracking, and a conversation about replication lag. The script looks identical in both cases, which is precisely the trap.
Run the select first. Not an estimate from a dashboard, the actual count with the actual predicate you are about to use in the update. The number is routinely larger than expected, because the predicate that describes the bug is almost never the predicate that describes the rows you were picturing.
Write the undo before the do
If the repair is wrong, how do you get back?
Sometimes the answer is easy: snapshot the affected rows into a side table first, keyed so you can restore them. Sometimes the original value is unrecoverable once overwritten, and that fact alone should change how carefully you proceed. Either way, decide before running, not after someone notices the numbers look strange.
The undo plan also forces honesty about scope. If it is hard to describe how to reverse the change, the change is bigger than it felt.
Batch, and make it resumable
A long single transaction is a bet that nothing will interrupt it. Connections drop, deploys happen, locks pile up behind you, and a rollback at ninety percent wastes the whole run.
Batch it. Commit per batch. Track a cursor so a restart picks up where it stopped instead of starting over or, worse, redoing work that was not safe to redo. Put a visible sleep between batches so the system has room to breathe and you have room to notice something going wrong.
Resumable also means interruptible, which is the property you want most. A backfill you can stop is a backfill you can stop when the graphs look bad.
Run it somewhere it cannot hurt the request path
Bulk writes and live traffic compete for the same resources. The backfill does not need to be fast. The application does.
Give it the lowest priority you can arrange: a replica where reads allow it, an off-peak window, a conservative batch size, a hard cap on concurrency. Then watch the same dashboards the on-call watches, because you are now a load generator and you should be the first to know.
Then check the result, not the exit code
A backfill that finishes without errors has not necessarily done the thing. Re-run the original count query. The number of bad rows should be zero, and if it is not, you want to learn that now rather than next quarter.
Spot check individual rows too. Exit codes describe the script. Queries describe the data.
Scoping work by blast radius, and the repair jobs that taught us to do it, are covered in One Agent, One Company ($9.97). A good one to read before your next data cleanup.