Verdict
Test it differently
Test data quality differently: after each load, run checks on the share of empty values, the row count and the time of the newest row, and alert when a check fails.
Why
- Blast radiususers
- Change frequencyconstantly
- Detectabilityeventually
- Reversibilitywith-effort
- Test costheavy
Test data quality differently: check each load with queries and alerts, not with tests in CI. The typical case is a product team whose tables fill daily from imports and forms and feed customer pages. Blast radius is users, because customers see wrong data, and Change frequency is constantly, because new rows arrive daily and upstream exports change without notice. Detectability is eventually, because an empty field looks normal, and Reversibility is with-effort, because bad rows need a re-import. Test cost is heavy, because a CI test sees only fixtures and data that changes daily allows only threshold assertions, so rule R9 gives Test it differently.
| When | Decision | Why |
|---|---|---|
| The data ships in the repository, such as a seed file of countries | Test: a unit test in CI loads the file and asserts unique keys and a value in every required field | Test cost falls to trivial and Change frequency to rarely, because the rows are fixed and a test compares them exactly |
| A column feeds charges, such as the unit price in a supplier price feed | Test mandatory: reject prices outside the allowed range before the load, and unit test that rejection | Blast radius rises to money and Reversibility to costly, because wrong charges need refunds |
| Each imported row carries the customer account that may see it | Test mandatory: a NOT NULL foreign key on the account column, and an import test with rows from two accounts | Blast radius rises to safety-or-legal and Detectability to never, because a leaked row looks like normal output |
| The rule fits a constraint on one row, such as a required value or a unique key | Do not write a data check for that rule; declare the constraint and alert when the import job fails | Detectability falls to same-day and Reversibility to trivial, because the insert fails and stores nothing |
| The data feeds only an internal dashboard that staff read every morning | Do not add data checks; rerun the import when staff report a gap | Blast radius falls to internal and Detectability to same-day, because the staff who read the numbers report a gap that day |
What breaks if you don't test
An upstream system switches its export from day-first to month-first dates. Every date from the 1st to the 12th imports as a valid wrong date, so 3 April is stored as 4 March. The import's unit tests stay green, because their fixtures use the old format, and customers see wrong dates until one complains.
What you lose if you over-test
A check with an exact value, such as count(*) = 4812, fails on the next load. A threshold set too tight fires on every smaller Sunday import, and people stop reading a channel with weekly false alarms. Copying production rows into CI puts personal data on CI runners and tests a copy that is stale the next morning.
What to do instead
- Declare every rule that one row can break as a constraint: NOT NULL, UNIQUE, a foreign key, or a CHECK on a range.
- After each load, query the rules that span many rows: the share of empty values per column, the row count against the usual load, and the time of the newest row. Use scheduled SQL, or dbt data tests and source freshness checks if the team uses dbt.
- Alert on every failed check, and stop the load when customers see the new rows next.
- Unit test the import code with fixture rows, adding a fixture for each shape a check finds.
When the answer changes
- The data ships in the repository, so a unit test in CI can read all of it.
- A column feeds charges, payouts or tax.
- Each row carries the customer account that may see it.
Real incident + Code example
The column rename that hid new stores for 23 days
On a store locator for a franchise chain I worked on, a nightly job imported stores from the CSV export of the franchise system. An update of that system renamed the column postcode to postal_code. Our importer allowed an empty postcode, because about 3% of stores have none, so every store imported afterwards was saved without one and vanished from postcode search. The unit tests passed, because their fixture CSV kept the old header. A franchisee asked 23 days later why his new store was missing. We re-imported the archived files and added this check:
-- Runs after each nightly import; the job alerts on every row it returns
WITH imported AS (
SELECT postcode FROM stores WHERE imported_at >= current_date
),
usual AS (
SELECT count(*) / 7.0 AS rows_per_night FROM stores
WHERE imported_at >= current_date - 7 AND imported_at < current_date
)
SELECT 'postcode missing in over 5% of imported rows' AS failed_check
FROM imported
HAVING avg((postcode IS NULL)::int) > 0.05
UNION ALL
SELECT 'fewer than half the usual number of rows'
WHERE (SELECT count(*) FROM imported) < 0.5 * (SELECT rows_per_night FROM usual);
The first check would have fired on the first night. A NOT NULL constraint could not have caught the rename, because some stores have no postcode.
Related questions
FAQ
- Should you write automated tests to check that data is correct?
Test data that ships with the code, such as seed files, with a unit test in CI. Check data that arrives at runtime with database constraints and with queries that run after each load and alert on failure, because a CI test never sees production rows.
- Should I use database constraints or data quality checks?
Use a database constraint for every rule that one row can break, such as a missing required value or a duplicate key. Use data quality checks for rules that span many rows, such as the share of empty values or the row count per load.
- Should a failed data quality check stop the pipeline?
A failed data quality check should stop the load when customers see the new rows next, and the previous rows should stay until the source is fixed. A check on data for an internal report can alert without stopping the load.