The integration work nobody scopes: reconciliation
Connecting two systems is a week. Proving they still agree eighteen months later is the actual project, and it's the line item that gets cut.
Integration quotes are usually written against the happy path: read from A, transform, write to B, handle errors, done. That part genuinely is quick. What doesn't appear on the quote is the answer to a question that arrives about four months later — how do you know the two systems still agree?
They drift. They always drift. Someone edits a record directly in the destination system. A webhook is dropped during a deploy. An API returns a 200 with a partial write. A retry succeeds twice. None of these throw an error anyone sees, and each one leaves the two systems slightly further apart.
Drift is normal; undetected drift is the failure
The goal isn't a pipeline that never drifts — you can't have one, because you don't control both ends. The goal is that drift is detected quickly, quantified, and either corrected automatically or put in front of a human with enough context to act.
That means a reconciliation job: a scheduled process that independently compares the two systems and reports on the difference. Not the sync job checking its own work — a separate process, reading both sources, answering 'do these agree?'
A sync pipeline that reports its own success is marking its own homework. Reconciliation has to read both systems independently, or it inherits the same bug that caused the drift.
What to compare
Comparing every field of every record is expensive and mostly noise. In practice three levels catch nearly everything:
- Counts — how many records exist on each side in a given window. Catches wholesale loss fast and costs almost nothing.
- Aggregates — sums of the numbers that matter (totals, quantities, balances). Catches partial writes and double-applied updates that counts miss.
- Row-level hashes — a checksum of the fields you actually care about, compared per record. Expensive, so scope it to recent windows or run it on a slower cadence.
Running the cheap checks often and the expensive one rarely gives you most of the coverage for a fraction of the cost.
Exceptions need an owner and a queue, not an alert
The common failure here is cultural, not technical. The reconciliation job finds twelve mismatches, fires an alert into a channel, and everyone assumes someone else is handling it. Within a month the alert is muted.
An exception needs to be a work item with state: open, who owns it, what was decided, closed. That can be a table and a small internal screen — it does not need to be sophisticated. What it needs is to make an unresolved mismatch visible and countable, so 'we have 340 open exceptions' is a fact somebody has to look at rather than a feeling.
Design for replay
When you do find drift, you need to fix it, and the safest fix is to re-run the original work rather than hand-patch the destination. That requires two things you have to build in early: the source events have to be retained long enough to replay, and the write path has to be idempotent so replaying is safe.
Keep the raw inbound payload, not just the transformed result. When a transformation turns out to be wrong — and one will — the raw payload is what lets you reprocess history correctly. Teams that discard it can only fix the bug going forward, which leaves a permanent scar in the data.
Put it in the scope
Reconciliation gets cut from proposals because it looks like overhead next to the visible feature. It's worth being direct with whoever is approving the budget: the alternative to building it is discovering the drift later, in a finance report or a customer complaint, with no history to reconstruct what happened.
The build is not large. A scheduled comparison, an exceptions table, a replay path, and a screen. Measured against manually unpicking a year of divergence, it is the cheapest part of the project.