dbt test: expect_table_row_count_to_equal

This page describes the expect_table_row_count_to_equal test from the dbt-expectations package. This test verifies that the number of rows in a specified model matches an expected value. It's useful for ensuring data consistency by confirming the presence of an expected quantity of data, especially after transformations or data migrations.

How it Works

The expect_table_row_count_to_equal test counts the rows in a specified model, seed, or source and checks if this count matches a predetermined expected value.

Steps and Conditions:

1. Model, Seed, or Source Selection: Identify the model, seed, or source for which you wish to validate the row count.
2. Set Expected Number of Rows: Define the expected number of rows using the value argument.
3. Optional Configuration:
   • Group By: The group_by argument enables grouping by specified columns before counting, catering to specific validation requirements.
   • Row Condition: You can also specify a row_condition to include or exclude rows based on certain conditions.
4. Execution: Apply any specified conditions or groupings and count the total number of rows. Compare this count to the expected value.
5. Outcome:
   • Pass: If the actual row count equals the expected count, the test passes, confirming the data quantity meets expectations.
   • Fail: If the row count differs from what is expected, the test fails, highlighting a potential issue with data completeness or transformation logic.

Example Usage: Fintech

For a Fintech company, accurately tracking the number of transactions processed each day is crucial for reconciling accounting figures and auditing purposes.

Consider a scenario where the daily_transactions table stores each day's transactions. The company expects exactly 300 transactions to be processed daily.

models:
  - name: daily_transactions
    tests:
      - dbt_expectations.expect_table_row_count_to_equal:
          value: 300
          row_condition: "transaction_date = current_date"

In this example, the expect_table_row_count_to_equal test ensures that the number of entries in the daily_transactions table for the current day equals 300. This test is important for validating that all expected transactions are captured and processed daily, crucial for financial accuracy and reporting.