DEV Community

Refael Dakar
Refael Dakar

Posted on Originally published at arcodash.com

Null rate data quality check in SQL, per column

#sql #postgres #dataengineering #monitoring

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';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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/

Top comments (0)