How it Works

The expect_multicolumn_sum_to_equal test from the dbt-expectations package validates that the combined sum of values across a list of columns equals a predefined target. This is ideal for budget reconciliation, allocation checks, or any scenario where multiple numeric fields must collectively add up to a known total.

Optional arguments include group_by for scoped checks within data segments and row_condition to filter rows before aggregation.

Steps and Conditions

1. Column List: Specify the columns to sum together.

2. Target Value: Define the expected total with sum_total.

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

4. Execution: Row conditions and groupings are applied, then the sum is computed and compared.

5. Outcome: Pass if the sum matches the target; fail if there's a discrepancy.

Example Usage: Insurance Company

An insurance company tracks claim payouts split across property_payout, medical_payout, and legal_payout columns. They want to verify that total payouts for approved claims match a known reconciliation figure.

models:
  - name: claim_payouts
    tests:
      - dbt_expectations.expect_multicolumn_sum_to_equal:
          column_list: ["property_payout", "medical_payout", "legal_payout"]
          sum_total: 1000000
          row_condition: "claim_status = 'approved'"

models:
  - name: claim_payouts
    tests:
      - dbt_expectations.expect_multicolumn_sum_to_equal:
          column_list: ["property_payout", "medical_payout", "legal_payout"]
          sum_total: 1000000
          row_condition: "claim_status = 'approved'"

models:
  - name: claim_payouts
    tests:
      - dbt_expectations.expect_multicolumn_sum_to_equal:
          column_list: ["property_payout", "medical_payout", "legal_payout"]
          sum_total: 1000000
          row_condition: "claim_status = 'approved'"

This confirms that approved claim payouts across all categories correctly reconcile to the expected total.