Skip to main content

01. DBT Testing Framework

1. What Is the DBT Testing Framework?​

The DBT testing framework validates data in your analytics pipeline. It helps you check:

  • schema constraints
  • data quality rules
  • business logic expectations
  • source data assumptions

A DBT test passes when it returns zero rows.

If the test query returns rows, those rows are treated as the failed records.


2. Why Testing Matters​

Testing helps data teams:

  • catch bad data before it reaches reports
  • build trust in dashboards and metrics
  • document assumptions as code
  • automate quality checks in CI/CD
  • debug data issues faster

2.1 The main goal of testing​

The purpose of testing is not just to find broken data. It is to create a repeatable process that protects the warehouse from bad assumptions and low-quality transformations.


3. The Core Testing Rule​

Every DBT test follows this idea:

SELECT *
FROM some_model
WHERE something_is_wrong
Query ResultDBT Result
0 rows returnedTest passed
1 or more rows returnedTest failed

This is true for built-in tests and custom tests.

3.1 How to think about a passing test​

A test passes when the condition you consider wrong does not appear in the data.

3.2 How to think about a failing test​

A test fails when it finds records that violate the rule. Those failing rows become the evidence for debugging and fixing the data issue.


4. Main Types of DBT Tests​

Test TypeWhere You Define ItBest For
Built-in testsmodels/schema.ymlCommon checks like not_null, unique, accepted_values, relationships
Generic custom teststests/generic/ + schema.ymlReusable business rules
Singular custom teststests/One specific SQL validation
Source freshness testsmodels/schema.ymlChecking whether source data is fresh

4.1 Built-in tests​

These are the most common tests and cover typical quality checks without requiring custom SQL.

4.2 Generic custom tests​

These are reusable checks you can apply to many models with the same pattern.

4.3 Singular custom tests​

These are ideal when a rule is specific to one model or one dataset.

4.4 Source tests​

These validate the raw data entering your pipeline before downstream models are built.


Read this module in this order:

  1. DBT Testing Framework - understand the testing concept.
  2. Beginner-Friendly Test Setup - create sample data and a simple model.
  3. Built-in Tests - learn not_null, unique, accepted_values, and relationships.
  4. Custom Tests Overview - understand generic vs singular tests.
  5. Generic Custom Tests - write reusable tests for schema.yml.
  6. Singular Custom Tests - write one-model SQL checks.
  7. Test Coverage Strategy - decide what to test in real projects.

6. Useful DBT Test Commands​

CommandPurpose
dbt testRun all tests in the project
dbt test --select <model_name>Run tests related to one model
dbt test --select test.<test_name>Run a specific generic test
dbt test --select <singular_test_file_name>Run a specific singular test
dbt test --select path:models/silverRun tests for a folder
dbt test --store-failuresStore failing rows for debugging

6.1 When to use each command​

Use the broad command during normal project validation, and use --select when you want to focus on one model, test, or folder during development.


7. Common DBT Test Failure Patterns​

  • not_null fails because a required column contains nulls.
  • unique fails because duplicate values exist.
  • accepted_values fails because unexpected values were loaded.
  • relationships fails because a foreign key does not match the parent table.
  • Custom tests fail because business rules are violated.

7.1 How to respond to failures​

Each failed test should lead to one question: “Is the data wrong, or is the model logic wrong?” This is the key to fast debugging.


8. Summary​

DBT testing makes data quality repeatable and transparent. Start with simple built-in tests, then add custom tests only when your business rules need more specific logic.

Key takeaway​

Testing is not optional in a modern dbt workflow. It is how you protect the trust, quality, and reliability of your data products.