How to Write Tests in dbt: Syntax, Examples, and Tips

In dbt, you write tests by defining tests in your model or schema YAML files using built-in or custom test macros. You specify the test type and the column or condition to check, then run dbt test to validate your data models.

Syntax

dbt tests are defined in YAML files under the tests key for models or columns. You can use built-in tests like unique, not_null, or write custom SQL tests. Each test specifies the test name and the column it applies to.

Example parts:

• model: The table or model name.
• columns: List of columns to test.
• tests: The test(s) to run on each column.

yaml

models/my_model.yml:
version: 2
models:
  - name: my_model
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: email
        tests:
          - not_null
          - unique

Example

This example shows how to write tests for a model customers to ensure the customer_id is unique and not null, and the email is not null.

yaml

models/customers.yml:
version: 2
models:
  - name: customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: email
        tests:
          - not_null

Output

Running with dbt=1.4.6
Found 1 model, 3 tests

1 of 3 START test unique_customers_customer_id................... [RUN]
1 of 3 PASS test unique_customers_customer_id.................... [PASS in 0.1s]
2 of 3 START test not_null_customers_customer_id................ [RUN]
2 of 3 PASS test not_null_customers_customer_id................. [PASS in 0.1s]
3 of 3 START test not_null_customers_email...................... [RUN]
3 of 3 PASS test not_null_customers_email....................... [PASS in 0.1s]

All tests passed!

Common Pitfalls

Common mistakes when writing dbt tests include:

• Forgetting to add version: 2 in the YAML file, which is required for tests.
• Using incorrect model or column names that do not match your project.
• Not running dbt test after adding tests to see results.
• Trying to write tests directly in SQL files instead of YAML schema files.

yaml

Incorrect example (missing version):
models/customers.yml:
models:
  - name: customers
    columns:
      - name: customer_id
        tests:
          - unique

Correct example:
models/customers.yml:
version: 2
models:
  - name: customers
    columns:
      - name: customer_id
        tests:
          - unique

Quick Reference

Test Name	Purpose	Usage Example
unique	Checks column values are unique	- unique
not_null	Checks column has no null values	- not_null
accepted_values	Checks column values are in a list	- accepted_values: values: ['A', 'B', 'C']
relationships	Checks foreign key relationships	- relationships: to: ref('other_model') field: id

Key Takeaways

Write tests in YAML schema files under the 'tests' key for models or columns.

Use built-in tests like 'unique' and 'not_null' to check data quality easily.

Always include 'version: 2' at the top of your YAML test files.

Run 'dbt test' to execute all defined tests and see results.

Avoid writing tests directly in SQL files; use YAML for test definitions.