Reconciliation Design: Proving Two Systems Agree

Reconciliation is the control that proves two systems agree, and the money is where the records say it is. Designing it well is about the matching key, the timing, and above all the breaks, because the exceptions are where the real work and the real risk live.

Reconciliation is the process of comparing two independent records of the same activity to confirm they agree, then investigating every difference. A bank reconciles its internal ledger against the camt.053 statement from its correspondent; a corporate reconciles its books against its bank statement. The point is to prove the money is where the records claim it is, and to surface every unexplained difference, called a break, because a break signals a missing transaction, a timing mismatch, an error, or fraud. Designing reconciliation is a core systems analyst and functional analyst responsibility, and it is judged not on how it matches the easy cases but on how it detects and handles the breaks.

I have designed and untangled reconciliations in payments where a single unhandled timing difference generated thousands of false breaks and buried the real one. The lesson is that reconciliation design is mostly exception design: matching the agreeing records is the easy part, and handling the ones that do not agree is the whole job. This builds on understanding the payment message flows, especially the camt reporting messages, and the domain context is in Break Into Banking.

What are the building blocks of a reconciliation?

Every reconciliation has the same five building blocks: two sources, a matching key, matching rules, timing, and exception handling. Define those five precisely and you have a reconciliation design; leave any of them vague and you have a source of false breaks and missed real ones.

The two sources are the independent records being compared, and they must genuinely be independent, reconciling a system against itself proves nothing. The matching key is the field that links a record in one source to its counterpart in the other. The matching rules define what counts as a match: exact amount, or amount within a tolerance; one-to-one, or many-to-one where several entries net to one. The timing defines the cutoff and as-of point for each source, because comparing a source as of 5pm against one as of midnight manufactures differences that are not real breaks. The exception handling defines what happens to everything that does not cleanly match.

These five are the skeleton of any reconciliation, from a simple account recon to a complex multi-leg payment recon. Getting them explicit is the design work, and it is the same precision that makes a good functional specification. The templates for capturing this kind of process design are in Real-World BA Deliverables.

How do you choose the matching key?

Choose a matching key that is unique and reliably present in both sources, and in payments that is almost always a carried reference like the end-to-end id or UETR. The whole reason these identifiers are carried across systems is to make this matching possible, so use them.

When a strong key exists, matching is precise: each record in one source links to exactly one in the other by the shared reference, and a record without a counterpart is unambiguously a break. This is the ideal, and it is why preserving the UETR end to end matters so much, a UETR that is dropped or regenerated at a service boundary breaks not just tracing but reconciliation, turning clean matches into a pile of false breaks. That is a real defect with downstream cost, and it is the kind you catch by following one transaction through the flow as in You Don’t Understand the System Until You Test It.

When no single reliable key exists, reconciliation falls back to fuzzy matching on a combination of fields: amount, value date, counterparty, and reference fragments. Fuzzy matching is necessary sometimes but always worse, because it produces ambiguous matches and more breaks to investigate. Part of reconciliation design is therefore advocating for a strong matching key upstream, in the message design and the systems that carry it, rather than accepting fuzzy matching as inevitable. That upstream influence, shaping how identifiers flow so reconciliation is even possible, is exactly the whole-system thinking the systems analyst brings.

Why is timing the hidden source of false breaks?

Timing is the most common cause of false breaks, because the two sources are rarely captured at the same instant, and a difference in timing looks exactly like a difference in substance unless you design for it. A transaction that exists in one source but not yet in the other is not a real break; it is a timing difference, and treating it as a break floods the investigation queue with noise.

Consider reconciling your ledger against a correspondent’s statement. Your ledger updates in real time; their statement arrives at end of day. A payment you posted at 4:59pm may be in your ledger and not yet in their statement, or vice versa, purely because of when each source was captured. If your reconciliation does not account for this, every such item shows as a break, and the real breaks, the ones that signal an actual problem, drown in the false ones. I have seen reconciliations abandoned because they generated so many timing-driven false breaks that nobody trusted them.

The design answer is to make timing explicit: define the as-of point for each source, identify items that are expected to be in transit between the two timings, and treat in-transit items as a known, pending category rather than a break. This is where reconciliation meets the batch vs event-driven reality, because a real-time source reconciled against a batch source is precisely the timing mismatch you have to handle. Getting timing right is what makes a reconciliation trustworthy, and trust is the whole value, an untrusted reconciliation is worse than none, because it consumes effort and catches nothing.

How do you design break handling?

Design break handling as the core of the reconciliation, because the breaks are the output that matters. Specify how breaks are detected, how they are categorized, and how they are routed for investigation and resolution, with a clear workflow, because an unhandled break is a financial risk sitting unexamined.

Start by categorizing breaks, since they are not all the same. An item in one source with no counterpart in the other is one type; a matched pair with a discrepancy, a different amount, is another; an in-transit timing item is a third and should be separated out as pending rather than a true break. Each category needs its own handling: timing items resolve themselves when the other source catches up, discrepancies need investigation of which source is right, and truly missing items need urgent attention because they may signal a lost transaction or fraud. Routing each category to the right queue with the right priority is the design.

Then specify the workflow: who investigates, what information they need (which the reconciliation should surface, the two records and the nature of the difference), how a break is resolved or escalated, and how resolution is recorded for audit. This exception workflow is where reconciliation actually delivers its value as a control, because detecting a break is useless if nothing happens to it. The break-handling design connects to requirements traceability and audit, because in a regulated context you must prove that breaks were detected and resolved. Designing this exception process well is the difference between a reconciliation that controls risk and one that merely generates reports nobody acts on, and it is high-value functional analysis.

The takeaway

Reconciliation is the control that proves two independent systems agree and the money is accounted for. Design it from five building blocks, two sources, a matching key, matching rules, timing, and exception handling, and put most of your attention on the matching key and the breaks. Use a strong carried key like the UETR, handle timing explicitly so in-transit items are not false breaks, and design the break workflow as the core deliverable, because detecting a break is worthless without a process to resolve it.

Get this right and the reconciliation becomes a trusted control rather than a noise generator. Start with Real-World BA Deliverables for the process templates and Break Into Banking for the domain, or browse everything at The Tech BA Toolkit.

Ahmed is a Senior Technical Business Analyst with 10+ years in banking and payments. He builds practical guides and tools for analysts at The Tech BA Toolkit.

Tags: Systems Analysis, Payments, Banking, Reconciliation, Functional Analysis