How it Works

The expect_column_max_to_be_between test from the dbt-expectations package validates that the maximum value found in a given column falls within a specified range. This is especially useful for catching outliers or erroneous spikes that could signal upstream data issues.

The test computes the column's maximum value and then verifies it sits between an optional min_value and max_value. You can also configure group-by columns to assess subsets of the data independently, use a row_condition to filter rows before evaluation, and set strictly: true to make the comparison exclusive of boundary values.

Steps and Conditions

1. Column Selection: Identify the column whose maximum value you want to validate.

2. Define Range: Set min_value and/or max_value to establish acceptable boundaries.

3. Optional Config: Apply group_by, row_condition, and strictly flags as needed.

4. Execution: The test computes the max and checks if it falls within range.

5. Outcome: Pass if the max is within bounds; fail if it falls outside.

Example Usage: SaaS Billing

A SaaS company wants to ensure that monthly invoice amounts never exceed their highest pricing tier and are always at least $1.

models:
  - name: monthly_invoices
    columns:
      - name: invoice_amount_usd
        tests:
          - dbt_expectations.expect_column_max_to_be_between:
              min_value: 1
              max_value: 50000
              row_condition: "status = 'paid'"

models:
  - name: monthly_invoices
    columns:
      - name: invoice_amount_usd
        tests:
          - dbt_expectations.expect_column_max_to_be_between:
              min_value: 1
              max_value: 50000
              row_condition: "status = 'paid'"

models:
  - name: monthly_invoices
    columns:
      - name: invoice_amount_usd
        tests:
          - dbt_expectations.expect_column_max_to_be_between:
              min_value: 1
              max_value: 50000
              row_condition: "status = 'paid'"

This ensures that no paid invoice unexpectedly exceeds $50,000, which could indicate a billing error, and that the maximum paid invoice is at least $1, ruling out zero-value anomalies.