Building Trustworthy Data Products Module 4 · Prove It

Tests That Run Every Time

Updated

Intermediate

What you'll learn

~20 min
  • Write the five assertions that catch most data defects, and wire them into the load
  • Distinguish a test that should block publication from one that should only warn
  • Avoid the alert fatigue that makes a test suite worse than no tests

From a one-time check to a standing one

Lesson 4.1’s reconciliation was something you did. This lesson makes correctness something that happens on every load, without you.

The distinction matters because your attention is the scarcest input here. You will check carefully the first three times, less carefully by the tenth, and not at all by the fortieth — which is roughly when the source system changes.

Prompt first: assertions with justified thresholds

Switch your workspace agent to Plan mode with your table definition and acceptance test file, and ask it to plan the pre-swap assertion suite (if you do not have live exports or files handy, ask it to generate a small Meridian practice sample from the lesson’s scenario first):

Using the table definition and acceptance test in this folder, plan the SQL assertions to run against staging before the table swap. Recommend BLOCK or WARN for each, and flag any threshold that needs historical data.

Check that the plan or draft covers:

  • All five core assertions (freshness, row-count volume band, grain uniqueness, referential integrity against circuit_master, and value ranges) plus the two known-value checks from your acceptance test
  • A BLOCK or WARN recommendation for each check justified by what happens if it fires and you publish anyway
  • The basis for every threshold, explicitly flagging where historical load statistics are needed instead of guessing round numbers
  • Execution against the staging table prior to the production swap

If the agent invents a row-count band out of thin air, follow up with: Replace guessed volume bounds with a query over the last 12 months of history to derive the band.

That constraint on thresholds is worth including every time. Asked for a row-count band, an agent will produce something like 1700 to 1950 because those look reasonable, even though it has no idea how much Meridian’s circuit count actually varies month to month. A threshold you derived from twelve months of history is a test; a threshold the agent guessed is a coin flip that will either never fire or fire constantly.

Stop and escalate when a threshold needs history you do not have — the query logs and load statistics are the platform team’s to expose, and a guessed band is a coin flip wearing a test’s clothing.

KNOWLEDGE CHECK

Your suite has twelve blocking assertions. Over two months, three of them have fired on benign variance and someone has twice re-run the job with tests skipped. What is the actual problem?

The five assertions

Most data defects are caught by five checks. They are unglamorous and they are the ones that fire.

1. Freshness — is the newest data as recent as it should be?

SELECT CASE WHEN MAX(month) < DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
THEN ERROR('stale: newest period is ' || MAX(month)::text) END
FROM circuit_monthly;

2. Volume — is the row count in the expected range? Catches partial loads and runaway fan-out in one check.

-- expected ~1,847 circuits per month; alert outside a sane band
SELECT CASE WHEN COUNT(*) NOT BETWEEN 1700 AND 1950
THEN ERROR('row count ' || COUNT(*)::text || ' outside expected band') END
FROM circuit_monthly WHERE month = :target_month;

3. Uniqueness — is the grain still what the definition says?

SELECT CASE WHEN COUNT(*) <> COUNT(DISTINCT (circuit_id, month))
THEN ERROR('grain violated: duplicate circuit-month rows') END
FROM circuit_monthly;

4. Referential — does every key exist where it should?

SELECT CASE WHEN COUNT(*) > 0
THEN ERROR(COUNT(*)::text || ' circuits not in circuit_master') END
FROM circuit_monthly c
LEFT JOIN circuit_master m USING (circuit_id)
WHERE m.circuit_id IS NULL;

5. Range and null — are values inside what is physically possible, and are nulls where the definition says they may be?

-- a negative duration means timestamps ran backwards somewhere
SELECT CASE WHEN COUNT(*) > 0 THEN ERROR('negative durations present') END
FROM circuit_monthly WHERE total_duration_seconds < 0;

Add to these the known values from your acceptance test in Lesson 1.4 — DIST-4471 in the top decile for last Q3, DIST-1102 absent after March. Those are the assertions with real domain content, and they are the ones an agent could never have written for you.

Block or warn

Every test needs this decision, and getting it wrong in either direction ruins the suite.

Block — the run fails, nothing publishes, someone is notified. Reserve for defects where publishing is worse than being late:

  • grain violated (every downstream aggregate is wrong)
  • freshness breached (the consumer would act on stale data believing it fresh)
  • reconciliation outside tolerance
  • referential integrity broken

Warn — the run publishes, someone is told. For things that are odd but not disqualifying:

  • volume outside the band but within a wider sanity range
  • null rate moved more than usual
  • a known-bad circuit’s telemetry still missing
⚠How a test suite becomes worse than nothing

Make everything blocking and you will be woken for a 3% volume variance that turns out to be a leap-year artifact. Two of those and someone adds --skip-tests to the job. Now you have no tests and a false belief that you do.

Make nothing blocking and the grain violation publishes.

The split is not a style preference. Ask of each test: if this fires and we publish anyway, does someone make a wrong decision? That is the whole rule.

Where the tests live

Run them after the transform and before the publish, against the staging output rather than the live table. The sequence:

1. build into staging
2. run assertions against staging
3. all blocking assertions pass -> swap staging into place
4. any blocking assertion fails -> leave the live table untouched, notify

This is why Lesson 3.4’s atomic write matters here: if the tests run after you have already replaced the live table, a failure means the bad data is already published and you are now doing incident response instead of prevention.

Key takeaway

Five assertions catch most defects — freshness, volume, uniqueness, referential integrity, and range/null — and the known values from your acceptance test add the domain content no agent could supply. Decide block-or-warn by one question: if this fires and we publish anyway, does someone make a wrong decision? Everything blocking produces a suite people bypass; nothing blocking publishes the grain violation. Run them against staging before the swap, so a failure means nothing shipped rather than an incident. And derive your thresholds from history — an agent will supply confident round numbers it has no basis for. Lesson 4.3 turns the review pass into a named artifact.

Search lessons