How to build a reliable dbt test suite: built-in tests, custom generic tests, singular tests, and plugging dbt-utils and dbt-expectations into your CI.
dbt tests are the cheapest form of data quality insurance available. The four built-in tests -- not_null, unique, accepted_values, relationships -- take five minutes to add to a model and catch the most common production failures: nulls in a primary key, broken foreign key relationships, and out-of-vocabulary values in categorical fields. There is no excuse for a production dbt project that does not have these on every model.
The right test architecture mirrors the right software test architecture: test at the boundary, not at every step. In dbt terms, this means putting your heaviest tests on staging models (which define the canonical shape of raw data) and on final mart models (which define the contract with BI and ML consumers). Testing every intermediate model is expensive and noisy. Be selective.
Singular tests (SQL queries that must return zero rows) are underused. They are the right tool for business logic assertions that cannot be expressed generically: "the sum of refunds must never exceed the sum of revenue for a given order," "every user who purchased must have an account_created_at before their first_order_at." Write these for your domain invariants. They run in CI and catch the regressions that generic schema tests miss.
dbt test coverage has a natural ceiling: tests can only catch what you thought to test for. Complement dbt tests with statistical anomaly detection (Elementary, Metaplane, Monte Carlo) that alerts on distribution shifts without requiring you to predefine thresholds. The combination -- rule-based tests in CI plus statistical monitoring in production -- covers both known and unknown failure modes.
The canonical reference for dbt's four built-in tests (not_null, unique, accepted_values, relationships) and the custom generic test interface. Start here.
A library of 30+ generic tests that cover 90% of the test cases you will want beyond the four built-ins. Install this before writing any custom tests.
Ports Great Expectations' expression language into dbt. Useful for statistical tests (column mean within range, row count within bounds) that dbt-utils does not cover.
Turns your dbt test results into an observability dashboard with anomaly detection. The open-source tier is genuinely useful for teams that want data quality monitoring without a paid tool.
New resources and perspective on building AI-ready data systems, a few times a month. No spam.