dbt test: expect_column_pair_values_to_be_equal

This page introduces the expect_column_pair_values_to_be_equal test from dbt-expectations. This test verifies that values in two specified columns are the same across all records. It is useful for ensuring the consistency and accuracy of data, particularly when data is duplicated across columns for various reasons such as system migrations or database design.

How it Works

The expect_column_pair_values_to_be_equal checks that for each row of data, the values in the two designated columns are identical. This test helps catch discrepancies that could indicate data processing errors or mismatches in data entry.

Steps and Conditions:

1. Column Selection: Choose two columns whose values need to be compared to each other.
2. Row Filtering (Optional): Apply a condition to select specific rows for testing, such as excluding rows with null IDs or focusing on a specific subset of the data.
3. Comparison Execution: For each row that meets the optional condition, the test checks if the value in column A is equal to the value in column B.
4. Outcome:
   • Pass: If all compared values are equal across the selected columns for all filtered rows, the test passes, indicating data consistency between the two columns.
   • Fail: If any value does not match between the two columns in the filtered rows, the test fails, highlighting a potential issue where values that are expected to be the same differ.

Example Usage: Fintech Company

For a Fintech company, maintaining accurate and consistent financial records across different systems or database schemas is critical. Let's consider a scenario involving a payments table, where the amount paid (amount_paid) and the transaction value (transaction_value) need to be consistent for audit and regulatory reasons.

models:
  - name: payments
    columns:
      - name: amount_paid
      - name: transaction_value
        tests:
          - dbt_expectations.expect_column_pair_values_to_be_equal:
              column_A: amount_paid
              column_B: transaction_value
              row_condition: "transaction_date >= '2023-01-01'"

In this example, the test ensures that for all transactions in the payments table dated from January 1, 2023, onward, the amount_paid is identical to the transaction_value. This check is essential for verifying that the reported and processed transaction amounts are in perfect alignment, crucial for financial accuracy and compliance.