Data reconciliation: why your systems disagree and how to fix it

The finance team completes the monthly close and finds that the revenue figure in the billing system differs from the amount in the general ledger; no funds were moved. A few records became inconsistent between the two systems due to a double-posted refund, a customer merged into an incorrect account, or a delayed batch job. When these discrepancies occur across all reporting systems, the data remains available but is no longer considered reliable.

Data reconciliation is the process of comparing data across two or more systems, or between a system and its source. This process confirms that figures match and allows for the investigation of any discrepancies. It serves as a check to prevent errors in extraction, timing, or transformation from causing inaccuracies in reports.

Reasons why two systems seldom match

Systems that share data can become inconsistent with each other, and this usually happens gradually. A customer relationship management (CRM) system and a billing platform might both store revenue information, but they may define revenue differently. For example, one system could record a deal when it is agreed upon, while the other only records it after payment is received. If a data update runs at midnight in one system and 2am in another, a report generated at 9am will be comparing data from different times. These inconsistencies are not errors. They occur because the same information is recorded, changed, and stored in multiple systems.

Duplicate records, a customer appearing under two account IDs, a currency conversion applied twice, or a rounding rule that differs between a source system and a data warehouse can cause small discrepancies to accumulate into significant numerical disagreements. Schema changes introduce additional problems: if a source field is renamed or resized, and a pipeline continues to map it using the old definition, the resulting totals will diverge without any changes to the core business processes. The longer these mismatches remain undetected, the more decisions will be based on inaccurate data.

The steps for the reconciliation process

To begin a reconciliation exercise, you must first decide which system serves as the primary source of truth for each specific figure.

If you do not have that anchor, every disagreement becomes a debate. You must then extract data from each system and align it to the same grain, currency, and time window to ensure the comparison is accurate.

The comparison itself matches records or totals between the sources, usually by a common key such as an order ID, account number, or transaction reference, and flags anything that doesn't tie out. Most of the effort, and most of the value, sits in what happens next: investigating each exception to find out whether it's a timing difference, a genuine error, or a gap in the mapping between systems.

The final step is to resolve the discrepancy and document its cause. If you adjust the figures without recording the reason for the error, you will likely have to perform the same investigation during the next period.

Financial, ETL and clinical reconciliation are the same idea in different clothes

The underlying processes remain the same, though the consequences and terminology vary depending on the field.

Financial reconciliation verifies that figures in ledgers, bank statements, and billing systems match. This process is a requirement for audits and regulations rather than a voluntary task. Errors in reconciliation lead to compliance risks and the need to restate accounts. These consequences are more serious than poorly designed reporting tools.

In ETL, reconciliation is the process of verifying that record counts, sums, or checksums from the source match the data in the target after extraction, transformation, and loading. This practice ensures that pipelines do not drop rows or double-count joins. It allows errors to be caught before downstream users rely on the output. In clinical data management, reconciliation involves comparing data across different systems. This includes matching a clinical database with lab results or safety reports. This step confirms that a patient's records are consistent across all sources before the data is locked for analysis.

This question applies to many industries: do these two records of the same item match?

Automating reconciliation without losing the judgement calls

Reconciling accounts manually in spreadsheets is suitable when you have only a few accounts to manage.

The process fails when data volume or the number of systems increases. This happens because staff must manually open every source, copy totals, and repeat these steps each period. To begin automating this, you should create SQL queries that compare record counts, sums, and key fields between the source and target systems on a schedule. These queries should flag any data that falls outside a specific tolerance instead of requiring an exact match for every value.

Specialised data reconciliation software and tools provide more advanced features for this process.

These systems manage matching logic, tolerance rules, and exception queues automatically. This ensures that staff only review items requiring a specific decision rather than rows that match predictably. People remain responsible for judgement calls, such as determining the difference between a genuine discrepancy and acceptable rounding. Automation is intended to identify exceptions more quickly, but it does not replace the requirement to understand those exceptions.

Map the process flow before you begin automation

You should document the reconciliation process flow before you purchase any tools.

Most failures occur because steps are skipped, rather than because the wrong software was chosen. A standard process involves extracting data from each source and transforming both datasets into a shared structure. You then match the data using an agreed key and categorise every result as matched, missing, or mismatched. Finally, you route the mismatched items to the data owners for investigation.

Mapping the flow identifies problems early. For example, you might find that a source refreshing weekly is being compared to one that updates in real time. You might also find steps where there is no agreement on how to handle rounding differences. It is less expensive to fix these issues on a whiteboard than to discover them three months into a live reconciliation process that produces unexplained exceptions.

Where reconciliation sits in a data pipeline

Reconciliation works best when it is built into the pipeline rather than being added after a report error occurs.

Ingestion checks detect when a source system delivers fewer rows than expected. Checks run after each transformation reveal joins or aggregations that mistakenly change totals. Checks executed before data reaches a dashboard or finance system uncover upstream errors missed by earlier steps.

This layered approach makes sure errors are found close to their source instead of later when reports have already reached clients. Reconciliation shifts from a monthly emergency job to a regular component of the data pipeline. It executes automatically whenever data moves, rather than depending on a team to do it under pressure. In architectures with bronze, silver, and gold stages, this usually means a simple count and checksum check between bronze and silver. A deeper reconciliation of business totals takes place between silver and gold. This guarantees that the rigor of the check matches the importance of the data at each stage.

Who is responsible for reconciliation

In many organisations, a designated analyst handles reconciliation. That analyst compares data across two or more systems, investigates exceptions, and logs how discrepancies were resolved. The role is important, yet it works better when an automated pipeline carries out the mechanical comparisons. This lets the analyst concentrate on resolving exceptions that need human judgement.

If no one is assigned responsibility for reconciliation, the process typically happens only after a failure. That moment is the most expensive time to discover a discrepancy. You can cut investigation time by assigning an owner to each reconciliation check and defining the failure procedure.

Including reconciliation in the initial design

Reconciling data is cheaper when the process is built into a pipeline from the beginning. Adding these functions later, after systems have diverged for several years, costs more. To achieve this, you must agree on sources of truth before constructing the pipeline. You should also insert checks at each transformation stage. Set tolerances according to business requirements rather than demanding perfect matches in every case.

We embed reconciliation as a standard part of our data pipeline architecture. We do this because reports rely on the verification processes that back them. If your teams manually fix data mismatches each month, your pipeline needs a trustworthy data foundation instead of extra spreadsheets.

Our data consultants build these systems for UK organisations. We set up the foundation first to guarantee reliable results.

Reconciliation is the step most often omitted from supplier quotes, as explained in our guide to budgeting a data migration.

Reconciliation is one stage of a wider plan, set out in our guide to running a data migration from assessment to hypercare.

Frequently asked questions

If you want to identify where AI can assist your business, Talk to us. We provide a clear assessment instead of a vendor demonstration.