dbt test: expect_table_row_count_to_equal_other_table

This page describes the expect_table_row_count_to_equal_other_table test from the dbt-expectations package. It is designed to check whether the number of rows in one model matches the number of rows in another model. The key use of this test is in scenarios where data consistency between two tables is essential, for example, when syncing data or ensuring data has been correctly transformed or migrated.

How it Works

The expect_table_row_count_to_equal_other_table test compares the row count of one model or table (source) against another (target). It helps in verifying that operations such as data transformations, migrations, or syncs have resulted in the correct number of records.

Steps and Conditions:

1. Select Models: Identify the two models, tables, or views whose row counts you want to compare.
2. Group By (Optional): If needed, specify columns in both the source and target by which to group the data before counting rows. This is useful when validations are needed on segmented parts of the tables rather than the entire table.
3. Row Conditions (Optional): Apply conditions to filter rows in both the source and target that are to be included in the count. For instance, excluding rows where the ID is null.
4. Execution: The test first filters the data as per any specified conditions, groups the data if required, counts the number of rows in each dataset, and then compares these counts.
5. Outcome:
   • Pass: The row count of the source matches the row count of the target, implying the datasets are aligned.
   • Fail: Any discrepancy between the row counts of the source and target indicates a potential issue with data handling or processing that needs to be investigated.

Example Usage: Fintech

For a Fintech company, ensuring that transaction records are consistent across different systems is critical. Consider a situation where daily transactions are recorded in two separate models: daily_transactions and backup_daily_transactions. It is crucial for reconciliation purposes that these two models have the same number of records.

models:
  - name: daily_transactions
    tests:
      - dbt_expectations.expect_table_row_count_to_equal_other_table:
          compare_model: ref("backup_daily_transactions")
          row_condition: "transaction_date = current_date"
          compare_row_condition: "transaction_date = current_date"

In this setup, the expect_table_row_count_to_equal_other_table test ensures that the number of transactions recorded in daily_transactions matches the number in backup_daily_transactions for the current day. This verification is essential to ensure data integrity and accuracy in financial reporting and auditing processes.