AI-Assisted Reconciliation of Enterprise Data: Detecting and Explaining Cross-Platform Discrepancies

Signup Write New Story AI-Assisted Reconciliation of Enterprise Data: Detecting and Explaining Cross-Platform Discrepancies by ajaysikharam by ajaysikharam | @ ajaysikharam ITS Manager - GDS at Ball Corporation Data Specialist|AI Enthusiast|Advocate data democratization. Build trusted data foundations that transform data into actionable business insights.

Data Specialist|AI Enthusiast|Advocate data democratization. Build trusted data foundations that transform data into actionable business insights.

Last quarter's revenue was $14.6 million. Or $14.2 million. Or $14.1 million. The ledger says one thing. The Lakehouse table feeding your BI says another. The dashboard the board looked at this morning says a third.

None of the three numbers is obviously wrong, and that is the problem. When the same metric lives in several systems, each with its own refresh schedule, transformations and quirks, the numbers drift apart. Reconciliation is the work of finding where they disagree and deciding which one, if any, you trust. Done by hand, it is slow, it misses things, and it scales badly as you add systems.

I split the job in two. Deterministic checks establish the facts: row counts, sums, join-key coverage. AI handles triage and explanation. It learns which differences are normal, ranks the ones that are not, and drafts a plain-language account of what probably happened. The checks give you the truth. The AI makes it something a person can act on.

Before any model gets involved, you need checks that are plain arithmetic. For every metric you reconcile, run the same small battery of comparisons:

Run them on a schedule. The output is a table of differences with no interpretation. Nothing here needs AI, and that is deliberate. When an engineer asks why a dashboard changed overnight, the first answer should come from a check that can be re-run and gives the same result every time.

The trouble with deterministic checks is volume, not accuracy. On a real pipeline they fire constantly, and most of what they flag is expected:

A human reviewer has to learn all of this, and the knowledge lives in people's heads. Two reviewers disagree on what is worth chasing. Context switching eats hours, and the checks keep firing either way.

This is where AI earns its place. Feed it the history of past reconciliations, meaning every flagged difference and how it was resolved. Two things happen:

Differences that match a known pattern get labeled and logged instead of escalated. The rest are ranked by dollar impact, how new the pattern is, and whether it is getting worse.

Reviewers notice the next part most. Instead of a row reading TABLE_A.SALES_TOTAL!= TABLE_B.SALES_TOTAL, they get a sentence saying what probably happened and why. Nobody has to decode column names before the coffee kicks in.

Go back to the three figures at the top: ledger $14.6M, lakehouse $14.2M, dashboard $14.1M. The deterministic check compares order counts and revenue per fulfillment center between the source system and the lakehouse table:

SELECT COALESCE(s.center, l.center) AS center, COALESCE(s.orders, 0) AS source_orders, COALESCE(l.orders, 0) AS lakehouse_orders, COALESCE(s.orders, 0) - COALESCE(l.orders, 0) AS order_gap, COALESCE(s.revenue, 0) - COALESCE(l.revenue, 0) AS revenue_gap FROM (SELECT center, COUNT(*) AS orders, SUM(amount) AS revenue FROM source.orders WHERE order_date >= '2026-07-01' AND order_date < '2026-10-01' GROUP BY center) s FULL OUTER JOIN (SELECT center, COUNT(*) AS orders, SUM(amount) AS revenue FROM lakehouse.orders WHERE order_date >= '2026-07-01' AND order_date < '2026-10-01' GROUP BY center) l ON s.center = l.center WHERE COALESCE(s.orders, 0) <> COALESCE(l.orders, 0) OR COALESCE(s.revenue, 0) <> COALESCE(l.revenue, 0);

It returns one row and says nothing about why:

The AI layer takes that row and the resolution log. The log shows OH-01 had the same gap on two earlier closes, both traced to the 2 a.m. extract.

OH-01 is missing about 12,000 orders (~$412K) in the lakehouse. The source extract ran at 2 a.m., before the late-day batch landed. This matches two earlier closes. Likely cause: extract timing, not data loss. Confidence: medium. Attached: row counts, extract timestamps, and the two prior resolutions.

A reviewer re-runs the extract, the gap closes, and the log records the cause as late_arriving_rows. That explains the $0.4M gap between the ledger and the lakehouse. The dashboard's remaining $0.1M is cache lag, a known pattern, so the model labels it and logs it without escalating.

Month-end reconciliation is a fire drill because differences are found late and investigated from scratch. With this setup, the checks run on every load, the noise is filtered and real breaks arrive with a draft explanation. Finance spends less time waiting on the close and more time on the breaks that matter.

Pick one metric this week. Write the deterministic checks and start the resolution log. The AI layer can wait until the log has a few months of history. Truth first, explanation second.