Generic tests, singular tests, custom generics, test configs, store_failures, and testing sources — the quality layer of every dbt project.
dbt has four test categories. Know when to use each.
Built-in: unique, not_null, accepted_values, relationships. Applied in .yml on any model or source column.
Custom SQL files in tests/ that return rows on failure. Highly flexible, model-specific logic.
Reusable parameterized tests defined as macros. Like generic tests but you write the logic.
Tests from installed packages (e.g. dbt_expectations, dbt_utils). Applied like generic tests.
The exam frequently tests test configuration parameters: where, severity, warn_if, error_if, and store_failures. Know what each does and when to use it. A common scenario: "test only recent data" → use where. "Test large incremental without scanning all rows" → use where or a contract NOT NULL constraint.
values: parametertests: at the column or model leveltests/ directory (e.g., tests/assert_total_revenue_positive.sql)dbt test — singular tests are picked up automatically from the tests/ foldertests/generic/ or macros/ directorymodel and column_name as required arguments; can accept additional parameters| Parameter | What It Does |
|---|---|
where | Adds a WHERE clause to the test query — limit which rows are tested (e.g., only last 7 days) |
severity | warn or error — warn logs a warning but doesn't fail the run; error (default) fails the run |
warn_if | Threshold expression for warning: ">=10" means warn if 10+ failures |
error_if | Threshold expression for erroring: ">=100" means only error if 100+ failures |
store_failures | Creates a table of failing rows in the warehouse for investigation |
limit | Caps the number of failure rows stored (with store_failures) |
where for scoping incremental tests and store_failures for production debugging. Know both cold.dbt test --select source:source_nameunique, not_null, accepted_values (values: list), relationships (to: ref/source, field:)
Write them in schema.yml under the model's columns section
SQL files in tests/ that return rows on failure; use ref() and source()
Example: assert that total_amount is never negative
Defined as macros, take model + column_name, useful for reusable logic
Use to limit testing on large incremental models without scanning all rows
Useful for debugging test failures in production; combine with severity: warn
warn = log only; error = fail the run; warn_if/error_if set numeric thresholds
Add not_null and unique to source columns; run dbt test --select source:name
dbt test --select model:stg_* runs tests on all staging models
dbt build runs model + its tests before moving to downstream models; dbt test runs all tests after all models
Covers custom generic tests, store_failures, and test configs in depth
5 exam-style questions on dbt testing.
The logic is inverted from what you'd expect
Candidates write singular tests that return "good" rows expecting them to pass. dbt fails a test when it returns ANY rows — zero rows = pass, any rows = fail.
Write singular tests to SELECT the FAILING rows. If you want "assert no negative amounts", write: SELECT * FROM model WHERE amount < 0. Passing = no rows returned.
warn still logs the failure — it doesn't hide it
Candidates set severity: warn thinking the test won't show up as a failure. The test still runs and logs a warning — it just doesn't cause dbt run to exit with an error code.
Use severity: warn for non-critical quality checks where you want visibility without blocking deployment. The warning is still logged and visible in dbt Cloud.
Duplication instead of reuse
The same test SQL is copy-pasted into multiple singular test files, creating maintenance burden. Changes to the logic must be applied in multiple files.
When the same test logic needs to apply to multiple models/columns, create a custom generic test macro instead. Apply it in .yml like any generic test.
Testing 3 years of data when you only need to test today's rows
Running not_null or unique on a 500M-row incremental table scans the entire table on every run, making test runs extremely slow and expensive.
Add a where config to scope the test to recent data: where: "created_at >= current_date - 7". This tests only the rows that the incremental model would have processed.
Order of operations matters for catching failures early
dbt run builds all models, then dbt test runs all tests — if a test on stg_orders fails, fct_orders was already built with bad data. This wastes compute and propagates errors downstream.
Use dbt build — it runs each node's model + tests before moving downstream. A test failure on stg_orders prevents fct_orders from building, stopping error propagation immediately.
store_failures: true causes dbt to create a table in your warehouse containing the rows that failed the test. This is invaluable for debugging production test failures — instead of just knowing "50 rows failed the not_null test", you can query the failure table to see exactly which rows are null and why. Enable it on high-value tests in production environments.store_failures and severity at the project level in dbt_project.yml under a tests: block. These apply to all tests unless overridden at the model or column level. This is useful for enabling store_failures in production without editing every test definition.warn_if and error_if set numeric thresholds for different severity levels. For example: warn_if: ">=1" and error_if: ">=50" means: if 1–49 rows fail → warn; if 50+ rows fail → error. This allows gradual escalation rather than a binary pass/fail.min_value and max_value parameters. These are passed in the .yml test definition: - between_values: {min_value: 0, max_value: 100}.dbt test --select tag:tag_name. Tags can be applied to tests in the same way as models — in the test's config block or in dbt_project.yml. This is useful for running a subset of expensive tests separately from routine tests.dbt test. Source tests defined in sources.yml are picked up alongside model tests from schema.yml. You can filter with --select source:source_name to run only source tests, or --select model_name for model tests. Both generate SQL queries that must return 0 rows to pass.