Data Engineering Worked Example — The Number That Was Wrong for Five Weeks

This is an illustrative example. The company, pipeline and figures are invented. The failure mode is not: a job that succeeds, writes nothing useful, and is never questioned.

The situation#

A retailer with 60 stores. The weekly trading review runs on Monday morning off a dashboard fed by a nightly pipeline. In early March, the head of trading noticed that one region's sales looked flat — not wrong, not alarming, just flat in a way that did not match what store managers were saying.

It had been wrong for five weeks.

What had happened#

A store had been refitted and reopened under a new store code. The extract joined store codes to a region mapping that was maintained by hand in a spreadsheet. The new code was not in it.

The join was an inner join. Every transaction from that store silently disappeared — roughly 2% of national sales and about 14% of that region's.

Nothing failed. The pipeline ran green for 35 consecutive nights.

Why nothing caught it#

Four separate things could have caught this, and each was defeated in an instructive way.

The pipeline "succeeded" every night. Exit code zero. The scheduler dashboard was green for the entire period. Success meant "the script ran", not "the output is right".

The volume check was too loose. There was one: row count within ±25% of the 28-day median. A 2% drop is not close to that threshold. The check was calibrated for a total failure, and this was a partial one.

There was no reconciliation. Nobody compared pipeline totals to the finance ledger. That comparison would have shown a 2% gap on the first morning, and it is the check that most directly answers "can I trust this number".

The regional split had no check at all. All checks ran on the national total, where 2% is invisible. The failure was concentrated in one region, where it was 14% and would have been obvious.

What made it hard to fix#

The pipeline transformed on read. There was no raw layer — the extract was joined, aggregated and written in one pass, and only the aggregate was kept.

This meant the five weeks of correct data could not be recovered from anything the pipeline had stored. The transactions still existed in the source system, but reprocessing them required a re-extract with the fixed mapping, and the source system retained only 30 days at transaction granularity. Five weeks is more than 30 days.

The last week of the period was reconstructed exactly. The first two weeks were estimated from store-level till reports, and the trading review for that period carries a footnote to this day.

A raw layer would have made this an afternoon of reprocessing. Its absence turned a mapping error into permanent damage to a historical series.

What was changed#

Five changes, in the order they were made.

Reconciliation against the finance ledger, nightly. Threshold ±0.1%. This is the check that would have caught it on day one, and it took an afternoon to write.

Checks at the grain the business uses. Volume and freshness now run per region as well as nationally. A failure concentrated anywhere visible is now visible.

The inner join became a left join with an alert. Unmapped store codes now flow through against an "unmapped" region and raise a warning. Data that does not match a lookup must never disappear silently — it must arrive somewhere ugly and noticeable.

A raw layer. Extracts now land exactly as received before anything transforms them, with 90-day retention. Reprocessing a fix over history is now a re-run rather than an archaeological exercise.

The store mapping moved out of the spreadsheet into a table with an owner, and new store codes now appear in a weekly exception report rather than waiting to be noticed.

The result#

Two months later, the same class of failure occurred: a new franchise location with an unrecognised code.

The May incident cost twenty minutes and never reached a dashboard.

What was learned#

Green does not mean correct. The pipeline reported success 35 times while producing wrong numbers every night. Exit codes describe the script, not the data.

Reconcile against the system of record. It is the only check that tests the whole pipeline end to end, and it was the cheapest of the five changes.

Check at the grain where a problem would be visible. Two per cent nationally is noise. Fourteen per cent regionally is an alarm. The same failure, measured at two grains.

Never let unmatched rows vanish. An inner join is a silent filter. Somebody chose it without deciding that unmatched data should be discarded, which is what it does.

A raw layer is insurance you buy before the fire. It is nearly free on the day you build it and impossible to add retroactively when you need it.

Back to Data Engineering