Checking the data before you trust it

Every query so far has trusted the data. Real data arrives from other systems, and each one has its own bugs. This lesson adds one such system to the shop: an export from its card provider, with one row per charge or refund. Before anyone reports revenue from it, it needs checking.

customers   (id, name, city, country, signup_date)
products    (id, name, category, price)
orders      (id, customer_id, order_date, status, shipping)
order_items (order_id, product_id, quantity, unit_price)
payments    (id, order_id, created_at, amount, status)

A data check is a query that should return nothing. If it returns rows, those rows are the problem. There are five kinds worth running on any new table, and then one more that ties it to the rest of the data.

1. Allowed values. Group by any column that should hold a short list of values, and read the list.

SELECT status, COUNT(*) AS payments
FROM payments
GROUP BY status;

-- status      payments
-- Succeeded   1
-- failed      1
-- refunded    2
-- succeeded   12
-- succeeded   1

Two rows both seem to say succeeded. The second one is 'succeeded ', with a trailing space you can't see in the output. With the capital-S version, that's 14 successful payments spelled three ways, and WHERE status = 'succeeded' finds 12 of them. LOWER(TRIM(status)) brings them back together.

2. Duplicates. Decide what should be unique, then look for groups with more than one row. Here each payment has its own id, so group by everything else.

SELECT order_id, created_at, amount, COUNT(*) AS copies
FROM payments
GROUP BY order_id, created_at, amount, status
HAVING COUNT(*) > 1;

-- order_id  created_at           amount  copies
-- 106       2026-02-06 13:20:00  154.0   2

Payments 7 and 8 are the same charge, recorded twice when the provider's notification arrived twice. Sum the export and order 106 has paid 308.0 for a 154.0 order.

3. Orphans. A row that points at something that doesn't exist. The anti-join from SQL Foundations' joins lesson finds them: LEFT JOIN the table it should match, and keep the rows that found nothing.

SELECT p.id, p.order_id
FROM payments AS p
LEFT JOIN orders AS o ON o.id = p.order_id
WHERE o.id IS NULL;

-- id  order_id
-- 17  999

4. Missing values. COUNT(*) counts rows and COUNT(amount) counts amounts, so the difference is how many are NULL. Here it's 17 against 16. That matters because SUM skips NULLs without a word: payment 16's missing amount just vanishes from the total.

5. Formats. A date that isn't YYYY-MM-DD sorts and filters as the wrong date. GLOB matches a pattern of characters, so this finds any row that doesn't start with one:

SELECT id, created_at
FROM payments
WHERE created_at NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]*';

-- id  created_at
-- 10  19/02/2026 12:47

As text, '19/02/2026' sorts before '2', so a filter for February finds 5 payments instead of 6. Don't fix a row like this by guessing, though. 19/02 can only mean the 19th of February, but 04/03 could be April or March. Ask where the export came from.

6. Reconciliation. Compare the new data with something you already trust. The succeeded charges total 1,655. The paid orders total 1,653: 1,628 in items plus 25 in shipping. That's so close that it looks like rounding.

It isn't. The 1,655 includes the duplicate (+154), the orphan (+49) and the charges on the two orders later refunded (+165). It's missing order 114, which has no payment (−324), and order 113's amount (−25). And order 112 was charged 17 less than its total. Those errors happen to cancel out to 2. A total that matches proves very little. Reconcile row by row, at the grain where things should match, which here is one row per order.

Loading your workspace…