Every Monday an analyst pulled three exports, cleaned them, pasted them into a sheet and hoped nothing had moved upstream. Nine hours a week, and once a month a wrong number reached a board deck. This engine is what replaced the ritual: a nine-task DAG that runs at 06:00, three gates that check the data before anyone sees it, and a rule that a wrong report is never published — yesterday's stays up instead. It is running below, in your browser, over thirty mornings you can make as unlucky as you like.
A weekly report by hand has two failure modes and nobody watches either. The first is time: export, dedupe, VLOOKUP, paste, nine analyst-hours a week that arrive on Monday afternoon when the chase list needed them on Monday morning. The second is silent: a column renamed upstream, a source that landed late, an export with a third of its rows missing — and the sheet still updates, looks fine, and is wrong. In the automation case the ritual went to forty minutes of review a week. This engine is the part of that work that made it safe to stop looking: a scheduler that runs the extracts, transforms and publish in the right order, and three gates that turn "the data moved" into a failed run at six in the morning rather than a wrong number in a deck at ten. SEAL and WATCH of the Leak Ledger, written down as a DAG and run on a clock.
There is no cleverness in the engine and that is the point. Every task has a printed duration, every gate is one sentence, every retry follows the same 2-4-8-16 second ladder, and the only decision the engine ever makes on its own is whether to publish. When it decides not to, it says which gate said no and leaves yesterday's sheet exactly where it was. The people who read the sheet learn, within a week, that a number on it is one that passed.
| TASK | BASE | RULE | WHAT IT CATCHES |
|---|---|---|---|
| extract_mysql · crm · zoho | 40 · 55 · 35 s | Pull yesterday's rows from each source, all three at once. An HTTP 429 fails fast (a fifth of the base) and retries after 2, 4, 8, 16 s. | Rate limits absorbed without a human. Retries are counted, not hidden. |
| gate_schema | 3 s | Every expected column present with the expected type. A renamed column → run STOPS, alert, no publish, yesterday's sheet stays. A human fix lands next morning. | The schema surprise leadership used to find. Counted as a wrong number prevented. |
| gate_freshness | 4 s | The newest row must be after midnight. If not, poll the source every 5 min for up to 45. Lands → re-extract and continue. Still stale → publish with a STALE banner on every tab, and alert. | A late source becomes a labelled sheet, never a silently old one. |
| gate_rowcount | 3 s | Today's rows against the last count that passed. A move of more than 20% either way → publish blocked, alert. | The export with a third of its rows missing. A wrong number prevented. |
| transform_orders · collections | 25 · 20 s | Orders needs the MySQL and CRM gates; collections needs Zoho and MySQL. Each starts the moment its gates are green. | Nothing is transformed from data that failed a gate. |
| publish_sheets | 30 s | One batch write per tab (not cell by cell — roughly fifty times less quota). A 429 climbs the same retry ladder. | The sheet is live before standup, or it is yesterday's. |
| notify | 2 s | Status to the owner: CLEAN, RETRIED, STALE or BLOCKED, with the gate and the source named. | Someone is told, every morning, whether to trust the sheet. |
| Analyst time | min | After: 8 min of review a morning (40 min a week), plus 25 for each blocked morning and 5 for each stale one. Before: 108 min a morning (9 h a week) with a 1-in-12 chance of a paste error that ships. | The two numbers the board asked for: hours returned, and errors that never left. |
# gate 3 — the sentence a controller can disagree with (engine.py) last = last_good[e] # the last count that passed, not yesterday's pct = int((rows[e] - last) / last * 100) if abs(rows[e] - last) > ROWCOUNT_TOL * last: # ROWCOUNT_TOL = 0.20 t = add("gate_rowcount", t, dur["gate_rowcount"], "fail", e) log(t, "gate_rowcount FAILED on %s · %d rows vs %d last published (%+d%%) · publish blocked · alert sent" % (e, rows[e], last, pct)) failed.append({"gate": "rowcount", "extract": e}) continue # this extract goes no further; no publish today
Run the month to see what the gates catch.
Two things to try. Drag schema drift and row-count anomaly to zero and the month goes almost entirely green — but notice the hours returned barely move, because the hours were never the point of the gates; the wrong-numbers-prevented cell is. Then push late source to 40%: the p95 minutes-to-publish balloons to the 45-minute wait, which is the honest cost of refusing to publish stale data silently. The wait is a slider in the real engine too; the decision to label rather than hide is not.
| WHEN | WHAT HAPPENS | WHO SEES IT |
|---|---|---|
| 06:00 daily | The three extracts start together. Rate limits retry on the 2-4-8-16 ladder; a fourth failure is an alert, not a fifth retry. | Nobody, on a clean morning. That is the design. |
| 06:01 | Gates, per extract, in order: schema, freshness, row count. A red schema or row-count gate stops the run and leaves yesterday's sheet up. A stale source waits up to 45 minutes, then publishes with a banner. | The owner gets one message naming the gate and the source. The reader sees a banner or nothing new — never a quietly wrong sheet. |
| 06:02 | Transforms, then one batch write per tab. Notify carries the status: CLEAN, RETRIED, STALE, BLOCKED. | Finance has the sheet before standup; the chase list from the collections work is the first tab. |
| Next morning | After a BLOCKED day the human fix (a renamed column, a broken export) lands before 06:00; the row-count gate compares against the last count that passed, so one bad day does not poison the next. | The analyst — twenty-five minutes, on the mornings it happens. |
| Weekly | The calendar strip above, as a chart: clean, retried, stale, blocked. A month with more than two blocked mornings is a conversation with whoever owns the source. | Leadership — the WATCH stage of the Leak Ledger. |
Automation is not a script that runs; it is a script that knows when not to. The nine hours came back in the first week. What made it possible to stop checking was the gates: a renamed column became a failed run at six in the morning instead of a wrong number at ten, and the sheet earned the only reputation a report needs — that what is on it passed.
Have a report someone rebuilds by hand every Monday? A report engine is a three-to-four-week SEAL: your sources, your sheet, your gates — arriving on its own before standup, with a check that tells you when it should not be trusted.
Start with a TRACE → engine.py README results.json The case it came from →