Data tests that catch real problems

Most test suites are wide and shallow: not_null on every column, unique on every ID, all green, and the numbers are still wrong. The problems that reach a dashboard are usually a grain that silently doubled, a join that silently dropped rows, a code nobody mapped, or data that arrived late. A handful of test types catch those. The rest is mostly noise.

The examples here come from my outpatient analytics pipeline, a dbt-style SQL project on synthetic hospital appointment data. I wrote a naive first version, the way a first pass usually goes, and ran the same 28 tests against it and against the fixed pipeline. The naive build failed 8 tests and warned on 2. The fixed build failed none and warned on 3. What failed, and what didn’t, is the useful part.

Test the grain first

Every table has a grain: one row per appointment, per session, per patient per month. If the grain breaks, every count and every rate built on it is wrong, and nothing looks broken. A unique test on the key is the cheapest insurance in analytics.

SELECT appointment_id, COUNT(*) AS copies
FROM stg_appointments
GROUP BY appointment_id
HAVING COUNT(*) > 1

In the project, two monthly extracts were re-sent, one in full and one for a single site, and the naive staging model kept both copies: 7,606 appointment IDs on 15,212 rows. Those duplicates also pushed 424 clinic sessions over their slot count, which a second test (attended + dna + cancelled_by_hospital <= slots_available) caught from a different angle. That kind of business-rule check is worth writing whenever the domain gives you one: bookings can’t exceed a session’s slots, or its overbooking limit if the service allows one.

Put the uniqueness test where the grain is promised, not where the data lands. The raw extract is allowed duplicates, because re-sends happen, so the same test on the source is a warning. Staging promises one row per appointment, so there it’s an error.

Relationships, and the join that hides orphans

A relationships test checks that every foreign key has a parent:

SELECT f.clinic_code, COUNT(*) AS rows
FROM fct_appointments AS f
LEFT JOIN (SELECT DISTINCT clinic_code FROM stg_clinics) AS c
    ON c.clinic_code = f.clinic_code
WHERE f.clinic_code IS NOT NULL
  AND c.clinic_code IS NULL
GROUP BY f.clinic_code

On the naive build this test passed. The naive model inner-joined appointments to the clinic lookup, so appointments at clinics missing from the lookup, or typed in lower case, never reached the fact table. There were no orphans left to find. The join had dropped 6,027 rows, 5,800 of them real appointments, and the test designed to catch that said everything was fine.

Two lessons. Test relationships on the table before the join, or keep the join a LEFT JOIN and test after it. And don’t treat a passing relationships test as proof that nothing was lost. Only a count comparison proves that, which is what the reconciliation section below covers.

In the fixed pipeline the join keeps every appointment, and the same test reports 4,094 rows at three clinic codes missing from the lookup, at warn severity. That warning is the booking team’s to-do list.

Accepted values, and the NULL gap

Status and category codes drift. A new screen writes dna instead of DNA, an old site sends 3, a supplier adds a code without telling anyone. accepted_values catches it:

SELECT attendance_outcome, COUNT(*) AS rows
FROM stg_appointments
WHERE attendance_outcome NOT IN
    ('ATTENDED', 'DNA', 'CANCELLED_PATIENT', 'CANCELLED_HOSPITAL', 'NOT_RECORDED')
GROUP BY attendance_outcome

On the naive build it found 11 unexpected values on 24,393 rows, led by the legacy code 5 on 11,137 rows. That is the test doing its job.

Now look at the WHERE clause. NULL NOT IN (...) is not true, so NULL rows are never returned. dbt’s built-in accepted_values behaves the same way. In the project the naive first/follow-up flag was NULL on 58,642 rows and accepted_values on that column passed; not_null caught it. Pair the two wherever NULL is not a legitimate value.

The mirror image: an empty string is not NULL. Blank specialty codes passed not_null on the naive build and failed relationships on 3,852 rows. Convert blanks with NULLIF(TRIM(col), '') in staging so one test covers both.

Better still, make unknown codes loud. The fixed staging model maps codes through a seed file and turns anything unmapped into 'UNMAPPED: ' || raw_code rather than NULL, so the accepted-values test names the new code instead of the row quietly dropping out of every rate.

Freshness and late-arriving data

A freshness test asks whether the newest record is recent enough. It catches the feed that stopped. It does not catch the subtler problem: data that is present but not finished.

In the project, each monthly extract runs on the 3rd of the following month, and some outcomes are keyed days after the appointment. Appointments early in the month almost always have an outcome by extract time. Appointments at the end do not.

Data table

Day of month	No outcome at extract
1	0.2%
2	0.2%
3	0.3%
4	0.2%
5	0.1%
6	0.1%
7	0.2%
8	0.2%
9	0.2%
10	0.3%
11	0.3%
12	0.3%
13	0.4%
14	0.5%
15	0.5%
16	0.7%
17	0.8%
18	0.7%
19	1.0%
20	1.1%
21	1.4%
22	1.7%
23	2.6%
24	3.2%
25	3.8%
26	5.3%
27	5.4%
28	6.4%
29	9.0%
30	10.7%
31	10.8%

Synthetic data. Source: projects/outpatient-analytics-pipeline.

