Guarding Required Columns with the dbt not_null Test: Practice & Analytics Engineer Prep

not_null is dbt's built-in generic test that guarantees a column contains zero NULL values.

Applying it at both the source and model layers to catch missing values early is important both in practice and on the exam.

not_null Basics and What the Exam Tests Most

not_null is a generic test that verifies a target column has zero NULLs. dbt runs every test as a SELECT that returns failing rows: 0 rows means pass, 1+ rows means fail. It is a stable feature of the official documentation and applies to models, sources, and seeds.

On the Analytics Engineer exam, expect questions on where to place not_null (both sources and staging/intermediate/fact layers), test configuration (severity, store_failures, where), and how to choose between related tests (unique, relationships, accepted_values).

• Pass condition: failing rows = 0
• Applies to: models, sources, seeds
• Common pairings: unique (primary key), relationships (foreign-key style integrity)
• Operations: roll out incrementally — severity=error for critical columns, warn for observation columns

Where not_null sits in the data quality chain

YAML Implementation Patterns (models, sources, seeds)

The minimal setup is to list not_null under a column's tests section. Use severity=error for high-importance columns and warn while you are still in observation mode, rolling CI out gradually. The where clause can exclude logically deleted rows. Enabling store_failures persists failing rows to an audit table, making root-cause analysis easier (the storage schema and naming depend on the adapter).

Define not_null at the source layer too so that upstream NULLs are caught early. From staging onward, the standard practice is to lock down primary keys, foreign keys, and business keys with not_null.

• models: target business keys, primary keys, and foreign keys
• sources: monitor upstream quality on columns that must always have a value
• seeds: apply to required columns of reference master data
• where: exclude rows with conditions like is_deleted=false
• store_failures: persist failing rows to shorten root-cause investigation

YAML example (applying not_null on models and sources)

version: 2

models:
  - name: fct_orders
    description: Final fact table for orders
    columns:
      - name: order_id
        description: Primary key
        tests:
          - unique
          - not_null:  # critical columns use error
              severity: error
              store_failures: true
      - name: customer_id
        description: Foreign key to dim_customers
        tests:
          - not_null:
              severity: error
              where: "is_deleted = false"  # exclude logically deleted rows
          - relationships:
              to: ref('dim_customers')
              field: customer_id

sources:
  - name: app
    schema: raw_app
    tables:
      - name: orders_raw
        columns:
          - name: order_id
            tests:
              - not_null  # catch upstream NULLs early
          - name: created_at
            tests:
              - not_null: { severity: warn }  # monitor while rolling out gradually

Running and Tuning (severity, store_failures, selective execution)

dbt test can be scoped to specific nodes. Specifying a model name and running only the tests attached to it is the most reliable workflow. In CI, the easiest policy is to block on critical columns with severity=error and just log warn-level issues during observation.

store_failures keeps failing rows for investigation. To avoid long-term accumulation, use a scheduled cleanup policy or a dedicated schema.

• Basic run: dbt test -s fct_orders
• By tag: tag critical tests and run dbt test -s tag:critical
• All generic tests: dbt test -s test_type:generic (selectors vary by adapter/version — check the official docs)
• CI: failure = deploy block (severity=error); warn only logs

Common Pitfalls and How to Avoid Them

Empty strings are not NULLs: in most warehouses (Snowflake, BigQuery, Redshift, etc.) '' and NULL are different. not_null does not catch empty strings, so when upstream may send them, normalize first in staging with NULLIF(TRIM(col), '') and only then apply not_null.

JOINs can introduce NULLs: a LEFT JOIN produces NULL on the right side for unmatched rows. When a column that must satisfy not_null ends up NULL after a join, reconsider the join strategy (switch to INNER JOIN, pre-filter, or apply COALESCE).

Casts and derived columns: failed CASTs or conditional branches can quietly produce NULLs. Either default the value with COALESCE immediately after derivation, or reinforce quality by pairing with accepted_values to constrain the expected range.

• Empty strings: normalize with NULLIF(TRIM(col), '') before applying not_null
• Joins: switch to INNER JOIN, pre-align data, or COALESCE
• Derived columns: COALESCE / default values + supplemental tests
• Large tables: narrow scope with where (combine with operational policy)

Designing and Governing Required Columns

not_null at the source layer is for early detection, while not_null in staging and core layers enforces the data contract. In particular, monitor primary keys, foreign keys, and business keys continuously by combining not_null with unique and relationships.

In CI/CD, make a not_null failure on a critical column a deploy-blocking condition. Persist failing rows to an audit schema via store_failures, and document the SLA from detection to recovery.

• Define required columns per layer and stack tests on top of each other
• Block on critical columns with severity=error
• Persist failing rows and alert on them to shorten MTTR
• Documentation: spell out which columns are required and why

Choosing Between not_null and Related Tests (Comparison Table)

not_null guarantees existence, but full integrity comes from combining it with other tests. Knowing how it compares to the main alternatives makes the exam's multiple-choice questions much easier.

• Primary key: not_null + unique
• Foreign-key style integrity: not_null + relationships
• Code values: not_null + accepted_values

Check Your Understanding

Frequently Asked Questions