Tests That Run Every Time

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

Here is my table definition and acceptance test [paste].

Write assertions covering: freshness, row-count band, grain
uniqueness, referential integrity against circuit_master, and value
ranges. Include the two known-value checks from my acceptance test.

For each assertion, recommend BLOCK or WARN and justify it with:
"if this fires and we publish anyway, the consequence is X."
Where you recommend a threshold, tell me what you based it on -
and if you are guessing, say so rather than picking a round number.

Structure them to run against a staging table before the swap.

That last constraint on thresholds is worth including every time. Asked for a row-count band, an agent will produce something like 1700–1950 because those look reasonable — and 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.

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

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.