DEV Community

Refael Dakar
Refael Dakar

Posted on Originally published at arcodash.com

Reconcile a dashboard number with the source table in SQL

#sql #postgres #dataengineering #analytics

"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.

Plain Postgres SQL below. The full write-up is on my blog.

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;
Enter fullscreen mode Exit fullscreen mode

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. If everything agrees, it returns nothing.

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.
  • 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.
  • The dashboard's filters, not yours. 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. Half a currency unit or 0.1%, whichever is larger, so rounding doesn't page anyone.

Where the gap usually comes from

  1. Timezone. The rollup groups by UTC date, the source query by local date. The diffs on neighbouring days cancel out.
  2. Late or updated rows. A refund or status change after the rollup ran.
  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.

Disclosure: I build arcodash, a BI tool that can run checks like this on a schedule. The SQL works anywhere. There's also a free, no-signup generator that writes checks for your table and dialect: https://www.arcodash.com/tools/sql-data-checks/?utm_source=devto&utm_medium=crosspost&utm_campaign=growth-v2&utm_content=reconcile-crosspost-20261110

Full version: https://www.arcodash.com/blog/reconcile-dashboard-number-sql/

Top comments (0)