The share with no outcome rises from 0.2% on the 1st to 10.8% on the 31st. Worse, some outcomes change after the extract: a DNA keyed in error is corrected to attended days or weeks later. The stale row has a perfectly valid code, so no column test can see the problem.

What caught it was a singular test, a query that returns the rows breaking a rule, comparing the fact table with the feed of later updates:

SELECT u.appointment_id
FROM stg_status_updates AS u
INNER JOIN fct_appointments AS f
    ON f.appointment_id = u.appointment_id
GROUP BY u.appointment_id, f.status_as_at
HAVING MAX(u.updated_at) > f.status_as_at

It returned 3,383 rows on the naive build and none on the fixed one. I could only write it because I knew the update feed existed. Tests check shape; they don’t know your source systems.

In dbt, freshness is configured on the source with warn_after and error_after thresholds on a loaded-at column, and it runs as its own command rather than as a data test.

Reconciliation

A reconciliation compares a count at one end of the pipeline with a count at the other, after the exclusions the pipeline is meant to make:

That table is from the fixed build. On the naive build it found 150,342 rows: a gap of 1,739, about 1%. That small number hides two large errors. The naive build kept most of the 7,766 rows it should have dropped and lost 5,800 it should have kept, and the two mostly cancelled out. A dashboard total would have looked plausible. Only the reconciliation failed.

Write every exclusion as its own count, with a label a manager can read. Then a failure tells you which step is off, and a pass tells a reviewer exactly what was left out. Reconcile marts back to facts too: the project checks that DNAs in the specialty-by-weekday mart add back to the DNAs in the fact table.

Distribution shifts

The last family catches the load that is complete, well-formed, and wrong: a doubled month, a partial month, a code that maps to the wrong outcome. Compare each period with its own recent history:

WITH monthly AS (
    SELECT strftime('%Y-%m-01', appointment_date) AS month, COUNT(*) AS appointments
    FROM fct_appointments
    GROUP BY month
),

compared AS (
    SELECT
        month,
        appointments,
        AVG(appointments) OVER (
            ORDER BY month ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
        ) AS trailing_mean
    FROM monthly
)

SELECT month, appointments, trailing_mean
FROM compared
WHERE trailing_mean IS NOT NULL
  AND ABS(appointments - trailing_mean) > 0.2 * trailing_mean

On the naive build it flagged March 2025, the month re-sent in full, and then April, May and June as well, because the doubled March inflated their baseline. November 2025, re-sent for one site only, stayed under the threshold. Both are typical of these checks: a spike contaminates the next few comparisons, and a small problem hides inside normal variation. A median baseline, or excluding flagged months from it, helps with the first. Nothing fixes the second except tighter tests nearer the source. Keep distribution checks at warn: they say “look at this”, not “this is wrong”. Anomaly detection for data feeds goes further for daily feeds, with day-of-week baselines, robust z-scores and a slow-drift check.

Severity: who acts, and when

Every test needs an answer to one question: if this fails at 6am, what should happen?

• Error when the numbers would be wrong and nobody should read them: broken grain, failed reconciliation, an unmapped outcome code in a rate.
• Warn when the pipeline is right but someone else has work to do: clinics missing from a lookup, appointments with no outcome recorded, a month that looks odd.

The fixed build in the project finishes with three warnings, and each has an owner: re-sent rows at source (expected, handled in staging), three unmapped clinics (the booking team), 201 unrecorded outcomes (the clinics). A warning with no owner becomes background noise within a month.

dbt sets this per test with severity, and can use thresholds instead of all-or-nothing: warn_if and error_if take conditions such as ">10" on the failure count, and --warn-error promotes warnings to errors when you want a strict run.

Testing sources vs models

Source tests describe what you receive. Model tests describe what you promise. They answer different questions, and their severities differ.

On sources I test what I’d complain to the supplier about: keys present, the lookup’s own keys unique, freshness. Duplicates in the raw extract are a warning, because they’re expected. On staging I test the promises staging makes: grain, codes mapped, no blanks pretending to be values. On facts I test relationships and business rules. End to end, I reconcile. The transformation code itself, SQL or Python, needs unit tests too; testing analytics code with pytest covers those.

How many tests is too many

In the project, 16 of the 28 tests passed on both builds. Some of that is fine: not_null and unique on a primary key cost nothing and catch the day someone changes a join. But a test that cannot fail is decoration. My own project has one: not_null on fct_appointments.specialty_code, a column built with a COALESCE that ends in 'UNKNOWN'. The relationships test on the same column is the one doing the work. A test nobody would act on is decoration too.

My rule of thumb:

1. unique and not_null on the key at every change of grain.
2. accepted_values plus not_null on every code that drives a metric.
3. relationships on every fact-to-dimension key, tested where rows can’t have been dropped already.
4. One reconciliation from source to each fact table, with exclusions counted.
5. A freshness check on each source, and a volume check on each fact table at warn.
6. A singular test for each thing you know about the source that the column tests can’t see.

That’s a dozen or two tests for a small project, not hundreds. If you only have time for one thing today, pick your most-used fact table and write the reconciliation. It is the test most likely to find something you didn’t know.