Data engineering6 min read23 September 2026

Testing data, not just the code that moves it

A data pipeline can be fully covered by unit tests and still deliver wrong numbers, because the code was correct and the data was not. The transform did exactly what it was told with a column that started arriving null three weeks ago, and nothing in the test suite was looking at the data.

Part ofData Engineering, Databases & Analytics

Software tests assert that logic behaves correctly given an input. Data tests assert that the input itself is what you believe. Both are necessary, and the second is routinely absent — which is why data problems are usually discovered by a person noticing a chart looks odd, weeks after the cause.

The four tests every table should have

These are cheap, uninteresting, and catch a large share of real incidents. Any table without them is unmonitored regardless of how well tested the code around it is.

Test	Asserts	Catches
Not null	Required columns have values	A source field silently discontinued
Unique	The grain holds — no duplicate keys	A pipeline re-run that appended instead of replacing
Referential	Foreign keys resolve to a dimension	Rows joining to nothing, disappearing from grouped reports
Accepted values	Categoricals are within a known set	A new status code nobody was told about

yaml

# Declared next to the model, so the assertions live with the table
# they describe rather than in a separate suite nobody updates.
models:
  - name: fct_orders
    description: One row per order. Grain enforced by the unique test.
    columns:
      - name: order_id
        tests: [not_null, unique]
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: [pending, paid, shipped, cancelled, refunded]
      - name: total
        tests: [not_null]

Freshness and volume, which catch what the four cannot

Every one of the four tests above passes on a table that stopped updating a week ago. Absence and anomaly need separate checks.

Freshness — the newest row is no older than expected. A job that stops running emits no error, so this is the check that notices
Row count against recent history — today's volume within a sensible band of the trailing average, allowing for weekday seasonality
Null rate against recent history — a column that was 2% null and is now 60% null is a source change, not a data trend
Distribution shift on key measures — an average order value that moves by an order of magnitude overnight is an integration problem, not a business event
Zero rows — worth its own check, because it is both the most likely anomaly and the most damaging

The band for volume checks needs a floor as well as a ceiling. A check for "not more than double yesterday" passes happily on a day that delivered eleven rows instead of eleven thousand, which is the failure that actually happens.

Reconcile against the source, periodically

Internal consistency is not correctness. A pipeline can be perfectly self-consistent and 3% short of the source because of a join condition dropping rows that arrived without a matching dimension.

Count rows at the source and at the destination for the same window, and assert the difference is within a stated tolerance — usually zero.
Sum a key measure at both ends. Row counts can match while values do not, if a transform is dropping or rounding.
Check the boundaries. Records at the exact start and end of a window are where timezone and inclusivity errors live.
Reconcile against the system of record, not against an intermediate copy that shares the same defect.
Do it on a schedule, unprompted. Reconciliation performed only when a number is questioned is not a control.

Timezone handling deserves specific mention because it produces a persistent, plausible-looking error. A source recording in UTC and a pipeline partitioning by local date will misassign every record in the offset window, every day. The totals look reasonable, the daily figures are consistently wrong at the edges, and it survives for years.

Decide what fails and what warns

Failing the build on every anomaly means the build is red most mornings and the team learns to override it. Warning on everything means nothing is ever blocked. Both outcomes come from not separating the two.

Condition	Severity	Why
Duplicate primary key	Fail — stop the pipeline	The grain is broken; downstream aggregates are wrong now
Required column null	Fail	Rows will be silently excluded or miscounted
Unknown categorical value	Fail	Data is being dropped or misbucketed
Freshness beyond threshold	Fail	The data is stale and will be used as current
Volume outside expected band	Warn, and investigate	Often a real business event rather than a defect
Distribution shift	Warn	Needs human judgement; too noisy to block on

Tests need an owner and a route to a person

Every test names an owning team, so a failure has a destination rather than a shared channel nobody reads
Failures route to the team that can fix the cause, which is often the source system's owners rather than the data team
A test that fails repeatedly without action is either wrong or unowned — fix it or delete it, but do not leave it failing
Record what each failure turned out to be; the pattern tells you which source needs a conversation rather than another test

Where to start

Unique and not-null on the primary key of every fact table. This is an afternoon, and it catches duplication from re-runs.
Freshness on every table feeding a dashboard anyone looks at.
Accepted values on every status and category column sourced from a system you do not control.
Row-count reconciliation against the source for your two or three most important tables.
Volume and null-rate anomaly checks, once the deterministic tests are green and stable.

Steps one to three cover most real incidents and can be in place within a week. The temptation is to begin with anomaly detection because it sounds more capable; in practice it is noisier, harder to tune, and finds less than a unique key test does on its first run.

Questions

What are the first data tests to add?

Unique and not-null on the primary key of every fact table, which catches the duplication that re-runs cause; freshness on every table feeding a dashboard, because a job that stops running emits no error; and accepted-values on every status or category column from a system you do not control. Those three cover most real incidents and take about a week.

Why isn't unit testing the pipeline code enough?

Because the code can be correct while the data is not. A transform does exactly what it was told with a column that started arriving null three weeks ago, and no logic test is looking at the data. Software tests assert that logic behaves given an input; data tests assert that the input is what you believe it is.

Should a failing data test stop the pipeline?

For anything that breaks the grain or drops rows — duplicate keys, null required columns, unknown categorical values, stale data — yes, fail closed. An empty dashboard prompts a question; a populated wrong dashboard produces a decision. Volume anomalies and distribution shifts should warn instead, because they are frequently real business events and too noisy to block on.

How do I stop anomaly alerts becoming noise?

Compare against the same weekday in prior weeks rather than against yesterday, or use a trailing window long enough to absorb the business cycle — otherwise the check fires every weekend and everyone learns to ignore the channel. Give each test an owner, and treat a permanently failing test as something to fix, retune or delete, since leaving it red disables every other test alongside it.