How it Works

The unique_combination_of_columns test from dbt-utils™ validates that the combination of values across a specified set of columns is unique for every row. Unlike the native unique test (which operates on a single column), this test handles composite keys where multiple columns together define a unique record.

An optional quote_columns argument handles column names that are reserved words.

Steps and Conditions

1. Column Combination: Define the columns that together must be unique.

2. Execution: All row combinations are checked for duplicates.

3. Outcome: Pass if no duplicate combinations exist; fail if any duplicates are found.

Example Usage: Analytics Model

A marketing analytics model tracks conversions by campaign_id and user_id. Each combination of campaign and user should appear only once.

models:
  - name: campaign_conversions
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns

models:
  - name: campaign_conversions
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns

models:
  - name: campaign_conversions
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns

Duplicate campaign-user combinations would inflate conversion counts in reports, making this a critical data quality gate.