How it Works

The expect_column_values_to_be_unique test from the dbt-expectations package validates that every value in a column is unique across all rows. This complements the native dbt™ unique test with additional options like row_condition to scope the uniqueness check to a subset of records, and supports grouping for partition-aware uniqueness checks.

Steps and Conditions

1. Column Selection: Identify the column to check for uniqueness.

2. Optional Row Condition: Scope the check using row_condition.

3. Execution: Duplicate values are detected across the filtered rows.

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

Example Usage: Identity Management

An identity management system wants to ensure that user email addresses are unique across all active accounts.

models:
  - name: user_accounts
    columns:
      - name: email
        tests:
          - dbt_expectations.expect_column_values_to_be_unique:
              row_condition: "account_status = 'active'"

models:
  - name: user_accounts
    columns:
      - name: email
        tests:
          - dbt_expectations.expect_column_values_to_be_unique:
              row_condition: "account_status = 'active'"

models:
  - name: user_accounts
    columns:
      - name: email
        tests:
          - dbt_expectations.expect_column_values_to_be_unique:
              row_condition: "account_status = 'active'"

This prevents duplicate registrations among active users, protecting login and communication flows.