dbt test: expect_column_min_to_be_between

This page explains the expect_column_min_to_be_between test from the dbt-expectations package, which checks if the minimum value of a specified column falls within a defined range. This test is critical for ensuring that column values meet expected minimum standards, which is particularly important for numerical data that should adhere to known limits.

How it Works

The expect_column_min_to_be_between test precisely measures the minimum value found in a designated column and validates if this minimum value is within a stipulated range. This functionality is pivotal for maintaining the integrity of numerical data boundaries in a dataset.

Steps and Conditions:

1. Column Selection: Pick the column whose minimum value needs to be tested.
2. Range Definition: Specify the acceptable minimum and maximum values for the column's minimum value. These parameters determine the allowed value range for the test.
3. Configuration Options:
   • Group By: Optionally group data by specified columns before evaluating the minimum, allowing for segmented analysis across different groups.
   • Row Condition: Apply conditions to filter rows that are included in the minimum value calculation.
   • Strictness: Determine whether the comparison includes the boundary values (using strictly parameter), which affects whether the bounds are considered as "less than and equal to" or "less than".
4. Execution: After applying any groupings and row conditions, the test computes the minimum value of the selected column and checks it against the defined range.
5. Outcome:
   • Pass: The test passes if the column's minimum value is within the specified range, indicating compliance with the defined numerical boundaries.
   • Fail: The test fails if the minimum value lies outside the specified range, signaling a potential problem that may require further investigation or correction.

Example Usage: Fintech

In a Fintech environment, ensuring that transaction amounts stay within authorized limits is essential for compliance and operational security. Consider a case where the transactions table records each transaction's amount, and it's crucial to maintain these amounts within designated boundaries to prevent fraud or processing errors.

models:
  - name: transactions
    tests:
      - dbt_expectations.expect_column_min_to_be_between:
          column: amount
          min_value: 0
          group_by: [currency]
          row_condition: "transaction_date >= '2023-01-01'"
          strictly: true

In this example, the expect_column_min_to_be_between test is used to ensure that all transaction amounts recorded since January 1, 2023, are non-negative, considering transactions grouped by currency. This setup helps verify that no transactions fall below the allowable minimum value, crucial for maintaining accurate fiscal records and adhering to regulatory standards in the financial sector.