Every script in my Pikes Peak Publication pipeline worked. I had run all of them, by hand, more than once. The database had 275,180 Colorado business entities and roughly 1.5 million filing transactions in it, and I had already published an article off those numbers.
Then last week I put the whole thing on a schedule, and inside a day I found four bugs that had been sitting there the entire time.
Not one of them could fire on a first run. That is the part I keep chewing on.
What "run it again" actually means
Three of the four were the same defect wearing different hats, and the hat is INSERT OR REPLACE.
In SQLite, INSERT OR REPLACE is not an update. It deletes the conflicting row and inserts a new one in its place. If your loader only knows about a subset of the table's columns, everything else in that row snaps back to its schema default. On a fresh table there is nothing to destroy. On the second run, the loader eats every column it does not personally own.
The IRS one. My nonprofits table has two columns, sos_registered and sos_match_type, written by a separate cross-reference script that matches IRS EINs against Colorado Secretary of State registrations. The BMF loader has no idea those columns exist. Every full refresh deleted and reinserted the rows and reset both to their defaults. I watched sos_registered=1 drop from 934 to 30 immediately after a refresh. The bridge between the two datasets, which is the actually interesting part of the whole project, was being wiped by a routine data pull.
The entities one. Same defect, different table. city_normalized is derived by a separate normalization pass, and it is the column every El Paso County filter reads. A full entity pull came back with 267,063 of 279,254 rows carrying a NULL city_normalized. Anything reading the county series between that pull and the next normalization run would have reported a county that had essentially ceased to exist.
The transactions one. This is the one that cost me something.
I had assumed Colorado's transactionid was unique. It is not. On a formation filing, SOS sets transactionid equal to entityid, and every later "Filing Officer Correction" against that filing comes back under the same id. With transactionid as the sole primary key, INSERT OR REPLACE overwrote the formation row with the correction, and the formation event stopped existing in my history. 12,563 rows in the live database are corrections sharing an id with a formation.
Those were real businesses that really formed in El Paso County, and my database had quietly eaten them.
The correction
I had already published a piece here with a 2024 formation-to-dissolution ratio of 2.52 to 1. After rebuilding sos_transactions on a composite key of (transactionid, historydes, effectivedate) and reloading, the number is 2.53 to 1: 16,279 formations against 6,437 dissolutions. Every year from 2019 through 2025 moved.
2.52 to 2.53 is not a difference that changes anyone's read on the Colorado Springs economy. It also does not matter that it is small. It was wrong, I published it, and the fix is a dated correction note at the bottom of the post, not a silent edit. If I am going to publish numbers on a cadence, the first time I get one wrong is the moment I decide what kind of publication this is.
Worth noting: recovering those lost formations made the local economy look slightly better, not worse. It could just as easily have gone the other way, and next time it will.
The fourth one, which is a different flavor
load_cpi.py had END_YEAR = 2025 hardcoded, along with a literal list of request windows for the BLS API. Perfectly correct today. Next January it silently stops advancing, the inflation adjustment quietly freezes, and nothing errors, because nothing is wrong from the code's point of view. It now derives the year from the clock and generates its request windows.
Same category as the other three. Code that is correct when you run it and wrong when it runs itself.
What I built instead of trusting myself
The fixes were the easy part. INSERT ... ON CONFLICT DO UPDATE instead of INSERT OR REPLACE, writing only the columns the loader owns. A composite primary key plus an idempotent table rebuild (1,499,020 rows in, 1,499,020 rows out, integrity check clean). A year derived from date.today().
The harder question was how I catch the next one.
So the schedule does not call the individual scripts at all anymore. There is one entry point, ops/run_ingest.py, with two lanes: a weekly SOS pull and a monthly full refresh. The orderings between steps are load-bearing (new transactions are dropped on the floor if their entity is not already loaded, the county filter is empty until normalization runs) and those orderings now live in code rather than in a prompt I wrote for an agent. A schedule cannot improvise on them.
Every run, without exception: a disk precheck, a schema check against the source API's column contract so a dropped upstream column halts the run before any write, a backup through SQLite's online backup API plus a full integrity check on the copy, a read-only state snapshot before and after with a JSON diff, and a written report.
And there is a file called published_baseline.json holding every number I have already put in print. Every run diffs against it. If a published ratio moves, if any row count goes down, if a table changes shape, the run exits 2 and tells me. A clean run exits 0 and says nothing at all.
The comment at the top of that file is the only documentation in the repo I wrote specifically for future me: never edit this to silence an alarm.
The takeaway
I have written a lot of pipelines. I know what idempotency is. I still shipped four bugs that were invisible right up until the moment I stopped being the one pressing the button.
The gap is not knowledge. It is that running a script by hand and scheduling a script are two different programs, and I had only ever tested the first one. A one-shot analysis lets you carry state in your head: I ran the normalizer after that pull, I remember doing it. A schedule has no head to carry it in. Everything the process was quietly relying on you to remember has to become code, or it becomes a bug with a delay fuse on it.
If you have a pipeline you have only ever run by hand, the useful exercise is not reading it again. It is running it twice and diffing the database.