A supplier adds a column to their nightly export on a Tuesday. Your loader ignores columns it doesn't recognise, so nothing errors and nothing gets logged. On Thursday someone notices the margin figure looks generous, and by then it has been in two client reports and a board pack.
Nobody was woken at 3am, because nothing failed. That is the part worth fixing. Data pipeline reliability has very little to do with jobs that never crash: a crash is the easy case, because it is loud and it stops. The expensive failures are the ones that finish green and hand you numbers that are quietly wrong.
Four habits do most of the work here. None of them are difficult. They are just rarely put in before the first incident rather than after it.
A run that completed is not a run that worked
Most orchestration tools tell you one thing: did the job exit cleanly. That is a useful signal about your code and a poor signal about your data.
A job can complete having loaded 4 rows instead of 40,000, because the source API returned an empty page and your loop handled it politely. It can complete having written the same day twice, because a retry fired after the first attempt had already committed half its work. It can complete having silently cast a currency column to text, so every downstream sum returns zero and the dashboard shows a tidy, confident 0.
So the first thing to separate is the job ran from the data is right. They need different checks, different owners and, ideally, different alerts. Once that distinction exists, the rest of this list has somewhere to live.
Make every run idempotent
Running the same job twice should leave you in the same state as running it once. That single property is what turns a 3am page into something that can wait until morning.
In practice it usually means three changes:
- Write by partition, not by append. Delete and rewrite the day you are loading rather than adding to whatever is already there. A re-run then replaces its own output instead of duplicating it.
- Give every record a stable key from the source, and merge on it. Row order and load timestamps are not keys.
- Make the load atomic. Stage into a temporary table, validate, then swap. A half-written table is worse than no table, because it looks finished.
The test is simple and worth doing on a quiet Wednesday: run yesterday's job again on purpose. If the row counts change, you don't have a retry story, you have a manual cleanup procedure that nobody has written down.
Backfills are the same property viewed from a different angle. If you can safely re-run a single day, you can re-run ninety of them when a source system turns out to have been wrong since March.
Validate at the door
Check the data the moment it arrives, before anything downstream depends on it. The checks that catch the most for the least effort:
- Row count within a sane range. Not "greater than zero". Compare against the same weekday for the last few weeks, and fail loudly on a load that is a tenth of normal.
- Required columns present, with the types you expect. This is the check that would have caught the supplier's new column, or rather the reordering that came with it.
- Primary key actually unique. Duplicates are the most common cause of a number that is inexplicably too high.
- No unexpected nulls in the fields your joins depend on.
- Freshness. The most recent record should be from when you think it should be. A pipeline reading a stale file for six days is a classic, and it never errors.
Most transformation tools will run these as part of the build. dbt's data tests are the common example: they live next to the model definition in version control, and they fail the build rather than emailing someone. Whatever you use, the important property is that a failed check stops the data moving forward. A test that logs a warning and lets the load continue is documentation, not a control.
Where to put the strictness: fail hard at ingestion, be more forgiving in the middle, and be strict again at the point where a human reads a number. Catching a bad load at the door costs an hour. Catching it in a board deck costs credibility you then spend a quarter rebuilding.
Alert on the numbers, not just the exit code
Here is the check almost nobody has, and it is the one that pays for itself. Yesterday had 40% fewer orders than any day this month. No job failed. Every test passed, because the rows that arrived were perfectly well-formed. There just weren't many of them.
Anomaly checks watch the values rather than the mechanics: order volume, revenue by channel, average basket, the ratio between two things that normally move together. You do not need a model for this. A rolling median and a band around it will find most of what matters, and you tighten the band on the metrics people actually make decisions with.
Two rules keep alerting useful:
- Route by severity, not by system. A failed load of the finance warehouse is a phone call. A late load of a marketing table that gets read on Mondays is a message in a channel. If everything pages, nothing does.
- Every alert names an owner and a first action. "Orders down 40% versus the trailing median, check the Shopify connector first" beats a stack trace at 3am by a wide margin.
There is a version of this trap on the marketing side too. A green Lighthouse score and a failing Core Web Vitals report are the same disagreement between a check and reality: the easy measurement quietly replaces the one that matters, and everyone optimises the proxy.
Decide now what happens when it breaks
It will break. A source will change without telling you, a credential will expire, an API will start rate-limiting on the day of a promotion. What separates a bad hour from a bad week is whether the answer to "what do we do" already exists in writing.
Four things to agree before you need them:
- Who owns each pipeline by name, and who covers when that person is on leave.
- What the business can tolerate: is a four-hour delay on this table acceptable, or does someone need to know within fifteen minutes?
- Where the runbook lives, and whether it has been followed by somebody other than its author.
- Whether stale data is served or hidden. A dashboard showing Tuesday's numbers with no warning on Thursday causes more damage than one showing an honest gap.
One thing you can do tomorrow
Pick the pipeline behind the number your leadership team quotes most often. Run it twice, back to back, and compare the row counts. Then write down what would happen if it failed silently on a Friday afternoon: who would notice, when, and how.
If the honest answer is "someone would spot it in a meeting", that pipeline is being monitored by luck. Fixing that is usually a few days of work, not a rebuild, and it is the layer we work in when we design and run data pipelines for clients who have already had the bad week.
