Data quality checks are small queries that fail when data breaks a rule you know must hold. Most lists of them start with not-null and uniqueness. Those are worth having. In my experience they are also the checks that almost never fire, because the source system already enforces them.
The checks that caught real bugs in my pipelines were different: a row count that was too low, a date that was not really new, a number that was NaN instead of NULL. This post gives you the common checks as runnable SQL, then the ones that earned their place, with the numbers.
The examples run as-is in DuckDB and need only small changes for Postgres. They use one toy table, orders, with a few bugs planted in it.
The common data quality checks, in SQL
Every check below has the same shape: a SELECT that returns the rows that break the rule. No rows means it passed. A failure hands you the bad rows, not just a red light. dbt's data tests work exactly this way.
1. Not null. Columns that must always have a value.
SELECT count(*) FILTER (WHERE customer_id IS NULL) AS null_customer_id
FROM orders;
2. Unique. Keys that must appear once.
SELECT order_id, count(*) AS copies
FROM orders
GROUP BY order_id
HAVING count(*) > 1;
3. Accepted values and ranges. A status from a fixed list, an amount that is never negative.
SELECT order_id, status, amount
FROM orders
WHERE status NOT IN ('paid', 'refunded', 'cancelled')
OR amount < 0;
4. Relationships. Every foreign key has a parent row.
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL
AND c.customer_id IS NULL;
Those four look at rows one at a time. The next four look at the table as a whole, and they catch what the first four cannot: data that is valid row by row and still wrong.
5. Freshness. How old is the newest data?
SELECT max(order_date) AS latest_order,
current_date - max(order_date) AS days_old
FROM orders;
6. Volume. Does each day look like a normal day?
SELECT order_date, count(*) AS n
FROM orders
GROUP BY order_date
ORDER BY order_date;
7. Jumps. A value far outside what is plausible.
SELECT order_id, amount, median_amount
FROM orders,
(SELECT median(amount) AS median_amount FROM orders WHERE NOT isnan(amount))
WHERE amount > 10 * median_amount;
8. Gaps. Missing days in a series that should be continuous.
SELECT d::DATE AS missing_day
FROM generate_series(DATE '2026-09-01', DATE '2026-09-06', INTERVAL 1 DAY) AS t(d)
WHERE d::DATE NOT IN (SELECT order_date FROM orders);
I ran all eight against the toy table in DuckDB. Each one returned the row I had planted for it. One result surprised me, and it leads to the most useful check in this post.
The NaN that passes your null check
One planted row has amount = NaN. Here is how the checks treated it:
- The not-null check on
amountwould not see it. NaN is not NULL. - The range check
amount < 0did not catch it. - The jump check
amount > 10 * mediandid flag it, because DuckDB sorts NaN above every other number.
So whether NaN passes a check depends on which way the comparison points. That is not a rule you want to discover in production.
I did discover it in production. My main pipeline writes to ClickHouse. A column declared Float64, not Nullable(Float64), cannot hold NULL. When a NULL was written into one, it did not fail. It landed as NaN. From the lesson I wrote down afterwards:
> WHERE col IS NULL then matches nothing, so every coverage check on that table reports full coverage — wrong in the reassuring direction.
The fix had two parts. The writer now logs every non-nullable column that receives a NULL, by name and count, on every write. And the rule: never answer it with a sentinel. One table filled the gap with 0.5, which is a real mid-range value, and 219 of 758 rows a day carried it.
The check to add:
SELECT count(*) FILTER (WHERE isnan(amount)) AS nan_amount
FROM orders;
Also: check the live schema, not the DDL file. CREATE TABLE IF NOT EXISTS never changes an existing table, and I had one table whose file said Nullable(Float64) while the real column was Float32 DEFAULT 0.
This is the first part. The full post — including the rest of the working details — is on my site: Data quality checks that catch real bugs, with the SQL
Top comments (1)
The point that NaN passes or fails depending on which way the comparison points is worth making explicit. In pandas the same value counts as missing under isna(), so the same check written in pandas and in SQL can disagree on one row.
The note about checking the live schema rather than the DDL file is useful too. Do you run the isnan count on every float column automatically, or only where you have already seen it happen?