How it Works

The expect_select_column_values_to_be_unique_within_record test from the dbt-expectations package validates that for each row, the values across a specified set of columns are all distinct from each other. This is useful when multiple columns hold ranked or ordered values that should never be duplicates within the same row (e.g., first, second, and third choice selections).

An ignore_row_if argument handles rows where some columns may be null.

Steps and Conditions

1. Column List: Define the columns to check within each row.

2. Null Handling: Configure ignore_row_if as needed.

3. Execution: Each row is checked to see if the values across the listed columns are all unique.

4. Outcome: Pass if values are unique within each row; fail if any row has duplicate values across columns.

Example Usage: Survey Platform

A survey platform asks respondents to rank their top three preferred products. Each rank position should hold a different product ID.

models:
  - name: survey_responses
    tests:
      - dbt_expectations.expect_select_column_values_to_be_unique_within_record:
          column_list: ["first_choice_product", "second_choice_product", "third_choice_product"]
          ignore_row_if: "any_value_is_missing"

models:
  - name: survey_responses
    tests:
      - dbt_expectations.expect_select_column_values_to_be_unique_within_record:
          column_list: ["first_choice_product", "second_choice_product", "third_choice_product"]
          ignore_row_if: "any_value_is_missing"

models:
  - name: survey_responses
    tests:
      - dbt_expectations.expect_select_column_values_to_be_unique_within_record:
          column_list: ["first_choice_product", "second_choice_product", "third_choice_product"]
          ignore_row_if: "any_value_is_missing"

This prevents a respondent from accidentally ranking the same product in multiple positions.