The first reconciliation pipeline I inherited was a 4,000-line stored procedure that ran once a night and emailed a spreadsheet to three people. When it broke, and it broke roughly twice a month, one of those three people would forward the email to me with the subject line "numbers look weird again." That was the entire alerting system. No dashboard, no lineage, no way to answer the only question that matters at 6am: which transactions are we actually missing, and how much money is that?
Reconciliation is unglamorous. Nobody gets promoted for building it well. But when it fails silently you find out from an auditor or a payment partner or, worst case, a customer whose refund vanished into a gap between two systems. So it is worth building properly, and there is a real craft to it that I have opinions about after doing it three times at three companies.
What we are actually reconciling
At its core reconciliation answers one question: do two independent records of the same money agree? On one side you have your ledger, the internal source of truth for what you think happened. On the other you have an external record, usually a settlement file from an acquirer, a bank statement, a scheme report, or a partner's payout API. In a healthy day the two agree to the penny. In a real day they never do, and the whole job is explaining the difference.
The differences fall into a small number of buckets, and naming them early saves you weeks of confusion later. Timing differences, where a transaction lands in one system today and the other tomorrow. Fee differences, where the acquirer took an interchange cut you booked differently. Genuine breaks, where a transaction exists on one side and simply does not on the other. And duplicates, which are their own special hell. If your pipeline cannot distinguish "will resolve itself tomorrow" from "money is genuinely gone," it is not a reconciliation pipeline, it is a difference calculator.
Matching is the whole game
People assume the hard part is ingestion or reporting. It is not. The hard part is matching, and specifically the fact that a clean one-to-one join on transaction ID almost never works in the real world. Your acquirer batches ten authorizations into one settlement line. A partial capture means one auth maps to two settlements. A refund references the original transaction by a different identifier than the one you stored. I have seen a provider send the merchant reference in a free-text field with inconsistent casing and a trailing space about 4% of the time.
So you build matching in tiers, from strictest to loosest, and you never let a looser tier overwrite a stricter match. The exact-ID match runs first and claims everything it can. Then a match on amount plus date plus last-four. Then fuzzier heuristics for the stragglers. Anything left unmatched after all tiers is a break, and breaks are the output that actually matters.
- Tier 1: exact match on external transaction ID. Deterministic, high confidence, claims 90%-plus on a good day.
- Tier 2: amount + settlement date + masked card, for the batched or re-referenced cases.
- Tier 3: amount within a tolerance window + a date range, flagged as low-confidence and always surfaced for human review, never auto-cleared.
- Unmatched: the break queue. This is your product. Everything else is plumbing.
Idempotency, because you will rerun everything
You will reprocess a day. A partner will resend a corrected file at 11pm. Someone will fat-finger a manual adjustment and you will need to replay the whole thing to unwind it. If your pipeline is not idempotent, every rerun risks double-counting, and double-counting in reconciliation is arguably worse than the original break because now your own tool is lying to you.
The rule I hold to: reprocessing the same inputs must always produce the same output, and the load step must be a merge keyed on a stable natural key, never a blind insert. Store the source file hash and reject a re-ingest that claims to be new but is byte-identical to one you already have. It sounds obvious. I have still watched a team spend a full day chasing a phantom 40,000 EUR discrepancy that turned out to be one settlement file loaded twice because the cron fired on both the primary and the failover box.
MERGE INTO recon_ledger AS target
USING staging_settlement AS src
ON target.external_txn_id = src.external_txn_id
AND target.source_system = src.source_system
WHEN MATCHED AND target.file_hash <> src.file_hash THEN
UPDATE SET target.amount_minor = src.amount_minor,
target.settled_at = src.settled_at,
target.file_hash = src.file_hash,
target.updated_at = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
INSERT (external_txn_id, source_system, amount_minor,
settled_at, file_hash, created_at)
VALUES (src.external_txn_id, src.source_system, src.amount_minor,
src.settled_at, src.file_hash, SYSUTCDATETIME());
Store money as integers, in one currency scheme
I will die on this hill. Store money as integer minor units, never as a floating-point decimal. The classic 0.1 + 0.2 = 0.30000000000000004 problem is funny in a blog post and genuinely dangerous in a settlement file with two million rows, where a thousandth-of-a-cent rounding drift accumulates into a break you cannot explain. Use a bigint of cents, or a fixed-precision decimal type your database guarantees, and pin the currency and scale in the schema.
Enjoying this article?
Get more like it in your inbox — practical engineering leadership, fintech, and AI. No spam, unsubscribe anytime.
Multi-currency makes this sharper. Never reconcile across currencies by converting on the fly with today's FX rate. You reconcile within a currency, and you carry the rate that was actually applied at settlement as a separate stored field. The number of times I have seen a "break" that was really just someone comparing EUR to the GBP equivalent at the wrong rate is too high to count. Reconcile like with like, always.
The break queue is the product
Here is the mindset shift that made every reconciliation system I have built actually useful: the report is not the deliverable, the break queue is. A finance analyst does not want a PDF that says "99.7% matched." They want a worklist of the 0.3% that did not, sorted by amount, with enough context attached to each break to resolve it without opening five other systems.
A reconciliation tool that only tells you the totals disagree is a smoke alarm with no address. The value is entirely in how fast it points a human at the specific transaction that is wrong and gives them the evidence to fix it.
So each break carries its lineage: both sides of the attempted match, which tier failed and why, the source file it came from, and any prior manual annotations. Give analysts the ability to categorize a break, assign it, and add a note that persists across reruns. That last part matters, because a break that reruns nightly and forgets it was already investigated will get investigated again by a tired person at 7am, and they will not thank you.
Cutoffs, timezones, and the T+1 lie
Timing differences cause more false breaks than anything else, and almost all of them trace back to two things: timezone confusion and settlement cutoffs. Your ledger records the authorization at the instant it happened in UTC. The acquirer's settlement day is defined by their cutoff, which might be 22:00 in a timezone nobody told you about, and their "settlement date" for a transaction can be a full day or two after the auth. If you naively match on the same calendar date, a huge slice of legitimate transactions look like breaks every single night.
The fix is to reconcile on settlement windows, not calendar days, and to expect a lag. Bank transactions especially settle on a T+1 or T+2 rhythm, so an unmatched transaction less than 48 hours old is usually just in flight, not lost. Age your breaks. A transaction unmatched for two hours is noise. The same transaction unmatched for five days is a genuine problem that needs a human and possibly a call to the partner. Do not treat those two the same, or your analysts will drown in noise and start ignoring the whole queue.
Instrument it like a payment system, because it is one
The old stored procedure I inherited had no metrics at all. You could not answer "is the match rate trending down over the last week" without querying the database by hand. That blindness is how silent failures survive. A reconciliation pipeline should emit the same operational telemetry you would demand of any production service: how many records ingested per source, match rate per tier, total unmatched value, and the age distribution of open breaks.
What I care about most is the derivative. An absolute match rate of 98% might be perfectly normal for one payment method and alarming for another. But a match rate that dropped from 99.4% to 96% overnight is a signal something changed upstream, a file format tweak, a new fee code, a schema change nobody warned you about. Alert on the change, not just the threshold. Set the alert to fire to a human channel, not to an inbox where it competes with a hundred other emails and loses.
Controls, because someone will audit this
In regulated fintech the reconciliation pipeline is not just an operational tool, it is a control, and eventually an auditor or a regulator will ask you to prove it works. That means the pipeline itself needs to be auditable. Who cleared this break, and when? What was the input file, and can you produce it? If a match was made by a loose heuristic, is that decision recorded and reviewable? "The system did it" is not an acceptable answer when real money moved.
Build for that from the start, because retrofitting an audit trail onto a system that mutates records in place is miserable. Append, do not overwrite. Keep the raw source files immutable in cheap storage with a retention policy that matches your regulatory obligations. Record every manual action with an actor and a timestamp. It costs a little more storage and a little more discipline, and it is the difference between a two-hour audit conversation and a two-week one.

Write the break-queue schema first
If I were starting a reconciliation pipeline tomorrow, I would write the break-queue schema before I wrote a single line of ingestion. Not because ingestion does not matter, but because deciding up front exactly what a "break" is, what context it carries, and how a human resolves it forces every other decision into place. The matching tiers, the idempotent load, the integer money, the aging logic, all of it exists to feed that queue cleanly. Get the output right and the plumbing has a purpose. Build the plumbing first and you end up with my old 4,000-line stored procedure, quietly emailing a spreadsheet to three people who have learned not to trust it.
Get new posts in your inbox
Occasional, practical notes on engineering leadership, fintech, and building with AI. No spam, unsubscribe anytime.
Comments (0)
Leave a Comment
No comments yet. Be the first to comment!
