Check Data Quality with SQL

A data quality check is an ordinary query with one rule attached: write it so that healthy data returns no rows. That inversion is what makes it a check rather than a report. You do not have to read the output and judge it. Empty means pass.

Every example below runs against order_imports, a staging table built the way a load would arrive: the real orders, plus two rows a real feed will eventually send you. Run this first.

create or replace table order_imports as
select * from orders
union all
select 900, 999, date '2024-07-01', date '2024-06-28', 'shipped', null
union all
select 901, 1, null, null, 'pending', null

select *
from order_imports
where order_date is null

That shape is right when you are fixing something, because it hands you the rows to look at. When you want a scoreboard instead, wrap the same condition in a count and label it. Now the check reports a number, and zero is the number you want.

select 'missing_order_date' as check_name,
       count(*) as failing_rows
from order_imports
where order_date is null

The four checks worth writing first

Most data problems fall into a small set of shapes. Null checks catch a column that was supposed to be filled in. Duplicate key checks group by the key and keep the groups with more than one row, which is how a load that ran twice shows up. Range checks catch values that are possible to store but impossible in the business: a negative quantity, a ship date before the order date.

Referential checks catch orphans, rows pointing at a parent that is not there. A LEFT JOIN to the parent table keeps every child row, so the ones where the parent side came back null are exactly the orphans.

select i.order_id,
       i.customer_id
from order_imports i
left join customers c on c.customer_id = i.customer_id
where c.customer_id is null
order by i.order_id

Check the load, not the warehouse

Run these against the incoming data before it is merged into the tables everyone reports on. A check that fires on staging is a load you can reject. The same check firing on the reporting tables is a week of numbers already sent out.

Three of the four checks found their row, and the fourth reported a clean zero. That is the report you want to be able to run on demand, which means saving it as a view and making the whole script safe to rerun. Both are coming up in this chapter.