← All Kits · SQL Kit

The Data Quality Checks to Run Before Anybody Sees the Numbers

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

Every analyst has had the meeting where somebody else finds the error. It is almost never a subtle one. It is a duplicated key, a missing week, or a category that got renamed upstream, and all of them are caught by checks that take ten minutes to run.

What you do: run these eight before you build anything, store the results, and compare them against the last run.

The short version. Ten minutes at the start beats an hour in the meeting, every time.

1. Row count against the source

SELECT COUNT(*) FROM orders;

Compare with the source system's own count. This is the single most valuable check and the most often skipped. It catches truncated files, failed loads, and a filter somebody added last month. The Excel version of the same trap is in the row limit: a file cut at 1,048,576 rows looks entirely normal.

2. Is the key actually unique

SELECT order_id, COUNT(*) AS n
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY n DESC;

If this returns anything, every join through order_id is multiplying rows and every sum downstream is inflated. Test the key you are about to join on, not just the primary key, since the join key is often a business column with no constraint on it.

3. Nulls per column

SELECT COUNT(*) AS rows,
       SUM(CASE WHEN region   IS NULL THEN 1 ELSE 0 END) AS null_region,
       SUM(CASE WHEN amount   IS NULL THEN 1 ELSE 0 END) AS null_amount,
       SUM(CASE WHEN order_dt IS NULL THEN 1 ELSE 0 END) AS null_date
FROM orders;

What matters is not the number today but the change since last load. A column that has always been 2 percent null and is suddenly 30 percent null means something changed upstream, and nobody will have told you.

4. Ranges and impossible values

SELECT MIN(amount), MAX(amount), AVG(amount),
       SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END) AS negatives,
       SUM(CASE WHEN amount = 0 THEN 1 ELSE 0 END) AS zeros
FROM orders;

Look for the placeholder values that systems use for missing: -1, 0, 999, 9999, and the date 1900-01-01. Each of those will be averaged into your results as if it were real. A quantity of 99,999 is not an outlier, it is a data entry convention.

5. Date sanity

SELECT MIN(order_dt), MAX(order_dt) FROM orders;

SELECT strftime('%Y-%m', order_dt) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

The second query is the one to read carefully. A missing month, or one with a tenth of the usual volume, is a failed load rather than a quiet trading period. Future dates and dates before the business existed both indicate parsing problems, usually a day-first file read as month-first.

6. Referential integrity

SELECT COUNT(*) AS orphan_orders
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c
                  WHERE c.customer_id = o.customer_id);

Orphans vanish from any inner join, silently, taking their revenue with them. That is why two reports on the same data can differ by a few percent with neither being obviously wrong. NOT EXISTS rather than NOT IN, for the NULL reason in the anti-join.

7. Category drift

SELECT region, COUNT(*) FROM orders GROUP BY region ORDER BY 2 DESC;

Read the whole list, including the small values at the bottom. New categories appear when a system is reconfigured; old ones stop appearing when a rename happens upstream. A grouped report will happily show two rows where there should be one, and it will look like a real split.

8. Reconcile one total to something external

The finance ledger, last month's published report, the source system's own dashboard. One number, agreed to the penny or explained. Everything above tests the data against itself; this is the only check that tests it against the world.

The ten-minute routine

How to apply this to your own work

1. Save the eight queries as one script per source, with the table names filled in.
2. Run it before you build, not after somebody asks a question.
3. Write the results into a log table with a date, so drift becomes visible.
4. Put the row count and the reconciliation into the report itself, as a footnote. It changes how the report is received.
5. When a check fails, tell the person who owns the source. Silent workarounds mean the same failure arrives every month forever.

The one habit to keep

Check before you build. The work is identical either way; the difference is whether you find the problem or somebody else does, and that difference is most of what people mean when they say an analyst is reliable.

Which of these eight would fail on the data you used most recently?