How it Works

The expect_column_pair_values_a_to_be_greater_than_b test from the dbt-expectations package validates that for every row, the value of one column is greater than the value of another column. The or_equal argument (default false) controls whether equal values are also acceptable.

This is ideal for enforcing business rules like end dates being after start dates, or maximum values exceeding minimums.

Steps and Conditions

1. Define Column Pair: Specify column_A (the greater column) and column_B (the lesser column).

2. Or Equal: Set or_equal: true if column_A >= column_B is acceptable.

3. Optional Row Condition: Filter rows using row_condition.

4. Execution: Each row is evaluated for the A > B (or A >= B) condition.

5. Outcome: Pass if all rows satisfy the condition; fail if any violate it.

Example Usage: Event Scheduling

An event scheduling platform stores event start and end times. The end time must always be after the start time.

models:
  - name: events
    tests:
      - dbt_expectations.expect_column_pair_values_a_to_be_greater_than_b:
          column_A: ends_at
          column_B: starts_at
          or_equal: false
          row_condition: "status = 'scheduled'"

models:
  - name: events
    tests:
      - dbt_expectations.expect_column_pair_values_a_to_be_greater_than_b:
          column_A: ends_at
          column_B: starts_at
          or_equal: false
          row_condition: "status = 'scheduled'"

models:
  - name: events
    tests:
      - dbt_expectations.expect_column_pair_values_a_to_be_greater_than_b:
          column_A: ends_at
          column_B: starts_at
          or_equal: false
          row_condition: "status = 'scheduled'"

Any event where the end time precedes the start time is flagged, preventing corrupted event records from reaching the calendar application.