dbt test: expect_column_values_to_be_between

This page details the expect_column_values_to_be_between test from the dbt-expectations package. This test is implemented to ensure that the values in a particular column fall within a specified range, which can be crucial for validating business rules and data accuracy.

How it Works

The expect_column_values_to_be_between test verifies that every value in a designated column lies within a defined minimum and maximum boundary. This check is fundamental for ensuring that field values meet expected numerical ranges, thus maintaining data integrity and relevance for analysis.

Steps and Conditions:

1. Column Selection: The test targets a specific column for which the value bounds are to be evaluated.
2. Define Bounds: Establish the minimum (min_value) and maximum (max_value) values that form the acceptable range for the column's data. These bounds can be inclusive or exclusive based on the strictly argument.
3. Optional Configurations:
   • Row Condition: Set a row_condition to filter the rows that should be evaluated by the test. For example, one might only want to check rows where the column id is not null.
   • Strictness: The strictly parameter defines whether the range boundaries are strictly less than and greater than (true) or inclusive of the min and max values (false, which is the default).
4. Execution: The test evaluates each row's value in the specified column, checking if it falls within the established range, adhering to any row conditions or strictness criteria.
5. Outcome:
   • Pass: All column values adhere to the defined range, confirming data validity as per the specified rules.
   • Fail: Any column value outside the set bounds indicates possible data issues or entry errors, signaling the need for review or correction.

Example Usage: Fintech

For a Fintech company, maintaining precise value ranges for transaction amounts is critical to ensure compliance with regulatory standards and internal risk management protocols.

Consider a scenario where the transactions table maintains records of financial transactions, and the amount column records the transaction value in dollars.

models:
  - name: transactions
    columns:
      - name: amount
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 1
              max_value: 10000
              row_condition: "transaction_type = 'domestic'"
              strictly: true

In this example, the test is ensuring that for domestic transactions, the transaction amounts are strictly greater than 1 dollar and less than 10,000 dollars. This checks help the Fintech company monitor compliance with limits on transaction sizes, which could be a requirement for anti-money laundering laws, or simply a business rule to limit exposure to high-value transactions.