How it Works

The expect_table_aggregation_to_equal_other_table test from the dbt-expectations package validates that an aggregate computation (e.g., sum, count, avg) on a column in one model matches the corresponding aggregate from a reference model. This is ideal for cross-table reconciliation checks.

Arguments include expression (the aggregation function), compare_model, and compare_expression for the reference side.

Steps and Conditions

1. Define Aggregation: Specify the column and aggregation expression.

2. Reference Model: Provide the comparison model and its aggregation expression.

3. Execution: Both aggregations are computed and compared.

4. Outcome: Pass if the aggregations match; fail if they differ.

Example Usage: Finance

A finance team wants to ensure the total revenue in the revenue_summary model matches the sum of individual line items in the invoice_line_items model.

models:
  - name: revenue_summary
    tests:
      - dbt_expectations.expect_table_aggregation_to_equal_other_table:
          expression: sum(total_revenue)
          compare_model: ref('invoice_line_items')
          compare_expression

models:
  - name: revenue_summary
    tests:
      - dbt_expectations.expect_table_aggregation_to_equal_other_table:
          expression: sum(total_revenue)
          compare_model: ref('invoice_line_items')
          compare_expression

models:
  - name: revenue_summary
    tests:
      - dbt_expectations.expect_table_aggregation_to_equal_other_table:
          expression: sum(total_revenue)
          compare_model: ref('invoice_line_items')
          compare_expression

A mismatch indicates the summary model is not correctly rolling up from its source, requiring investigation into the transformation logic.