How it Works

The not_null_proportion test from dbt-utils™ validates that the fraction of non-null values in a column meets or exceeds a specified threshold. Unlike a strict not_null test, this allows for a configurable level of acceptable nulls — useful for columns that are optional but should still be populated for the majority of records.

The at_least argument specifies the minimum proportion (between 0 and 1) of non-null values required.

Steps and Conditions

1. Column Selection: Identify the column to evaluate.

2. Set Threshold: Define the minimum non-null proportion using at_least.

3. Execution: The proportion of non-null values is computed.

4. Outcome: Pass if the proportion meets or exceeds the threshold; fail otherwise.

Example Usage: User Profiles

A platform collects user phone numbers as an optional field, but expects at least 70% of users to have provided one.

models:
  - name: user_profiles
    columns:
      - name: phone_number
        tests:
          - dbt_utils.not_null_proportion:
              at_least: 0.70

models:
  - name: user_profiles
    columns:
      - name: phone_number
        tests:
          - dbt_utils.not_null_proportion:
              at_least: 0.70

models:
  - name: user_profiles
    columns:
      - name: phone_number
        tests:
          - dbt_utils.not_null_proportion:
              at_least: 0.70

If fewer than 70% of records have a phone number, the test fails and triggers a review of the data collection flow.