DBT testing, Best Practices for DBT Testing
“Data testing is the foundation of trust; because decisions are only as good as the data behind them.”
Why testing matters
Every pipeline breaks eventually. A source changes a column name. A new upstream team pushes nulls. An ETL job silently doubles rows. Without tests, none of these surfaces until someone notices the dashboard is wrong, and by then, decisions have already been made on bad data.
dbt gives you two tools to catch this early: generic tests (built-in, zero SQL) and singular tests (custom SQL for your business rules). Use both.
Generic Tests: fast coverage, zero SQL
Generic tests are reusable assertions about your models. DBT comes with four built-in generic tests:
Unique: Tests that values in a column are unique (E.g, Primary Keys)
Not Null: Tests that a column contains no null values (E.g, ProductPrice)
Relationships: Tests referential integrity between tables, it confirms value in one column exists in another column (e.g, Foreign Key)
Accepted Values: Tests that values in a column are from a defined set (e.g, Accepted Gender in a Column Gender)
Models/schema.yml
Why generic tests are not enough?
But simple truth is generic test do not cover the key dimensions about data quality, so if your goal is true data quality not just testing “pipeline didn’t break”, then generic test alone won’t satisfy your conditions
Some of Core Dimensions of Data Quality are
Accuracy: Is the data correct?
Validity: Does it follow defined rules/formats?
Completeness: Is required data present?
Consistency: Is data consistent across systems?
Timeliness: Is data up to date?
Imagine you are working on e commerce company, where the orders table has full generic test coverage, and every dbt test passes daily, But the finance team notices revenue is higher than expected.
The issue? Refunded orders are still being counted as revenue. Generic tests did not catch this because the data was structurally valid (no nulls, duplicates, invalid values), but the business logic was wrong. This shows generic tests endures data is well formed, not necessarily correct.
“Data pipelines rarely fail loudly; they fail silently with wrong results.”
This is the gap. Generic tests guard columns. They cannot guard intent. To address this gap, we introduce a singular test that explicitly validates business correctness
Singular Test: Enforcing Business Logic
Singular data tests are custom SQL queries stored in the tests/ folder; if the query returns any rows, the test fails.
Unlike generic tests, Singular tests are designed for complex, one-off business logic checks (e.g., “no order should have a refund greater than its total”) and are not reusable across models.
THE RULE THIS TEST ENFORCES A refunded order must never appear in the revenue fact table. If any rows come back, something upstream is broken.
Best practices of DBT Testing
1: Test every model, not just sources
Most teams test raw sources and call it done. But transformations break too, a faulty join, an unexpected null, an upstream schema change can silently corrupt your final models.
Rule: Add tests to staging, intermediate, and final (mart) models , not just raw data.
2: Name tests after the rule
Use refunded_orders_not_in_revenue.sql not test_orders.sql. It will help future team to clear picture the purpose of the test. Future-you will thank present-you.
3: One rule per singular test
Don’t pack 3 checks into one SQL file. Keep each test focused and easy to debug. Always focus on one rule per test
4: Add a comment explaining why
Not just what the test checks, but why the rule exists. Context ages well; code alone doesn’t.
5: Store Failures for Debugging
When a test failed in production, you want to see which rows failed, not just that something went wrong, use store_failure: true constraint in schema.yml file
6: Use Thresholds and severity levels
You can configure Test in different ways
Severity Set tests to either error (default) or warn, Use error for things that must never reach production, and warn for checks that signal something worth investigating but not critical enough to block a run.
Thresholds Allow a certain number or percentage of failures, used to set realistic thresholds that distinguish a minor data quality issue from a critical failure.
Orchestrating your tests
Tests only help if they run. Wire them into your workflow at every stage not just at the end.
CI/CD RULE OF THUMB Block merges if any severity: error test fails. Let severity: warn tests alert on Slack but not block the PR. This keeps your pipeline moving without hiding real problems.
Scheduling: when to run what
Match test frequency to the cost of a failure. Mission-critical checks run on every deploy. Trend checks run nightly.
After every PR must run Generic Tests + critical singular tests on changed models, and after each run use dbt build command to run models and tests always run together
Use dbt Cloud, Airflow, or GitHub Actions – the scheduler does not matter. The habit does. Run tests. Every time. Automatically.
“Good dbt tests aren’t about catching every bug. They’re about making sure that when something breaks, you find out before your stakeholders do.”
Resources:
Netflix Data Analysis | DBT (databuildtool) Tutorial Course by Darshil Parmar
https://publish.obsidian.md/datavidhya/Course+Notes/Dbt(databuildtool)/9.+Testing
Leave a Reply