Key takeaways
- Start with the ten tables that feed your most-used reports, not the whole warehouse.
- Five check types cover most failures: freshness, uniqueness, nulls, referential integrity and value ranges.
- Decide in advance who gets alerted and what happens when a test fails.
- Tests are a living asset: add one every time a bug escapes.
The number that was wrong for three days
A report shows revenue down sharply. People react, meetings happen, and then someone discovers that an upstream load failed silently and the table was simply stale. The data was not wrong in a subtle way; it was late, and nothing told anyone. Most data incidents look like this: boring, detectable and expensive only because they were found by a person at the wrong moment.
A small automated test suite can catch the majority of them. You can have a useful first version in a week if you keep the scope tight.
Day 1: choose what to protect
List the reports and dashboards that leadership and customers actually use. Trace each back to the tables it depends on. Pick roughly the top ten tables by importance. Write down, for each, who owns it and how fresh it needs to be. Ignore the long tail for now; coverage of the right ten beats shallow coverage of five hundred.
Days 2 to 3: the five checks
- Freshness: has this table been updated within its expected window? Compare the latest load or event timestamp against now.
- Uniqueness: is the key really unique? Duplicate order ids or customer ids are a classic source of inflated totals.
- Not null: are required fields populated? Focus on keys, dates, amounts and status fields rather than every column.
- Referential integrity: does every foreign key point to an existing parent, such as each order line to an order and each order to a customer?
- Value and volume checks: are amounts non-negative where they should be, dates in a sane range, statuses within the allowed list, and row counts within a reasonable band of yesterday's?
Most modern data tooling supports these as declarative tests, and you can also write them as simple SQL queries that return zero rows when everything is fine. Use whatever your team can maintain.
Day 4: set thresholds and severity
Not every failure deserves a page at midnight. Classify tests as blocking or warning. A duplicate key in a revenue table may block downstream builds; a small rise in nulls in an optional field may only warn. For volume checks, prefer a tolerance band based on recent history over a fixed number, so normal weekly patterns do not create noise.
Alert fatigue kills test suites. If a check fires often and nobody acts, tune it or remove it. A quiet suite that people trust is worth more than a loud one they mute.
Day 5: wire in ownership and response
- Send alerts to a channel that the owning team really reads, with the table name, the failed check and a link to the failing rows.
- Name an owner for each table and an on-call or rotating triage person for the suite.
- Decide what happens on failure: stop the pipeline, flag the dashboard as stale, or continue with a warning banner.
- Show data status where people look. A visible last-updated time on a dashboard prevents many false alarms.
- Keep a short runbook for the most common failures and their usual causes.
After the first week
Run the suite on every scheduled load, and where you can, on changes before they are deployed. Track which tests fail most and treat repeat offenders as upstream problems to fix with the source owner, not as noise to suppress.
Add a test every time a defect escapes. If a bad join inflated totals last month, write the uniqueness check that would have caught it. Over a few months the suite becomes a record of your real failure modes. Later you can add reconciliation against source systems and business-rule tests such as net revenue never exceeding gross, but the five basic checks give you most of the safety for very little effort.
