SQL Data Quality Checks
A report can be syntactically correct and still describe inconsistent data. Add reconciliation queries for important invariants, such as an order total matching its line items. Make the result show exceptions that someone can investigate.
Understand the idea
This query totals each order's line amounts and compares them with the stored header amount. A LEFT JOIN keeps orders with missing lines in scope. COALESCE treats no lines as a zero total for this particular check. The expected result is empty, but an empty result only proves this one invariant for the rows tested.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT o.order_id, o.total_cents,
COALESCE(SUM(i.quantity * i.unit_price_cents), 0) AS line_cents
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY o.order_id, o.total_cents
HAVING o.total_cents <> COALESCE(SUM(i.quantity * i.unit_price_cents), 0)
ORDER BY o.order_id;
Read the result
No rows are returned because all four order headers match their lines. Change one header total in a disposable run and the mismatched order should appear.
Try one change
Add a deliberate one-cent mismatch, run the check, then explain how a correction would be reviewed rather than silently overwriting the amount.