Data quality checks

Why it matters

Every analysis inherits the problems of its data. A duplicated order inflates revenue, a negative quantity hides returns inside sales, a missing customer id drops rows out of a join, and a date in the future breaks a "last 30 days" filter. None of these cause an error: the query runs and the number is simply wrong. Experienced analysts run data quality checks before trusting a table, and data teams schedule the same checks to run after every load. Each check is a query that should return zero problem rows.

The main kinds of check

Worked examples

Completeness. Empty strings often hide where NULL checks do not look:

SELECT COUNT(*) AS missing_email
FROM orders
WHERE email IS NULL OR TRIM(email) = '';

Uniqueness. List the duplicated keys and how often they appear:

SELECT order_id, COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

To count the extra rows in one number: SELECT COUNT(*) - COUNT(DISTINCT order_id) FROM orders. An id that appears three times contributes two extra rows. (Note that COUNT(DISTINCT ...) ignores NULL, so check for NULL keys separately.)

Validity. Values outside an allowed list:

SELECT status, COUNT(*) AS n
FROM orders
WHERE status NOT IN ('pending', 'shipped', 'cancelled', 'refunded')
   OR status IS NULL
GROUP BY status;

The OR status IS NULL matters: NULL NOT IN (...) is not true, so NULL rows would otherwise slip through.

Referential integrity. Orders whose customer does not exist (an "orphan"):

SELECT o.id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;

Consistency between columns:

SELECT id FROM shipments WHERE delivered_at < shipped_at;

One query, many checks

Running checks one at a time is slow to review. Combine them with UNION ALL into a single report with one row per check:

SELECT 'missing_email' AS check_name, COUNT(*) AS failing_rows
FROM orders WHERE email IS NULL OR email = ''
UNION ALL
SELECT 'negative_amount', COUNT(*)
FROM orders WHERE amount < 0
UNION ALL
SELECT 'orphan_customer', COUNT(*)
FROM orders AS o LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;

Because each part uses COUNT(*) with no GROUP BY, every check returns exactly one row, even when it finds nothing. That is what you want: a row showing 0 proves the check ran. Tools such as dbt tests and Great Expectations are built on the same idea, turning checks like these into automatic tests that fail a pipeline when the count is not zero.

Find, then decide

A check tells you something is wrong; it does not tell you what to do. Before you filter rows out:

1. Look at examples. Change COUNT(*) to SELECT * and read 10 failing rows.
2. Ask the data owner. A negative amount might be a refund that is valid in this system.
3. Fix at the source if you can, rather than patching every report.
4. If you must exclude rows in your analysis, say so in the report, with the count ("12 duplicate orders removed").

Never silently "clean" data in a way that changes a business number without telling anyone.

Profiling a new table

When you first meet a table, a quick profile catches most issues:

SELECT COUNT(*)                        AS row_count,
       COUNT(DISTINCT order_id)        AS distinct_ids,
       SUM(email IS NULL)              AS null_emails,
       MIN(amount), MAX(amount),
       MIN(order_date), MAX(order_date)
FROM orders;

In SQLite a comparison such as email IS NULL returns 1 or 0, so SUM(...) counts matches. In PostgreSQL write COUNT(*) FILTER (WHERE email IS NULL) or SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Common mistakes

• Forgetting empty strings and whitespace in completeness checks.
• NOT IN with NULLs, which lets bad rows through.
• Using INNER JOIN to find orphans. An inner join hides them; use LEFT JOIN ... IS NULL.
• Checks that return no row when everything is fine, so you cannot tell "passed" from "did not run".
• Hard-coding today's date in a scheduled check; use the load date or date('now').

Quick recap

• Check completeness, uniqueness, validity, referential integrity, date ranges and consistency.
• Each check is a query that should return 0 problems; combine them with UNION ALL.
• Investigate and document before excluding rows.

Try it

Open the Do tab and write a four-check quality report for a messy orders table.