DEV Community: Refael Dakar

Reconcile a dashboard number with the source table in SQL

Refael Dakar — Fri, 06 Nov 2026 08:29:49 +0000

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

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

Timezone. The rollup groups by UTC date, the source query by local date. The diffs on neighbouring days cancel out.
Late or updated rows. A refund or status change after the rollup ran.
A double load. The diff is a clean multiple of a known batch.
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/

Null rate data quality check in SQL, per column

Refael Dakar — Tue, 03 Nov 2026 13:45:24 +0000

A dashboard goes wrong quietly in two ways. The rows stop arriving, which a freshness or row count check catches. Or the rows keep arriving and one column fills with nulls. Nothing errors, the load job is green, and revenue by coupon quietly drops to zero.

A null rate check is the cheap answer to the second one. Plain Postgres SQL below; the full write-up (every column at once, per-role tolerances, backtesting) is on my blog.

Null rate for one column

SELECT count(*) AS total,
       round(100.0 * count(*) FILTER (WHERE email IS NULL)
             / NULLIF(count(*), 0), 2) AS email_null_pct
FROM orders
WHERE created_at >= now() - interval '28 days';

NULLIF(count(*), 0) turns an empty window into a null instead of a division error, so the check doesn't crash on exactly the day the table is empty. Always put a time window on it: a whole-table rate is dominated by old rows, so a column that broke yesterday barely moves it.

Alert on the jump, not the level

Some columns are legitimately sparse, so "nonzero" is the wrong trigger. Compare yesterday to the previous four weeks:

SELECT
  round(100.0 * count(*) FILTER (WHERE created_at >= current_date - 1
                                   AND coupon_code IS NULL)
    / NULLIF(count(*) FILTER (WHERE created_at >= current_date - 1), 0), 1) AS recent_pct,
  round(100.0 * count(*) FILTER (WHERE created_at < current_date - 1
                                   AND coupon_code IS NULL)
    / NULLIF(count(*) FILTER (WHERE created_at < current_date - 1), 0), 1) AS baseline_pct
FROM orders
WHERE created_at >= current_date - 29 AND created_at < current_date;

