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

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.