How it Works

The expect_column_proportion_of_unique_values_to_be_between test from the dbt-expectations package validates that the ratio of distinct values to total values (uniqueness rate) in a column falls within a defined range. Unlike absolute distinct count checks, this proportion-based approach scales naturally as a table grows.

A ratio of 1.0 means every value is unique; a ratio close to 0 means nearly all rows share the same value.

Steps and Conditions

1. Column Selection: Identify the column to evaluate.

2. Define Range: Set min_value and/or max_value for the acceptable proportion.

3. Execution: The uniqueness ratio (distinct count / total count) is computed.

4. Outcome: Pass if within range; fail otherwise.

Example Usage: User Analytics

An analytics team expects the session_id column in their events table to have a very high uniqueness rate — between 0.90 and 1.0 — indicating that most sessions appear a limited number of times.

models:
  - name: web_session_events
    columns:
      - name: session_id
        tests:
          - dbt_expectations.expect_column_proportion_of_unique_values_to_be_between:
              min_value: 0.90
              max_value: 1.0

models:
  - name: web_session_events
    columns:
      - name: session_id
        tests:
          - dbt_expectations.expect_column_proportion_of_unique_values_to_be_between:
              min_value: 0.90
              max_value: 1.0

models:
  - name: web_session_events
    columns:
      - name: session_id
        tests:
          - dbt_expectations.expect_column_proportion_of_unique_values_to_be_between:
              min_value: 0.90
              max_value: 1.0

A drop in uniqueness rate below 0.90 would suggest unexpected session ID reuse or a data duplication issue in the ingestion pipeline.