Data quality checks to run before trusting a dataset

Most bad analysis isn’t bad math. It’s correct math on data that doesn’t mean what you assumed. These seven checks take a few minutes and catch the problems that would otherwise surface in the middle of a meeting.

1. Confirm the row count

Compare the count with what the source system says it exported. If they don’t match, you’re looking at a truncated file, a filter nobody told you about, or a header row counted as data.

SELECT COUNT(*) AS rows FROM orders;

2. Check for duplicate keys

Columns that should be unique often aren’t. Every join on a duplicated key multiplies rows, and you won’t notice until the totals look odd. Check before you join.

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

3. Look at nulls per column

Null rates tell you which columns you can safely aggregate. If a column is 60% null, its average describes a minority of your rows and still looks convincing on a slide.

SELECT
  COUNT(*)                                   AS rows,
  COUNT(*) - COUNT(revenue)                  AS revenue_nulls,
  COUNT(*) - COUNT(order_date)               AS date_nulls,
  COUNT(*) - COUNT(customer_id)              AS customer_nulls
FROM orders;

4. Catch numbers that arrived as text

This is the most common parsing error, and the most damaging. If a numeric column came in as text, sorting and sums are both wrong, and neither throws an error.

Try casting it. Any value that fails the cast is one you need to look at.

SELECT revenue
FROM orders
WHERE TRY_CAST(revenue AS DOUBLE) IS NULL
  AND revenue IS NOT NULL;

5. Range-check anything physical

Negative quantities, dates in 1899 or 2087, percentages over 100. Each one points to a problem upstream. Watch for placeholders like -1 or 9999 standing in for "unknown", because they slip right into your totals.

SELECT *
FROM orders
WHERE quantity < 0
   OR revenue < 0
   OR order_date < DATE '2000-01-01'
   OR order_date > CURRENT_DATE;

6. Review categorical values

List the distinct values of every column you plan to group by. You’ll find the variants that split one category into several. As far as GROUP BY is concerned, "UK", "U.K.", "uk", and " UK" are four different groups.

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

7. Check the date range is what you expect

Look at the first and last date. It catches partial exports and timezone shifts. If a full-year report stops in October, the extract got cut off. That’s not a trend.

SELECT
  MIN(order_date) AS first_order,
  MAX(order_date) AS last_order,
  COUNT(DISTINCT order_date) AS distinct_days
FROM orders;

Run them all at once

NoEgress profiles every column when you open a file: type, null count, distinct values, min and max. That covers most of these checks at a glance and shows you which columns deserve a closer look.