How it Works

The unique test is one of dbt™'s four built-in generic tests. It validates that a column contains no duplicate non-null values. This is the most fundamental uniqueness constraint in dbt™ and is typically applied to primary key columns to enforce entity integrity.

Steps and Conditions

1. Column Selection: Identify the column to validate.

2. Execution: All non-null values are checked for duplicates.

3. Outcome: Pass if all non-null values are unique; fail if duplicates exist.

Example Usage: User Table

A user management model should have a unique user_id for every record.

models:
  - name: users
    columns:
      - name: user_id
        tests

models:
  - name: users
    columns:
      - name: user_id
        tests

models:
  - name: users
    columns:
      - name: user_id
        tests

Duplicate user IDs would cause broken joins, inflated metrics, and incorrect downstream reporting, making this one of the most critical tests to apply on any primary key.