A Test Plan per Layer

Concepts covered: Data tests

A failing test skips every model downstream of it, so the test that fires first on a fault is the one whose message gets read. If a null user_id enters from the source and the first test to see it sits on the mart, the alert names fct_user_xp, and the investigation starts 3 models away from the fault. The plan below places each test at the layer where its fault can first appear, and at no layer after that.

Layer	What enters here	Tests that belong	Severity
Sources	Rows from systems the team does not control	Freshness; unique and not_null on the natural key; accepted_values on raw codes	Warn up to the measured count, error above it
Staging	Casts, renames, trimmed and mapped values	unique and not_null on the key; accepted_values on mapped values; 1 unit test on the mapping	Error
Intermediate	Joins, aggregations, business rules	A unit test per rule with a row per boundary; relationships between joined keys; singular tests for invariants	Error
Marts	The shape consumers read	Contracts; relationships from facts to dimensions; reconciliation to staging with fail_calc	Error, thresholds only on drift
Every layer	A change to the SQL	An empty build before merge	Error

Sources and staging: 1 column, 2 questions

A source test on raw.lesson_completions finds 12 rows with a null user_id today, the app's known gap for deleted accounts, so its threshold warns at 12 and errors at 13. The staging model drops those rows with a filter, and its own not_null test errors on any null, because after staging a null user_id means the filter broke. The 2 tests read the same column and answer different questions: the source test measures the upstream system, the staging test guards the project's first promise. A mart test on the same column would fire only after both had passed, and would add a third alert to a fault already named.

Intermediate: where the logic lives

Staging has 1 kind of logic, a cast or a mapping per column, and 1 unit test per staging model with a row per mapped value covers it. Intermediate models hold the rules people argue about, the outcome thresholds, the 7-day activity window, the join that keeps users with no completions, and each rule gets a unit test named for the rule with 1 row per boundary. A relationships test from int_lesson_outcomes.user_id to stg_users belongs here too, since an orphan between keys joined in intermediate first changes a result there; a mart's own joins to dimensions carry their own relationships tests.

Marts: shape and reconciliation

A mart's readers are dashboards and other teams, so the tests here guard what they read. A contract fixes the columns and types, relationships from each fact key to its dimension proves every row can be joined, and 1 singular test per measure reconciles it back to staging with fail_calc measuring drift rather than counting rows. Row-level rules stay out: a mart that re-tests not_null on a key proves only that staging's test already ran, and costs a scan of the largest table in the project to prove it.

Each gate holds the tests for the layer before it, so a failure stops the build at its own layer

raw.lesson_completions (source, app database)
source checks (quality gate, freshness, key, thresholds)
stg_lesson_completions (silver, dbt view)
staging checks (quality gate, key tests, 1 unit test)
int_lesson_outcomes (transform, dbt table)
logic checks (quality gate, unit tests, relationships)
fct_user_xp (gold, contract, reconciliation)
dashboards (consumer, BI tool)

raw.lesson_completions -> source checks
source checks -> stg_lesson_completions
stg_lesson_completions -> staging checks
staging checks -> int_lesson_outcomes
int_lesson_outcomes -> logic checks
logic checks -> fct_user_xp
fct_user_xp -> dashboards

Sources: raw.lesson_completions, source checks
Staging: stg_lesson_completions, staging checks
Intermediate: int_lesson_outcomes, logic checks
Marts: fct_user_xp, dashboards

What the first failure says, by layer

Source: A warn past its threshold or a stale freshness check: nothing in the project is wrong yet, and the fix is a conversation with the system's owners
Staging: A key test errors on rows that staging promised to clean, so the fix is in 1 staging model
Intermediate: A unit test prints the boundary row that moved, before the model is built, so the fix is the comparison the row names
Mart: A reconciliation test reports the size of the drift, with every row-level guarantee already proven upstream

Do

Place each test at the first layer its fault can appear
Name a unit test for the rule it states
Set a source threshold from today's measured count
Run the empty build on every change, before anything else

Avoid

Repeat a staging key test on every mart that reads the key
Raise a mart threshold to turn a red run green without reading the rows
Write 20 rows into a staging unit test that has 2 boundaries
Let a warn-only test stand in for a decision nobody made

About this lesson

This section is part of the Testing: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.