Null rate: how to calculate and alert on it in SQL

The formula

Null rate is nulls divided by rows. The one thing to get right is the denominator.

count(*) counts every row. count(col) counts only rows where col is not NULL. So:

• count(*) - count(col) is the number of NULLs.
• count(col) / count(*) is the fill rate.
• count(col) / count(col) is always 1, which is the trap. A null rate built on it reads 0 forever.

SELECT count(*)                                           AS total,
       count(email)                                       AS non_null,
       count(*) - count(email)                            AS nulls,
       round(100.0 * count(*) FILTER (WHERE email IS NULL)
             / nullif(count(*), 0), 2)                    AS null_pct
FROM users;

nullif(count(*), 0) avoids a divide-by-zero on an empty table, where the result becomes NULL instead of an error. That NULL is worth knowing about: a monitor that reads a non-numeric value cannot compare it. In arcodash such a monitor sits in the unknown state, per the alerts overview, so an empty table shows as unknown, not as ok.

Alert on a recent window

Whole-table null rate moves slowly. A pipeline that breaks today barely shifts a number built on five years of rows. Measure the recent window instead, and require a minimum row count so a quiet hour does not fire.

SELECT round(100.0 * count(*) FILTER (WHERE email IS NULL)
             / count(*), 1) AS null_pct,
       count(*)             AS rows_checked
FROM signups
WHERE created_at >= now() - interval '24 hours'
HAVING count(*) >= 20;

This returns one row with null_pct, which is the shape a monitor needs. arcodash reads the first row of the query, takes one named column, and compares it to your threshold, so point a monitor at null_pct with > and a value. The data observability monitors page has the same recipe for a whole table. If the HAVING filters the single row out, the result is empty and the monitor goes to unknown rather than triggered.

Pick the level from the column’s history

To choose a threshold, look at what the column normally does. Per day:

SELECT created_at::date AS day,
       count(*) AS n,
       round(100.0 * count(*) FILTER (WHERE email IS NULL)
             / count(*), 1) AS null_pct
FROM signups
WHERE created_at >= now() - interval '3 days'
GROUP BY 1
ORDER BY 1;

Note the partial first day in a rolling window: a day with one row can show 100 percent. Use whole days (created_at >= current_date - 3 AND created_at < current_date) when you chart this. For how to set the level once you can see the history, read alert thresholds.

Where null rate checks go wrong

• Wrong denominator. Covered above. Use count(*).
• Placeholders that are not NULL. Empty strings, 'unknown', 0 and 1970-01-01 all hide missing data. Count them explicitly.
• Intentional nulls. A cancelled_at column is NULL for every active subscription. Alert on change, not on presence.
• Joins. A left join that stops matching fills the joined columns with NULL. If one column’s null rate spikes, check the join key before the source.

A null check is one kind of validity check; scheduled SQL checks shows it running on a schedule.

The SQL data check generator writes a null rate check for the columns you list, in your database’s dialect.