Data Quality Checks with SQL

Every report in the last three lessons assumed the data underneath was clean. It almost never is, not entirely. A new hire gets entered twice, a department's city is left blank, a project points at a department that was later deleted. None of these raises an error, because each is perfectly legal as far as the schema is concerned, so they sail into your reports and quietly distort every number built on top of them.

A data quality check is a SELECT written to find problems rather than to answer a business question. Its result set is a list of offenders: the duplicate rows, the missing values, the broken references. A check that returns zero rows is not a failure, it is the best possible outcome, because it means the data passed. This lesson teaches the three checks that catch the most common corruption: missing values, duplicates, and orphans.

flowchart LR
  Q["Run the check<br/>SELECT ... WHERE ..."] --> R{"rows returned?"}
  R -->|"yes"| B["offenders listed<br/>data needs fixing"]
  R -->|"no, zero rows"| G["clean<br/>the check passed"]
  style G fill:#0e7490,color:#fff
  style B fill:#b45309,color:#fff

A quality check splits on its result: any rows are offenders to fix; zero rows means the data passed.

Missing values: test for absence

Missing data is stored as NULL, and NULL does not behave like a value. A department with no city has city IS NULL, not city = '' and certainly not city = NULL. The check for a blank is therefore SELECT name FROM departments WHERE city IS NULL, which returns exactly the rows where the value is unknown. Run it against this company and Research appears, the one department whose location was never filled in.

The habit is to pair every column a report depends on with an IS NULL check before you trust the report. If five percent of salaries were NULL, an AVG(salary) average would silently average only the known ninety-five percent and still look perfectly healthy. Checking SELECT COUNT(*) FROM employees WHERE salary IS NULL first tells you whether the average even applies to everyone.

Completeness check: which departments are missing a city. Research turns up.

SELECT name AS department, city
FROM departments
WHERE city IS NULL;

Zero rows is the answer you want

This is the mindset shift that sets quality work apart from reporting. A reporting query that returns nothing feels broken; a quality check that returns nothing has succeeded. When you run the missing-city check against a clean import and get an empty result, that emptiness is the deliverable, the evidence that the check passed.

Keep the two purposes straight as you read on. The runnable examples in this lesson sometimes return a row or two because this small dataset has a few deliberate gaps; in production the same queries usually return nothing, and that nothing is exactly what you hope to see before a report ships. A non-empty result is not a query error, it is a lead to investigate.

flowchart TD
  R["all rows"] --> G["GROUP BY the key column"]
  G --> C["COUNT(*) per group"]
  C --> H{"HAVING COUNT(*) > 1"}
  H -->|"yes"| K["keep this group<br/>it is a duplicate"]
  H -->|"no"| D["drop it<br/>the value is unique"]
  style K fill:#b45309,color:#fff
  style D fill:#1e293b,color:#fff

Duplicate detection: group by the key, count each group, keep only those with more than one member.

Duplicates: group the key, keep the repeats

A duplicate is two rows that should be one. The detection pattern never changes: take the column, or columns, that ought to be unique, group by them, and keep only the groups with more than one member using HAVING COUNT(*) > 1. The groups that survive are your offenders.

SELECT name
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

Run that and you get nothing, because every employee name here is distinct, the check passing. To practise on a row you know is bad, attach one with UNION ALL, which stacks extra rows onto a result for testing. Adding a second identical name and re-running the same grouping turns up the collision. The grouping logic is identical whether the duplicate is real or simulated; only the data underneath differs.

The same pattern with a duplicate stitched in for testing: Ada Nilsen now appears twice.

WITH checked AS (
  SELECT name FROM employees
  UNION ALL
  SELECT 'Ada Nilsen'
)
SELECT name AS duplicated_name
FROM checked
GROUP BY name
HAVING COUNT(*) > 1;

flowchart LR
  C["child row<br/>employee"] -->|"LEFT JOIN"| P{"parent found?"}
  P -->|"yes"| M["matched<br/>drop it"]
  P -->|"no, parent is NULL"| O["orphan<br/>keep it"]
  style O fill:#b45309,color:#fff
  style M fill:#1e293b,color:#fff

An anti-join keeps the child rows whose LEFT JOIN found no matching parent.

Orphans: the anti-join

