Skip to main content

How to Assert on Deviation Percentage

The is_anomaly flag tells you that a value fell outside the model's confidence bounds. It does not tell you by how much. A 3% miss and a 60% drop both produce the same flag. For alerting purposes, the size of the deviation matters.

This guide walks through creating a Dataform assertion that computes the percentage deviation between the actual value and the nearest bound, then flags only the anomalies that exceed a threshold you define and have persisted for more than one day.

When the assertion returns rows, Dataform marks it as failed. You can connect that failure to a notification channel (email, Slack, or a Cloud Monitoring alert) to turn it into an operational alert.

What you will need​

Step 1: Open a workspace in Dataform​

Go to console.cloud.google.com, navigate to BigQuery → Dataform, and open your repository.

From the Workspaces tab, create a new development workspace by clicking Create workspace. Give it a name (e.g. anomaly-deviation-assertion) and click Create.

Step 2: Create the assertion file​

Navigate to definitions/custom/modules/anomaly_detection/ and create a new file named assert_deviation_threshold.sqlx. If the directory does not exist yet, create it.

Paste the following into the file:

config {
  type: "assertion",
  tags: ["module_anomaly_detection"],
  description: "Flags anomalies where deviation from expected bounds exceeds the threshold for two or more consecutive days on reliable series."
}

WITH candidate_anomalies AS (
  SELECT
    date,
    case_name,
    time_series_id,
    metric_value,
    lower_bound,
    upper_bound,
    CASE
      WHEN metric_value > upper_bound
        THEN ROUND(100 * (metric_value - upper_bound) / upper_bound, 1)
      WHEN metric_value < lower_bound
        THEN ROUND(100 * (lower_bound - metric_value) / lower_bound, 1)
      ELSE 0
    END AS percent_off_expected
  FROM ${ref("anomaly_detection_report")}
  WHERE source = 'anomaly_detection'
    AND is_anomaly = TRUE
    AND is_strong_series = TRUE
    AND date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
),

persistence_check AS (
  SELECT
    case_name,
    time_series_id,
    COUNT(*) AS anomaly_days
  FROM candidate_anomalies
  GROUP BY case_name, time_series_id
)

SELECT
  c.date,
  c.case_name,
  c.time_series_id,
  c.metric_value,
  c.lower_bound,
  c.upper_bound,
  c.percent_off_expected,
  p.anomaly_days
FROM candidate_anomalies c
JOIN persistence_check p USING (case_name, time_series_id)
WHERE p.anomaly_days >= 2
  AND c.percent_off_expected >= 15
ORDER BY c.percent_off_expected DESC, c.date DESC

Step 3: Understand and adjust the thresholds​

The assertion has two parameters you should set based on your property and tolerance for false positives.

percent_off_expected >= 15

This controls how large a deviation must be before it qualifies as an alert candidate. The value is computed differently depending on the direction of the anomaly:

  • For a drop: (lower_bound − metric_value) / lower_bound × 100
  • For a spike: (metric_value − upper_bound) / upper_bound × 100

A threshold of 15 means the value must be at least 15% outside the nearest bound. Lower this to catch smaller deviations; raise it to reduce noise. For high-stakes series like purchase events, a threshold of 10% may be appropriate. For session counts, 20–30% is often more practical.

anomaly_days >= 2

This controls how many consecutive flagged days are required before the assertion triggers. A value of 2 filters out single-day blips, which are common and usually self-correct. Raise this to 3 if you want stronger evidence before alerting; lower it to 1 only for the most critical series.

Step 4: Run the assertion​

In the top-right of the Dataform editor, click Start execution.

In the execution panel:

  • Select Tags or actions to run and filter by module_anomaly_detection
  • Click Start execution

Dataform runs the assertion alongside the pipeline. After execution, open the run log and look for assert_deviation_threshold. If it shows Passed, no rows were returned: no anomalies exceeded the threshold. If it shows Failed, the assertion returned rows: these are your alert candidates.

Click on the failed assertion in the log to see the rows it returned and inspect which series and dates triggered it.

Step 5: Commit your changes​

Once the assertion runs as expected, go back to your Dataform workspace and click Commit in the top bar.

Write a short commit message, for example:

feat: add deviation threshold assertion for anomaly alerting

Click Commit. On subsequent scheduled pipeline runs, Dataform will evaluate the assertion automatically and surface failures in the run log. From there, you can connect Dataform run notifications to an email address or a Cloud Monitoring alert to receive a notification whenever the assertion fails.

Next steps​

  • Query Library: ready-made SQL to explore deviation and persistence patterns outside of Dataform
  • When to Trigger an Alert: guidance on reading results and choosing the right threshold for your property