How it Works

The expect_column_pair_values_to_be_in_set test from the dbt-expectations package validates that for every row, the combination of values from two columns is a member of an explicitly defined set of allowed pairs. This is useful for enforcing business rules where certain column value combinations are valid while others are not.

Steps and Conditions

1. Column Pair: Specify the two columns using column_A and column_B.

2. Define Allowed Pairs: List the acceptable value combinations using value_pairs_set.

3. Optional Row Condition: Filter rows using row_condition.

4. Execution: Each row's column pair is checked against the allowed set.

5. Outcome: Pass if all pairs are in the allowed set; fail if any invalid combination exists.

Example Usage: Product Configuration

A product configuration model should only allow specific combinations of product_tier and billing_cycle that the pricing team has approved.

models:
  - name: product_configurations
    tests:
      - dbt_expectations.expect_column_pair_values_to_be_in_set:
          column_A: product_tier
          column_B: billing_cycle
          value_pairs_set:
            - ['starter', 'monthly']
            - ['professional', 'monthly']
            - ['professional', 'annual']
            - ['enterprise', 'annual'

models:
  - name: product_configurations
    tests:
      - dbt_expectations.expect_column_pair_values_to_be_in_set:
          column_A: product_tier
          column_B: billing_cycle
          value_pairs_set:
            - ['starter', 'monthly']
            - ['professional', 'monthly']
            - ['professional', 'annual']
            - ['enterprise', 'annual'

models:
  - name: product_configurations
    tests:
      - dbt_expectations.expect_column_pair_values_to_be_in_set:
          column_A: product_tier
          column_B: billing_cycle
          value_pairs_set:
            - ['starter', 'monthly']
            - ['professional', 'monthly']
            - ['professional', 'annual']
            - ['enterprise', 'annual'

This prevents invalid tier-billing combinations from entering the system, protecting pricing integrity.