SQL for Analysis · 16 min · 190 XP

Checking the data before you trust it

Find duplicates, orphans, bad values and totals that don't reconcile before you report.

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.

The shop, plus the payments export
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)
The payments export17 rows
idorder_idcreated_atamountstatus
11012026-01-04 10:12:00173succeeded
21022026-01-09 18:40:00180succeeded
31032026-01-15 09:05:0070succeeded
41032026-01-19 11:30:00-70refunded
51042026-01-22 20:14:00195Succeeded
61052026-02-02 08:55:0095succeeded
71062026-02-06 13:20:00154succeeded
81062026-02-06 13:20:00154succeeded
91072026-02-11 16:02:00185failed
1010819/02/2026 12:4760succeeded
111092026-02-25 07:31:00173succeeded
121102026-03-03 19:45:0077succeeded
131112026-03-08 10:10:0095succeeded
141112026-03-10 15:00:00-95refunded
151122026-03-12 21:05:00180succeeded
161132026-03-18 12:00:00NULLsucceeded
179992026-03-20 14:22:0049succeeded
The query
SELECT * FROM payments ORDER BY id;

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.

sql
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.

sql
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.

sql
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:

sql
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. Compare them one order at a time instead. expected is what each order should have brought in: its total if it's paid, nothing if it isn't. Seven orders disagree:

Every order whose payments don't match
order_idstatusexpectedcollecteddifference
103refunded07070
106paid154308154
111refunded09595
112paid197180-17
113paid250-25
114paid3240-324
999no such order04949

106 is the duplicate. 103 and 111 were charged and later refunded, so they aren't revenue. 112 was charged 17 too little, 113's amount is missing, 114 was never charged, and 999 is the orphan. The differences add up to the 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.

Run the checks before the analysis and keep them. When the next export arrives, run them again first. A check that returned nothing last month is exactly the one that catches the new bug.

Loading your workspace…