Somewhere in your stack there is a sync job with a green checkmark next to it. The unit tests pass. The integration tests pass. The last deploy didn't throw a single error. And yet last week someone on the finance team noticed that the order totals in the warehouse were off by four percent from what the source system actually recorded.
Nobody broke anything. Nothing crashed. The job just quietly stopped telling the truth, and the tests never noticed because they weren't built to catch that kind of failure in the first place.
What "drift" actually means in a sync job
Drift is the gap between what a source system says is true and what a downstream copy of that data actually contains, measured over time rather than at a single point. It's different from an outage. An outage is loud: the job fails, an alert fires, someone gets paged. Drift is quiet. The job keeps running, keeps reporting success, and the gap between source and destination just grows a little wider with every run.
A pipeline can drift for weeks before anyone notices, because the symptom usually isn't an error in a log file. It's a stakeholder asking why a dashboard number doesn't match what they see in the source tool. By the time that question gets asked, the underlying cause may already be buried under dozens of subsequent runs.
Why passing tests don't catch it
Most test suites for a data pipeline check that the code does what the code is supposed to do: given this input, produce this output. That's a valuable check, but it tests the pipeline's logic against fixtures you control, not against the live, messy behavior of a production source system.
Photo by panumas nikhomkhai on Pexels
Tests rarely simulate the conditions that actually cause drift: a source API that silently changes a field's type, a webhook that gets delivered twice, a batch job that gets killed halfway through a write, or a downstream table that has a slightly different rounding rule than the source. None of these are logic bugs. They're gaps between the assumptions your code makes and what the real system actually does under real conditions, and a green test suite has no way to see them.
The failure modes that don't throw errors
A handful of patterns account for most silent drift, and it's worth naming them specifically because each one needs a different fix.
Partial writes. A batch job dies midway through a transaction, and depending on how the destination handles partial commits, some rows land and others don't. The job's own log says "completed" because the process technically finished, it just didn't finish writing everything it read.
Race conditions on concurrent updates. Two processes write to the same record close together, and whichever write lands last wins, even if it was working from stale data. The record ends up technically present but factually wrong.
Schema drift upstream. A source system adds a field, renames one, or changes a data type, and your pipeline either drops the new information silently or coerces it into the wrong shape without throwing an exception.
Soft deletes that don't propagate. A record gets marked deleted or archived in the source, but the sync job only picks up creates and updates, so the destination keeps a "zombie" record that should have disappeared.
Timezone and rounding mismatches. Currency gets rounded differently in two systems, or a timestamp gets stored in local time in one place and UTC in another. Each individual discrepancy is tiny. Summed across millions of rows, it's the four percent gap someone in finance eventually notices.
How reconciliation catches what tests miss
Reconciliation means periodically comparing the source and the destination directly, rather than trusting that the pipeline that moved the data did so correctly. It's a different kind of check: not "does the code work," but "does the data actually match, right now, in production."
There are three levels of reconciliation worth building, roughly in order of effort:
- Row count checks. The cheapest signal. If the source has 40,212 active records and the destination has 39,988, something is wrong, even if you don't yet know what.
- Checksum or hash comparisons. Compute a hash of each record's relevant fields on both sides and compare. This catches drift within records that still technically "exist" on both ends but no longer agree on content.
- Sampled field-level comparison. For a random sample of records each run, pull the actual field values from both systems and diff them. Slower, but it tells you exactly which fields drift and how often, which is the information you need to actually fix the root cause.
Projects like Great Expectations exist specifically to make the second and third levels of this practical to run on a schedule, by letting you define expectations about your data once and check them automatically on every run rather than writing bespoke comparison scripts for every table.
Designing the reconciliation job itself
A reconciliation job is its own pipeline, and it deserves the same design discipline as the sync job it's checking. A few principles that make the difference between a reconciliation job that actually gets used and one that gets disabled after a month of noisy false positives:
Run it on a schedule independent of the sync job. If reconciliation only runs right after the sync job, a systemic failure in the sync job's own scheduling (a cron job that silently stopped firing, for instance) can take both down together. Tools like Apache Airflow make it straightforward to define reconciliation as its own DAG with its own triggers, decoupled from the pipeline it audits.
Compare against a stable snapshot, not a moving target. If the source system is still being written to while you're reconciling, you'll get false positives from records that were mid-update at the exact moment you sampled them. Either reconcile against a point-in-time snapshot or build in a short grace window before flagging a mismatch as real.
Photo by Robert Clark on Pexels
Log the specific diff, not just the fact that one exists. "347 records mismatched" is nearly useless on its own. "347 records mismatched, all missing a updated_at field that changed type from integer to string on 2026-08-14" tells an engineer exactly where to look.
Alerting without crying wolf
The fastest way to kill a reconciliation system is to have it page someone for a 0.02% mismatch rate that turns out to be normal eventual-consistency lag rather than an actual bug. Once a few false alarms happen, the alerts get muted, and you're back to silent drift with extra steps.
Set explicit tolerance thresholds instead of treating any mismatch as an incident. A small percentage of records that resolve on the next run, within an expected eventual consistency window, is normal in most distributed systems and shouldn't page anyone. A mismatch rate that's growing run over run, or one that persists past the expected consistency window, is the pattern that actually deserves an alert.
Photo by Tima Miroshnichenko on Pexels
Tools built for change data capture, like Debezium, can reduce how much reconciliation work you need in the first place by streaming every row-level change from the source as it happens rather than relying on periodic batch syncs that are more prone to missing intermediate states. It's not a replacement for reconciliation, but it shrinks the surface area where drift can quietly accumulate between checks.
Treating reconciliation as continuous, not a one-off audit
Teams often reach for reconciliation only after drift has already caused a visible problem: a support ticket, a wrong invoice, a dashboard number that doesn't match a source report. At that point it's forensics, not prevention. The better model treats data synchronization integrity the same way you'd treat uptime: something you monitor continuously with a dashboard and a trend line, not something you check manually when someone complains.
That shift in framing matters because drift is cumulative. A pipeline that's 99.98% accurate today and trending downward is a very different problem from one that's steady at 99.98% and staying there. You can't tell the difference without a continuous signal, and you can't build a continuous signal after the fact from log files that were never designed to answer this question.
"The pipelines that surprise teams are never the ones that fail loudly. They're the ones that keep running, keep looking healthy, and just quietly stop being accurate. Reconciliation is how you turn that invisible risk into a number you can actually watch." - Dennis Traina, founder of 137Foundry
What this looks like in practice
None of this requires rebuilding your pipeline from scratch. Most teams start with row count checks on their highest-risk tables, running daily, with a simple threshold for what counts as worth investigating. From there, checksum comparisons get added to the tables where correctness actually matters for a business decision, like billing or inventory. Full field-level reconciliation gets reserved for the handful of tables where a silent mismatch would be genuinely expensive to leave unnoticed.
The point isn't to reconcile everything with equal rigor. It's to know, for the data that actually matters, whether your green test suite is telling you the truth about production or just about your fixtures.
It also changes how a team responds when a stakeholder does eventually spot a discrepancy. Instead of a scramble through logs and a guess about which run introduced the problem, you have a trend line showing exactly when the mismatch rate started climbing and which table it started on. That turns a multi-day investigation into something you can usually diagnose in an afternoon, because the evidence was already being collected before anyone asked the question.
It's worth budgeting real engineering time for this rather than treating it as a nice-to-have. A reconciliation job that only gets built after an incident tends to get built narrowly, scoped just to the table that caused the last problem. A reconciliation job built proactively, covering every table where correctness actually matters to the business, catches the next drift before it becomes a support ticket instead of after.
If you're seeing numbers drift between systems and can't pin down where the gap starts, 137Foundry's data integration service builds exactly this kind of reconciliation tooling into existing pipelines rather than requiring a rewrite. Our broader services cover the sync jobs themselves, and you can read more about how we approach this kind of work on our homepage or about page.
