Finding Causes with Anomaly Detection and Top Insights

1.Anomaly Detection: finding when something went wrong

Before you can explain an anomaly, you have to find it reliably. Snowflake's Anomaly Detection ML function trains a model on time-series data and flags outliers, meaning points outside the expected range. According to the documentation, it is useful for pinpointing the origin of problems or deviations in processes when there is no obvious cause. Two of its examples are working out when a logging pipeline started failing and finding the days when compute costs were higher than expected.

The model works by comparing against a forecast. It forecasts the period you are checking and then compares the actual values with that forecast. Under the hood, a gradient boosting machine uses differencing for non-stationary trends, autoregressive lags, rolling averages and calendar variables (such as day of week) derived from the timestamp. You cannot choose or tune the algorithm. Trend and seasonality are inferred from the data. The input needs a timestamp column and a target column. It can be multi-series, where one model checks each series separately based on an identifier such as the store. It can also include exogenous variables, but if you train with them, you must supply their values for the timestamps you later check.

The prediction interval is the range expected to contain a given share of the data. The default is 0.99, and the documentation suggests you may want a value very close to 1.0, such as 0.9999. The most important limit for diagnosis is about which data gets checked. The model only finds anomalies in the test data, never in the training data, and every test timestamp must come after the training timestamps. A spike in the training set therefore distorts what the model treats as normal. You can avoid this by training on a typical period with no outliers or by labelling known outliers in a Boolean column, which is called supervised training. Trained models are immutable. To include new data, you drop the model and train a new one.

Sources1

2.Training a model and reading its verdict

The workflow has two steps. First you create the model object with CREATE SNOWFLAKE.ML.ANOMALY_DETECTION, which trains it on the input you pass. Then you call the model's DETECT_ANOMALIES method on later data. The function runs with limited privileges, so you pass data as references: the TABLE keyword, SYSTEM$REFERENCE, or SYSTEM$QUERY_REFERENCE for an inline query. Pass an empty string for LABEL_COLNAME when training is unsupervised.

CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION sensor_model(
  INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'sensor_data_view'),
  TIMESTAMP_COLNAME => 'timestamp',
  TARGET_COLNAME => 'temperature',
  LABEL_COLNAME => '');

CALL sensor_model! ? (
  INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'sensor_data_device3'),
  TIMESTAMP_COLNAME => 'timestamp',
  TARGET_COLNAME => 'temperature'
);

For each test row, the output gives the observed value Y, the FORECAST, a LOWER_BOUND and UPPER_BOUND, IS_ANOMALY, a PERCENTILE and a DISTANCE. In the sensor example, the reading at 00:00:30 was 36.0422 against an upper bound of 36.036839539, so IS_ANOMALY is True. The next two readings were nearly as high but stayed inside their bounds. Keep the test data close in time to the training data. If you have per-second timestamps, don't test on data millions of seconds later. To keep the verdicts for further analysis, call the method inside FROM with the TABLE keyword (and no CALL) and save the result with CREATE TABLE … AS SELECT.

CREATE TABLE my_anomalies AS
  SELECT * FROM TABLE(basic_model!DETECT_ANOMALIES(
    INPUT_DATA => TABLE(view_with_data_to_analyze),
    TIMESTAMP_COLNAME =>'date',
    TARGET_COLNAME => 'sales'
  ));

Diagnose the model as well as the data. In the documentation's jacket-sales example, the results looked inaccurate for two reasons: the training set was tiny, and one day with 30 jackets sold pulled the predictions upward and widened the interval. The fix is more training data or labelled training data.

Sources12

3.Top Insights: which segments explain the change

Anomaly detection tells you when. Top Insights tells you who and where. It is Snowflake's ML function for key driver analysis. A decision tree splits the data into segments that behave differently with respect to a metric and then compares a control group (the baseline) with a test group (the points of interest). This covers two diagnostic questions. Time-series analysis asks what drove this month's change compared with before. Vertical analysis asks what explains the difference between populations, such as which user segments account for different new-user growth in the United States and in EMEA. That makes it the main tool for identifying demographics and relationships in many columns at once. It suits datasets with too many dimensions to search by intuition.

