Null rate data quality check in SQL, per column

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. This post builds it in plain Postgres SQL, from a single column up to a scheduled check that only speaks when something changed.

The one-column version

Null rate is the share of rows where a column has no value:

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

Two details matter. NULLIF(count(*), 0) turns an empty window into a null instead of a division by zero error, so the check doesn’t crash on exactly the day the table is empty. And FILTER (WHERE col IS NULL) counts nulls explicitly. The alternative, count(*) - count(col), gives the same number, but it’s easier to misread.

Always put a time window on it. A null rate over the whole table is dominated by old rows, so a column that broke yesterday barely moves the number.

Every column at once

If you don’t know which column will break, check all of them. Postgres can do this without dynamic SQL by running a count per column through query_to_xml:

SELECT column_name,
       (xpath('/row/n/text()', query_to_xml(
         format('SELECT count(*) - count(%I) AS n FROM %I.%I',
                column_name, table_schema, table_name),
         false, true, '')))[1]::text::bigint AS nulls
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'
ORDER BY ordinal_position;

This scans the table once per column, so it’s an audit you run by hand or weekly, not something to schedule every five minutes on a large table. %I quotes identifiers, which keeps odd column names from breaking the query.

Use it to find your baselines. A few things it usually shows:

• Columns that are never null and should stay that way (ids, timestamps, foreign keys).
• Columns that are legitimately sparse (an optional coupon code, a cancelled-at date).
• Columns that are null more than you’d like, which is a finding by itself.

Decide the tolerance per column role

One threshold for every column doesn’t work. Roughly:

• Required columns (primary key, created_at, a customer id): any null is a bug. Alert on count(*) FILTER (WHERE col IS NULL) > 0.
• Mostly-filled columns (email, amount): alert when the rate rises well past its normal level. A column at 2% nulls that goes to 9% deserves a look.
• Sparse columns (coupon code, referrer): the absolute level means nothing. Only a change in rate matters, and a drop to zero nulls can be as suspicious as a jump.

For the first group a plain count is enough. The interesting one is the middle and last group, where you compare against history.

Alert on the jump, not the level

Compare yesterday’s null rate 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;

The last row of the window is yesterday, the rest is the baseline. Yesterday is a full day, so the check doesn’t compare a half-finished morning to complete history. The same reason is behind the “check the last complete day” rule in the row count anomaly post.

To make it a check that is silent when healthy, return a row only on a jump. This version covers several columns at once and reports only the ones that moved:

WITH rates AS (
  SELECT
    count(*) FILTER (WHERE created_at >= current_date - 1)                         AS n_recent,
    count(*) FILTER (WHERE created_at <  current_date - 1)                         AS n_base,
    count(*) FILTER (WHERE created_at >= current_date - 1 AND email IS NULL)       AS email_recent,
    count(*) FILTER (WHERE created_at <  current_date - 1 AND email IS NULL)       AS email_base,
    count(*) FILTER (WHERE created_at >= current_date - 1 AND coupon_code IS NULL) AS coupon_recent,
    count(*) FILTER (WHERE created_at <  current_date - 1 AND coupon_code IS NULL) AS coupon_base
  FROM orders
  WHERE created_at >= current_date - 29 AND created_at < current_date
)
SELECT col,
       round(100.0 * recent / n_recent, 1) AS recent_pct,
       round(100.0 * base / n_base, 1)     AS baseline_pct
FROM rates,
     LATERAL (VALUES ('email', email_recent, email_base),
                     ('coupon_code', coupon_recent, coupon_base)) v(col, recent, base)
WHERE n_recent > 0 AND n_base > 0
  AND recent::numeric / n_recent > 2 * base::numeric / n_base + 0.02;

The condition says: yesterday’s null share is more than double the baseline, plus two percentage points. The + 0.02 stops a column at 0.1% from alerting when it goes to 0.3%. I tested this on a fake orders table where coupon_code went null for a whole day. It returned one row, coupon_code, 100% against a 0% baseline, and nothing for email.

To watch another column, add a pair of FILTER counts and one more VALUES row. For sparse columns where a drop is the problem, flip the comparison.

The usual false alarms

• A new column. Right after you add one, history is all null by definition. Start the check after the backfill, or give it its own start date.
• A real change in the data. A product launch or a new signup source can shift a null rate for good reasons. The alert should tell someone to look, not claim something is broken.
• Small volume. On a table with 40 rows a day, one row moves the rate by 2.5 points. Use a minimum row count (n_recent >= 50) or a count of nulls instead of a rate.
• Empty string, 0 and 'N/A'. These aren’t nulls, and a pipeline that swaps nulls for placeholders will pass this check forever. If you suspect it, count the placeholder too: col IS NULL OR col IN ('', 'N/A').

Backtest before you schedule it

Take the last 90 days, run the check for each day, and see how often it would have fired. Pick the multiplier that stays quiet on days you know were fine. The method is the same one from the row count post, and the reasoning holds here too: a check people learn to ignore is worse than no check.

Running it on a schedule

Anything that can run SQL and notify on a result will do: cron with psql, or your orchestrator. If you’d rather not run that yourself, arcodash can run the query on a schedule and alert Slack, email or PagerDuty. Monitors there check a threshold on one column, so wrap the final select as SELECT count(*) AS bad_columns FROM (...) t and alert when bad_columns > 0.

For a null rate query written in your own database’s dialect, the SQL data check generator produces one for any table.

If you want the whole set rather than one check, see five SQL checks that cover most incidents.