How it Works

The expect_column_stdev_to_be_between test from the dbt-expectations package validates that the standard deviation of a numeric column falls within a specified range. High or unexpected standard deviation can indicate data corruption, outliers, or anomalous injections that would skew statistical analysis.

Optional arguments include group_by, row_condition, and strictly for exclusive boundary comparisons.

Steps and Conditions

1. Column Selection: Choose the numeric column to evaluate.

2. Define Range: Set min_value and/or max_value for acceptable standard deviation.

3. Optional Config: Apply group_by and row_condition to refine scope.

4. Execution: The standard deviation is computed and compared to the range.

5. Outcome: Pass if within range; fail otherwise.

Example Usage: Sensor Data

An engineering team monitors temperature readings from industrial sensors. The standard deviation across a day's readings should remain stable; a sudden spike could indicate a faulty sensor.

models:
  - name: daily_sensor_readings
    columns:
      - name: temperature_celsius
        tests:
          - dbt_expectations.expect_column_stdev_to_be_between:
              min_value: 0
              max_value: 5

models:
  - name: daily_sensor_readings
    columns:
      - name: temperature_celsius
        tests:
          - dbt_expectations.expect_column_stdev_to_be_between:
              min_value: 0
              max_value: 5

models:
  - name: daily_sensor_readings
    columns:
      - name: temperature_celsius
        tests:
          - dbt_expectations.expect_column_stdev_to_be_between:
              min_value: 0
              max_value: 5

A standard deviation above 5°C for daily temperature readings suggests sensor malfunction or extreme outliers requiring investigation.