dbt test: expect_column_max_to_be_between

This page provides details on the expect_column_max_to_be_between test from the dbt-expectations package. This test validates that the maximum value in a specified column resides within a set range. It is useful for ensuring that data values do not exceed expected limits, which may indicate errors or outliers impacting the overall data integrity.

How it Works

The test expect_column_max_to_be_between calculates the maximum value found in a chosen column and checks whether this value falls between an optionally defined minimum and maximum range.

Steps and Conditions:

1. Column Selection: Choose the column whose maximum value needs to be assessed.
2. Define Range: Set the optional min_value and max_value to establish the range within which the column's maximum should lie.
3. Optional Configuration:
   • Group By: Apply the test to subsets of data defined by one or more group columns, which can help assess conditions within categorized data segments.
   • Row Condition: Specify conditions to narrow down the rows considered in the test, allowing for focused and relevant data assessment.
   • Strictness: Decide whether the comparison to min and max values includes these bounds (strictly: false) or not (strictly: true), where the latter makes the range exclusive of the endpoints.
4. Execution: After considering all optional configurations, the test computes the maximum value for the specified column or groups and verifies its compliance with the defined range.
5. Outcome:
   • Pass: The test concludes successfully if the maximum value respects the defined barriers.
   • Fail: Should the maximum value fall outside the designated range, the test fails, signaling potential exceptional cases or data errors.

Example Usage: Fintech

For a Fintech company, ensuring that transaction amounts do not exceed predefined limits is vital for fraud prevention and risk management.

Suppose the transactions table logs every transaction, with a transaction_amount column indicating the money transferred in each transaction.

models:
  - name: transactions
    columns:
      - name: transaction_amount
        tests:
          - dbt_expectations.expect_column_max_to_be_between:
              min_value: 1
              max_value: 10000
              row_condition: "transaction_date >= '2023-01-01'"

In this setup, the expect_column_max_to_be_between test checks that all transaction amounts from January 1, 2023, onward do not exceed $10,000 and are at least $1, helping to monitor and control transaction values in accordance with company policies and regulatory requirements.