How it Works

The expect_column_sum_to_be_between test from the dbt-expectations package validates that the total sum of all values in a numeric column falls within a specified range. This is useful for validating financial totals, volume metrics, or any aggregate that should remain within predictable bounds across a data refresh.

Options include group_by, row_condition, and strictly for exclusive boundary comparisons.

Steps and Conditions

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

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

3. Optional Config: Apply group_by and row_condition to refine scope.

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

5. Outcome: Pass if the sum is within range; fail otherwise.

Example Usage: Payroll

A payroll system wants to ensure that the total gross salary disbursed each month stays within an expected range, guarding against erroneous mass payroll entries.

models:
  - name: monthly_payroll
    columns:
      - name: gross_salary
        tests:
          - dbt_expectations.expect_column_sum_to_be_between:
              min_value: 500000
              max_value: 2000000
              row_condition: "pay_period = current_pay_period"

models:
  - name: monthly_payroll
    columns:
      - name: gross_salary
        tests:
          - dbt_expectations.expect_column_sum_to_be_between:
              min_value: 500000
              max_value: 2000000
              row_condition: "pay_period = current_pay_period"

models:
  - name: monthly_payroll
    columns:
      - name: gross_salary
        tests:
          - dbt_expectations.expect_column_sum_to_be_between:
              min_value: 500000
              max_value: 2000000
              row_condition: "pay_period = current_pay_period"

If total payroll spikes or drops outside the expected band, the test fails and prompts a review.