How it Works

The expect_column_distinct_values_to_equal_set test from the dbt-expectations package validates that the complete set of distinct values in a column exactly matches a predefined list — no more, no less. This is stricter than expect_column_distinct_values_to_be_in_set (which allows a subset) and is ideal for enumerated fields like status codes where the full value space must be controlled.

Steps and Conditions

1. Column Selection: Identify the column to evaluate.

2. Define Expected Set: List all expected distinct values using value_set.

3. Execution: The actual distinct values are compared against the expected set.

4. Outcome: Pass if the actual and expected sets are identical; fail if there are any additions or omissions.

Example Usage: Order Management

An order management system uses a fixed set of order statuses. The team wants to ensure no undocumented status codes have been introduced.

models:
  - name: orders
    columns:
      - name: order_status
        tests:
          - dbt_expectations.expect_column_distinct_values_to_equal_set:
              value_set: ['pending', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded'

models:
  - name: orders
    columns:
      - name: order_status
        tests:
          - dbt_expectations.expect_column_distinct_values_to_equal_set:
              value_set: ['pending', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded'

models:
  - name: orders
    columns:
      - name: order_status
        tests:
          - dbt_expectations.expect_column_distinct_values_to_equal_set:
              value_set: ['pending', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded'

Any new or missing status code will cause the test to fail, prompting a documentation and review cycle.