Data reconciliation check in SQL: compare two systems

Compare aggregates, not rows

The cheap version of reconciliation never moves raw data. Pick a measure that both systems should agree on, aggregate it for a closed window, and compare the two numbers. If they match within tolerance, you are done. If not, drill into rows only then.

Good windows are closed ones: yesterday, last hour boundary, last closed month. Today is still changing on both sides at different speeds, so it produces false alarms.

Count and sum with a tolerance

When both tables are reachable from one query, say a source table and a replicated copy in the same Postgres, one statement does the compare. As written it returns a row only when counts or totals differ by more than 0.1 percent.

WITH s AS (SELECT count(*) AS n, sum(amount) AS total
           FROM src_orders WHERE created_at = current_date - 1),
     d AS (SELECT count(*) AS n, sum(amount) AS total
           FROM dst_orders WHERE created_at = current_date - 1)
SELECT s.n     AS src_rows,  d.n     AS dst_rows,
       s.total AS src_total, d.total AS dst_total,
       round(100.0 * abs(s.total - d.total)
             / nullif(s.total, 0), 3) AS diff_pct
FROM s CROSS JOIN d
WHERE abs(s.n - d.n)         > 0.001 * s.n
   OR abs(s.total - d.total) > 0.001 * abs(s.total);

A healthy run returns nothing. With 1,000 source rows and 997 copied, it returns one row with diff_pct of 0.300. As a monitor query, drop the final WHERE and monitor diff_pct with > and 0.1, so the query always returns a row. Once a gap shows up, find the missing keys:

SELECT s.id
FROM src_orders s
LEFT JOIN dst_orders d USING (id)
WHERE d.id IS NULL
ORDER BY 1;

When the systems are separate

Often the two sides live in different databases. Run the aggregate on each as its own saved query, then compare the two results.

arcodash has a Query Results meta-source for exactly this. It exposes the cached result of a saved query as a table named query_<id>, and joins them in an in-memory SQLite database. Two caveats from the docs matter here. The SQL dialect is SQLite’s, not your source’s. And you are reading each query’s last cached result, not live data, so both source queries need to have run recently. Put them on a schedule before relying on the compare.

Assuming query 12 is the source daily totals and query 15 is the destination’s:

SELECT count(*) AS bad_days
FROM query_12 a
LEFT JOIN query_15 b ON b.day = a.day
WHERE b.day IS NULL
   OR abs(a.revenue - b.revenue) > 0.001 * a.revenue;

The LEFT JOIN with b.day IS NULL catches a day that is missing entirely on the destination, which an inner join would hide. The query always returns exactly one row, which matters because arcodash reads one numeric column from the first row and leaves the monitor unknown on an empty result or a NULL. Monitor bad_days with > and 0.

Where reconciliation goes wrong

• Different definitions. One side counts cancelled orders and the other does not. Agree on the filter before blaming the pipeline.
• Time zones. A day boundary in UTC on one side and local time on the other shifts rows across days. Compare with the same cast on both.
• Comparing floats exactly. Sums of decimals can differ in the last digits. Compare with a tolerance, which you already have.
• A tolerance that hides a trend. A steady 0.09 percent loss every day passes a 0.1 percent test. Review the history, not just the alert.

Signups and payments monitoring is where this check usually earns its keep.