dbt test: expect_multicolumn_sum_to_equal

This page details the expect_multicolumn_sum_to_equal test from the dbt-expectations package. This test ensures that the sum of values across specified columns for all rows matches a predefined total. This is especially important for models where the aggregation of multiple fields must conform to a known value, ensuring data accuracy and consistency.

How it Works

The expect_multicolumn_sum_to_equal test performs a cumulative sum of specified columns across all rows in a model, seed, or source, and ensures the total matches a specific target value. This function is imperative for validating summed data points, such as financial totals or counts, align with expected values.

Steps and Conditions:

1. Column Selection: Define a list of columns whose values will be summed together.
2. Total Value Specification: Establish the exact sum that the combined values of the selected columns should equal.
3. Optional Configuration:
   • Group By: Allows you to specify one or more columns to group the data by before summing, which is useful for sum checks within categorised subsets of data.
   • Row Condition: Enables setting conditions to filter which rows are considered in the summation. For example, excluding rows where IDs are null or other specific criteria are met.
4. Execution: Apply any row conditions and groupings, compute the sum of the selected columns across the relevant rows, and compare this sum to the defined target value.
5. Outcome:
   • Pass: If the computed sum matches the target value, the test passes, confirming the data's accuracy concerning the aggregated sum.
   • Fail: If there is a discrepancy between the computed sum and the target value, the test fails, indicating possible inaccuracies or anomalies in the data that need further investigation.

Example Usage: E-commerce

In an E-commerce context, ensuring accurate financial reporting is essential for maintaining reliable business operations. Consider the scenario where the daily_sales table records transactions with columns for various types of payments (credit_card, debit_card, gift_card).

models:
  - name: daily_sales
    tests:
      - dbt_expectations.expect_multicolumn_sum_to_equal:
          column_list: ["credit_card", "debit_card", "gift_card"]
          sum_total: 5000

In this example, the expect_multicolumn_sum_to_equal test checks the sum of payments made by credit card, debit card, and gift card in the daily_sales table to ensure that the total matches the expected daily total of $5000. This sum total validation ensures that all transactions are accounted for correctly and that the reported payment totals are accurate, crucial for daily financial reconciliations and reporting.