How it Works

The expect_column_unique_value_count_to_be_between test from the dbt-expectations package checks that the count of distinct values in a column falls within a specified minimum and maximum. This is a softer version of exact distinct count checks and is well-suited for columns whose cardinality is expected to grow over time but within predictable bounds.

Steps and Conditions

1. Column Selection: Identify the column to evaluate.

2. Define Range: Set min_value and/or max_value.

3. Execution: The distinct count is computed and compared to the range.

4. Outcome: Pass if the count is within range; fail otherwise.

Example Usage: Platform Analytics

A platform analytics team expects their active_experiment column to contain between 5 and 50 distinct experiment IDs at any given time.

models:
  - name: user_experiments
    columns:
      - name: experiment_id
        tests:
          - dbt_expectations.expect_column_unique_value_count_to_be_between:
              min_value: 5
              max_value: 50

models:
  - name: user_experiments
    columns:
      - name: experiment_id
        tests:
          - dbt_expectations.expect_column_unique_value_count_to_be_between:
              min_value: 5
              max_value: 50

models:
  - name: user_experiments
    columns:
      - name: experiment_id
        tests:
          - dbt_expectations.expect_column_unique_value_count_to_be_between:
              min_value: 5
              max_value: 50

Fewer than 5 distinct experiments suggests experiments are being dropped; more than 50 could indicate runaway experiment proliferation.