Why fragmented, messy source data should shape your architecture from day one.
Most data pipelines are designed against the data an organisation believes it has. They meet production and discover the data it actually has: five systems that each define a customer differently, a decade of exports where a nullable column silently changed meaning, a nightly feed that arrives late one week in four, and a spreadsheet that turns out to be the authoritative record for a process nobody documented.
The pipeline does not usually fail loudly at that point. It keeps running and quietly produces numbers that are wrong in ways no one notices until a decision has already been made on them. That is the failure mode worth designing against.
Treat source data as a constraint, not an input
A clean model of the domain is a useful destination. It is a poor starting assumption. If the architecture only works when the sources behave, every deviation becomes an incident rather than an expected case that the system already handles.
In practice this means separating what arrived from what it means. Land source data unmodified and keep it — including the rows that failed validation, the batch that arrived twice, and the file whose schema drifted. Derive the clean model from that record rather than in place of it. When a number is later questioned, the answer is a query rather than an archaeology project, and reprocessing a corrected batch is a routine operation instead of a rebuild.
Make the contract explicit at every boundary
Between each stage, state what is expected: the shape of the data, the columns that must be present, the ranges that are plausible, the volumes that are normal, and the freshness the downstream consumer depends on. Check those expectations on every run and record the result.
The value is not in rejecting bad data — often you cannot afford to reject it — but in knowing precisely when a source changed and what changed. A pipeline that reports "this feed's null rate moved from 0.2% to 40% overnight" is doing more for the business than one that succeeds silently and pushes the problem into a dashboard.
Design for reprocessing from the start
Source data is corrected retrospectively. A supplier reissues a month of records; a bug in an upstream system is fixed and the history rewritten; a definition changes and the last two years need restating on the new basis. If the pipeline can only move forward, each of these becomes a manual recovery.
Idempotent steps, deterministic transformations and clearly bounded units of work make reprocessing a normal operation. It is far cheaper to build that in at the outset than to retrofit it into a system whose transformations quietly depend on when they last ran.
Decide what happens to the bad rows
Every pipeline handling real data eventually meets a record it cannot process. The question is whether that decision is made deliberately in the design, or accidentally by whoever wrote the first exception handler.
Some domains can quarantine and continue. In regulated and clinical settings, dropping a record silently is not an option at all, and the pipeline must stop and escalate. Either answer can be right. What matters is that the choice is explicit, visible to the people who own the data, and consistent across the system rather than varying by which stage happened to fail.
Instrument the data, not just the job
Monitoring that reports whether the job ran tells you very little. The failures that cause damage are the ones where every job succeeded. Track the properties of the data itself over time — row counts, distributions, null rates, referential integrity, the lag between an event happening and it appearing — and alert on the shape of those changing.
Most serious data incidents we are called in to investigate were visible in that signal for weeks before anyone noticed downstream.
The pattern underneath
None of this is exotic engineering. It is the difference between designing for the data you were promised and designing for the data that will actually arrive — and it is almost always cheaper to make that decision at the architecture stage than after the first quarter of questionable reporting.
If you are building or rebuilding a data platform and the source landscape is more complicated than anyone would like, that is the normal case rather than an obstacle to clear first. Tell us what you're working with.