Yesterday is a full day, so you never compare a half-finished morning to complete history. To make it silent when healthy, return a row only when recent / n_recent > 2 * base / n_base + 0.02 (more than double the baseline plus two points, so 0.1% to 0.3% doesn't page anyone).

False alarms to expect

A new column: history is all null by definition. Start the check after the backfill.
Small volume: on 40 rows a day one row moves the rate 2.5 points. Add a minimum row count.
Placeholders: '', 0 and 'N/A' are not nulls. If a pipeline swaps nulls for placeholders, count those too.

Backtest on the last 90 days before you schedule it, and pick the multiplier that stays quiet on days you know were fine. A check people learn to ignore is worse than none.

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 these 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=nullrate-crosspost-20261103

Full version with the all-columns query: https://www.arcodash.com/blog/null-rate-check-sql/

Your dashboard is green and the number is wrong: the SQL checks I schedule next to every metric

Refael Dakar — Mon, 28 Sep 2026 13:55:46 +0000

Most broken dashboards don't look broken. The chart renders, the numbers are plausible, the pipeline says success. Then someone in finance asks why last month's revenue changed after the fact, and you find out a join has been double-counting refunds since a schema change three weeks ago.

Pipeline monitoring doesn't catch this. The job ran fine. It just produced the wrong number. What does catch it is a handful of boring SQL queries that run on a schedule and complain when the data stops looking like itself.

Here's the set I start with. The examples are Postgres, but they translate to any warehouse.

One rule: a check returns rows only when something's wrong

Every check below is written so that zero rows means healthy. That makes the alerting side trivial (rows > 0, notify someone), and the query result doubles as the alert body. You get the offending day or key, not just "check failed."

1. Freshness

The most common silent failure is nothing arriving at all. The dashboard keeps showing yesterday's number forever and nobody notices.

SELECT 'orders' AS table_name, max(created_at) AS latest
FROM orders
HAVING coalesce(max(created_at), '-infinity') < now() - interval '3 hours';

Pick the interval from how the table actually loads, not how you wish it loaded. If it's a nightly batch, a 3 hour window will page you every morning and you'll have muted it by Friday.

2. Volume against the same weekday

Row count checks with a fixed floor ("alert under 1,000 orders") break on every weekend and holiday. Compare against the same weekday over the last few weeks instead.

WITH daily AS (
  SELECT created_at::date AS d, count(*) AS n
  FROM orders
  WHERE created_at >= current_date - 29
  GROUP BY 1
),
baseline AS (
  SELECT avg(n) AS avg_n
  FROM daily
  WHERE d IN (current_date - 8, current_date - 15, current_date - 22, current_date - 29)
)
SELECT current_date - 1 AS d, coalesce(y.n, 0) AS n, round(b.avg_n) AS typical
FROM baseline b
LEFT JOIN daily y ON y.d = current_date - 1
WHERE coalesce(y.n, 0) < b.avg_n * 0.6
   OR coalesce(y.n, 0) > b.avg_n * 1.6;

It checks yesterday, not today, on purpose. A half-loaded day always looks like a 50% drop. And note the LEFT JOIN: a day with zero orders has no row in daily at all, so the obvious inner-join version stays quiet on exactly the day you need it. Same reason the freshness check wraps max() in coalesce, since an empty table's max is NULL and NULL never compares true.

3. Grain

This is the one behind the double-counted revenue. A model that's supposed to be one row per order quietly becomes one row per order line after someone adds a join.

SELECT count(*) AS row_count, count(DISTINCT order_id) AS order_count
FROM fct_orders
HAVING count(*) <> count(DISTINCT order_id);

It's cheap, it basically never false-alarms, and it catches fan-out before anyone sums anything.

4. The unknown bucket

Mapping tables drift. A new plan, country or campaign shows up upstream, matches nothing, and lands in NULL or "other." Totals stay right while every breakdown slowly goes wrong.

SELECT round(100.0 * count(*) FILTER (WHERE plan_name IS NULL) / count(*), 1) AS pct_unmapped
FROM fct_subscriptions
WHERE created_at >= current_date - 7
HAVING count(*) FILTER (WHERE plan_name IS NULL) > 0.02 * count(*);

5. Reconcile against a second source

The strongest check compares the number people look at against the same thing computed a different way. Orders against payments, signups in the app DB against the CRM, warehouse revenue against the billing provider.

WITH o AS (
  SELECT created_at::date AS d, sum(amount) AS order_total
  FROM fct_orders
  WHERE created_at >= current_date - 7 AND created_at < current_date
  GROUP BY 1
),
p AS (
  SELECT paid_at::date AS d, sum(amount) AS paid_total
  FROM payments
  WHERE paid_at >= current_date - 7 AND paid_at < current_date
    AND status = 'succeeded'
  GROUP BY 1
)
SELECT d, order_total, paid_total
FROM o FULL JOIN p USING (d)
WHERE abs(coalesce(order_total, 0) - coalesce(paid_total, 0))
      > 0.01 * greatest(coalesce(order_total, 0), coalesce(paid_total, 0));

These never match exactly. Timezones, refunds and payments that land a day late all get in the way. Start the tolerance loose, watch what it flags for a week, then tighten it. A check that's always red is worse than no check, because people learn to ignore the channel.

Running them

You don't need a platform to start. A cron entry and psql go a surprisingly long way:

#!/usr/bin/env bash
# usage: run-check.sh path/to/check.sql
out=$(psql "$DATABASE_URL" -X -A -t -F ' | ' -f "$1")
if [ -n "$out" ]; then
  curl -s -X POST -H 'Content-Type: application/json' \
    --data "$(jq -n --arg t "Check failed: $1
$out" '{text: $t}')" \
    "$SLACK_WEBHOOK_URL"
fi

0 * * * *  /opt/checks/run-check.sh /opt/checks/orders_freshness.sql
15 7 * * * /opt/checks/run-check.sh /opt/checks/orders_volume.sql

Once this is doing real work, add something that notices when cron itself stops. Have the script write a heartbeat row and check its age from somewhere else. A scheduler that silently stopped running is the same problem one level up.

Where the cron version gets awkward

It holds up until other people want to add checks, you need different routes (the orders check should page someone, the unmapped-plan one can be an email), and the people reading the dashboard have no idea which numbers are actually covered by a check and which ones are just vibes.

That's the gap I've been building arcodash for. Disclosure: it's my product. You connect the database, write queries like the ones above, put them on dashboards, and schedule checks that alert by email, Slack, webhook or PagerDuty when a result crosses a threshold (a row count above zero works fine). It's SQL-first on purpose, so everything in this post pastes straight in. There's a 14-day trial, no card: arcodash.com

Cron job or tool, start with grain and freshness on your single most-looked-at metric. Those two catch most of the embarrassing ones.