Test Your Data Like You Test Your Code

Software engineering learned decades ago not to ship without tests. Data pipelines are frequently held to a lower standard: they are monitored for whether the job completed, not for whether the output is correct. The result is the characteristic data failure — everything green, numbers wrong, discovered by a business user three weeks later.

The four tests to start with

Most real data incidents would have been caught by a small, unglamorous set of assertions.

• Uniqueness — the primary key is actually unique. Duplicated joins are the most common cause of inflated metrics.

• Not-null — required fields are populated, especially foreign keys and dates.

• Referential integrity — every foreign key resolves. Orphans silently drop rows from joins.

• Accepted values — status and category fields contain only known members.

Then add the ones that catch silent drift

The four above check individual rows. The dangerous failures are structural, where every row is valid and the set is wrong.

• Volume anomaly — today's row count within a sensible band of the recent average.

• Freshness — the newest record is recent enough to be plausible.

• Reconciliation — key aggregates match the source system, to the penny for financials.

• Distribution — the share of rows per category hasn't shifted abruptly.

Every row can be individually valid while the table as a whole is badly wrong. Test the set, not just the rows.

Warn and fail are different

If every test blocks the pipeline, the pipeline stops nightly for known cosmetic issues and people learn to override it. Classify deliberately: anything that would produce a wrong published number should fail and stop; anything indicative should warn and be reviewed. Review the warnings weekly or they become a log nobody reads.

Test at the boundary

Put assertions where data enters your control — immediately after ingestion — as well as on the final models. A test at the end tells you something is wrong; a test at the boundary tells you which upstream system changed, which is most of the diagnosis.

Alert to a person, with the query attached

A failure that pages a rota and includes the failing rows gets fixed. A failure that emails a shared mailbox does not. Give every test an owner and make the alert contain enough context to start work — the assertion, the actual value, and a query that reproduces it.

Write the test when the incident happens

The most efficient way to build a useful suite is to add one assertion for every data incident you actually suffer, at the point of fixing it. Within a year the suite reflects your real failure modes rather than a generic checklist, and it does so without anyone having to plan a data quality programme.