A referential orphan is a row whose foreign key points at nothing, like an employee assigned to a department that does not exist. The detection is an anti-join: LEFT JOIN the child to its parent, then keep only the rows where the parent came back NULL. Those are the children with no matching parent.

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE e.department_id IS NOT NULL
  AND d.id IS NULL;

The two conditions in the WHERE matter together. d.id IS NULL flags the unmatched rows, while e.department_id IS NOT NULL excludes the merely unassigned, like Omar, whose department is genuinely blank rather than broken. An unassigned row is a completeness gap from the previous check; an orphan is a broken reference. They need different fixes, so the checks keep them apart.

The orphan check with a broken row attached: dept 999 exists nowhere, so the ghost hire is flagged.

WITH checked AS (
  SELECT id, name, department_id FROM employees
  UNION ALL
  SELECT 90, 'Ghost Hire', 999
)
SELECT c.name AS orphan
FROM checked c
LEFT JOIN departments d ON c.department_id = d.id
WHERE c.department_id IS NOT NULL
  AND d.id IS NULL;

Checks find problems; they do not fix them

A quality check tells you that something is wrong and where. It does not correct it. The duplicate it lists still has to be merged or deleted; the orphan still has to be re-linked or removed. Those are UPDATE and DELETE jobs, and they belong to the modification lessons, with their own safety habits around the WHERE clause.

What SQL gives you here is a repeatable detector you can run after every import or nightly, watching the offender list shrink toward zero. A team that runs the same three checks, missing values, duplicates, and orphans, after each load catches corruption at the door instead of discovering it weeks later inside a misread report. Write the checks once, run them often, and chase every non-empty result down before the data reaches anyone else.

Exercise

Write a completeness check that lists the name of every department whose city is missing. Use the operator that actually tests for absence.

SELECT name AS department
FROM departments
-- which operator tests for a missing value?
;

SELECT name AS department FROM departments WHERE city IS NULL;

Exercise

A second copy of an employee was entered by mistake, stitched into the checked CTE below for testing. Find the name that now appears more than once. Group by name and keep only groups with COUNT(*) greater than 1.

WITH checked AS (
  SELECT name FROM employees
  UNION ALL
  SELECT 'Ada Nilsen'
)
SELECT name AS duplicated_name
FROM checked
-- add your GROUP BY and HAVING here
;

WITH checked AS (SELECT name FROM employees UNION ALL SELECT 'Ada Nilsen') SELECT name AS duplicated_name FROM checked GROUP BY name HAVING COUNT(*) > 1;

Exercise

This check is meant to find departments missing a city, but WHERE city = '' matches only a literal empty string and silently skips real NULLs, so it finds nothing. Fix the operator so Research turns up.

SELECT name AS department
FROM departments
WHERE city = ''
;

SELECT name AS department FROM departments WHERE city IS NULL;

Exercise

Transfer skill: an import introduced a broken row, attached below as a row whose department is 999, which exists nowhere. Write an anti-join that LEFT JOINs checked to departments and returns the name of the orphan, the row whose department_id is set but matches no department. Exclude rows whose department_id is itself missing.

WITH checked AS (
  SELECT id, name, department_id FROM employees
  UNION ALL
  SELECT 90, 'Ghost Hire', 999
)
SELECT c.name AS orphan
FROM checked c
-- LEFT JOIN departments and keep the unmatched rows
;

WITH checked AS (SELECT id, name, department_id FROM employees UNION ALL SELECT 90, 'Ghost Hire', 999) SELECT c.name AS orphan FROM checked c LEFT JOIN departments d ON c.department_id = d.id WHERE c.department_id IS NOT NULL AND d.id IS NULL;

Exercise

You run a data-quality check and it returns zero rows. What does that mean?

Recap

A data quality check is a SELECT that finds problems; zero rows means the data passed.
Test missing values with IS NULL, never = NULL or = ''.
Detect duplicates with GROUP BY key HAVING COUNT(*) > 1; the surviving groups are the offenders.
Find referential orphans with a LEFT JOIN anti-join, keeping rows where the parent is NULL but the child key is not.
COUNT(*) counts rows; COUNT(col) skips NULLs, so pick the one your check actually means.

Next you will make these checks and reports reusable, writing queries where a single value can be swapped to re-run them for any department.

Data Quality Checks with SQL

Missing values: test for absence

Zero rows is the answer you want

Duplicates: group the key, keep the repeats

Orphans: the anti-join

Checks find problems; they do not fix them

Recap

Checkpoint quiz

Go deeper — technical resources