23.2 Testing SQL & Data Quality — The Complete SQL Course

SQL review turns assumptions into comments. SQL tests turn those assumptions into queries that fail loudly before a dashboard, migration, or meeting depends on them.

In 23.1, style made SQL reviewable. Now you will make review assumptions executable: fixture tests for query behavior, data quality assertions for source tables, and regression checks for optimized SQL.

A Test Is A Query With A Contract

A useful SQL test has a crisp contract. Two shapes cover most of the work:

zero bad rows — the query returns only violations, so (0 rows) means pass
one summary row — the query returns pass or fail with the evidence beside it

Avoid "looks good" checks. A test should say exactly what would be wrong.

For query unit tests, build tiny fixture tables with the cases you care about. The fixture below is deliberately sharp: duplicate customer ids, an orphan order, a stale newest order date, a negative amount, and an invalid status. The query under test reports paid order counts and revenue by customer row, then compares the actual result to an expected result with EXCEPT.

CREATE TABLE quality_customers (
  customer_id int,
  email text NOT NULL,
  signup_on date NOT NULL
);
 
CREATE TABLE quality_orders (
  order_id int,
  customer_id int NOT NULL,
  created_on date NOT NULL,
  status text NOT NULL,
  amount numeric(8,2) NOT NULL
);
 
INSERT INTO quality_customers (customer_id, email, signup_on) VALUES
  (1, 'ava@example.com', DATE '2026-06-01'),
  (2, 'ben@example.com', DATE '2026-06-02'),
  (2, 'ben-copy@example.com', DATE '2026-06-03'),
  (3, 'chloe@example.com', DATE '2026-06-04');
 
INSERT INTO quality_orders (order_id, customer_id, created_on, status, amount) VALUES
  (1001, 1, DATE '2026-06-12', 'paid', 30.00),
  (1002, 2, DATE '2026-06-13', 'paid', 18.50),
  (1003, 3, DATE '2026-06-01', 'paid', -7.00),
  (1004, 4, DATE '2026-06-14', 'paid', 22.00),
  (1005, 3, DATE '2026-06-02', 'draft', 12.00),
  (1006, 1, DATE '2026-06-10', 'cancelled', 0.00);
 
WITH actual AS (
  SELECT qc.customer_id,
         qc.email,
         count(qo.order_id) FILTER (WHERE qo.status = 'paid') AS paid_orders,
         COALESCE(
           sum(qo.amount) FILTER (WHERE qo.status = 'paid'),
           0
         ) AS paid_revenue
  FROM quality_customers qc
  LEFT JOIN quality_orders qo
    ON qo.customer_id = qc.customer_id
  GROUP BY qc.customer_id, qc.email
),
expected(customer_id, email, paid_orders, paid_revenue) AS (
  VALUES
    (1, 'ava@example.com', 1::bigint, 30.00::numeric),
    (2, 'ben-copy@example.com', 1::bigint, 18.50::numeric),
    (2, 'ben@example.com', 1::bigint, 18.50::numeric),
    (3, 'chloe@example.com', 1::bigint, -7.00::numeric)
),
actual_not_expected AS (
  SELECT a.customer_id, a.email, a.paid_orders, a.paid_revenue
  FROM actual a
  EXCEPT
  SELECT e.customer_id, e.email, e.paid_orders, e.paid_revenue
  FROM expected e
),
expected_not_actual AS (
  SELECT e.customer_id, e.email, e.paid_orders, e.paid_revenue
  FROM expected e
  EXCEPT
  SELECT a.customer_id, a.email, a.paid_orders, a.paid_revenue
  FROM actual a
),
diff AS (
  SELECT 'actual_not_expected' AS problem,
         ane.customer_id,
         ane.email,
         ane.paid_orders,
         ane.paid_revenue
  FROM actual_not_expected ane
  UNION ALL
  SELECT 'expected_not_actual' AS problem,
         ena.customer_id,
         ena.email,
         ena.paid_orders,
         ena.paid_revenue
  FROM expected_not_actual ena
)
SELECT d.problem,
       d.customer_id,
       d.email,
       d.paid_orders,
       d.paid_revenue
FROM diff d
ORDER BY d.problem, d.customer_id, d.email;

CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 6
 problem | customer_id | email | paid_orders | paid_revenue
---------+-------------+-------+-------------+--------------
(0 rows)

That (0 rows) is not "nothing happened." It means the actual output matched the expected output exactly. If an optimization, join change, or filter edit changes one row, the diff query returns evidence.

