The worst way to discover a data problem is when a manager asks why the dashboard shows revenue doubling overnight. Data quality tests are small, automatic checks that run with every pipeline and stop bad data before it reaches a report. You do not need an expensive platform to start. Six checks cover the vast majority of real-world issues.
1. Uniqueness: one row per thing
If a table is meant to have one row per order, prove it. Duplicate keys cause fan-out in joins and inflated totals.
SELECT order_id, COUNT(*) AS n
FROM fct_orders
GROUP BY order_id
HAVING COUNT(*) > 1; -- any row returned is a failure2. Completeness: no missing critical values
SELECT COUNT(*) AS missing
FROM fct_orders
WHERE customer_id IS NULL OR order_date IS NULL OR amount IS NULL;3. Referential integrity: every key has a match
Orders pointing at customers that do not exist will vanish from inner joins and show as "blank" in Power BI.
SELECT o.order_id, o.customer_id
FROM fct_orders o
LEFT JOIN dim_customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;4. Accepted values and ranges
SELECT *
FROM fct_orders
WHERE order_status NOT IN ('completed', 'refunded', 'cancelled')
OR amount < 0
OR order_date > CURRENT_DATE;5. Freshness: is the data up to date?
A pipeline that silently stops is worse than one that fails loudly. Check the newest record against an agreed threshold.
SELECT MAX(_loaded_at) AS last_load,
DATEDIFF('hour', MAX(_loaded_at), CURRENT_TIMESTAMP()) AS hours_old
FROM raw.orders
HAVING DATEDIFF('hour', MAX(_loaded_at), CURRENT_TIMESTAMP()) > 24;6. Volume: did we get roughly what we expected?
Compare today's row count with the recent average. A sudden drop usually means a broken extract; a sudden spike often means duplicates.
WITH daily AS (
SELECT order_date, COUNT(*) AS n
FROM fct_orders
WHERE order_date >= CURRENT_DATE - 29
GROUP BY order_date
)
SELECT *
FROM daily
WHERE order_date = CURRENT_DATE - 1
AND n NOT BETWEEN
0.5 * (SELECT AVG(n) FROM daily WHERE order_date < CURRENT_DATE - 1)
AND 2.0 * (SELECT AVG(n) FROM daily WHERE order_date < CURRENT_DATE - 1);The same checks in dbt
If you use dbt, the first four checks are one line each, and freshness is built in. See the dbt guide for a full example.
columns:
- name: order_id
data_tests: [unique, not_null]
- name: customer_id
data_tests:
- relationships:
to: ref('dim_customers')
field: customer_idThe same checks in Python
For smaller pipelines built with pandas, a few assertions before saving or sending a report go a long way.
import pandas as pd
def check_orders(df: pd.DataFrame) -> None:
problems = []
if df["order_id"].duplicated().any():
problems.append("duplicate order_id values")
if df[["customer_id", "order_date", "amount"]].isna().any().any():
problems.append("missing values in required columns")
if (df["amount"] < 0).any():
problems.append("negative amounts")
if problems:
raise ValueError("Data quality check failed: " + "; ".join(problems))
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
check_orders(orders) # stops the pipeline before a bad report goes outWhat to do when a test fails
- Decide severity up front. Some failures should stop the pipeline; others should warn and continue.
- Alert a person. A failing test nobody sees is not a test.
- Fix the cause, not the symptom. Filtering out duplicates in the report hides a problem that will return.
- Add a test for every bug you fix, so the same issue can never slip through twice.
Written by Alessandro Ecclesie Agazzi, freelance analytics engineer in London. Updated 30 September 2026.