Building dbt Tests with an AI Agent
The project went from 24 tests to 53 in one session. The agent wrote 30 new tests, ran them, and confirmed all passing, for $1.51 total.
OVERVIEW
Writing dbt tests is one of those tasks that data engineers know they should do more of, and consistently don't do enough of. The result is models that look correct until they hit production. Then they start producing wrong answers that could have been caught by a not_null or accepted_values test that took 10 seconds to write.
This demo audits a dbt project's existing 24 tests, identifies the gaps (zero coverage in intermediate and mart layers), plans the additions, and implements 30 new tests, including generic tests, accepted_values with extracted thresholds, singular tests converted to proper dbt format, and a dbt unit test for price segment logic.
The agent doesn't just blindly generate tests. When it discovers that 15 hosts in the source data have null names (a real data quality issue in the Airbnb dataset), it downgrades the test from error to warn severity, because a test that breaks the build for a known source data quirk is worse than no test at all. After all additions, the full dbt build runs: 53 tests, 2 expected warnings, zero errors.
WHAT YOU'LL LEARN
- →How to audit test coverage gaps across staging, intermediate, and mart layers
- →How to write accepted_values tests by reading CASE logic in existing models
- →How to handle real-world data quality quirks without breaking the build
- →How dbt unit tests work and when to use them for transformation logic
KEY POINTS
- Audits 24 existing tests, finds zero coverage in intermediate and mart layers
- Adds not_null, unique, accepted_values, and unit tests per layer
- Extracts accepted_values thresholds directly from CASE statements in the SQL
- Discovers real data quality issue (null host names) and correctly sets warn severity
- Final build: 53 tests pass, 2 expected warnings, zero errors, $1.51 total
