How it Works

The expect_column_values_to_be_within_n_stdevs test from the dbt-expectations package validates that every value in a column is within a specified number of standard deviations from the overall column mean. This is a static outlier detection check, contrasted with the moving-window variant expect_column_values_to_be_within_n_moving_stdevs.

The n_sigmas argument defines how many standard deviations from the mean are considered acceptable.

Steps and Conditions

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

2. Set Sensitivity: Define n_sigmas for acceptable deviation.

3. Execution: The mean and standard deviation are computed; each value is checked against the derived bounds.

4. Outcome: Pass if all values are within bounds; fail if outliers are found.

Example Usage: Fraud Detection

A fraud detection team wants to flag any transaction amounts that are more than 4 standard deviations from the mean for a given day, which could indicate fraudulent outliers.

models:
  - name: daily_transactions
    columns:
      - name: amount_usd
        tests:
          - dbt_expectations.expect_column_values_to_be_within_n_stdevs:
              group_by: [transaction_date]
              n_sigmas: 4

models:
  - name: daily_transactions
    columns:
      - name: amount_usd
        tests:
          - dbt_expectations.expect_column_values_to_be_within_n_stdevs:
              group_by: [transaction_date]
              n_sigmas: 4

models:
  - name: daily_transactions
    columns:
      - name: amount_usd
        tests:
          - dbt_expectations.expect_column_values_to_be_within_n_stdevs:
              group_by: [transaction_date]
              n_sigmas: 4

Exceptionally large or small transaction amounts that fall outside 4 standard deviations are surfaced for fraud review.