How it Works

The expect_compound_columns_to_be_unique test from the dbt-expectations package verifies that the combination of values across a specified set of columns is unique for every row. This is useful when no single column acts as a primary key but a composite key is expected to be unique.

An optional ignore_row_if argument allows you to exclude rows with null values in any or all of the specified columns from the uniqueness check.

Steps and Conditions

1. Column List: Define the columns that together form the compound key.

2. Optional Null Handling: Use ignore_row_if to handle rows with null values.

3. Execution: All column combinations are checked for duplicates.

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

Example Usage: Logistics

A logistics company tracks delivery attempts where the combination of shipment_id and attempt_number should be unique, but neither column alone is a unique identifier.

models:
  - name: delivery_attempts
    tests:
      - dbt_expectations.expect_compound_columns_to_be_unique:
          column_list: ["shipment_id", "attempt_number"]
          ignore_row_if: "any_value_is_missing"

models:
  - name: delivery_attempts
    tests:
      - dbt_expectations.expect_compound_columns_to_be_unique:
          column_list: ["shipment_id", "attempt_number"]
          ignore_row_if: "any_value_is_missing"

models:
  - name: delivery_attempts
    tests:
      - dbt_expectations.expect_compound_columns_to_be_unique:
          column_list: ["shipment_id", "attempt_number"]
          ignore_row_if: "any_value_is_missing"

This prevents duplicate delivery attempt records from corrupting fulfilment tracking reports.