How it Works

The expect_column_values_to_be_in_set test from the dbt-expectations package validates that every value in a column is a member of a specified allowed set. This is the row-level equivalent of expect_column_distinct_values_to_be_in_set — it checks every individual row, not just the set of distinct values.

An optional quote_values argument controls whether list values are treated as strings, and row_condition can scope the test to a subset of rows.

Steps and Conditions

1. Column Selection: Choose the column to validate.

2. Define Allowed Set: List acceptable values using value_set.

3. Optional Config: Use quote_values and row_condition as needed.

4. Execution: Every row is checked against the allowed set.

5. Outcome: Pass if all values are in the set; fail if any fall outside it.

Example Usage: Ride-sharing Platform

A ride-sharing platform tracks trip status and wants to ensure every row contains only valid, recognized status codes.

models:
  - name: trips
    columns:
      - name: trip_status
        tests:
          - dbt_expectations.expect_column_values_to_be_in_set:
              value_set: ['requested', 'accepted', 'in_progress', 'completed', 'cancelled'

models:
  - name: trips
    columns:
      - name: trip_status
        tests:
          - dbt_expectations.expect_column_values_to_be_in_set:
              value_set: ['requested', 'accepted', 'in_progress', 'completed', 'cancelled'

models:
  - name: trips
    columns:
      - name: trip_status
        tests:
          - dbt_expectations.expect_column_values_to_be_in_set:
              value_set: ['requested', 'accepted', 'in_progress', 'completed', 'cancelled'

Any unrecognized status code triggers a test failure, protecting downstream dashboards from broken groupings.