The Nightly Job That Reported Success on the Two Days It Shouldn't Have

I built a retail demand-forecasting pipeline once that ingested daily point-of-sale transactions for fifty SKUs across ten stores, cleaned and deduped them into a silver layer, and aggregated them into gold feature tables that a downstream forecasting model consumed. I ran it over a thirty-day window with two incidents planted on purpose: a duplicate-transaction burst on day twelve, the kind of thing a point-of-sale retry produces when a network hiccup causes the same sale to get recorded twice, and a corrupted feed on day twenty-one, negative quantities from a decimal-parsing bug upstream.

The problem

In the first version of this pipeline, promotion from bronze to silver to gold happened on a fixed nightly schedule, unconditionally, regardless of whether that night's data had actually cleared any quality check. There was no quality check. There was just a schedule and a promotion.

Day twelve's duplicated transactions flowed straight through to gold and inflated that day's sales aggregate. Day twenty-one's negative quantities flowed through too, and corrupted the rolling-average feature the forecasting model depended on. The model consumed both without complaint, because a model has no way of knowing a number is wrong unless something upstream tells it so. The pipeline's own nightly job reported success on all thirty nights, including the two that very much should not have passed. Gold-layer violations, measured against the data quality rules I already knew this feed could break, spiked visibly on exactly those two days, and the forecast's error rate got measurably worse in the days that followed each one. Nobody watching the job status would have known anything was wrong, because the job status was never checking the thing that actually went wrong.

That is the specific shape of failure I keep coming back to with data pipelines: everything reports success the whole way down, right up until someone downstream notices the numbers don't add up, and by then the bad data has already been consumed by whatever depends on it.

The pattern

The fix is to check something before letting it through. I inserted an explicit, automated gate at both promotion boundaries: no duplicate transaction IDs, no negative quantities, and a row count within an expected range of the trailing seven-day average, checked before bronze-to-silver and again before silver-to-gold. When day twelve's duplicate burst or day twenty-one's corrupted feed got detected, that day's promotion held at whichever tier caught it and got flagged for review, and the forecasting model fell back to the last known-good gold snapshot instead of consuming corrupted data for that day. Both incidents got held before they reached gold. The gold-layer violation rate stayed near zero across all thirty days, and forecast error held steady through both incident days, against the spikes I had measured running the same scenario without the gate.

This reconciles cleanly with an idea I had kept as an open question for a while: whether this gate is the same thing as comparing a challenger model's live performance against an incumbent champion before letting the challenger take over. It is the same gating shape wearing two different comparison rules. One compares a row count to a trailing average. The other compares a challenger's metrics to a champion's. Both exist to guarantee that something new, a day's data in one case, a candidate model in the other, doesn't get to become the thing consumers trust until it clears an explicit comparison against a standard.

Apache Iceberg documents a table-format-native version of the identical guarantee under its "Audit Branch" use case: write candidate data to an isolated branch, validate against that branch, and only fast-forward or merge into the branch consumers actually query once validation passes. My own held-and-flagged, fall-back-to-last-known-good approach achieves the same thing at the application layer that Iceberg's branch pointer achieves at the catalog layer. Different mechanics, same non-negotiable rule: unvalidated data must never become visible to whoever is relying on it.

Design considerations

The checks I run here look inside a schema I already assume is stable. No duplicate IDs, no negative quantities, a row count in range, all of these are checks on the values that arrived, not on whether the shape of the data changed since yesterday. A column silently dropped, or a data type silently changed, on a table other teams depend on is a structural failure this gate was never built to catch, because the schema was never the thing being checked. That is a different problem with its own separate fix, and pretending one gate covers both is how a team gets blindsided by the failure mode it thought it had already solved.

There is a real limitation here, too. A gold-tier table that clears every check I run here, correct shape, correct values, no duplicates, no corrupted quantities, can still carry the wrong business meaning if two different consumers compute something like "adjusted revenue" two different ways from that same trusted table. This gate was never built to interpret what a column means, only whether its shape and values are trustworthy, and those are genuinely separate questions. Passing this gate tells you the data is clean. It does not tell you two teams agree on what the data represents.

The calibration question I'd flag for anyone building this is the row-count-range check specifically, because "within an expected range of the trailing seven-day average" hides a real decision about how wide that range gets to be. Too narrow and you're holding legitimate spikes, a real promotion or a genuine demand surge, as though they were errors. Too wide and duplicate bursts sail through the exact check meant to catch them. The right width depends on how volatile the actual data is day to day, so revisit it after watching the gate run for a few real weeks rather than trusting whatever number felt reasonable on day one.

Where this gate genuinely doesn't fit is anywhere the cost of a held promotion is higher than the cost of the bad data it might occasionally catch. A pipeline feeding a low-stakes internal dashboard that a person glances at once a week can probably tolerate an occasional bad day far more cheaply than it can tolerate the operational overhead of building and maintaining a promotion gate. Reach for this specifically where a bad batch becoming someone else's trusted input is the expensive outcome, because that is the exact failure this gate exists to prevent.