Testing data pipelines¶

Checking that transformation code is correct and that the data it produces meets expectations, using several kinds of test at different points between a code change and a published table.

Before reading

dbt

Why it matters¶

A pipeline can fail in two ways: the code is wrong, or the data is not what the code assumed. Both produce numbers that look plausible, and the error is usually found by a consumer, weeks later. Tests move that discovery to before the change is merged or before the data is published.

How it works¶

Four kinds of test cover different failures.

Schema tests check structure: expected columns exist with the expected types, and constraints hold, such as a key being unique and not null, or a status being one of a known list. They are cheap and catch most breakages caused by source changes. dbt checks columns and types with model contracts, and calls the constraint checks and the next kind data tests; this page separates them by what they check.

Data tests check business rules on the actual data: an order's amount is not negative, every order line belongs to an existing order, a delivered order has a delivery date. They are written as queries that return the rows that break the rule, and pass when no rows come back.

Unit tests check logic, not data. They run a transformation on a few hand-written input rows and compare the output with expected rows: for example, that a cancelled order is excluded from revenue, or that the latest change wins when two have the same timestamp. They need no real data and can run on every code change.

Reconciliation tests compare totals across a boundary: rows loaded against rows in the source, revenue in the gold table against the sum of the clean order lines, payments against orders. They catch losses and duplications that pass every rule inside one table.

flowchart LR
  C[Code change] --> CI[CI: unit tests,<br/>build on sample data,<br/>schema tests]
  CI --> M[Merge]
  M --> RUN[Pipeline run:<br/>schema, data and<br/>reconciliation tests]
  RUN -->|pass| P[Publish]
  RUN -->|fail| S[Stop and alert;<br/>consumers keep<br/>yesterday's data]

Each kind runs where it is useful. Unit tests and builds on sample data run in continuous integration on every change, before merging. Schema, data and reconciliation tests run inside each pipeline run, after a table is built and before anything downstream uses it. Monitoring then watches published data over time, for freshness, volume and distribution changes, which is covered in Data quality dimensions.

Test data is a design choice. Hand-written rows suit unit tests, because the expected result is known. A small, anonymised sample of production data suits CI builds, because it contains the real oddities. Full production data is used only in the pipeline run itself.

A failing test needs a defined effect. Blocking tests stop the pipeline so that bad data is not published; warning tests let it continue and alert someone. Most tests on keys and reconciliations should block. Rules that occasionally have legitimate exceptions can warn, with a threshold.

What to test first: primary keys of every clean and gold table (unique and not null), relationships between facts and dimensions, a reconciliation for each important metric, and unit tests for the logic that has caused incidents before. Hundreds of untargeted tests slow every run and train people to ignore failures.

Key terms¶

Schema test: checks columns, types and constraints.
Data test: a query that returns rows breaking a business rule.
Unit test: runs logic on fixed inputs and compares with expected output.
Reconciliation test: compares counts or totals across tables or systems.
Blocking test: a test whose failure stops publishing.
Test fixture: the fixed input rows used by a unit test.

Example¶

Testing that daily sales equal the sum of order line amounts on orders that are not cancelled, as a dbt singular test that returns days that do not match:

-- tests/fct_daily_sales_reconciles.sql
WITH from_orders AS (
  SELECT CAST(o.ordered_at AS date) AS sales_date, sum(i.amount) AS revenue
  FROM {{ ref('clean_order_items') }} AS i
  JOIN {{ ref('clean_orders') }} AS o ON i.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY 1
)
SELECT f.sales_date, f.revenue, o.revenue AS expected
FROM (SELECT sales_date, sum(revenue) AS revenue
      FROM {{ ref('fct_daily_sales') }} GROUP BY 1) AS f
FULL JOIN from_orders AS o ON f.sales_date = o.sales_date
WHERE coalesce(f.revenue, 0) <> coalesce(o.revenue, 0);

A bug that joins order lines twice doubles revenue for orders with two lines. Every schema test still passes; this test returns the affected days and blocks publishing.

Pitfalls¶

Testing only structure. Duplicated revenue passes not_null and unique on the wrong columns.
Tests that always warn. Failures nobody acts on are noise; make important tests block.
No unit tests for tricky logic. Edge cases such as ties and deletes are only found in production.
Production data in CI. It is slow, and it can expose personal data to development environments.
Many tests, none on keys. Start with uniqueness and reconciliation.

Learn more¶

dbt documentation, data tests: https://docs.getdbt.com/docs/build/data-tests
dbt documentation, unit tests: https://docs.getdbt.com/docs/build/unit-tests

Write-audit-publish · Data quality dimensions · Environments and CI · Data contracts