You create one stateless instance per schema with CREATE SNOWFLAKE.ML.TOP_INSIGHTS, which needs the CREATE SNOWFLAKE.ML.TOP_INSIGHTS privilege. Callers who are not the owner need USAGE on the instance. The input must include a Boolean label column: FALSE for control rows and TRUE for test rows. This label is usually derived, so you build it in a view.

CREATE VIEW input_table_time_series_label (
  ds, metric, dim_country, dim_vertical, label ) AS
  SELECT
    ds,
    metric,
    dim_country,
    dim_vertical,
    ds >= dateadd(month, -1, current_date) AS label
  FROM input_table;

For vertical analysis, the label would compare populations instead, for example dim_country <> 'USA' as label. You don't declare dimension types. Numeric columns are treated as continuous and string or Boolean columns as categorical, so a numeric code such as a store number must be cast to a string if you want it treated as a category. You then pass the whole input as one reference to GET_DRIVERS:

CALL my_insights!get_drivers (
  INPUT_DATA => TABLE(my_table),
  LABEL_COLNAME => 'label',
  METRIC_COLNAMe => 'sales');

Sources3

4.Reading Top Insights results and sizing the run

Top Insights returns one row per segment of interest, with a plain-English description that can combine several criteria, such as 'COUNTRY = france, not VERTICAL = fashion, not VERTICAL = tech'. Segments are filtered for significance and distinctiveness, so redundant ones don't appear. Each row measures how much that segment contributes to the gap between control and test:

Negative values are not errors. A negative contribution, relative contribution or growth rate means the segment pulled the metric down, which is often exactly the answer to 'why did revenue fall?'. On cost, execution time grows with the number of rows and dimensions. The data must fit in memory, and datasets beyond roughly 1,000,000 rows and 1,000 columns may run out of it. A bigger standard warehouse doesn't generally help. Snowflake recommends a Snowpark-optimized warehouse, which has more memory at the same size.

Sources3

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Train the anomaly model on the full history and it will flag the outliers in that history too.Why is that wrong?

   Detection runs only on test data whose timestamps come after the training data. Outliers in the training set aren't flagged, and they skew the model unless labelled.

   Covered in Anomaly Detection: finding when something went wrong

2. 2.A numeric region or store code will be treated as a category in Top Insights automatically.Why is that wrong?

   Dimension types are inferred from data type. Numbers are treated as continuous, so a numeric code has to be cast to a string to act as a category.

   Covered in Top Insights: which segments explain the change

3. 3.If Top Insights is slow or runs out of memory, move to a larger standard warehouse.Why is that wrong?

   Performance doesn't generally improve beyond the size needed to hold the data in memory. Snowflake recommends a Snowpark-optimized warehouse instead.

   Covered in Reading Top Insights results and sizing the run

Practise it for real

Train an anomaly detection model on synthetic sensor data and confirm it flags a reading above its upper bound

1. 1.Create table sensor_data_30_rows and fill it with 30 per-second DEVICE3 readings using UNIFORM, RANDOM and GENERATOR(ROWCOUNT => 30), then create sensor_data_view on it.

   Why: The view lets you retrain with different row counts later without touching the source table.

   You should see: A 30-row table with timestamps from 2024-03-01 00:00:00 to 00:00:29.

2. 2.Run CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION sensor_model with INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'sensor_data_view'), TIMESTAMP_COLNAME 'timestamp', TARGET_COLNAME 'temperature' and LABEL_COLNAME ''.

   Why: Creating the object trains the model. The empty label means unsupervised training.

   You should see: Status 'Instance SENSOR_MODEL successfully created.'

3. 3.Create table sensor_data_device3 and insert the three readings for 00:00:30, 00:00:31 and 00:00:32.

   Why: Test timestamps must come after the training timestamps, without a large gap.

   You should see: Three rows with temperatures around 36.

4. 4.CALL sensor_model!DETECT_ANOMALIES with INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'sensor_data_device3') and the same column names.

   Why: This compares each actual reading with the forecast and the prediction interval.

   You should see: Rows with Y, FORECAST, LOWER_BOUND, UPPER_BOUND and IS_ANOMALY. Any row where Y is outside the bounds shows True. Exact values vary because the data is random.