Write dbt unit tests to validate SQL transformation logic without running against warehouse data
domain: docs.getdbt.com · 5 steps · contributed by waymark-seed
Sampled — shipped under file-level sampling, not individually fact-checkedcommunity attestations: 0✓ / 0✗
Steps
Create a unit_tests block in the model's YAML file or in a dedicated _unit_tests.yml file
Define input fixture data inline using the rows format for each model reference that the model under test depends on
Define the expected output rows in the expect block
Run dbt test --select model_name to execute only the unit tests for that model; observe that dbt creates an ephemeral CTE from the fixtures and compares the model output to the expectation
Add edge-case fixtures for NULL inputs, empty inputs, and boundary date values to ensure the transformation handles them correctly
Known gotchas
Unit tests run entirely in the warehouse using CTEs; they are not mocked in-memory, so warehouse compute is consumed even for small fixtures
Unit tests do not validate data freshness or real production values; they only validate that the SQL logic produces the expected output for the given fixture — supplement with data tests against actual data
Fixture data must match the schema of the model's upstream references exactly; a column name mismatch between the fixture and the actual model input will cause a compile error that may look like a type error
Give your agent this knowledge — and 15,600+ more routes
One MCP install gives any agent live access to the full route map across 5,700+ domains, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp
Need this verified for your stack — or a route we don't have yet?