Skip to main content

Anomaly Detection

An anomaly detector flags observations that differ from an expected distribution or recent baseline. Use it for changes in latency, transaction size, or sensor measurements when reliable labels are scarce. A flagged row needs interpretation: unusual activity can be legitimate, and a statistical score is not a calibrated probability of fraud or failure.

Durable SQL anomaly streams​

The current durable SQL path supports EMA (exponential moving average). It compares incoming values against a prior running baseline and exposes the baseline used for each result.

After creating the source stream in the streaming tutorial:

CREATE OR REPLACE STREAM tutorial.streaming.anomalies AS
DETECT ANOMALIES IN tutorial.streaming.txn_stream
    ON amount
    USING METHOD 'ema'
    WINDOW '5m';

REFRESH STREAM tutorial.streaming.anomalies;

SELECT txn_id, amount, anomaly_score, is_anomaly,
       ema_prior_mean, ema_prior_stddev, ema_prior_count, anomaly_status
FROM tutorial.streaming.anomalies
ORDER BY txn_id;

WINDOW accepts a positive duration up to 24 hours. See streaming ML for source, scope, and background execution requirements.

Reading the result​

ColumnInterpretation
anomaly_scoreDeviation score; inspect status and baseline before applying a business rule
is_anomalyDetector's flag for the observation
ema_prior_mean, ema_prior_stddevBaseline before incorporating this observation
ema_prior_countPrior observations represented by the detector
anomaly_statusProcessing state, including warm-up

The tutorial inserts normal baseline events before a normal probe and a large outlier. This allows inspection of both outcomes and the prior statistics. Do not judge quality solely by whether the largest value was flagged.

Practical evaluation​

For a payment monitor, replay a representative chronological history containing ordinary purchases, known incidents, and legitimate large purchases. Measure the alert volume, missed incidents, and time to detection. Check behavior during warm-up, after inactivity, and after distribution changes. Keep identifiers so every flag can be traced to its input.

Batch analysis with SQL statistics​

For an offline baseline, compute a z-score explicitly with aggregate functions. This example assumes an existing measurements(value) table:

WITH baseline AS (
    SELECT AVG(value) AS mean_value, STDDEV_POP(value) AS sd
    FROM measurements
)
SELECT m.value,
       (m.value - b.mean_value) / NULLIF(b.sd, 0) AS z_score
FROM measurements m CROSS JOIN baseline b;

A zero-variance baseline produces NULL rather than a division-by-zero result. For an evaluation, fit the baseline on a separate historical period so the probe does not influence its own threshold. See statistical functions.

Other detection methods​

Only the EMA method is available for durable anomaly streams. There are no standalone SQL functions named anomaly_score_iforest, ZSCORE_ANOMALY, or IQR_OUTLIER, and METHOD 'ensemble' is not available. Compute z-score or quartile (IQR) baselines with SQL aggregates such as AVG, STDDEV, and PERCENTILE_CONT; the z-score example above shows the pattern.

Streaming tutorial · Streaming ML reference

Last updated on