How to Use not_null Test in dbt for Data Quality

In dbt, use the not_null test to check that a column contains no null values by adding it to your model's schema file under tests. This test runs automatically during dbt runs and fails if any nulls are found in the specified column.

Syntax

The not_null test is defined in your model's schema YAML file under the tests key for a specific column. It requires only the column name and no additional parameters.

Basic structure:

• models/your_model.yml: The schema file where tests are declared.
• columns: List of columns in the model.
• tests: List of tests to apply to each column.
• not_null: The test that ensures no null values exist in the column.

yaml

version: 2
models:
  - name: your_model
    columns:
      - name: your_column
        tests:
          - not_null

Example

This example shows how to apply the not_null test to the user_id column in a model called users. When you run dbt test, dbt will check that user_id has no null values.

yaml

version: 2
models:
  - name: users
    columns:
      - name: user_id
        tests:
          - not_null
      - name: email
        tests:
          - not_null

Output

Running with dbt=1.4.0
Found 1 model, 2 tests

1 of 2 START test not_null_users_user_id.................... [RUN]
1 of 2 PASS test not_null_users_user_id..................... [PASS]
2 of 2 START test not_null_users_email...................... [RUN]
2 of 2 PASS test not_null_users_email....................... [PASS]

All tests passed!

Common Pitfalls

Common mistakes when using not_null test include:

• Forgetting to add the test under the correct column in the schema file.
• Running dbt run but not dbt test, so tests are not executed.
• Assuming not_null fixes data; it only reports nulls.
• Using not_null on columns that legitimately contain nulls, causing test failures.

Always verify your data expectations before applying this test.

yaml

version: 2
models:
  - name: users
    columns:
      - name: user_id
        # Incorrect: test name misspelled
        tests:
          - notnull  # wrong

# Correct:
version: 2
models:
  - name: users
    columns:
      - name: user_id
        tests:
          - not_null

Quick Reference

Element	Description
version	Schema file version, usually 2
models	List of models to define tests for
name	Name of the model or column
columns	List of columns in the model
tests	List of tests to apply to a column
not_null	Test to ensure no null values in the column

Key Takeaways

Add the not_null test in your model's schema YAML under the column's tests list.

Run dbt test to execute the not_null test and catch null values.

The not_null test only checks for nulls; it does not fix data issues.

Ensure the column should never have nulls before applying this test to avoid false failures.

Use correct YAML syntax and test names to avoid configuration errors.