The replace that deleted everything
I imported one month of ad revenue and lost the eight months before it, because replace was written as delete first.
By Andrew Pyle
The warning that would have saved the data was already in the code, written in a docstring that nothing enforced.
01The monthly close
I followed my own runbook
On October 5 I did the monthly close for ad revenue. The routine lives in a runbook I wrote: export the network's revenue as a CSV, then run the importer. The runbook said to import September on its own, so I did. The import reported success.
Afterward, the stored history held September and nothing else. January through August, 547 rows, were gone.
The runbook was wrong, and the error was mine. I had written it without reading a note in the importer that said to pass every export file together. The note was in the docstring, and it said the importer was idempotent only when all the files went in one run. The warning existed, and the code did nothing to enforce it.
02What replace meant
One delete, then one bulk insert
Inside one transaction, the importer deleted every revenue row for the network, then bulk-inserted the rows from the file in batches of 500.
with transaction.atomic():
rows.filter(network=network).delete()
rows.bulk_create(objs, batch_size=500)A comment above that code explained the choice:
one DELETE + one bulk_create is idempotent (we always pass the full file set) and avoids hundreds of slow per-row round-trips.
The speed argument was real. "We always pass the full file set" was the assumption, and my runbook broke it the first time anyone followed it.
Read that way, replacing the network's data meant deleting everything that is not in the file I am importing right now. For a full-history file, that is the same as replacing. For a one-month close, it is data loss, and the importer had no way to tell which kind of file it had been handed.
03It happened twice
The restore deleted September again
The history still existed at the ad network, so I could export it again. The first try was a full-range export, and it hit the network's 20,000-row cap and stopped partway through July. So I split it. A July to September export came to about 4,800 rows, well under the cap.
Then the restore import deleted September again. I had run it with the old importer from my main checkout, which did not have the fix yet. The fixed importer lived in a separate checkout on the latest main. The command looked the same. The code behind it was older, and it did exactly what it had always done. It was a local version of the gap between what is deployed and what is served. The fix existed, and the copy I ran did not have it.
The next attempt ran the fixed importer from the up-to-date checkout. July through September went in, and then I restored the earlier months from a copy of the full export filtered to before July. By the end of that day the history was back. It came back only because the network still held it. If the only copy had been mine, the September close would have been the end of those months.
04Scope the delete
Replace only what the file covers
The fix was to delete only the network, year and month combinations that appear in the file, built as an OR of one condition per distinct month, inside one transaction, and then bulk-insert. A September file touches September and nothing else. A full-history file still replaces every month, so a full export is always safe, and it is also the way to undo a bad import.
An empty file must delete nothing
The change had a trap of its own. With a file that held no usable rows, the set of conditions would be empty, and an empty Q object in Django matches everything. The narrow delete would have turned back into the wide one, on the one input where deleting anything is least justified. So the delete runs only when there is something to replace, and a file with no usable rows deletes nothing.
A success line that names the code
The success line changed too. It now reports how many old rows it replaced in how many months and how many it wrote, and it ends with other months untouched.
replaced N old row(s) in M month(s), wrote K; other months untouchedThe runbook tells me to stop if the line does not end that way, because that would mean the old importer ran. That check exists because of the second deletion. The output now tells me which code ran.
Four tests and a short window
Four tests pin it down: a one-month import keeps the other months, a re-import replaces only its own month and is idempotent, a full-history file restores every month, and a file with no usable rows deletes nothing. Restoring the network-wide delete fails two of them, and removing the empty-file guard fails one. The pull request was open for 17 minutes. Until it shipped, the runbook required full-history exports. After the merge, the close went back to exporting last month only.
Related
writing
A measurement killed my own rule
I wrote a style rule telling my posts to stop saying my, and the impression data showed the rule was making my writing worse.
writing
I assumed the bigger model was the better judge
The most expensive line in my content pipeline was a choice I made by reflex, and a blind test showed the reflex was wrong in both directions.
writing
An alarm on the wrong thing drowns the signal
My mispriced-model alarm fired on ten call sites that were priced exactly right, and buried the spend I needed to see.
writing
Edit the thing you see, not the thing it is
My ordering tool listed every site the database called standalone — including nine that never appear standalone to a visitor.
writing
A default is a decision I didn't know I was making
I shipped a feature, then ran a review against it on purpose — and it found the one line I'd written without thinking.