How it Works

The expect_column_distinct_values_to_be_in_set test from the dbt-expectations package validates that every distinct value present in a column is a member of a specified allowed set. Unlike expect_column_distinct_values_to_equal_set, this test does not require all listed values to be present — it only ensures that no unlisted values appear. This is ideal for columns with optional or sparse enumerations.

Steps and Conditions

1. Column Selection: Identify the column to validate.

2. Define Allowed Set: List all permitted distinct values using value_set.

3. Execution: All distinct values in the column are compared against the allowed set.

4. Outcome: Pass if every distinct value is in the set; fail if any unlisted value appears.

Example Usage: Media Platform

A streaming platform tags content by region. The allowed regions are well-defined, but not all regions may be present in every data refresh.

models:
  - name: content_catalogue
    columns:
      - name: region_code
        tests:
          - dbt_expectations.expect_column_distinct_values_to_be_in_set:
              value_set: ['US', 'GB', 'DE', 'FR', 'AU', 'CA', 'JP'

models:
  - name: content_catalogue
    columns:
      - name: region_code
        tests:
          - dbt_expectations.expect_column_distinct_values_to_be_in_set:
              value_set: ['US', 'GB', 'DE', 'FR', 'AU', 'CA', 'JP'

models:
  - name: content_catalogue
    columns:
      - name: region_code
        tests:
          - dbt_expectations.expect_column_distinct_values_to_be_in_set:
              value_set: ['US', 'GB', 'DE', 'FR', 'AU', 'CA', 'JP'

This ensures no unknown or malformed region codes enter the catalogue.