Data Quality Assertions

Unit tests prove a query behaves on a known fixture. Data quality assertions prove the input still satisfies the promises the query depends on.

Start with uniqueness. If customer_id is supposed to identify one customer row, duplicates are bad rows:

SELECT qc.customer_id,
       count(*) AS rows_seen
FROM quality_customers qc
GROUP BY qc.customer_id
HAVING count(*) > 1
ORDER BY qc.customer_id;

 customer_id | rows_seen
-------------+-----------
           2 |         2
(1 row)

Then check referential integrity. If every order should belong to a known customer, an orphan check returns the missing links:

SELECT qo.order_id,
       qo.customer_id
FROM quality_orders qo
LEFT JOIN quality_customers qc
  ON qc.customer_id = qo.customer_id
WHERE qc.customer_id IS NULL
ORDER BY qo.order_id;

 order_id | customer_id
----------+-------------
     1004 |           4
(1 row)

Freshness should use a fixed clock in tests. Do not let a test depend on today's wall clock unless the pipeline is explicitly designed for it:

WITH freshness AS (
  SELECT max(qo.created_on) AS newest_order_on,
         DATE '2026-06-17' AS checked_on
  FROM quality_orders qo
)
SELECT CASE
         WHEN f.newest_order_on >= f.checked_on - 1 THEN 'pass'
         ELSE 'fail'
       END AS freshness_status,
       f.newest_order_on,
       f.checked_on - f.newest_order_on AS days_old
FROM freshness f;

 freshness_status | newest_order_on | days_old
------------------+-----------------+----------
 fail             | 2026-06-14      |        3
(1 row)

Finally, accepted ranges and accepted values catch values that are syntactically valid but semantically impossible:

SELECT qo.order_id,
       qo.status,
       qo.amount
FROM quality_orders qo
WHERE qo.amount < 0
   OR qo.status NOT IN ('paid', 'cancelled', 'refunded')
ORDER BY qo.order_id;

 order_id | status | amount
----------+--------+--------
     1003 | paid   |  -7.00
     1005 | draft  |  12.00
(2 rows)

These are not vague audits. Each query either returns the bad rows to fix or returns a summary that says exactly why the table is stale.

dbt: Fail The Pipeline, Not The Meeting

In a dbt project, uniqueness, not-null, accepted-values, and relationship checks usually live beside models as data tests. The browser lab is running PostgreSQL, not dbt, so treat this as a structural transcript:

$ dbt test --select quality_orders
 
not_null_quality_orders_order_id ................................ PASS
unique_quality_orders_order_id .................................. PASS
relationships_quality_orders_customer_id__quality_customers ..... FAIL 1 row
accepted_values_quality_orders_status ........................... FAIL 1 row
 
Completed with 2 errors.

Source freshness is a separate dbt check because it asks whether a source table is recent enough:

$ dbt source freshness --select source:raw.quality_orders
 
source:raw.quality_orders freshness ............................. FAIL
  newest loaded row: 2026-06-14
  checked at:         2026-06-17
  age:                3 days
 
Completed with 1 error.

The philosophy matters more than the tool name: fail the pipeline, not the meeting. If a relationship, uniqueness, freshness, or accepted-values check fails at build time, the bad report should never become tomorrow's slide.

Regression-Test The Optimized Query

When you tune SQL, you need two proofs: the new query is fast enough to justify the change, and the new query returns the same answer. Performance lessons taught you how to inspect plans. Regression tests prove the result did not move.

Here are two equivalent daily paid-revenue queries. The second version changes the shape, so compare the outputs both ways:

WITH old_report AS (
  SELECT qo.created_on,
         sum(qo.amount) AS paid_revenue
  FROM quality_orders qo
  WHERE qo.status = 'paid'
  GROUP BY qo.created_on
),
new_report AS (
  SELECT qo.created_on,
         sum(qo.amount) FILTER (WHERE qo.status = 'paid') AS paid_revenue
  FROM quality_orders qo
  GROUP BY qo.created_on
  HAVING count(*) FILTER (WHERE qo.status = 'paid') > 0
),
old_not_new AS (
  SELECT old_report.created_on, old_report.paid_revenue
  FROM old_report
  EXCEPT
  SELECT new_report.created_on, new_report.paid_revenue
  FROM new_report
),
new_not_old AS (
  SELECT new_report.created_on, new_report.paid_revenue
  FROM new_report
  EXCEPT
  SELECT old_report.created_on, old_report.paid_revenue
  FROM old_report
),
diff AS (
  SELECT 'old_not_new' AS problem,
         onn.created_on,
         onn.paid_revenue
  FROM old_not_new onn
  UNION ALL
  SELECT 'new_not_old' AS problem,
         nno.created_on,
         nno.paid_revenue
  FROM new_not_old nno
)
SELECT d.problem,
       d.created_on,
       d.paid_revenue
