How it Works

The not_accepted_values test from dbt-utils™ is the logical complement of the built-in accepted_values test. It asserts that a column contains no values from a specified list. This is useful for enforcing data quality rules where certain values should never appear — for example, deprecated status codes, placeholder strings, or system-generated sentinel values.

Steps and Conditions

1. Column Selection: Choose the column to validate.

2. Define Disallowed Values: List the values that must not appear using the values argument.

3. Execution: Each row is checked against the disallowed list.

4. Outcome: Pass if no disallowed values are found; fail if any are present.

Example Usage: CRM Platform

A CRM platform decommissioned several legacy lead source codes. The team wants to ensure these codes are fully purged from the active leads table.

models:
  - name: leads
    columns:
      - name: lead_source
        tests:
          - dbt_utils.not_accepted_values:
              values: ['imported_legacy', 'manual_entry_v1', 'unknown_source'

models:
  - name: leads
    columns:
      - name: lead_source
        tests:
          - dbt_utils.not_accepted_values:
              values: ['imported_legacy', 'manual_entry_v1', 'unknown_source'

models:
  - name: leads
    columns:
      - name: lead_source
        tests:
          - dbt_utils.not_accepted_values:
              values: ['imported_legacy', 'manual_entry_v1', 'unknown_source'

Any appearance of these deprecated codes triggers a test failure, alerting the team to investigate the source of stale data.