Data reconciliation testing: plant known breaks, count what comes back

Data reconciliation testing checks that a reconciliation catches real differences and does not flag legitimate ones. For regulatory reports, where the general ledger, the Call Report, the FR Y-9C, the FR 2900 and the FR 2052a all carry the same balances under different rules, the most reliable form is a seeded-error test: take a quarter that has already been filed and reconciled, plant known misclassifications drawn from real misreports, run the cross-report rules and any agents, and count what comes back. A control that reports no breaks on a clean quarter has proved nothing; one that finds nine planted errors out of ten, and raises no false alarm on the legitimate differences, has. Botminds' Regulatory Reporting Reconciliation is built to be tested this way, and it tests its own agents against the last quarter's breaks before any change goes live.

The sample figures on this page come from a fictional bank, about $92 billion in assets, closing its third quarter of 2026 in the solution. The seeds come from public cases and from the agencies' own instructions, linked where they appear.

What is reconciliation testing?

Reconciliation testing means two things, and a regulatory reporting team needs both. In data engineering it is the check after a load that counts and sums in the target match the source, often called reconciliation in ETL testing. In internal control it is the test of whether a reconciliation control is designed to catch the errors it exists for, and whether it operated as designed. Cross-report reconciliation sits on top of the first and is the second: it is a control whose inputs are the outputs of many loads.

The supervisory expectation is plain. The OCC's examiner booklet says "The audit function should periodically test internal controls to identify unreliable financial and regulatory reporting" (OCC Comptroller's Handbook, Regulatory Reporting). The Basel Committee's principles for risk data ask for "Integrated procedures for identifying, reporting and explaining data errors or weaknesses in data integrity via exceptions reports" (BCBS 239, paragraph 53). And for insured institutions with $5 billion or more in assets, the independent accountant attests to internal control over financial reporting, which 12 CFR 363.1(c) defines to include "financial statements prepared for regulatory reporting purposes". None of these says how to test a reconciliation; a seeded test is one way to produce the evidence they ask for.

Why a pass on clean data proves little

A reconciliation that finds no breaks has either met clean data or failed to look, and the result alone cannot say which. Three failures produce the same silent pass. A tolerance set wider than the errors it should catch: a rule that accepts $5.0 million of noise on total deposits will never see a $4.2 million misposting. An explanation copied forward from last quarter that now hides a real difference: the sample bank's manual process carried reconciling items forward from a tab without re-deriving them. And a rule that compares the wrong lines, which ties every quarter because both sides are wrong in the same way.

Sampling, the auditor's usual tool, does not catch the first two well. It checks that the reconciliation was performed and signed, and that a sample of reconciling items is supported. It cannot show what a rule would have done with an error that was not there. Planting the error is the only way to see that.

How do you test data reconciliation? Seed, run, score

You test data reconciliation by giving it errors whose answers you already know. Start from a quarter the bank has filed and reconciled, so every legitimate difference is documented. Make an unchanged copy, the clean twin, and a seeded copy. Have someone who did not write the rules plant errors in the seeded copy from a catalogue, and keep the answer key away from the people and agents being tested. Run both copies exactly as on a live night, then score.

The clean twin matters as much as the seeded copy. Every flag it raises is a false alarm, because the clean quarter was filed and reconciled with its differences explained. A control that catches every seed while raising forty false alarms on the twin has shifted the work, not removed it: analysts will learn to ignore its flags, and the forty-first will be real.

Two practical rules make the test honest. Plant some seeds just below and just above each tolerance, so the result shows where the threshold really sits. And plant at least one seed in every report pair and every kind of difference the bank's rules cover, so a pass is not a pass on deposits alone.

Where the seeds come from: what Republic First and others misreported

The best seeds are errors that real banks made and the agencies wrote down. They have the shape real errors have: a balance on the wrong line, a deposit reduced for the wrong reason, a product code mapped to the wrong table. The Federal Reserve's list of legitimate differences between the FR 2900 and the Call Report supplies the other half, the differences a control must not flag.

Three of the rows deserve a word. Republic First is the plainest case of a single classification error with consequences: FDIC officials told the Inspector General that the cost of the bank's liquidation rose in part because it misreported $664 million of secured borrowings as unsecured on its June 2023 Call Report (FDIC OIG, EVAL-25-01). A reconciliation that only compares total borrowings would pass that quarter.

FIL-37-2023 supplies two seeds from one letter. The FDIC found institutions understating estimated uninsured deposits by netting out collateral, and others leaving subsidiaries' intercompany deposits out of Schedule RC-O even though the instructions require deposits eliminated in consolidation to be included there, and it told those institutions to amend, with up to three years of revisions or more if appropriate (FDIC FIL-37-2023). The second is a perfect test of whether an elimination that is right for the balance sheet is wrongly carried into a schedule where it does not belong.

