dbt test: expect_column_sum_to_be_between

This page introduces the expect_column_sum_to_be_between test from the dbt-expectations package. This test is used to validate that the sum of values in a column lies within a specified range. It is valuable in confirming that totals, aggregations, or sums are correct and within expected boundaries, which could be critical for financial calculations, inventory totals, or other cumulative data.

How it Works

expect_column_sum_to_be_between calculates the total sum of the values in a selected column and confirms if this total is within a defined minimum and maximum value range. This range can be set as inclusive or exclusive based on requirements using the strictly parameter.

Steps and Conditions:

1. Column Identification: Establish which column's total sum you need to verify.
2. Range Specification: Define the permissible range for the sum with a minimum (min_value) and maximum (max_value). Either or both can be specified.
3. Optional Configuration:
   • Group By: Use the group_by argument to specify columns for grouping the data, which will apply the sum check within each group separately.
   • Row Condition: The row_condition argument lets you filter rows that will be included in the test.
   • Strictness: By default, the comparison includes the boundary values (strictly: false). If set to true, the test checks for strict inequality (excluding the boundary values).
4. Calculation and Verification: The test applies any specified filters or groupings, then computes the sum of the selected column, finally verifying if it falls within the set range.

Example Usage: Fintech

For a Fintech company, keeping track of daily transactions processed through their platform is crucial. Ensuring that the total sum of transactions lies within expected limits can help in monitoring for discrepancies like transaction overflows or underflows, which could be indicative of errors or fraudulent activities.

Consider a scenario where the transactions table records each financial transaction with an amount field:

tests:
  - dbt_expectations.expect_column_sum_to_be_between:
      column: amount
      min_value: 10000
      max_value: 20000
      row_condition: "date = current_date"
      group_by: [currency]

In this example, the test checks that the daily sum of transaction amounts, grouped by currency, is between 10,000 and 20,000. This helps ensure that all transactions processed in any single currency within a day are within a healthy and expected financial threshold.