How it Works

The expect_column_mean_to_be_between test from the dbt-expectations package validates that the arithmetic mean of a numeric column's values falls within a defined range. This guards against data issues that shift the average — such as erroneous zero values inflating or deflating the mean, or data truncation removing high-value records.

Supports group_by, row_condition, and strictly for exclusive comparisons.

Steps and Conditions

1. Column Selection: Choose the numeric column to evaluate.

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

3. Optional Config: Apply group_by, row_condition, and strictly as needed.

4. Execution: The mean is computed and compared to the range.

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

Example Usage: Customer Success

A customer success team tracks average NPS scores per quarter. The mean score should remain between 20 and 80 — extreme deviations likely indicate data collection errors.

models:
  - name: quarterly_nps
    columns:
      - name: nps_score
        tests:
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 20
              max_value: 80

models:
  - name: quarterly_nps
    columns:
      - name: nps_score
        tests:
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 20
              max_value: 80

models:
  - name: quarterly_nps
    columns:
      - name: nps_score
        tests:
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 20
              max_value: 80

A mean outside this range prompts a review of the NPS data collection pipeline.