The savings deposit seed comes from a note in the FR Y-9C instructions: "Deposits defined in Regulation D as transaction accounts include demand deposits, NOW accounts, telephone and preauthorized transfer accounts, and savings deposits. However, for Call Report purposes, savings deposits are classified as a type of nontransaction account" (FR Y-9C instructions). One definition, two treatments, and a feed shared by both reports: an easy error to make and a good one to plant.

How do you measure reconciliation accuracy?

Measure reconciliation accuracy with two numbers at minimum, the share of seeds caught and the number of false alarms on the clean twin, and three more if agents explain and trace. A catch is not enough on its own: a rule that breaks for the wrong reason sends the break to the wrong owner, and the planted error survives the fix.

What are false positives in reconciliation, and why do they matter as much?

A false positive in reconciliation is a flag on a difference that is legitimate, and it matters because it spends the same analyst time as a real break and teaches people to stop reading flags. The risk is sharper with language models than with fixed rules. A 2026 benchmark that planted errors in financial statements built from SEC XBRL filings found that most of the model runs it scored flagged nearly every clean statement as wrong (Panda, FinVerBench, arXiv 2605.29586, 2026, a preprint not yet peer reviewed).

Two lessons carry straight into regulatory reporting. First, the clean twin is not optional: the benchmark's headline finding is about clean statements, and a test with only seeded data would have missed it. Second, presentation changes measured recall. The calibrated model caught every planted error on unrounded figures and 79% once the figures were rounded as filings round them. Regulatory reports are filed in thousands of dollars, and a seed smaller than the rounding will vanish; plant seeds at the precision the reports actually carry.

This is also why the sample bank's rules are deterministic and its agents are confined to explaining and tracing with a citation for every claim. A rule decides whether two figures differ; the agents' job is to say why, and every answer can be checked against the passage it cites.

A correct trace in Regulatory Reporting Reconciliation: the accounts, the feed and the mapping row

How do auditors test reconciliations?

Auditors usually test reconciliations by inspecting a sample: was the reconciliation done on time, was it reviewed by someone other than the preparer, are the reconciling items supported, and were old items resolved. The OCC describes operational audits that include "procedures to test the integrity of accounts, regulatory reports, management information systems, or other aspects of bank operations". That tests whether the control operated. A seeded test answers the other question, whether the control as designed would catch the errors that matter, and gives an auditor something to re-perform.

Testing the agents each time they change

A seeded test at go-live is not the end; every change to a rule, a tolerance or an agent is a new control that has not been tested. The sample bank treats its agents like people with versions: each has a versioned prompt, a model and a confidence threshold (0.90 for the explainer agent, 0.85 for the trace agent, 0.95 for the checker agent), and a change is run against the last quarter's 38 breaks before a manager and an administrator approve it. Every human override of an agent's output carries a reason code and is counted each quarter.

The last quarter's breaks are a regression set: they show a change does not lose what the old version caught. Seeds are the other half: they show what nobody has seen yet. A bank that keeps both, and adds each quarter's real breaks to the regression set, ends up with a test library that grows with its own history.

The checker agent in Regulatory Reporting Reconciliation confirms a fix at the source

Seeds are worth writing down in the same form a real break takes; what a break has to carry to reach its owner is that form. Tolerances, which decide which seeds a rule should catch at all, are the subject of reconciling items and how close is close. To see the rules, agents and checks on the sample bank's quarter, start from Regulatory Reporting Reconciliation.

Questions people ask

What is seeded error testing?

Seeded error testing plants known errors in data or a system, runs the control or program being tested, and counts how many of the planted errors it finds. Because the answers are known, it measures detection directly rather than inferring it from a quiet run. For regulatory reconciliation the seeds are misclassifications, missing eliminations and mapping errors planted in a filed quarter.

How many errors should a reconciliation test plant?

No rule sets a number. A practical floor is one seed for each report pair and each kind of difference the rules cover, plus a pair around each tolerance; for a bank tying the general ledger, the Call Report, the FR Y-9C, the FR 2900 and the FR 2052a, that is a few dozen seeds. More matters less than coverage: ten seeds all in deposits test one rule family.

What did Republic First misreport on its Call Report?

According to FDIC officials quoted by the Inspector General, it reported $664 million of secured borrowings as unsecured on Schedule RC-O of its June 30, 2023 Call Report. Separately, in September 2023 it amended its Call Reports for three quarters after its auditors raised a deferred tax asset valuation issue, and the amendments moved it to "Adequately Capitalized" for prompt corrective action purposes.

What is reconciliation in ETL testing?

It is the check after data is loaded from a source to a target that record counts, sums and key totals agree on both sides. It catches dropped or duplicated rows. It does not catch a row loaded intact onto the wrong line, which is the error that matters most between regulatory reports, so it complements a cross-report seeded test rather than replacing it.

Can a seeded test use real customer data?

It should run on a copy of the quarter with customer names and account numbers masked, as the sample bank's traces are. The seeds change balances and mappings, not identities, so masking costs the test nothing, and the copy keeps planted errors well away from any figure that could be filed.