Reconcile a dashboard number with the source table in SQL

“The dashboard says 349 and finance says 312.” Nobody knows which is right, and the argument usually lasts longer than the fix. A reconciliation check ends it: compute the number again from the source, line it up day by day against what the dashboard shows, and look only at the rows that disagree.

This post builds that check in plain Postgres SQL.

The idea

The dashboard reads from some derived table, a rollup, a cached extract or a materialized view. The source is the table the events actually landed in. If both are right they agree for every complete day, so the check returns nothing. Any row it returns is a day to investigate.

The query

WITH src AS (
  SELECT created_at::date AS day, sum(amount) AS src_total
  FROM orders
  WHERE status = 'paid' AND created_at >= current_date - 7 AND created_at < current_date
  GROUP BY 1
), shown AS (
  SELECT day, revenue AS shown_total FROM dash_daily
  WHERE day >= current_date - 7 AND day < current_date
)
SELECT coalesce(s.day, d.day) AS day, s.src_total, d.shown_total,
       coalesce(d.shown_total, 0) - coalesce(s.src_total, 0) AS diff
FROM src s FULL JOIN shown d ON d.day = s.day
WHERE abs(coalesce(d.shown_total, 0) - coalesce(s.src_total, 0)) > greatest(0.5, 0.001 * coalesce(s.src_total, 0))
ORDER BY 1;

If one day in the rollup was inflated by 37, you get one row: that day, 312 in the source, 349 on the dashboard, a diff of 37.

Why it is written this way

• FULL JOIN, not INNER. An inner join drops the day that exists on only one side, which is exactly the day a load job skipped or a rollup invented. The coalesce turns the missing side into zero so the diff still shows.
• Complete days only. created_at < current_date leaves out today, because a dashboard refreshed at 09:00 will legitimately disagree with a source that kept growing. Compare finished days.
• The dashboard’s filters, not yours. If the dashboard counts only paid orders, the source query must too. Most “mismatches” are a different definition of the metric, not bad data. Write the definition down once and use it on both sides.
• A tolerance with two parts. greatest(0.5, 0.001 * total) allows half a currency unit or 0.1%, whichever is larger. Rounding in a rollup should not page anyone.

Where the gap usually comes from

When the check returns a day, these are the usual suspects, roughly in order of how often they turn out to be it:

1. Timezone. The rollup groups by UTC date and the source query by local date, so late evening orders land on different days. The diffs on neighbouring days cancel out.
2. Late or updated rows. A refund or status change after the rollup ran. The source moved, the rollup did not.
3. A double load. The diff is a clean multiple of a known batch.
4. A filter drifted. Someone changed the dashboard’s definition and not the source query, or the other way round.

The sign of the diff and whether neighbouring days offset each other tell you which one before you open a single job log.

Make it a scheduled check

Run it daily after the rollup finishes. Anything that runs SQL and notifies on a result will do: cron with psql, or your orchestrator. In arcodash, wrap the final select as SELECT count(*) AS bad_days FROM (...) t and alert when bad_days > 0. The alert then says which dashboard stopped agreeing with its source, not just that something looks odd.

Limits

If the source query itself joins to a many-side table, check it for join fan-out first. This catches a rollup that disagrees with its source. It does not catch a source that is itself wrong, such as a column filled with nulls (see the null rate check) or a table that stopped loading (see the row count anomaly check). The three together cover most “the number looks off” incidents.

For a check in your own dialect, the SQL data check generator writes one for any table, and the reconciliation check glossary entry has the short definition.