Data Quality, Debugging & Auditing

A query that runs is not always a query that is right. Data can be wrong, and SQL does not warn you.

In the pipeline chapter you saw that a check is a query that returns the bad rows. Zero rows means the check passes. This chapter shows the SQL patterns for many kinds of checks.

This chapter uses the shop tables from chapters 1 to 3. You know customers, orders and order_items. Here is customers again:

The shop also has a load. A load is a batch of new rows copied into the database.

This load is the table raw_orders. It has 10 rows:

Four rows are wrong:

• Order 106 appears twice. This is a duplicate.
• Order 107 has an amount of -20.00. An amount cannot be below zero.
• Order 108 belongs to customer 9. No customer 9 exists.
• Order 109 has the status “Paid ” with a capital P and a space at the end. The other paid orders use “paid”.

You will find each problem with a query. Here is the first check. The rule is: no order has a negative amount.

SELECT order_id, customer_id, amount
FROM raw_orders
WHERE amount < 0;

The check found order 107. If no row had a negative amount, the result would be empty. Empty means pass.

A duplicate is the same thing stored twice. It makes counts and sums too big.

First decide which column should be unique. In raw_orders, each order_id should appear once. Group by that column and keep the groups with more than one row:

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

Order 106 has two rows. Every other order_id has one row, so HAVING removes them. A clean table returns no rows here.

To see the extra copies, number the rows inside each order_id with ROW_NUMBER (from chapter 5). Row number 2 or more is a copy:

WITH numbered AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY order_date
         ) AS rn
  FROM raw_orders
)
SELECT order_id, customer_id, status, amount, order_date, rn
FROM numbered
WHERE rn > 1;

Change WHERE rn > 1 to WHERE rn = 1 and you keep one row per order. That gives 9 rows.

The two copies of 106 are the same, so any copy is fine. If the copies differ, ORDER BY decides which copy gets number 1. For example, ORDER BY order_date DESC keeps the newest order_date.

In chapter 1 you learned that COUNT(*) counts all rows, and COUNT(column) skips NULL. The difference between them is the number of missing values.

One query can check many columns. Subtract COUNT(column) from COUNT(*) for each column:

SELECT
  COUNT(*)                  AS total_rows,
  COUNT(*) - COUNT(customer_id) AS no_customer_id,
  COUNT(*) - COUNT(status)      AS no_status,
  COUNT(*) - COUNT(amount)      AS no_amount
FROM raw_orders;

Is that one NULL a problem? Order 105 is pending. A pending order has no amount yet.

So this NULL is expected. A check needs a rule, not only a count.

To list the rows with a missing value, use IS NULL. Dev has no country:

SELECT customer_id, name
FROM customers
WHERE country IS NULL;

A reference is a column that points at a row in another table. raw_orders.customer_id points at customers.customer_id. A reference is broken when the row it points at does not exist.

You find broken references with an anti-join (chapter 2). Join with LEFT JOIN, then keep the rows that found no match:

SELECT r.order_id, r.customer_id
FROM raw_orders AS r
LEFT JOIN customers AS c
  ON c.customer_id = r.customer_id
WHERE c.customer_id IS NULL;

Order 108 points at customer 9, and there is no customer 9. All other orders found a customer.

You can also look for a parent row with no child rows. Which orders have no items?

SELECT o.order_id, o.status
FROM orders AS o
LEFT JOIN order_items AS i
  ON i.order_id = o.order_id
WHERE i.order_id IS NULL;

Is this a problem? It depends on the rule. A pending order may have no items yet.

You decide the rule first. Then you write the check.

A column often has a short list of allowed values. In orders, status is paid, pending or refunded. First look at which values really exist, and how often:

SELECT status, LENGTH(status) AS chars, COUNT(*) AS copies
FROM raw_orders
GROUP BY status
ORDER BY copies DESC, chars;

Four statuses, but only three are allowed. Now write the check. It keeps the rows whose status is not in the allowed list:

SELECT order_id, status
FROM raw_orders
WHERE status NOT IN ('paid', 'pending', 'refunded');

TRIM removes spaces at the start and the end. LOWER makes every letter small. So LOWER(TRIM(status)) turns “Paid ” into “paid”.

Clean the data in the load step. Keep the check on the raw value, so it still finds the problem.

A NULL status would not show here. NULL NOT IN (...) is unknown, not true, so WHERE drops the row. The missing-value check above finds those.

Never use NOT IN with a subquery that can return NULL: it then returns no rows at all. Use the anti-join instead.

A range check works the same way. The rule is a lower and an upper limit. Say the shop never sells an order above 1000:

SELECT order_id, amount
FROM raw_orders
WHERE amount NOT BETWEEN 0 AND 1000;

A date range is also useful. Every order should be from January 2026:

SELECT order_id, order_date
FROM raw_orders
WHERE order_date NOT BETWEEN '2026-01-01' AND '2026-01-31';

Each column can look fine alone, but two columns together can be impossible. A paid order with no amount is one example.

Write the rule in words first. Then write a query for the rows that break it. The rule here: a paid order has an amount, and a pending order has none.

SELECT order_id, status, amount
FROM raw_orders
WHERE (status = 'paid' AND amount IS NULL)
   OR (status = 'pending' AND amount IS NOT NULL);

Order 109 has status “Paid ”, so this rule never looks at it. If 109 had no amount, this check would miss it.

One broken value can hide a row from another check. Fix allowed values first.

Reconciliation means counting the same thing in two places and comparing. The source is where data comes from. The target is where it goes.

Here raw_orders is the source. The table orders is the target. Start with the counts:

