Lesson 18.2

Check Data Quality with SQL

A data quality check is a query written so that a clean table returns nothing. Anything it returns is the problem.

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 don't 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.

The load to check

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

A check returns the offenders

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.

The same check, as a score

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

Many common data-quality 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 isn't 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.

Orphan check

select i.order_id,
       i.customer_id
from order_imports as i
left join customers as 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

When possible, run these checks against incoming data before it is merged into reporting tables. Catching a problem in staging gives you a chance to reject or quarantine the load before downstream reports consume it.

Schema · Garden Shop Tables · orders, customers 2 tables

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Your task

Build a check report over order_imports. Return check_name and failing_rows for four checks: duplicate_order_id, missing_order_date, shipped_before_ordered, and unknown_customer (an order whose customer isn't in customers). Sort by check_name. Keep the setup statement at the top of the editor.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: check_name, failing_rows.
Rows: 4, one per check, sorted by check name.
duplicate_order_id passes with 0. The other three each find the one bad row that was loaded.

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.

Previous · 18.1 Profile a table before you query

Next · 18.3 Build a semantic view →

Reference

SQL DROP Statements

Remove tables, views, schemas, and more without breakage.

Reference

SQL Table Constraints

NOT NULL, DEFAULT, CHECK, UNIQUE, and keys.

Reference

SQL Schemas

Group objects into namespaces like staging and reporting.

Lesson