How it Works

The expect_column_pair_values_to_be_equal test from the dbt-expectations package verifies that for every row in a model, the value of one column equals the value of another. This is useful for validating denormalized copies of data, enforcing business rules where two fields must stay in sync, or detecting drift between calculated and stored values.

An optional row_condition can scope the check to relevant rows.

Steps and Conditions

1. Column Pair: Specify the two columns to compare using column_A and column_B.

2. Optional Row Condition: Filter rows before comparison.

3. Execution: Each row's values for the two columns are compared.

4. Outcome: Pass if all compared values are equal; fail if any differ.

Example Usage: Accounting System

An accounting system stores both a calculated_tax column and a posted_tax column. These should always be equal for finalized transactions.

models:
  - name: finalized_transactions
    tests:
      - dbt_expectations.expect_column_pair_values_to_be_equal:
          column_A: calculated_tax
          column_B: posted_tax
          row_condition: "status = 'finalized'"

models:
  - name: finalized_transactions
    tests:
      - dbt_expectations.expect_column_pair_values_to_be_equal:
          column_A: calculated_tax
          column_B: posted_tax
          row_condition: "status = 'finalized'"

models:
  - name: finalized_transactions
    tests:
      - dbt_expectations.expect_column_pair_values_to_be_equal:
          column_A: calculated_tax
          column_B: posted_tax
          row_condition: "status = 'finalized'"

Any discrepancy between calculated and posted tax values on finalized records will trigger the test to fail.