Lightweight data observability for small teams: five SQL checks

Data observability sounds like a product category, and it is one. But for a team of ten, the core of it is a short list of questions you ask your tables every morning. Is it fresh? Is it the right size? Is it unique? Is it filled in? Does it match the source?

Each question is a query. Each query returns zero rows when the answer is fine, and a row when it isn’t. That is the whole design, and it fits in a cron job. Below are the five I’d start with, written for Postgres and tested on a small orders table.

The convention: a check returns rows only when something is wrong

Write every check so a healthy result is empty. Then scheduling is trivial: run it, and notify if there is a row. No dashboards to read, and nothing to compare by eye. This is the same rule behind the row count anomaly check, and it’s what keeps alerts quiet enough that people still read them.

1. Freshness: did the data stop arriving?

SELECT now() - max(created_at) AS lag
FROM orders
HAVING now() - max(created_at) > interval '6 hours';

The HAVING clause makes the query return nothing while the newest row is recent. Pick the window from how the table loads: an hourly job gets a few hours, a nightly job gets about 26. The freshness post covers multiple tables and timezone traps.

This catches the most common failure by far: the job is green, the table is old.

2. Volume: is yesterday the right size?

WITH daily AS (
  SELECT created_at::date AS d, count(*) AS n
  FROM orders
  WHERE created_at >= current_date - 29 AND created_at < current_date
  GROUP BY 1
)
SELECT d, n
FROM daily y
WHERE d = current_date - 1
  AND n < 0.6 * (SELECT avg(n) FROM daily b
                 WHERE extract(dow FROM b.d) = extract(dow FROM y.d)
                   AND b.d < y.d);

It compares yesterday to the average of the same weekday in the previous weeks, so a normal Sunday dip doesn’t page anyone. On my test table, deleting half of yesterday’s rows (750 against a 1,500 baseline) returned one row; the untouched table returned none. Tune the 0.6 by backtesting 90 days, as described in the week-over-week alert post.

3. Uniqueness: did duplicates sneak in?

SELECT id, count(*) AS copies
FROM orders
WHERE created_at >= current_date - 7
GROUP BY id
HAVING count(*) > 1;

Retries, replays and double loads all produce this. Limit it to recent rows so the check stays cheap on a large table, and use whatever your natural key is. Duplicated order rows quietly inflate revenue, which is exactly the “number is wrong but nothing errored” case. The full version, with exact repeats versus changed retries and a safe dedupe, is in finding duplicate rows in SQL.

4. Nulls: did a column go quiet?

SELECT round(100.0 * count(*) FILTER (WHERE customer_id IS NULL)
             / NULLIF(count(*), 0), 1) AS null_pct
FROM orders
WHERE created_at >= current_date - 1 AND created_at < current_date
HAVING count(*) FILTER (WHERE customer_id IS NULL) > 0;

This is the version for a column that must never be null. For columns that are legitimately sparse, compare the rate with its own history instead; the null rate post shows how and lists the false alarms.

5. Reconciliation: does it match the source?

SELECT s.total AS source_total, w.total AS warehouse_total
FROM (SELECT sum(amount) AS total FROM src_orders
      WHERE created_at >= current_date - 1 AND created_at < current_date) s,
     (SELECT sum(amount) AS total FROM orders
      WHERE created_at >= current_date - 1 AND created_at < current_date) w
WHERE abs(s.total - w.total) > 0.005 * greatest(s.total, 1);

Here src_orders stands for the source system’s copy and orders for the warehouse’s. If they live in different databases, run the two sums as two small queries and compare the results; the check is the same idea. The tolerance is 0.5%, so late-arriving rows don’t fire it. In my test, losing half of yesterday’s orders gave a source total of 82,650 against 41,700 in the warehouse, and the check fired.

What these five miss

Be honest about the gaps:

• Silent logic changes. A metric definition that changes in a dbt model passes all five.
• Schema changes. A renamed column breaks a query loudly, but a changed type might not.
• Distribution shifts. The mean of a column drifting slowly won’t trip a null or volume check.
• Lineage. When a check fails, these tell you which table, not which upstream job caused it.

For a team with a handful of key tables, that’s an acceptable trade. You’ll learn about breakage the same morning rather than from a stakeholder.

When to buy a platform instead

A hosted observability platform earns its price when you have hundreds of tables, where writing and maintaining checks by hand stops being realistic, or when lineage and automatic anomaly detection across everything matter more than a few good checks. If you have about ten tables that feed the numbers people use to make decisions, start with the five above. You can always buy later, and you’ll know what you want it to catch.

Running them on a schedule

Cron with psql works, as does your orchestrator. If you’d rather not host that, arcodash can run each query on a schedule and notify Slack, email or PagerDuty. A monitor reads one named column of the first result row, so wrap each check as SELECT count(*) AS bad FROM (...) t and alert when bad > 0. See the data quality monitors docs for how a check is evaluated.

To get these five written for your own table and dialect, the SQL data check generator is free and needs no signup.