Data quality checks that catch real bugs, with the SQL

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 amount would not see it. NaN is not NULL.
• The range check amount < 0 did not catch it.
• The jump check amount > 10 * median did 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.

Checks that caught real bugs

These come from a production pipeline I run on Dagster: 112 assets and 93 asset checks. The domain is market data, but every one of these failure shapes turns up in ordinary business data too.

A file that repeats yesterday. One public data source answers a request for a non-trading day with the previous day's file, not an error. Saturdays return 404; Sundays and holidays return a byte-identical copy. One asset trusted the requested date and stamped 7,509 rows (20.3% of its table) onto 229 dates when nothing had traded. 99.2% of them were exact copies of the day before. Every row was valid. The table was wrong. The fix reads the date inside the file and returns nothing when it differs from the requested date.

Freshness measured by the wrong clock. A cache hit re-saved old rows under the next day's partition, which gave months-old data a fresh file timestamp. A freshness check on file time said everything was current. The check now reads the rows' own downloaded_at. Measure staleness by the age of the data, not the age of the file.

A scraper that "succeeds" with a short page. A source returns HTTP 200 with a fraction of the usual rows. Every scraper in the pipeline now has a row-count floor (2,500 to 5,000 rows, depending on the source), and one with seasonal volume has a recency guard instead: the newest date must be within seven days. A short response must raise, not overwrite good data.

Output that was look-ahead, not stale. A model-output table held 57,501 rows below the point a rebuild could reach. I assumed they were merely stale. Joined to the model registry, 18,059 predicted dates inside their own model's training window and 39,442 predicted dates before it started. None were genuine out-of-sample predictions. A warn-level check now reports the floor date and the count of rows below it on every rebuild, until the migration that removes them runs.

Prices that break their own definition. For daily price bars, the high must be at least the open, close and low, and the low at most all of them. The quality table in that pipeline records four counters per symbol per day: OHLC violations, price jumps, data gaps and volume issues. The OHLC rule as SQL:

SELECT symbol, trade_date, open, high, low, close
FROM prices
WHERE high < low OR high < open OR high < close
   OR low > open OR low > close;

Every domain has rules like this: an invoice total equals the sum of its lines; an end date is not before its start date. They are the cheapest checks to write and among the most useful, because no generic tool knows them.

How to automate data quality checks in a pipeline

Three decisions matter more than the tool.

Run them in the same run that writes the data. A check that runs nightly, separately, tells you about a problem after downstream jobs have used the bad data.

Decide which failures stop the pipeline. Some checks should block: a duplicate key, a volume collapse. Others should warn: a slightly late source. dbt has severity: warn or error per test. Dagster has asset checks with WARN and ERROR severities, and blocking=True, which stops downstream assets from running when the check fails.

Know what "blocking" actually blocks. This one cost me. In Dagster, a blocking check runs after its asset. It stops what comes downstream. One of my assets wrote rows to ClickHouse from inside its own code, then the blocking check failed, and the run went red with the bad rows already in the table. The fix was to return the data from the asset and let the IO manager do the insert after the checks pass. Whatever your tool, ask: when this check fails, is the bad data already where readers can see it?

Data quality tools: dbt tests, Great Expectations, Soda, Dagster asset checks

All four can express the eight checks above. They differ in where they run. What I verified in each project's docs and repositories on 1 October 2026:

Tool	How checks are written	Notes
dbt data tests	YAML on a model, or a SQL file returning failing rows	Four built-in generic tests: unique, not_null, accepted_values, relationships. The YAML key is now data_tests:; tests: still works. store_failures saves failing rows to a table.
Great Expectations (GX Core)	Python, from a library of named expectations	Apache 2.0. Release 1.23.2 on 28 September 2026. Expectations such as expect_column_values_to_not_be_null and expect_table_row_count_to_be_between.
Soda Core	YAML data contracts	v4.25.0 on 23 September 2026. The README lists 50+ built-in checks and support for Postgres, Snowflake, BigQuery, Databricks, DuckDB and more. v3 packages used different names, so check which docs you are reading.
Dagster asset checks	Python functions attached to an asset	WARN or ERROR severity; blocking=True stops downstream assets. Your SQL runs inside the function.

I run Dagster asset checks in production. I have not run Great Expectations or Soda in production, so I won't rank them. My rule of thumb: put the checks where the transformation already lives. If your models are dbt, start with dbt tests. If Dagster runs your pipeline, use asset checks, as I do. Add a separate tool only when you need what it adds, such as a shared catalogue of checks across teams.

The same applies to anything a rule cannot express. Whether a product description matches its category is a judgement, not a range. For that, see using an LLM to catch the data quality problems your tests can't.

A checklist for your first ten checks

1. Not-null and unique on every primary key.
2. Relationships on the foreign keys that joins depend on.
3. A NaN check on every float column you aggregate.
4. Freshness, measured from a date inside the data.
5. A volume floor on every source you load, set from a normal day.
6. A gap check against a calendar for anything daily.
7. One domain invariant per important table: totals add up, end follows start.
8. A jump check on the numbers people make decisions with.
9. A decision, written down, of which checks block and which warn.
10. A test that a blocking failure really stops the bad data reaching readers.

So: which data quality checks are worth writing?

The table-level ones, first. Volume, freshness, gaps and your own domain rules catch the failures where every row looks fine. Then a NaN check, because nothing else will catch it. Not-null and unique are cheap, so keep them, but don't mistake them for coverage.

More on how that pipeline is built is in building a data platform solo; it has grown since that post. The SQL above runs in DuckDB, which is the engine Local-First Analytics is built around; chapter 1 is free to read, the rest is on Amazon.

If you'd like someone to look at which checks your pipeline is missing, the teardown linked below is a free place to start. If your checks already fire on real problems and stay quiet otherwise, you are in better shape than most, and you don't need me.