How it Works

The expect_column_values_to_not_be_in_set test from the dbt-expectations package verifies that none of the values in a column match any value from a specified exclusion set. This is the inverse of expect_column_values_to_be_in_set and is useful for flagging deprecated, invalid, or sentinel values that should no longer appear in production data.

The quote_values argument (default true) controls whether values in the set are treated as strings. A row_condition can be used to scope the check to a subset of rows.

Steps and Conditions

1. Column Selection: Choose the column to validate.

2. Define Exclusion Set: List values that must not appear using value_set.

3. Optional Row Condition: Filter rows before evaluation with row_condition.

4. Execution: Each row is checked; any match to the exclusion set triggers a failure.

5. Outcome: Pass if no values appear in the set; fail if any do.

Example Usage: HR Platform

An HR platform retired several employment status codes but wants to ensure they no longer appear in active employee records.

models:
  - name: employees
    columns:
      - name: employment_status
        tests:
          - dbt_expectations.expect_column_values_to_not_be_in_set:
              value_set: ['legacy', 'archived', 'unknown']
              row_condition: "is_active = true"

models:
  - name: employees
    columns:
      - name: employment_status
        tests:
          - dbt_expectations.expect_column_values_to_not_be_in_set:
              value_set: ['legacy', 'archived', 'unknown']
              row_condition: "is_active = true"

models:
  - name: employees
    columns:
      - name: employment_status
        tests:
          - dbt_expectations.expect_column_values_to_not_be_in_set:
              value_set: ['legacy', 'archived', 'unknown']
              row_condition: "is_active = true"

This guarantees that active employee records only carry valid, current status codes.