Nightly Reconciliation at Scale

Most of what I test never gets talked about outside engineering, and reconciliation is the clearest example. Nobody in a board meeting asks how the nightly matching job performed. But when it quietly falls behind, or starts flagging false breaks nobody trusts, that's the moment a risk officer finds out about it — usually not on a convenient day. This one's for the people who own that risk, not the people who write the SQL. 

The job nobody notices, until it's the job that's late 

Every bank runs some version of the same nightly ritual: internal ledger entries get matched against an external settlement file — a card network, a correspondent bank, a payment processor — to confirm that what your system thinks happened actually happened. At low volume, this is unglamorous and forgettable. At the volumes we test for our banking clients — tens of millions of transactions a night, across multiple settlement partners with their own formats and their own timing — it stops being a background job and starts being infrastructure that either holds or doesn't. 

When it doesn't hold, the failure is rarely dramatic. It's a job that used to finish in ninety minutes now finishing in six hours, quietly eating into the operational window before the business day starts. It's a database that locks up under the load of a full-table comparison, degrading every other system sharing that instance overnight. It's a growing backlog of "unmatched" transactions that nobody has time to investigate individually, so they get waved through with a shrug — which is precisely the kind of control gap a regulator, an auditor, or DORA's operational resilience expectations will eventually ask you to explain. 

Why this is a risk conversation, not just an engineering one 

Reconciliation isn't a nice-to-have batch job. It's one of the primary controls a bank has for catching the things that matter most: a settlement that never arrived, a duplicate charge, a fee applied twice, a transaction posted against the wrong account. A reconciliation process that can't keep pace with transaction growth doesn't fail loudly — it fails by getting slower, then by getting less precise, then by training the operations team to trust it less, until someone stops actually reading the exception report and starts just clearing it. 

That's the gap I spend most of my time as a tester trying to close: proving, before a system goes live, that it will hold at the volumes it's actually going to see — not the volumes it was demoed at. A reconciliation engine that performs well on a sample dataset in a sprint demo and buckles at three years of real transaction growth is not a hypothetical failure mode. It's the single most common gap we find when we test these systems properly. 

What "engineered well" actually looks like 

The technical choices behind a reconciliation job that scales aren't exotic, but getting them right is the difference between a process that degrades gracefully as volume grows and one that hits a wall. 

Batching, not brute force. A naive reconciliation job pulls the full day's ledger and the full settlement file into memory and compares them wholesale. That works fine in a proof of concept and falls over the first time volume triples. The systems that hold up process in bounded batches — by account range, by settlement partner, by time window — so that a spike in one segment doesn't stall the entire nightly run, and so a failure in one batch can be retried without re-running everything else. 

Indexing for the comparison you're actually doing, not the one you started with. Reconciliation is fundamentally a matching problem, and matching problems live or die on whether the database can find candidate matches without scanning everything. A composite index aligned to how records are actually matched — transaction reference, amount, and settlement date together, rather than three separate single-column indexes — is usually the difference between a job that scales linearly with volume and one that scales quadratically and quietly stops finishing on time. 

Diffing incrementally, not from scratch every night. The most resilient designs we test don't re-diff the entire ledger against the entire settlement file every night. They diff what changed since the last confirmed match, carry forward previously reconciled records without re-touching them, and isolate the comparison to genuinely new or previously-unmatched activity. That single design decision is usually what separates a reconciliation job with a stable, predictable runtime from one whose runtime grows in lockstep with the size of the ledger itself — which, for a growing bank, means a job that gets slower every single quarter by design, not by accident. 

Where testing actually catches this before it becomes a production incident 

This is the part that doesn't show up in an architecture diagram: none of the choices above are safe to assume just because they're in the design document. We test reconciliation systems the way we'd test any other control that a regulator will eventually ask about — not just "does it match correctly on a clean dataset," but "does it still match correctly, and finish on time, at realistic volume, with realistic messiness." 

That means load-testing against a dataset sized for where the business will be in two years, not where it is today. It means deliberately injecting the ugly cases — a settlement file that arrives late, a duplicate record, a currency mismatch, a transaction that legitimately has no counterpart yet because it's still in flight — and confirming the system flags them correctly instead of either silently dropping them or burying the operations team in false positives. A reconciliation engine that's too noisy is nearly as dangerous as one that's too quiet: both end with a human learning to stop trusting the exception report, which is the actual control failure, whatever the underlying code looks like. 

It also means testing what happens when the job itself fails partway through — because at scale, it eventually will. Does a failed batch retry cleanly, or does it leave the ledger in a state where some records are double-counted? Does the system tell someone the job didn't finish, or does the absence of an alert just look like a quiet, uneventful night? 

The standard worth holding this to 

A reconciliation process is one of the places where "it works" and "it's a control you can rely on under scrutiny" are genuinely different bars, and the gap between them only shows up at the volumes and the failure conditions a demo never covers. Getting it right isn't about writing more code — it's about designing the batching, indexing, and diffing strategy for the business's actual growth curve, and then proving it holds under the same pressure a live incident, an audit, or a regulator's operational resilience test eventually will. 

That's the standard we hold reconciliation systems to before we sign off on them, and it's the same standard we'd want any delivery partner testing a control this important to be held to.