Dashboard shows wrong numbers? 5 SQL checks to schedule

Most dashboards that show wrong numbers don’t look broken. The chart renders and the pipeline says success. Say someone in finance asks why last month’s revenue changed after the fact, and you find 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 on a schedule that complain when the data stops looking like itself.

Why a dashboard shows wrong numbers

Before writing checks, it helps to know what you’re hunting:

• Nothing arrived. The load stopped, so the dashboard keeps showing yesterday’s number.
• Some rows went missing. A partial load or a filter change cut the volume in half.
• The grain changed. A model meant to be one row per order became one row per order line after someone added a join, and every sum inflates.
• New values landed in the unknown bucket. A new plan or country matched no mapping row. Totals still add up while every breakdown drifts.
• Two sources disagree. The dashboard’s revenue no longer matches what the billing system says.

Each of these has a query that returns something only when it happens.

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

Write every check so zero rows means healthy. Alerting becomes trivial (rows > 0, tell someone) and the result is the alert body. The message names the offending day or key.

1. Freshness

The simplest silent failure is nothing arriving at all.

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

The coalesce matters: an empty table’s max() is NULL, and NULL never compares true, so without it the check stays quiet on the worst case. Pick the interval from how the table really loads. A nightly batch with a 3 hour window pages you every morning until you mute it. The full treatment, including many tables at once and timezone traps, is in Postgres data freshness check.

2. Volume against the same weekday

A fixed floor (“alert under 1,000 orders”) breaks on every weekend. Compare yesterday to the same weekday over the last four weeks:

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 looks at yesterday because a half-loaded today always reads as a drop. The LEFT JOIN is deliberate: a day with zero orders has no row in daily, so an inner join would go silent on exactly the day you need it. Tolerances and holidays are in Row count anomaly detection in SQL.

3. Grain

This is the check behind the double-counted revenue. Declare what one row means, then test it:

SELECT order_id, count(*) AS rows
FROM fct_orders
GROUP BY order_id
HAVING count(*) > 1
ORDER BY rows DESC, order_id
LIMIT 20;

If three orders appear twice, it returns exactly those three keys with rows = 2; on a clean table it returns nothing. Because it lists the duplicated keys, the alert tells you which orders to look at, and the pattern usually points straight at the join that fanned out. It’s cheap and it fires before anyone sums anything.

Duplicate keys and null rates also come up in why a green dashboard can hide stale data. Put this check on every model that’s supposed to have a unique key. Why joins fan out and how to fix the model is its own topic; this check just makes sure you hear about it the same day.

4. The unknown bucket

Mapping tables drift. A new plan name shows up upstream, matches nothing in the lookup, and becomes NULL or “other.” Overall totals stay correct while every breakdown quietly goes wrong, and nobody looks at the small slice labelled “other.”

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

Say 10 of 100 rows have a NULL plan: the check returns 10.0 | 10. With 1 in 100 it stays quiet. Set the threshold from what’s normal for the column: 2% is a starting guess, and a column that should never be NULL gets > 0. If your pipeline writes a literal 'other' or 'unknown' instead of NULL, test for that string instead. An empty window returns nothing too, which is the freshness check’s job to flag.

5. Reconcile against a second source

The strongest check compares the number people look at against the same thing computed another way: orders against payments, or 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));

The FULL JOIN catches a day that exists on only one side. These never match exactly: timezones and payments that land a day late both 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 long way:

#!/usr/bin/env bash
# usage: run-check.sh path/to/check.sql
out=$(psql "$DATABASE_URL" -X -A -t -F ' | ' -v ON_ERROR_STOP=1 -f "$1" 2>&1)
status=$?
if [ "$status" -ne 0 ] || [ -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

ON_ERROR_STOP matters because psql exits 0 on a SQL error otherwise, so a broken check would stay quiet; this way the error text is posted too. Once it does 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 died is the same problem one level up. If Slack is where alerts should land, SQL alert to Slack covers the wiring and how to track state so a failing check doesn’t repost every run.

Where cron gets awkward

It holds up until different checks need different routes (orders pages someone, the unmapped-plan one is an email), and the readers of the dashboard can’t tell which numbers are covered by a check and which are vibes.

That’s the gap I’m building arcodash for: you write queries like these, schedule them as monitors, and they alert by email, Slack, webhook or PagerDuty when a column of the result crosses a threshold. A monitor reads one column of the first row, and a zero-row result leaves it in unknown, so wrap each check as a single count and alert on dupes > 0 (keep the unwrapped query saved alongside to see which keys):

SELECT count(*) AS dupes
FROM (
  SELECT order_id
  FROM fct_orders
  GROUP BY order_id
  HAVING count(*) > 1
) x;

How monitors are set up is in the data quality monitors docs. Start free: 14 days, no card.

Whatever you run them with, put grain and freshness on your single most-looked-at metric first. Those two catch most of the embarrassing ones.