How it Works

The equal_rowcount test from dbt-utils™ ensures that two models contain exactly the same number of rows. It's a straightforward but powerful check for validating that no records are inadvertently dropped or duplicated during a transformation step. This test is commonly used to compare a staging model against its raw source, or to verify that a join-based model preserves row counts correctly.

Steps and Conditions

1. Dataset Selection: Identify the model to test and the comparison model (compare_model).

2. Row Count Comparison: Both models are counted and compared.

3. Outcome: Pass if counts are equal; fail if they differ.

Example Usage: Retail Data Pipeline

A retail company loads point-of-sale events into a raw table and then transforms them into a cleaned staging layer. They want to confirm no events are lost during transformation.

models:
  - name: stg_pos_events
    tests:
      - dbt_utils.equal_rowcount:
          compare_model: ref('raw_pos_events'

models:
  - name: stg_pos_events
    tests:
      - dbt_utils.equal_rowcount:
          compare_model: ref('raw_pos_events'

models:
  - name: stg_pos_events
    tests:
      - dbt_utils.equal_rowcount:
          compare_model: ref('raw_pos_events'

If stg_pos_events has fewer or more rows than raw_pos_events, the test fails and alerts the team to investigate the transformation logic.