How it Works

The expect_column_values_to_be_null test from the dbt-expectations package is the inverse of expect_column_values_to_not_be_null. It validates that every value in a column IS null. This may seem unusual, but it's useful for testing columns that represent optional fields that have been intentionally nulled out, deprecated columns scheduled for removal, or the output of conditional logic that should produce nulls in a specific scenario.

An optional row_condition can scope the check to a relevant subset of rows.

Steps and Conditions

1. Column Selection: Identify the column to validate.

2. Optional Row Condition: Filter rows using row_condition.

3. Execution: Each row is checked to confirm the value is null.

4. Outcome: Pass if all (filtered) values are null; fail if any non-null values are present.

Example Usage: Deprecation Testing

A data engineering team is deprecating the legacy_account_code column, which should be null for all new records created after a migration date.

models:
  - name: accounts
    columns:
      - name: legacy_account_code
        tests:
          - dbt_expectations.expect_column_values_to_be_null:
              row_condition: "created_at >= '2024-01-01'"

models:
  - name: accounts
    columns:
      - name: legacy_account_code
        tests:
          - dbt_expectations.expect_column_values_to_be_null:
              row_condition: "created_at >= '2024-01-01'"

models:
  - name: accounts
    columns:
      - name: legacy_account_code
        tests:
          - dbt_expectations.expect_column_values_to_be_null:
              row_condition: "created_at >= '2024-01-01'"

Any new record that inadvertently populates the legacy column will fail the test, alerting the team to fix the upstream system.