Data quality tests every pipeline needs

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 failure

2. 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?

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 and Python

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. For smaller pipelines built with pandas, a few assertions before saving or sending a report go a long way.

columns:
  - name: order_id
    data_tests: [unique, not_null]
  - name: customer_id
    data_tests:
      - relationships:
          to: ref('dim_customers')
          field: customer_id

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 out

What to do when a test fails

Good tests matter even more when AI reads your data: see the semantic layer for AI agents.

Alessandro Ecclesie AgazziWritten by Alessandro Ecclesie Agazzi, analytics engineer in London. Updated 30 September 2026. About me