← All lessons
Warehouses and pipelines · Lesson 7 of 7

Data quality checks

Bad data is worse than no data: people make decisions on it. Data quality checks catch problems before anyone sees a wrong number. They fall into a few families.

Freshness: is the data as recent as it should be? A dashboard that quietly stopped updating on Tuesday is a common and embarrassing failure.

SELECT MAX(paid_at) AS latest_payment, COUNT(*) AS payments
FROM payments;
Returns
latest_payment | payments
---------------+---------
2024-10-12     | 11
(1 row)

Volume: is the number of rows in the usual range? Zero orders on a Monday, or three times the usual, usually means a broken load, not a real change. Compare with the same day last week, or a rolling average.

Schema: did a column appear, vanish or change type? Validity: are values in range and in the allowed set (amounts not negative, currency codes known, dates not in the future)? Uniqueness and relationships: are keys unique and do foreign keys point at real rows? These are the tests from the models lesson.

Reconciliation: does the warehouse agree with the source? Compare counts and totals per day between the two. Within one source, cross-check: total payments for shipped orders should match the order totals.

SELECT p.order_id, p.amount, p.currency
FROM payments p
WHERE p.amount < 0 OR p.currency IS NULL OR p.currency <> 'USD'
ORDER BY p.order_id;
Returns
order_id | amount | currency
---------+--------+---------
106      | 59     | usd
107      | -80    | USD
109      | 50     | NULL
(3 rows)

Decide what each failure does. Some should stop the pipeline (duplicate keys in a finance table); some should only warn (a slightly low row count). Send alerts to a named owner, not a shared inbox, and record failures so you can see trends. Tools such as dbt tests, Great Expectations and Soda give you a framework, but a check is just a query and a threshold.

Prevent as well as detect: agree a data contract with the team that produces the data (the columns, types, meanings and how changes will be announced), so a renamed field doesn't arrive as a surprise.

Practice

A validity check: the payments with a negative amount. Show their id and amount.

The tables
  • customers (id, name, city, joined)
  • products (id, name, category, price)
  • orders (id, customer_id, ordered_on, status)
  • order_items (order_id, product_id, qty)
  • signups (id, email, name, signed_up_at)
  • payments (id, order_id, amount, currency, paid_at)
More info

Quick reference

FreshnessIs the latest data as recent as it should be?
VolumeIs the row count in the usual range?
SchemaColumns and types as expected?
ValidityValues in range and in the allowed set?
ReconciliationCounts and totals match the source.
Data contractAn agreed shape and meaning with the producing team.

The other AI and data engineering courses