How it Works

The expect_column_distinct_values_to_contain_set test from the dbt-expectations package validates that a column contains at least all the values specified in a required set. Unlike expect_column_distinct_values_to_equal_set, additional values beyond the required set are permitted. This is useful when certain values are mandatory but the full value space may be larger.

Steps and Conditions

1. Column Selection: Identify the column to evaluate.

2. Define Required Set: List the values that must be present using value_set.

3. Execution: The actual distinct values are checked to confirm all required values are present.

4. Outcome: Pass if all required values are found; fail if any are missing.

Example Usage: Country Coverage

A global platform wants to ensure its country_code column always includes at least the key markets: US, GB, and DE.

models:
  - name: global_customers
    columns:
      - name: country_code
        tests:
          - dbt_expectations.expect_column_distinct_values_to_contain_set:
              value_set: ['US', 'GB', 'DE'

models:
  - name: global_customers
    columns:
      - name: country_code
        tests:
          - dbt_expectations.expect_column_distinct_values_to_contain_set:
              value_set: ['US', 'GB', 'DE'

models:
  - name: global_customers
    columns:
      - name: country_code
        tests:
          - dbt_expectations.expect_column_distinct_values_to_contain_set:
              value_set: ['US', 'GB', 'DE'

If any of the key market codes disappears from the dataset, the test fails, alerting the team to a potential data segmentation or filtering issue.