SELECT
  (SELECT COUNT(*) FROM raw_orders)              AS source_rows,
  (SELECT COUNT(DISTINCT order_id) FROM raw_orders) AS source_orders,
  (SELECT COUNT(*) FROM orders)                  AS target_rows;

The source has 9 orders. The target has 6. Three are missing.

Which ones? Use an anti-join on the key:

SELECT r.order_id, r.status, r.amount
FROM raw_orders AS r
LEFT JOIN orders AS o
  ON o.order_id = r.order_id
WHERE o.order_id IS NULL
ORDER BY r.order_id;

The three bad orders did not reach the target. The copy of 106 was also removed.

Now compare the sums. Count each order once in the source, so remove the copy of 106 first:

WITH source AS (
  SELECT DISTINCT order_id, amount
  FROM raw_orders
)
SELECT
  (SELECT SUM(amount) FROM source) AS source_total,
  (SELECT SUM(amount) FROM orders) AS target_total,
  (SELECT SUM(amount) FROM source)
    - (SELECT SUM(amount) FROM orders) AS difference;

The difference of 55.00 is fully explained by the three missing orders. A difference you can explain is a pass. A difference you cannot explain is a bug.

Do not trust matching totals alone. Say two orders are missing: one of +30.00 and one of -30.00.

The totals still match, but two orders are lost. Compare the counts and the keys too.

The pipeline chapter showed a freshness check with MAX. Here you turn it into a number of days.

Fresh data is data that is recent enough. If the newest order is many days old, the load may have stopped.

Find the newest date with MAX. Then subtract it from the date of the check.

DATE '2026-01-25' turns the text into a date. We use a fixed date so the result stays the same. In real work you write CURRENT_DATE.

SELECT
  MAX(order_date) AS newest_order,
  DATE '2026-01-25' - MAX(order_date) AS days_old
FROM raw_orders;

The newest order is from 2026-01-24, so the data is 1 day old.

This date comes from order 109. Order 109 never reached the orders table.

In orders, the newest date is 2026-01-21, so that table is 4 days old. Check freshness on the table people read.

Now write the rule as a check. HAVING without GROUP BY treats the whole table as one group. So the query returns one row or no row.

Return a row only when the data is more than 1 day old:

SELECT MAX(order_date) AS newest_order
FROM raw_orders
HAVING DATE '2026-01-25' - MAX(order_date) > 1;

An audit runs many checks and keeps the results together. Make each check return a name and a count of bad rows. UNION ALL puts the rows of one query under the rows of another.

Run all the raw_orders checks at once. Add a CASE that says pass or fail:

WITH checks AS (
  SELECT 'duplicate_order_ids' AS check_name, COUNT(*) AS bad_rows
  FROM (SELECT order_id FROM raw_orders
        GROUP BY order_id HAVING COUNT(*) > 1) AS d
  UNION ALL
  SELECT 'orders_without_customer', COUNT(*)
  FROM raw_orders AS r
  LEFT JOIN customers AS c ON c.customer_id = r.customer_id
  WHERE c.customer_id IS NULL
  UNION ALL
  SELECT 'bad_status', COUNT(*)
  FROM raw_orders
  WHERE status NOT IN ('paid', 'pending', 'refunded')
  UNION ALL
  SELECT 'negative_amount', COUNT(*)
  FROM raw_orders WHERE amount < 0
  UNION ALL
  SELECT 'date_outside_january', COUNT(*)
  FROM raw_orders
  WHERE order_date NOT BETWEEN '2026-01-01' AND '2026-01-31'
)
SELECT check_name, bad_rows,
       CASE WHEN bad_rows = 0 THEN 'pass' ELSE 'fail' END AS result
FROM checks
ORDER BY check_name;

Four checks fail, and one passes. Keep the passing line. It proves the check ran.

For duplicates, the count is the number of repeated order_ids.

Every branch of UNION ALL must return the same columns, in the same order. Here that is a name, then a count.

Sometimes a number is wrong and no check failed. Do not rewrite the whole query.

Start with the simplest version and add one step at a time. Count the rows after each step.

The task: total revenue of paid orders. Step 1: use only the orders table.

SELECT COUNT(*) AS rows, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid';

This is the right number: 4 paid orders, 160.50. Step 2: add the join to order_items, because the report also shows products. Count the rows again.

SELECT COUNT(*) AS rows_after_join,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(o.amount) AS revenue
FROM orders AS o
JOIN order_items AS i
  ON i.order_id = o.order_id
WHERE o.status = 'paid';

The rows grew, so the join is the cause. Step 3: find which orders were repeated.

SELECT o.order_id, COUNT(*) AS rows
FROM orders AS o
JOIN order_items AS i
  ON i.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.order_id
ORDER BY o.order_id;

This is fan-out (chapter 2). A join gives one order several rows, and SUM adds the amount once per row. The extra 120.00 is 40.00 + 80.00.

The fix is the one from chapter 4: make order_items one row per order before the join. Then the revenue is 160.50 again.

A failed check is a question, not an alarm. Ask first: is this wrong, or is it normal?

Here is the NULL amount of order 105 again, split by status:

SELECT status,
       COUNT(*) AS orders,
       COUNT(*) - COUNT(amount) AS no_amount
FROM orders
GROUP BY status
ORDER BY status;

Ask three questions about every failed check:

1. Is it normal for this data? Then change the rule, not the data.
2. Is the rule too strict? A check that fails on thousands of rows is often a wrong rule.
3. Does it change a number people use? Fix that first.

Each exercise opens in the SQL workspace. The workspace tables are marketplace tables, so the names differ a little from the shop. Read the table first.

Say the rule in one sentence. Guess if the check will pass or fail. Then run it.