Automated data reconciliation checks that two systems hold the same records. It runs in layers: record counts first, then control totals such as the sum of amounts, then a match on a shared key, then a field-by-field comparison. Rules close the predictable differences. Breaks with no clear cause, or with money at stake, go to a person.
What does automated data reconciliation compare between two systems?
Automated data reconciliation compares a source system with a target system at four levels of detail: how many records each holds, what the records add up to, which records pair with which, and whether paired records agree field by field. Each level catches a different kind of fault, and a run should report all four.
The two systems can be almost anything that should agree. A payment platform and the general ledger. A payroll provider and the payroll bank account. A billing system and the receivables subledger. A data warehouse and the system it copies from. The method stays the same; only the key and the fields change.
Why the levels run in order
The levels run from coarse to fine for a practical reason. A count or total failure usually means a whole file is missing, doubled or cut off. Matching line by line before you know that produces thousands of breaks with one cause. Checking the cheap totals first tells you whether line matching is even worth starting.
The worked example used in this post
The sections below follow one example. A company exports a day's card payments from its payment platform and imports them into its ledger. The figures are made up for illustration.
- Source: payment platform export, 1,204 records, total 186,420.50.
- Target: ledger import for the same day, 1,203 records, total 186,371.50.
Record counts and control totals as the first data reconciliation check
Record counts and control totals are the first data reconciliation check because they are fast and they catch the biggest failures. A count compares how many records each side holds. A control total compares the sum of a numeric field, usually the amount. If both agree, a load is probably complete, though not certainly correct.
In the example, the source has 1,204 records and the target 1,203. The amounts differ by 49.00. One record is missing, or one is missing and others changed. That tells the reviewer to expect a small number of breaks, not a systemic failure.
Totals that add more than the amount
A single amount total can hide offsetting errors: a payment dropped and another doubled for the same value nets to zero. Extra control totals reduce that risk:
- Sum of amounts, the basic check.
- Sum of absolute amounts, which stops refunds and payments cancelling each other.
- Count by status, such as settled, refunded and failed.
- A hash total, the sum of a field that has no meaning as a sum, such as a numeric customer ID. It changes if any record is swapped for another.
Cutoff is the usual culprit
Count and total failures often come from cutoff, not data loss. One system closes its day at midnight in one time zone, and the other in another. A payment at 11:58 p.m. lands in different days. Before chasing missing records, confirm both extracts use the same date field and the same boundary.
Key matching: pairing records across source and target systems
Key matching pairs each source record with its counterpart in the target using an identifier both systems carry. The best key is one the source system generates and the target stores unchanged, such as a payment ID. When no such key exists, a composite key built from several fields, such as date, amount and customer, does the job less reliably.
After matching, every record lands in one of three groups: matched, in the source only, or in the target only. In the example, 1,202 records match on payment ID, two exist only in the source, and one exists only in the target.
Composite keys and their risks
A composite key can match the wrong pair when two records share the same values. Two payments of 25.00 from the same customer on the same day are indistinguishable by date, amount and customer. A sound process flags any composite key that appears more than once on either side and sends those records to review instead of pairing them by order.
These pairing rules are the same family used for bank lines. How transaction matching software applies date windows and tolerances explains the settings that decide how loose a match may be.
Keys that change in transit
Some integrations alter the key on the way: they strip leading zeros, change case, or add a prefix. A payment ID of "00481" in the source may arrive as "481". Normalize keys the same way on both sides before matching, and record the rule, so a reviewer knows the match was not exact text.
Field-level breaks in automated data reconciliation
A field-level break is a matched pair whose other fields disagree. The key says the two records are the same payment, but the amount, date, currency or status differs. Automated data reconciliation compares each mapped field and reports the ones that differ, with both values side by side.
The example's break table
After matching, the example run produces four breaks. The values are illustrative.
| Break | Payment ID | Source value | Target value | Likely cause |
|---|---|---|---|---|
| Missing in target | P-77120 | 49.00, settled | (none) | Import rejected one record |
| Missing in target | P-77131 | -120.00, refund | (none) | Refunds filtered out by import rule |
| Missing in source | P-77131-R | (none) | -120.00 | Refund posted under a new ID |
| Field break | P-76988 | 88.40 USD | 88.40 CAD | Currency field mapped wrong |
Reading the table shows something the totals did not. The 49.00 difference is one rejected record. The two refund rows are one event recorded under two IDs, so they cancel out in the totals. The currency break does not change the total at all, yet it is the most serious finding, because every CAD record may now be mis-valued.
Tolerances for fields
Some fields should match exactly, such as IDs and currencies. Others may tolerate a small difference, such as an amount rounded differently, or a timestamp a few seconds apart. Set tolerances per field, record them with the run, and report matches inside tolerance separately from exact matches.
Which data reconciliation breaks need a person to resolve?
Data reconciliation breaks need a person when the cause is unknown, when the fix changes money in the books, or when the same break pattern could mean either a harmless timing gap or a real error. Rules can close breaks whose cause is known and repeatable, such as a refund that always posts under a new ID.
Sorting breaks into three groups
- Auto-closed: a known pattern with a rule that pairs the two sides, logged with the rule's name. The refund pair in the example fits here once the rule exists.
- Routine review: a known cause, but a person confirms each one, such as cutoff items that should appear tomorrow.
- Investigation: no known cause, or a field break that may affect many records. The currency mapping belongs here.
Mistake: writing a rule to make a break disappear
When a break recurs, the tempting fix is a rule that closes it. That is right only when the cause is understood and the rule is narrow. A rule that says "ignore differences under 50.00" hides the rejected 49.00 payment and every future one like it. Fix the import, then reconcile again. The breaks list should get shorter because the systems agree, not because the rules stopped looking.
Supplier balances raise the same question with a different source. Automated supplier statement reconciliation covers breaks between a vendor's statement and your payables ledger, and reconciliation tools built for banks covers the high-volume version inside financial institutions.
When automated data reconciliation results must stand up as evidence
Automated data reconciliation results must stand up as evidence when an auditor, a regulator or a court relies on them. In that case the run must be repeatable: the same inputs and rules should give the same breaks, and someone outside the team should be able to see how each break was closed.
What to keep from every run
- The source and target extracts, or a fingerprint of each, with the time they were taken.
- The key and field mappings, including any normalization.
- The tolerances and auto-close rules, with version dates.
- The full break list, before and after resolution.
- For each resolved break, who closed it, when and on what evidence.
When the reconciliation is part of an expert's work
In litigation, a forensic accountant's reconciliation of bank and accounting records may be tested for whether its method is reliable and was applied consistently. Undocumented rules or a break list that cannot be reproduced give the other side an opening. How a Daubert challenge to a forensic accountant works covers that test in more detail.
Bank statements are a common source system in these reconciliations, and they need checking before they are compared with anything. PreciseBankFlow reads each statement line with a confidence score, proves the statement foots from opening to closing balance, and exports the exceptions with their source lines for the workpapers. To try it on your own statements, request early access.