Data-Quality Checks

Karim is presenting 2025 results to the board next week. His request is short and serious: “Before these numbers go on a slide, how sure are we about the data?” Every query you’ve written assumes the data means what the documentation says. This lesson is about checking that assumption systematically, so that “I’m confident” is backed by queries, not feelings.

A check is a query that returns the bad rows

The most useful habit in data quality is to phrase each rule as a query that returns the rows breaking it. An empty result means the rule holds; anything else is evidence you can show someone. Here’s the rule “a product sits in a sub-category, never directly in a top-level one”, from the data documentation:

SELECT p.product_id, p.name, c.name AS category
FROM products AS p
JOIN categories AS c ON c.category_id = p.category_id
WHERE c.parent_id IS NULL;

Two rows: the Picnic Blanket filed directly under Outdoor and the Bamboo Cutting Board under Kitchen. That explains the odd “Kitchen” and “Outdoor” rows in the category revenue table from Section 2. A check that returns rows you can name is far more persuasive than a count.

The five families of checks

Most rules fall into five families. Running at least one check from each catches the large majority of problems:

Database constraints (PRIMARY KEY, NOT NULL, CHECK, FOREIGN KEY) enforce some of these at write time, if they were declared and, in SQLite’s case, if foreign-key enforcement was switched on. Analysts rarely control that, and consistency rules across tables usually can’t be expressed as constraints at all. So you check.

A suite you can rerun

One check is a query; a dozen checks are a suite. Give each a name, count its failures, and stack them with UNION ALL:

SELECT 'customers: duplicate email (any case)' AS check_name, COUNT(*) AS failures
FROM (SELECT LOWER(email) FROM customers GROUP BY LOWER(email) HAVING COUNT(*) > 1)
UNION ALL
SELECT 'order_items: no matching order', COUNT(*)
FROM order_items AS oi
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.order_id = oi.order_id)
UNION ALL
SELECT 'order_items: discount outside 0-0.25', COUNT(*)
FROM order_items
WHERE discount NOT BETWEEN 0 AND 0.25
UNION ALL
SELECT 'products: filed under a top-level category', COUNT(*)
FROM products AS p
JOIN categories AS c ON c.category_id = p.category_id
WHERE c.parent_id IS NULL
UNION ALL
SELECT 'orders: returned but no return record', COUNT(*)
FROM orders AS o
WHERE o.status = 'returned'
  AND NOT EXISTS (
    SELECT 1 FROM order_items AS oi
    JOIN returns AS r ON r.order_item_id = oi.order_item_id
    WHERE oi.order_id = o.order_id)
UNION ALL
SELECT 'tickets: rated while still open', COUNT(*)
FROM support_tickets
WHERE closed_at IS NULL AND satisfaction IS NOT NULL;

Three checks fail. Each compound part must return the same number of columns; the first SELECT names them. UNION ALL keeps every row, whereas UNION would deduplicate, which costs time and could merge two checks that happen to share a name and count. Save the suite next to your analysis and run it every time the data is refreshed; a check that passed in October can fail in December.

Failures are questions, not verdicts

Now read the failures like an analyst, not a linter:

• Two returned orders without return records were both placed in the last two weeks of December 2025. That smells like a process delay (the return is logged when the parcel arrives back), not corruption. Ask operations.
• Three open tickets with a satisfaction score were opened in the last days of December. Perhaps the survey goes out before the ticket is formally closed. Ask Julia.
• Two misfiled products are almost certainly a catalogue error. Report it to whoever maintains the catalogue, and in the meantime decide how your category report treats them.

There’s one more failure hiding in this data, and it’s the most interesting one: products that appear in orders before their recorded launch date. Finding it is your exercise.

Next you’ll look at the other side of trust, performance: why some of these queries are instant and others crawl, and how to see what the database is actually doing.