How it Works

The expect_column_values_to_be_between test from the dbt-expectations package validates that every individual value in a column falls within a specified minimum and maximum range. This is a row-level range check, unlike expect_column_max_to_be_between or expect_column_min_to_be_between which check aggregate statistics.

An optional row_condition can scope the check, and strictly makes boundaries exclusive.

Steps and Conditions

1. Column Selection: Identify the numeric column to validate.

2. Define Range: Set min_value and/or max_value.

3. Optional Config: Apply row_condition and strictly as needed.

4. Execution: Each row's value is individually checked against the range.

5. Outcome: Pass if all values are within range; fail if any fall outside.

Example Usage: Temperature Monitoring

A cold chain logistics company monitors refrigerated storage temperatures. Every recorded temperature must stay between 2°C and 8°C to ensure compliance.

models:
  - name: storage_temperature_log
    columns:
      - name: temperature_celsius
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 2
              max_value: 8
              row_condition: "storage_type = 'refrigerated'"

models:
  - name: storage_temperature_log
    columns:
      - name: temperature_celsius
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 2
              max_value: 8
              row_condition: "storage_type = 'refrigerated'"

models:
  - name: storage_temperature_log
    columns:
      - name: temperature_celsius
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 2
              max_value: 8
              row_condition: "storage_type = 'refrigerated'"

Any temperature reading outside the compliance range immediately fails the test, triggering a regulatory alert.