How it Works

The fewer_rows_than test from dbt-utils™ validates that a model has strictly fewer rows than a specified comparison model. This is useful for asserting that a filtered or aggregated model always produces fewer records than its upstream source, which would be expected in scenarios like deduplication, filtering, or rollup transformations.

Steps and Conditions

1. Comparison Model: Specify the model to compare against using compare_model.

2. Execution: Both row counts are computed and compared.

3. Outcome: Pass if the test model has fewer rows than the comparison; fail if it has equal or more.

Example Usage: Deduplication Pipeline

A deduplication pipeline processes raw events and should always produce a deduplicated output with fewer rows than the raw input.

models:
  - name: deduped_events
    tests:
      - dbt_utils.fewer_rows_than:
          compare_model: ref('raw_events'

models:
  - name: deduped_events
    tests:
      - dbt_utils.fewer_rows_than:
          compare_model: ref('raw_events'

models:
  - name: deduped_events
    tests:
      - dbt_utils.fewer_rows_than:
          compare_model: ref('raw_events'

If the deduplicated model has as many or more rows than the raw source, something has gone wrong in the deduplication logic.