Reconciliation: only say "it works" once your numbers match the client's books

Acceptance meetings tend to fall apart in a familiar way. You put the dashboard on screen: the agent has read every October invoice, the pipeline has thrown no errors, and total revenue is displayed. The client’s chief accountant opens the ledger, is silent for a moment, then asks why the figure is off.

From that point the project is no longer about how good the model is. The only question is whether you can explain the variance. Answer on the spot and the client will trust you. Say “let me check and get back to you”, and the whole system falls under suspicion.

Reconciliation is the skill that keeps you out of that situation. Accountants have practised it for a long time: account reconciliation means comparing internal records with external statements or supporting documents. This guide shows how to bring that discipline into an AI project, so that “it works” becomes a claim you can prove.

What is reconciliation, in plain terms?

The documentation for Soda, a data quality tool, defines a reconciliation check as a test of whether a target dataset matches one or more source datasets. It works at two levels. At aggregate level you compare metrics such as row counts or totals. At row level you compare records one by one.

For an FDE, the “target” is what your system produces: an invoice table extracted by an agent, an inventory forecast, or data migrated to a new warehouse. The “source” is what the client treats as the truth, usually the general ledger, the ERP system or an Excel file the finance team has closed.

One small concept decides everything: the threshold. Soda requires the acceptable variance to be defined as a threshold, and its default is 0. That default is very much in the spirit of accounting: if no threshold has been written down, no variance counts as “acceptable”.

In what order should you check?

Comparing every row across the full dataset is both expensive and slow. Soda recommends a cheaper order: run aggregate checks first (row counts, totals, averages), then compare rows selectively on the most important datasets.

Counting rows alone is not enough. A migration testing guide from Xylity proposes computing SUM, MIN, MAX and AVG for every numeric column. Two tables can both have 4,812 rows while one has been truncated or rounded. Row counts cannot see these errors; totals and extremes expose them immediately.

The same guide calls time-zone mismatch the most common error with date data. So after the aggregate comparison, the next step should be to break the variance down by day. If the gap is bunched on the first or last day of the period, the time zone is usually the first suspect.

A worked example

The following example is hypothetical, including the figures. A distributor in UTC+7 asks you to build an agent that reads invoices and writes them to the ai_invoices table. The agent stores the invoice timestamp in the invoice_ts column in UTC, while the finance team closes its books in the ledger_invoices table with an invoice_date column in local time.

Before comparing anything, you and the chief accountant agree in writing: the row count variance must be 0, and the total may differ by at most 0.01 per invoice because of rounding rules.

The first run uses an aggregate query, filtering October on the column each side actually stores:

SELECT 'ai' AS src, COUNT(*) AS n, SUM(amount) AS total,
       MIN(amount) AS mn, MAX(amount) AS mx, AVG(amount) AS avg_amt
FROM ai_invoices WHERE invoice_ts >= '2026-10-01' AND invoice_ts < '2026-11-01'
UNION ALL
SELECT 'ledger', COUNT(*), SUM(amount), MIN(amount), MAX(amount), AVG(amount)
FROM ledger_invoices WHERE invoice_date >= '2026-10-01' AND invoice_date < '2026-11-01';

Suppose both sides have 4,812 rows, but the agent’s total is 18,640.25 higher than the ledger. Matching row counts reassure many people too early, but here they only mean that errors are cancelling each other out. You move on to a daily breakdown, deriving the agent’s date by casting invoice_ts to DATE:

SELECT d, SUM(ai_amt) - SUM(ledger_amt) AS diff FROM (
  SELECT CAST(invoice_ts AS DATE) AS d, amount AS ai_amt, 0 AS ledger_amt FROM ai_invoices
  UNION ALL
  SELECT invoice_date, 0, amount FROM ledger_invoices
) t GROUP BY d HAVING SUM(ai_amt) - SUM(ledger_amt) <> 0 ORDER BY d;

This query deliberately does not filter by period. Keep the October condition and you will not see invoices that have been pushed into the neighbouring month.

The variance is concentrated on 30 September, 1 October, 31 October and 1 November. That is the signature of a time-zone problem. Because invoice_ts is in UTC, an invoice issued at 6am local time on 1 November becomes 11pm on 31 October and is counted in October.

The fix is to convert invoice_ts to the client’s closing time zone before deriving the date and filtering by period, then run again. In this example, the total variance drops from 18,640.25 to 0.37.

The remaining 0.37 is when row-by-row comparison is warranted, and only on a narrow set: invoices whose variance exceeds the 0.01 threshold.

You find a handful of invoices where the agent rounds tax per line item, while the accountants round on the total. This rounding error only surfaces after the time-zone fix, because until then it was buried inside the larger variance.

Doing it yourself: from threshold to sign-off

Before writing any SQL, ask the client what the source of truth is, which time zone the closing period uses, and what the variance threshold is for each metric. Write it all on one page and send it to them for confirmation. If no threshold has been agreed, treat it as 0.

Next, build layers of automated checks. Xylity’s model has five layers: the first three are automated and re-run after every iteration; the last two are confirmed by people before switching to the new system (cutover).

For an AI project, the three automated layers might be row counts, aggregates for each numeric column, and daily variance. Run them whenever you change a prompt, change the model or modify the pipeline.

Then comes the evidence pack. Solvexia’s account reconciliation guide calls for recording the explanation, the calculation and the evidence for every account reviewed. In the example above, the pack contains the queries, the results before and after the time-zone fix, and the list of invoices off because of rounding, with how each was handled.

The final step is sign-off. Also according to Solvexia, reconciliation results must be reviewed by an independent party before they are finalised. That reviewer is not you, because you built the system. Set a clear go/no-go gate, as in Xylity’s model: cut over only when every layer has passed.

Mistakes that cost you credibility

The first is leaving reconciliation until the end of the project. A post on the Databricks engineering blog observes that teams who treat validation as an afterthought end up spending more time on reconciliation than on the migration itself. When the three automated layers run from the first week, each error is caught as soon as it appears.

The second is stopping once row counts match. As the example shows, row counts can match while two different errors offset each other. The third is choosing the threshold yourself, for instance quietly assuming that variance under 0.1% is “acceptable”. That number is for the client to decide, not you.

The fourth is certifying your own system. If you both wrote the pipeline and signed off on its results, you have skipped the very principle the client’s finance team applies every month.

How should this skill show up when you apply for jobs?

When reading job descriptions, look for phrases such as “data migration”, “financial data” or “production validation”. Those teams need people who can reconcile. On your CV, instead of writing “ensured data quality”, state specifically which source you reconciled results against, to what threshold, and who signed off.

In interviews, have an answer ready for a question like: “Tell me about a time your system’s numbers didn’t match the source. How did you find the cause?” A good story has four beats: how large the variance was and who spotted it; the order in which you checked; what the real cause was; and who signed off on the final result.

A story like that is usually more persuasive than any model accuracy figure. The reason is that the client will not judge your system on accuracy. They will judge it on whether your numbers match their books.