How it Works

The mutually_exclusive_ranges test from dbt-utils™ validates that a set of ranges defined by two columns (a lower bound and an upper bound) do not overlap with each other within the same partition. This is critical for slowly-changing dimension (SCD) tables, pricing tiers, validity windows, or any data structure where ranges must be non-overlapping.

Key arguments include lower_bound_column, upper_bound_column, partition_by (optional), gaps (whether gaps between ranges are allowed), and zero_length_range_allowed.

Steps and Conditions

1. Define Bounds: Specify the lower and upper bound columns.

2. Optional Partition: Group ranges by a partition key (e.g., entity ID).

3. Configure Gap Policy: Set whether gaps between ranges are allowed.

4. Execution: All range combinations within each partition are checked for overlaps.

5. Outcome: Pass if no overlaps exist; fail if any ranges overlap.

Example Usage: Pricing Tiers

A SaaS company defines pricing tiers by customer segment using date ranges. Each tier period must not overlap with another for the same segment.

models:
  - name: pricing_tiers
    tests:
      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: valid_from
          upper_bound_column: valid_to
          partition_by: segment_id
          gaps

models:
  - name: pricing_tiers
    tests:
      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: valid_from
          upper_bound_column: valid_to
          partition_by: segment_id
          gaps

models:
  - name: pricing_tiers
    tests:
      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: valid_from
          upper_bound_column: valid_to
          partition_by: segment_id
          gaps

Overlapping pricing periods would cause billing errors, so this test catches any period conflicts immediately.