How it Works

The expect_column_values_to_be_within_n_moving_stdevs test from the dbt-expectations package checks that values in a numeric column do not deviate beyond a specified number of standard deviations from a rolling mean. It uses a moving window approach to dynamically adapt to trends, making it more robust than a static threshold for time-series data.

Key arguments include group_by, lookback_periods, trend_periods, test_periods, and n_sigmas.

Steps and Conditions

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

2. Configure Window: Set lookback_periods, trend_periods, and test_periods.

3. Set Sensitivity: Define n_sigmas for the acceptable deviation multiplier.

4. Execution: Rolling mean and standard deviation are computed; values are checked against the dynamic bounds.

5. Outcome: Pass if no values exceed the moving bounds; fail if outliers are detected.

Example Usage: Financial Metrics

A fintech company monitors daily transaction volumes. Rather than a fixed ceiling, they want to flag days that deviate more than 3 standard deviations from a 30-day rolling mean.

models:
  - name: daily_transaction_summary
    columns:
      - name: transaction_count
        tests:
          - dbt_expectations.expect_column_values_to_be_within_n_moving_stdevs:
              group_by: [date_day]
              lookback_periods: 30
              n_sigmas: 3

models:
  - name: daily_transaction_summary
    columns:
      - name: transaction_count
        tests:
          - dbt_expectations.expect_column_values_to_be_within_n_moving_stdevs:
              group_by: [date_day]
              lookback_periods: 30
              n_sigmas: 3

models:
  - name: daily_transaction_summary
    columns:
      - name: transaction_count
        tests:
          - dbt_expectations.expect_column_values_to_be_within_n_moving_stdevs:
              group_by: [date_day]
              lookback_periods: 30
              n_sigmas: 3

This catches anomalous spikes or drops in transaction volume dynamically, adapting to seasonal trends.