Choosing between generic, singular, custom, and unit tests

dbt supports generic tests (unique, not_null, accepted_values, relationships) applied via YAML properties, singular tests written as standalone SQL files, and custom generic tests built as reusable macros—together covering models, sources, seeds, and snapshots. Unit tests, added in dbt Core 1.8+, let you validate transformation logic against static mock inputs/outputs independent of warehouse data, complementing the data-quality focus of the other test types.

1 · Learn the must-know

• 1Generic tests (unique, not_null, accepted_values, relationships) are defined once as parametrized macros and applied to any column/model/source via YAML under a tests: or data_tests: key.
• 2Singular tests are one-off SQL SELECT statements stored as .sql files in the tests/ directory that return failing rows; no macro or reuse mechanism is needed.
• 3Custom generic tests are authored as macros named test_<name> (typically in the tests/generic or macros/ folder) accepting model and column_name plus optional arguments, then invoked like built-in generic tests in YAML—commonly sourced from packages like dbt-utils or dbt-expectations.
• 4Any test can be tuned with config options such as severity: warn/error, error_if/warn_if thresholds, store_failures, and limit, and can target models, sources, seeds, or snapshots.
• 5Unit tests (dbt Core 1.8+) are defined under a unit_tests: key on a model, using given (mocked input rows/refs) and expect (expected output rows) blocks to validate SQL logic without querying the live warehouse.
• 6dbt test runs generic and singular data tests by default; unit tests are typically invoked via dbt test --select test_type:unit (or included in dbt build) and are meant to run fast in CI before deploying model logic.

2 · Check your understanding