How it Works

The equality test from dbt-utils™ validates that two models contain exactly the same rows and columns. It performs a symmetric difference check — rows present in one model but not the other are reported as failures. This is commonly used to validate that a refactored model produces identical output to the original, or to confirm that two independently derived pipelines agree.

An optional compare_columns argument allows limiting the comparison to a specific subset of columns.

Steps and Conditions

1. Comparison Model: Specify the reference model using compare_model.

2. Optional Column Subset: Use compare_columns to compare only selected columns.

3. Execution: A symmetric difference is computed between the two models.

4. Outcome: Pass if both models contain identical rows for the compared columns; fail if any discrepancies exist.

Example Usage: Model Refactoring

A data engineer is refactoring the revenue model and wants to confirm the new version produces identical output to the old one before deprecating it.

models:
  - name: revenue_v2
    tests:
      - dbt_utils.equality:
          compare_model: ref('revenue_v1')
          compare_columns: ['date', 'product_id', 'revenue_usd'

models:
  - name: revenue_v2
    tests:
      - dbt_utils.equality:
          compare_model: ref('revenue_v1')
          compare_columns: ['date', 'product_id', 'revenue_usd'

models:
  - name: revenue_v2
    tests:
      - dbt_utils.equality:
          compare_model: ref('revenue_v1')
          compare_columns: ['date', 'product_id', 'revenue_usd'

Any row-level difference between revenue_v2 and revenue_v1 surfaces a failure, providing confidence before the old model is retired.