Data quality

Data reconciliation: how to sign off a report when two systems disagree

A practical guide to choosing the control total, tracing a difference and deciding when a replacement report is ready to use.

5 min readBy Mayur Kale

Ask someone to bring last month's report and walk through the decision it triggered. In finance the decision might be to release a regional sales pack. In transport it might be to change a dispatch rota. At a factory it might be to investigate a shift with heavy rework. Ask which change in the figure would actually alter that decision. The answer determines whether you need individual lines, daily groups, an exact monetary control or a range with an explicit coverage figure.

Write a reporting agreement that another analyst could apply without speaking to its author. For the retail case, the finance owner wants September posted sales by region in integer GBP pence, net of credit lines, using the most recent supplied revision for each transaction line. A customer order can have several lines. Grouping by order before resolving line revisions would hide which amount changed. A credit has its own line identity and contributes a negative amount.

Keep a dated example with the agreement. Record the business owner, reporting period, cutoff, units, grouping, exclusions, revision rule and the independent control. Ask the owner to show what they would do with a correction received after sign-off. One answer is to restate September; another is to preserve the signed snapshot and publish a separately authorised adjustment. Those decisions require different stored histories. A dashboard filter alone cannot reconstruct information that has already been overwritten.

Follow a record through the system

Here is how a sales line moves from an export to a signed report. Read it from the top down. The two right-hand outputs serve different readers. The report owner needs a summary while the operator needs the rows that explain a difference.

Source file or event stream
  │
  ▼
Bronze · one row per arrival ──────────► Batch manifest
  │                                     file, counts, received time
  ▼
Silver · one row per agreed identity ──► Exceptions
  │                                     reason, owner, resolution
  ▼
Gold · one row per period and group
  ├──► Published measure and refresh time
  └──► Reconciliation against signed controls

An entity relationship diagram answers a different question. In the retail lab the relationship runs from one business line to many arrivals. A region can have many accepted lines, and a region can recur in different months. The reporting key therefore includes both the region and the month.

Business line (line_id)       1 ─── many  Arrival (arrival)
Business line                 1 ─── 1     Current accepted revision
Month + region                1 ─── many  Accepted lines in group

In the sample sales export, arrival sequence also records revision order. That assumption is testable and deliberately narrow. In a real feed an older revision can arrive late. Store the source revision and received time separately. Where two records claim the same revision with different amounts, hold the conflict for an owner. A later ingestion timestamp cannot establish which amount finance approved.

Design the pilot

The three layers describe responsibilities that can be implemented on different platforms. A Fabric design could land arrivals in a Lakehouse, transform them with a notebook and expose approved reporting tables to Power BI. A Databricks design could use Delta tables, governed views and a SQL warehouse. A Snowflake design could use a landing table, revision-selection views and a reporting role. In each design the first acceptance query is the same regional control. Platform choice comes after the expected behaviour is written down.

For a pilot, choose the platform already operated by the team and implement one complete reporting slice. Record the input version, transformation revision, execution identifier, elapsed time, compute configuration and output snapshot. Repeat with a replay and then a late correction. Measure the cost of that agreed workload against the required refresh window. A six-row SQLite result supports a claim about the calculation. Cloud runtime, permissions, recovery and operating cost each require observations on the deployed configuration.

Separate the reader who can see a regional total from the operator who can inspect source records. Test an allowed region and an excluded region using the same reporting identity the dashboard will use. Retain the last successful snapshot on refresh failure and expose its cutoff. A technically successful refresh can still publish yesterday's inputs, so track both pipeline completion and source freshness.

Hand over the report

Before switching reports, run the old and new versions through the same reporting cutoff. Keep a difference register with the period, group, old value, new value, explanation, evidence and approver. In the retail example, the £170 difference comes from one repeated delivery and one superseded price. For transport, an apparent improvement may come from falling coverage. For manufacturing, a higher eventual pass rate can coexist with stagnant first-pass yield. A useful review forces each difference back to the decision agreed with the report owner.

The lab establishes that the published transformations can be examined against small, known cases. It does not estimate the frequency of duplicates in a real company, the accuracy of a public feed or the savings a client might achieve. To evaluate an engagement, collect a baseline across comparable reporting cycles. Track preparation time, unresolved differences at sign-off and elapsed time from cutoff to publication. Document changes in volume and staffing before attributing an improvement to the pipeline.

For handover, ask a second operator to reproduce the control, explain one rejected row, recover a failed refresh and locate the signed definition without the original developer's help. Leave them the source contract, diagrams, workbook, query, exception owner and rollback procedure. The strongest closing demonstration is a new reporting period that the receiving team can publish and explain for itself.

Download the example workbook. The formulas cover the supplied rows. Extend the ranges before adding new records.

Run a meeting with a difference somebody can actually resolve

Start by printing one row of the difference register. Choose a group with a discrepancy large enough to matter and small enough that a colleague can inspect the individual transactions. Write the old amount, the proposed amount and their difference across the top. Underneath, reserve a line for each explanation. A line should carry an amount, a source reference and the name of somebody who can confirm its meaning. When the explanations add up to the difference, ask whether the underlying records also match.

Consider two imaginary systems reporting £1,000 and £980. The smaller figure might omit a £20 sale. It could just as easily omit a £120 sale and include £100 belonging to another period. Searching exclusively for a transaction worth £20 can waste an entire afternoon. Match the populations first. Put records present on only one side into two separate lists. Then compare the amounts and classifications on the records present on both sides. The shape of those lists often tells the owner where to look next.

Be precise about the direction of a difference. If the register describes the movement from the old figure to the proposed figure, use that convention on every line. Ask a second analyst to read one entry aloud. They should be able to say which amount was excluded, which period changed and how the closing total follows. Ambiguous signs become especially troublesome when a credit is missing from one system. Restoring a missed credit can reduce a figure even though the corrected system now contains more records.

Make a decision when the figures agree by accident

Take a small example in which the same £50 is assigned to different branches in the two systems. The company totals agree exactly. A regional director reviewing performance still sees a material difference. For the pilot, ask which decisions rely on which breakdowns and compare at those levels. Good candidates include the branches responsible for action, the product categories used for purchasing and the periods used for close. Testing every imaginable combination can produce an unwieldy exception list that no one owns.

Now reverse the example. Some records can match individually while the reporting summaries disagree because one report rounds each line and the other rounds the final total. Work through a tiny invoice with fractional amounts. Ask finance to identify the agreed rounding point and currency precision. Put the worked example in the acceptance notes. If a tolerance is necessary, record who approved it, how large it is and whether it applies to a line, a group or an entire period. Otherwise a tolerance intended for a single total can be applied independently to thousands of rows.

Agree a stopping rule before the meeting. An unexplained difference affecting a decision due this afternoon deserves different treatment from a known historical presentation difference that has no effect on the next close. Assign an owner and a date to anything carried forward. If the owner authorises publication with an exception, include the affected scope in the report seen by its readers. A private note in the engineering backlog cannot inform the manager who is about to act on the number.

Turn the trend into shipped software

I contract as a senior fullstack and data engineer, building the production systems your customers and your reporting depend on.

Explore services↗