Read eight rows before any bulk write
A calculation in a system I work on derives a value from a start date and an end date. A ratio test found about a hundred records where that value looked wrong by a wide margin, in a consistent direction. The pattern was clean enough to be convincing.
So I wrote the backfill. Query, batch, correct, log. Ready in an afternoon.
Before running it I pulled eight of the hundred records and read them properly.
What eight rows showed
Seventy seven of the hundred were not errors. They were agreements that had ended early, and for those the short window is not a mistaken input, it is the period that was actually worked. The value calculated from it is correct.
Running the backfill would have recalculated seventy seven closed agreements downward on the basis of a rule that only applies to open ones. Roughly a million pounds of recorded value, moved in the wrong direction, on records nobody looks at until they do.
Re-measuring with those excluded, the real population was twenty three rows, not a hundred.
The procedure, which takes twenty minutes
This is now what I do before any bulk write, every time:
- Pull eight rows and read them like a person, not like a filter. Not a count, not an aggregate. The actual rows, with their actual history.
- Say out loud what else could produce this pattern. Then go looking for that specifically. There is almost always a second explanation and it is usually a legitimate state you forgot exists.
- Re-measure after excluding it. The number almost always moves. Sometimes it moves enough that the job is no longer worth doing.
- Write the command with a default scope of the re-measured population, not the original one, so the safe number is the one that runs by accident.
The afternoon spent building the backfill was the cheap part. The twenty minutes is the job.
The failure was not in the code
This is the part I want to be exact about, because it is the thesis of most of what I write here.
The query was correct. The ratio test was correct. The backfill was correct. If you had reviewed that work line by line you would have approved all of it, and you would have been right to, because every line did what it said.
What was wrong was the assumption underneath: that an unusual value means a mistaken value. That assumption is not expressed anywhere in the code. No test can check it, because the test would have to encode the same assumption. It lives entirely in the step where somebody looks at the data and asks what else could produce this.
An agent will not take that step unless you ask for it specifically, and even then it is pattern matching on your question rather than carrying any suspicion of its own. Suspicion is the thing experience actually buys you.
The control that saved me was not the one I designed
Two things stopped that write. The planned one is that a production write of that size needs a person to approve it, and I do not approve a write I have not sampled.
The unplanned one is that my credentials for that environment are read only by policy. Even if I had been confident and wrong, the command would have failed.
I set that up for an entirely different reason and it would have caught this for free. Worth noticing in general: the control that saves you is often not the control you designed for the situation, it is a boring general restriction that happens to sit in the path. Those are cheap, and you should have more of them than you think you need.