FROM diff d
ORDER BY d.problem, d.created_on;

 problem | created_on | paid_revenue
---------+------------+--------------
(0 rows)

That zero-row difference does not prove the new query is fast. It proves the rewrite preserved the result on this fixture. Keep both kinds of evidence: plan evidence for speed, regression evidence for correctness.

Try the checks below. The starter code rebuilds the same fixture, runs one assertion that returns failing rows, and then runs the regression diff.

sql — playgroundlive

CREATE TABLE quality_customers (
customer_id int,
email text NOT NULL,
signup_on date NOT NULL
);

CREATE TABLE quality_orders (
order_id int,
customer_id int NOT NULL,
created_on date NOT NULL,
status text NOT NULL,
amount numeric(8,2) NOT NULL
);

INSERT INTO quality_customers (customer_id, email, signup_on) VALUES
(1, 'ava@example.com', DATE '2026-06-01'),
(2, 'ben@example.com', DATE '2026-06-02'),
(2, 'ben-copy@example.com', DATE '2026-06-03'),
(3, 'chloe@example.com', DATE '2026-06-04');

INSERT INTO quality_orders (order_id, customer_id, created_on, status, amount) VALUES
(1001, 1, DATE '2026-06-12', 'paid', 30.00),
(1002, 2, DATE '2026-06-13', 'paid', 18.50),
(1003, 3, DATE '2026-06-01', 'paid', -7.00),
(1004, 4, DATE '2026-06-14', 'paid', 22.00),
(1005, 3, DATE '2026-06-02', 'draft', 12.00),
(1006, 1, DATE '2026-06-10', 'cancelled', 0.00);

SELECT qc.customer_id,
     count(*) AS rows_seen
FROM quality_customers qc
GROUP BY qc.customer_id
HAVING count(*) > 1
ORDER BY qc.customer_id;

WITH old_report AS (
SELECT qo.created_on,
       sum(qo.amount) AS paid_revenue
FROM quality_orders qo
WHERE qo.status = 'paid'
GROUP BY qo.created_on
),
new_report AS (
SELECT qo.created_on,
       sum(qo.amount) FILTER (WHERE qo.status = 'paid') AS paid_revenue
FROM quality_orders qo
GROUP BY qo.created_on
HAVING count(*) FILTER (WHERE qo.status = 'paid') > 0
),
old_not_new AS (
SELECT old_report.created_on, old_report.paid_revenue
FROM old_report
EXCEPT
SELECT new_report.created_on, new_report.paid_revenue
FROM new_report
),
new_not_old AS (
SELECT new_report.created_on, new_report.paid_revenue
FROM new_report
EXCEPT
SELECT old_report.created_on, old_report.paid_revenue
FROM old_report
),
diff AS (
SELECT 'old_not_new' AS problem,
       onn.created_on,
       onn.paid_revenue
FROM old_not_new onn
UNION ALL
SELECT 'new_not_old' AS problem,
       nno.created_on,
       nno.paid_revenue
FROM new_not_old nno
)
SELECT d.problem,
     d.created_on,
     d.paid_revenue
FROM diff d
ORDER BY d.problem, d.created_on;

-- try it: this accepted-values assertion returns the invalid rows
-- SELECT qo.order_id, qo.status, qo.amount
-- FROM quality_orders qo
-- WHERE qo.amount < 0
--    OR qo.status NOT IN ('paid', 'cancelled', 'refunded')
-- ORDER BY qo.order_id;

-- break it: uncomment — this misspelled column creates a real SQL error
-- SELECT qo.created_at FROM quality_orders qo;

⌘/Ctrl + Enter to run

Tests and checks are not ceremony. They are part of becoming the person other people trust with data: the one who can explain the assumption, write the query, and make the database prove it.

Checkpoint

Answer all three to mark this lesson complete

Testing SQL & Data Quality

A Test Is A Query With A Contract

Data Quality Assertions

dbt: Fail The Pipeline, Not The Meeting

Regression-Test The Optimized Query

Checkpoint ✦

Checkpoint