Write comprehensive dbt tests including custom macros
Take an existing dbt project and build a testing layer that goes beyond not_null and unique. You will write singular tests for complex business rules, create a custom generic test as a Jinja macro, add source freshness checks, and run the full test suite in a CI workflow.
Why this matters
Most dbt projects have schema.yml tests that check not_null and unique, and nothing else. Those tests catch structural problems; they do not catch business rule violations. A customer cannot have negative lifetime value. Orders cannot complete before they are created. Revenue cannot go backwards. These rules live in your head until you write dbt tests for them.
Before you start
- dbt Core installed with a DuckDB or PostgreSQL adapter (pip install dbt-core dbt-duckdb)
- A dbt project with at least 2 models (the revenue model from the previous exercise is perfect)
- Basic dbt knowledge: you can run dbt run and dbt test
- Basic Jinja templating knowledge (learnable during the exercise)
Step-by-step guide
- 1
Audit your current tests
Run dbt test and note what passes. Then read every model and list 3-5 business rules that are not tested: 'revenue cannot be negative', 'order_count cannot be zero if revenue is non-zero', 'date must be in the past'. These are your test targets.
- 2
Write singular tests
Create tests/assert_revenue_is_positive.sql. Write a SELECT that returns rows violating the rule: SELECT * FROM {{ ref('daily_revenue') }} WHERE revenue < 0. If any rows are returned, dbt treats it as a test failure. Run dbt test --select assert_revenue_is_positive. Then insert a bad row manually and verify the test catches it.
- 3
Write a custom generic test macro
Create macros/test_not_negative.sql. Use the dbt macro pattern: {% macro test_not_negative(model, column_name) %} SELECT * FROM {{ model }} WHERE {{ column_name }} < 0 {% endmacro %}. Add it to schema.yml as a column test: - not_negative. This macro is now reusable across every model.
- 4
Add referential integrity tests
Write a singular test that checks every customer_id in your fact table exists in your dimension table. Referential integrity failures are the most common data warehouse quality issue; they mean your joins silently drop rows without any error.
- 5
Configure source freshness
In sources.yml, add loaded_at_field and freshness thresholds for each source table. Run dbt source freshness. Set warn_after to 24 hours and error_after to 48 hours. Source freshness runs independently of dbt test and should be scheduled separately.
- 6
Run the full suite in CI
Configure a GitHub Actions workflow that runs dbt build --target ci: compile, run models against a test schema, run all tests, produce JUnit XML as an artifact. A suite that passes locally but fails in CI means the CI environment is wrong; fix the environment, not the tests.