Anomaly Detection (Snowflake ML Functions)¶

Limitations¶

• You cannot choose or adjust the anomaly detection algorithm. In particular, the algorithm does not provide parameters to override trend, seasonality, or seasonal amplitudes; these are inferred from the data.

• The minimum number of rows for the main anomaly detection algorithm is 12 per time series. For time series with between 2 and 11 observations, anomaly detection produces a “naive” result in which all predicted values are equal to the last observed target value. For the labeled anomaly detection case, the number of observations used is the number of rows where the label column is false.

• The minimum acceptable granularity of data is one second. (Timestamps must not be less than one second apart.)

• The minimum granularity of seasonal components is one minute. (The function cannot detect cyclic patterns at smaller time deltas.)

• The “season length” of autoregressive features is tied to the input frequency (24 for hourly data, 7 for daily data, and so on).

• Anomaly detection models, once trained, are immutable. You cannot update existing models with new data; you must train an entirely new model. Models do not support versioning. Generally, you should retrain models on a regular cadence, such as once a day, once a week, or once a month, depending on how frequently you receive new data, to help the model keep up with changing trends.

• This feature only detects anomalies in the test data; it cannot detect anomalies in the training data. Furthermore, timestamps in the test data must all be greater than timestamps in the training data. Ensure that the training data covers a typical period free of actual outliers, or label known outliers in a Boolean column.

• You cannot clone models or share models across roles or accounts. When cloning a schema or database, model objects are skipped.

• You cannot replicate an instance of the ANOMALY_DETECTION class.

Selecting a Virtual Warehouse¶

A Snowflake virtual warehouse provides the compute resources for training and using the machine learning models for this feature. This section provides general guidance on selecting the best size and type of warehouse for this purpose, focusing on the training step (the most time-consuming and memory-intensive part of the process).

Granting Privileges to Create Anomaly Detection Objects¶

Training an anomaly detection model results in a schema-level object. Therefore, the role you use to create models must have the CREATE SNOWFLAKE.ML.ANOMALY_DETECTION privilege on the schema where the model is created, which allows the model to be stored there. This privilege is similar to other schema privileges like CREATE TABLE or CREATE VIEW.

Snowflake recommends that you create a role named analyst to be used by people who need to detect anomalies.

In the following example, the admin role is the owner of the schema admin_db.admin_schema. The analyst role needs to create models in this schema.

USE ROLE admin;
GRANT USAGE ON DATABASE admin_db TO ROLE analyst;
GRANT USAGE ON SCHEMA admin_schema TO ROLE analyst;
GRANT CREATE SNOWFLAKE.ML.ANOMALY_DETECTION ON SCHEMA admin_db.admin_schema TO ROLE analyst;

To use this schema, a user assumes the role analyst:

USE ROLE analyst;
USE SCHEMA admin_db.admin_schema;

If the analyst role has CREATE SCHEMA privileges in database analyst_db, the role can create a new schema analyst_db.analyst_schema and create anomaly detection models in that schema:

USE ROLE analyst;
CREATE SCHEMA analyst_db.analyst_schema;
USE SCHEMA analyst_db.analyst_schema;

To revoke a role’s model creation privilege on the schema, use REVOKE <privileges>:

REVOKE CREATE SNOWFLAKE.ML.ANOMALY_DETECTION ON SCHEMA admin_db.admin_schema FROM ROLE analyst;

Detecting Anomalies for a Single Time Series (Unsupervised)¶

To detect anomalies in your data:

1. Train an anomaly detection model using historical data.

2. Use the trained anomaly detection model to detect anomalies in historical or projected data. The timestamps in the test data must chronologically follow the timestamps in the training data. You need at least 2 data points to train a model, at least 12 for non-naive results, and at least 60 for non-linear results.

See ANOMALY_DETECTION (SNOWFLAKE.ML) for information on the parameters used in creating and using a model.

+--------+-------------------------+----+----------+--------------+--------------+------------+--------------+--------------+
| SERIES | TS                      |  Y | FORECAST |  LOWER_BOUND |  UPPER_BOUND | IS_ANOMALY |   PERCENTILE |     DISTANCE |
+--------|-------------------------+----+----------+--------------+--------------+------------+--------------+--------------|
| NULL   | 2020-01-16 00:00:00.000 |  6 |      4.6 | -7.185885251 | 16.385885251 | False      | 0.6201873452 | 0.3059728606 |
| NULL   | 2020-01-17 00:00:00.000 | 20 |      9   | -2.785885251 | 20.785885251 | False      | 0.9918932208 | 2.404072476  |
+--------+-------------------------+----+----------+--------------+--------------+------------+--------------+--------------|

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'
  ));

Training an Anomaly Detection Model with Labeled Data¶

In the previous example, the result of the model appears to be inaccurate. This is probably because:

• The anomaly detection model was trained on very little input data.

• A larger number of jackets (30) were sold on 2020-01-03. This skewed the predictions upward and increased the size of the prediction interval.

To improve the accuracy of the anomaly detection model, you can either include more training data or label the training data (supervised training). Labeled training data has an additional Boolean column that indicates whether each row is a known anomaly. Labeling can help the anomaly detection model to avoid overfitting to known anomalies in the training data.

To include labeled data in the training data, specify the column containing the label in the LABEL_COLNAME constructor argument of the CREATE SNOWFLAKE.ML.ANOMALY_DETECTION command. For example:

1. Create a view or design a query that returns the labels with the training data.

   For this example, execute the CREATE VIEW command to create a view named view_with_labeled_data that contains the labels in a column named label:

   CREATE OR REPLACE VIEW view_with_labeled_data_for_training
     AS SELECT date, sales, label FROM historical_sales_data
       WHERE store_id=1 and item='jacket';
   

   Copy

2. Create an object for the anomaly detection model, and train the model on the data in that view.

   For this example, execute the CREATE SNOWFLAKE.ML.ANOMALY_DETECTION command to create an anomaly detection object named model_trained_with_labeled_data. The following statement creates the anomaly detection object:

   CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION model_trained_with_labeled_data(
     INPUT_DATA => TABLE(view_with_labeled_data_for_training),
     TIMESTAMP_COLNAME => 'date',
     TARGET_COLNAME => 'sales',
     LABEL_COLNAME => 'label'
   );
   

   Copy

3. Using this new anomaly detection model, call the <model_name>!DETECT_ANOMALIES method, passing in the same arguments that you used in Detecting Anomalies for a Single Time Series (Unsupervised):

   CALL model_trained_with_labeled_data!DETECT_ANOMALIES(
     INPUT_DATA => TABLE(view_with_data_to_analyze),
     TIMESTAMP_COLNAME =>'date',
     TARGET_COLNAME => 'sales'
   );
   

   Copy

   The method returns a table that includes rows for the data currently in the view view_with_data_to_analyze along with the prediction of the detector. For a description of the columns in this table, see Returns.

Output

The results have been rounded for readability.

+--------+-------------------------+----+----------+---------------+--------------+------------+--------------+------------+
| SERIES | TS                      |  Y | FORECAST |   LOWER_BOUND |  UPPER_BOUND | IS_ANOMALY |   PERCENTILE |   DISTANCE |
+--------|-------------------------+----+----------+---------------+--------------+------------+--------------+------------|
| NULL   | 2020-01-16 00:00:00.000 |  6 |        6 |  0.82         | 11.18        | False      | 0.5          | 0          |
| NULL   | 2020-01-17 00:00:00.000 | 20 |        6 | -0.39         | 12.33        | True       | 0.99         | 5.70       |
+--------+-------------------------+----+----------+---------------+--------------+------------+--------------+------------+

Specifying the Prediction Interval For Anomaly Detection¶

You can detect anomalies with varying levels of sensitivity. To specify the percentage of observations to classify as anomalies, create an OBJECT that contains configuration settings for <model_name>!DETECT_ANOMALIES, and set the prediction_interval key to the percentage of the observations that should be marked as anomalies.

To construct this object, you can use either an object constant or the OBJECT_CONSTRUCT function.

Then, when calling the <model_name>!DETECT_ANOMALIES method, pass in this object as the CONFIG_OBJECT argument.

By default, the value associated with the prediction_interval key is set to 0.99, which means that roughly 1% of the data is marked as anomalies. You can specify a value between 0 and 1:

• To mark fewer observations as anomalies, specify a higher value for prediction_interval.

• To mark more observations as anomalies, reduce the prediction_interval value.

The following example configures anomaly detection to be more strict by setting the prediction_interval to 0.995. The example also uses the model trained on labeled data (that you set up in Training an Anomaly Detection Model with Labeled Data) with the view that contains the data to analyze (that you set up in Detecting Anomalies for a Single Time Series (Unsupervised)).

CALL model_trained_with_labeled_data!DETECT_ANOMALIES(
  INPUT_DATA => TABLE(view_with_data_to_analyze),
  TIMESTAMP_COLNAME => 'date',
  TARGET_COLNAME => 'sales',
  CONFIG_OBJECT => {'prediction_interval':0.995}
);

This statement produces a table that includes rows for the data currently in the view view_with_data_to_analyze. Each row includes a column with the prediction of the detector. You can see that the result of this model is more accurate than the unlabeled example.

Output

The results have been rounded for readability.

+--------+-------------------------+----+----------+---------------+--------------+------------+--------------+------------+
| SERIES | TS                      |  Y | FORECAST |   LOWER_BOUND |  UPPER_BOUND | IS_ANOMALY |   PERCENTILE |   DISTANCE |
+--------|-------------------------+----+----------+---------------+--------------+------------+--------------+------------|
| NULL   | 2020-01-16 00:00:00.000 |  6 |        6 |  0.36         | 11.64        | False      | 0.5          | 0          |
| NULL   | 2020-01-17 00:00:00.000 | 20 |        6 | -0.90         | 12.90        | True       | 0.99         | 5.70       |
+--------+-------------------------+----+----------+---------------+--------------+------------+--------------+------------+

Including Additional Columns for Analysis¶

You can include additional columns in the data (for example, temperature, weather, is_black_friday) in the data for training and analysis, if these columns can help you improve the identification of true anomalies.

To include new columns for analysis:

1. For the training data, create a view or design a query that includes the new columns, and create a new anomaly detection object, passing in a reference to that view or query.

2. For the data to analyze, create a view or design a query that includes the new columns, and pass a reference to that view or query to the <model_name>!DETECT_ANOMALIES method.

The anomaly detection model detects and uses the additional columns automatically.

For example, suppose that you want to add the columns temperature, humidity, and holiday:

1. Create a view or design a query that returns the training data with these additional columns.

   For this example, execute the CREATE VIEW command to create a view named view_with_training_data_extra_columns:

   CREATE OR REPLACE VIEW view_with_training_data_extra_columns
     AS SELECT date, sales, label, temperature, humidity, holiday
       FROM historical_sales_data
       WHERE store_id=1 AND item='jacket';
   

   Copy

2. Create an object for the anomaly detection model, and train the model on the data in that view.

   For this example, execute the CREATE SNOWFLAKE.ML.ANOMALY_DETECTION command to create an anomaly detection object named model_with_additional_columns, passing in a reference to the new view:

   CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION model_with_additional_columns(
     INPUT_DATA => TABLE(view_with_training_data_extra_columns),
     TIMESTAMP_COLNAME => 'date',
     TARGET_COLNAME => 'sales',
     LABEL_COLNAME => 'label'
   );
   

   Copy

3. Create a view or design a query that returns the data to analyze with these additional columns.

   For this example, execute the CREATE VIEW command to create a view named view_with_data_for_analysis_extra_columns:

   CREATE OR REPLACE VIEW view_with_data_for_analysis_extra_columns
     AS SELECT date, sales, temperature, humidity, holiday
       FROM new_sales_data
       WHERE store_id=1 AND item='jacket';
   

   Copy

4. Using this new anomaly detection object, call the <model_name>!DETECT_ANOMALIES method, passing in the new view:

   CALL model_with_additional_columns!DETECT_ANOMALIES(
     INPUT_DATA => TABLE(view_with_data_for_analysis_extra_columns),
     TIMESTAMP_COLNAME => 'date',
     TARGET_COLNAME => 'sales',
     CONFIG_OBJECT => {'prediction_interval':0.93}
   );
   

   Copy

   This statement produces a table that includes rows for the data currently in the view view_with_data_for_analysis_extra_columns along with the prediction of the detector. The format of the output is the same as the format of the output shown for the commands that you ran earlier.

Output

The results have been rounded for readability.

+--------+-------------------------+----+----------+-------------+--------------+------------+--------------+------------+
| SERIES | TS                      |  Y | FORECAST | LOWER_BOUND |  UPPER_BOUND | IS_ANOMALY |   PERCENTILE |   DISTANCE |
+--------|-------------------------+----+----------+-------------+--------------+------------+--------------+------------|
| NULL   | 2020-01-16 00:00:00.000 |  6 |        6 | 2.34        |  9.64        | False      | 0.5          | 0          |
| NULL   | 2020-01-17 00:00:00.000 | 20 |        6 | 1.56        | 10.451       | True       | 0.99         | 5.70       |
+--------+-------------------------+----+----------+-------------+--------------+------------+--------------+------------+

Detecting Anomalies in Multiple Series¶

The previous sections provided examples of detecting anomalies for a single series. These examples flagged anomalies for the sale of one type of item (jackets) in one store (store ID 1). To detect anomalies for multiple time series at the same time (for example, for multiple combinations of items and stores):

1. For the training data, create a view or design a query that includes a column that identifies the series, and create a new anomaly detection object, passing in a reference to that view or query and specifying the name of the series column for the SERIES_COLNAME argument.

2. For the data to analyze, create a view or design a query that includes the column that identifies the series. Call the <model_name>!DETECT_ANOMALIES method, passing in a reference to that view or query and specifying the name of the series column for the SERIES_COLNAME argument.

For example, suppose that you want to use the combination of the store_id and item columns to identify the series:

1. Create a view or design a query that returns the training data with the column for the series.

   For this example, execute the CREATE VIEW command to create a view named view_with_training_data_multiple_series that contains a column named store_item that identifies the series as a combination of store ID and item:

   CREATE OR REPLACE VIEW view_with_training_data_multiple_series
     AS SELECT
       [store_id, item] AS store_item,
       date,
       sales,
       label,
       temperature,
       humidity,
       holiday
     FROM historical_sales_data;
   

   Copy

2. Create an object for the anomaly detection, and train the model on the data in that view.

   For this example, execute the CREATE SNOWFLAKE.ML.ANOMALY_DETECTION command to create an anomaly detection object named model_for_multiple_series, passing in a reference to the new view and specifying store_item for the SERIES_COLNAME argument:

   CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION model_for_multiple_series(
     INPUT_DATA => TABLE(view_with_training_data_multiple_series),
     SERIES_COLNAME => 'store_item',
     TIMESTAMP_COLNAME => 'date',
     TARGET_COLNAME => 'sales',
     LABEL_COLNAME => 'label'
   );
   

   Copy

3. Create a view or design a query that returns the data to analyze with the series column.

   For this example, execute the CREATE VIEW command to create a view named view_with_data_for_analysis_multiple_series that contains a column named store_item for the series:

   CREATE OR REPLACE VIEW view_with_data_for_analysis_multiple_series
     AS SELECT
       [store_id, item] AS store_item,
       date,
       sales,
       temperature,
       humidity,
       holiday
     FROM new_sales_data;
   

   Copy

4. Using this new anomaly detection object, call the <model_name>!DETECT_ANOMALIES method, passing in the new view and specifying store_item for the SERIES_COLNAME argument:

   CALL model_for_multiple_series!DETECT_ANOMALIES(
     INPUT_DATA => TABLE(view_with_data_for_analysis_multiple_series),
     SERIES_COLNAME => 'store_item',
     TIMESTAMP_COLNAME => 'date',
     TARGET_COLNAME => 'sales',
     CONFIG_OBJECT => {'prediction_interval':0.995}
   );
   

   Copy

   This statement produces a table that includes rows for the data currently in the view view_with_data_for_analysis_multiple_series along with the prediction of the detector. The output includes the column that identifies the series.

Output

The results have been rounded for readability.

+--------------+-------------------------+----+----------+---------------+--------------+------------+---------------+--------------+
| SERIES       | TS                      |  Y | FORECAST |   LOWER_BOUND |  UPPER_BOUND | IS_ANOMALY |    PERCENTILE |     DISTANCE |
|--------------+-------------------------+----+----------+---------------+--------------+------------+---------------+--------------|
| [            | 2020-01-16 00:00:00.000 |  3 |      6.3 |  2.07         | 10.53        | False      | 0.01          | -2.19         |
|   2,         |                         |    |          |               |              |            |               |              |
|   "umbrella" |                         |    |          |               |              |            |               |              |
| ]            |                         |    |          |               |              |            |               |              |
| [            | 2020-01-17 00:00:00.000 | 70 |      2.9 | -1.33         |  7.13        | True       | 1             | 44.54         |
|   2,         |                         |    |          |               |              |            |               |              |
|   "umbrella" |                         |    |          |               |              |            |               |              |
| ]            |                         |    |          |               |              |            |               |              |
| [            | 2020-01-16 00:00:00.000 |  6 |      6   |  0.36         | 11.64        | False      | 0.5           |  0           |
|   1,         |                         |    |          |               |              |            |               |              |
|   "jacket"   |                         |    |          |               |              |            |               |              |
| ]            |                         |    |          |               |              |            |               |              |
| [            | 2020-01-17 00:00:00.000 | 20 |      6   | -0.90         | 12.90        | True       | 0.99          |  5.70         |
|   1,         |                         |    |          |               |              |            |               |              |
|   "jacket"   |                         |    |          |               |              |            |               |              |
| ]            |                         |    |          |               |              |            |               |              |
+--------------+-------------------------+----+----------+---------------+--------------+------------+---------------+--------------+

Recurring Training with a Snowflake Task¶

You can update your model to reflect the most up-to-date data using Snowflake Tasks.

To create a task that refreshes the anomaly detection object every hour, run following statement, replacing your_warehouse_name with your warehouse name:

CREATE OR REPLACE TASK ad_model_retrain_task
WAREHOUSE = <your_warehouse_name>
SCHEDULE = '60 MINUTE'
AS
EXECUTE IMMEDIATE
$$
BEGIN
  CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION model_trained_with_labeled_data(
    INPUT_DATA => TABLE(view_with_labeled_data_for_training),
    TIMESTAMP_COLNAME => 'date',
    TARGET_COLNAME => 'sales',
    LABEL_COLNAME => 'label'
  );
END;
$$;

By default, newly created tasks are suspended.

To resume the task, execute the ALTER TASK … RESUME command:

ALTER TASK ad_model_retrain_task RESUME;

To pause the task, execute the ALTER TASK … SUSPEND command:

ALTER TASK ad_model_retrain_task SUSPEND;

Monitoring with a Snowflake Task¶

You can also use Snowflake Tasks to monitor your data at a given frequency.

First, create a table to hold the results of anomaly detection:

CREATE OR REPLACE TABLE anomaly_res_table (
  ts TIMESTAMP_NTZ, y FLOAT, forecast FLOAT, lower_bound FLOAT, upper_bound FLOAT,
  is_anomaly BOOLEAN, percentile FLOAT, distance FLOAT);

Create a task to store the results of a recurring anomaly detection operation in the table. This example sets the WAREHOUSE parameter to snowhouse. You can replace that with your own warehouse:

CREATE OR REPLACE TASK ad_model_monitoring_task
WAREHOUSE = snowhouse
SCHEDULE = '1 minute'
AS
EXECUTE IMMEDIATE
$$
BEGIN
  INSERT INTO anomaly_res_table (ts, y, forecast, lower_bound, upper_bound, is_anomaly, percentile, distance)
    SELECT * FROM TABLE(
      model_trained_with_labeled_data!DETECT_ANOMALIES(
        INPUT_DATA => TABLE(view_with_data_to_analyze),
        TIMESTAMP_COLNAME => 'date',
        TARGET_COLNAME => 'sales',
        CONFIG_OBJECT => {'prediction_interval':0.99}
    )
  );
END;
$$;

To resume the task, execute the ALTER TASK … RESUME command:

ALTER TASK ad_model_monitoring_task RESUME;

anomaly_res_table then contains all the results for each task run.

To pause the task, execute the ALTER TASK … SUSPEND command:

ALTER TASK ad_model_monitoring_task SUSPEND;

Monitoring with a Snowflake Alert¶

You can also use Snowflake Alerts to monitor your data at a given frequency and send you email with detected anomalies. The following statements create an alert that detects anomalies every minute. First you define a stored procedure to detect anomalies, then create an alert that uses that stored procedure.

CREATE OR REPLACE PROCEDURE extract_anomalies()
  RETURNS TABLE()
  LANGUAGE SQL
  AS
  $$
    BEGIN
      let res RESULTSET := (SELECT * FROM TABLE(
        model_trained_with_labeled_data!DETECT_ANOMALIES(
          INPUT_DATA => TABLE(view_with_data_to_analyze),
          TIMESTAMP_COLNAME => 'date',
          TARGET_COLNAME => 'sales',
          CONFIG_OBJECT => {'prediction_interval':0.99}
        ))
        WHERE is_anomaly = TRUE
      );
      RETURN TABLE(res);
    END;
  $$
  ;

CREATE OR REPLACE ALERT sample_sales_alert
WAREHOUSE = <your_warehouse_name>
SCHEDULE = '1 MINUTE'
IF (EXISTS (CALL extract_anomalies()))
THEN
CALL SYSTEM$SEND_EMAIL(
  'sales_email_alert',
  'your_email@snowflake.com',
  'Anomalous Sales Data Detected in data stream',
  CONCAT(
    'Anomalous Sales Data Detected in data stream \n',
    'Value outside of prediction interval detected in the most recent run at ',
    current_timestamp(1)
  ));

To start or resume the alert, execute the ALTER ALERT … RESUME command:

ALTER ALERT sample_sales_alert RESUME;

To pause the alert, execute the ALTER ALERT … SUSPEND command:

ALTER ALERT sample_sales_alert SUSPEND;

Limitations¶

• You cannot choose the technique used to calculate feature importance.

• Feature importance scores can be helpful for gaining intuition about which features are important to your model’s accuracy, but the actual values should be considered estimates.

Example¶

To understand the relative importance of your features to your model, train a model, and then call <model_name>!EXPLAIN_FEATURE_IMPORTANCE. In this example, you first create random data with two exogenous variables: one that is random and therefore unlikely to be very important to your model, and one that is a copy of your target and therefore likely to be more important to your model.

Execute the following statements to generate the data, train a model on it, and get the importance of the features:

CREATE OR REPLACE VIEW v_random_data AS SELECT
  DATEADD('minute', ROW_NUMBER() over (ORDER BY 1), '2023-12-01')::TIMESTAMP_NTZ ts,
  MOD(SEQ1(),10) y,
  UNIFORM(1, 100, RANDOM(0)) exog_a
FROM TABLE(GENERATOR(ROWCOUNT => 500));

CREATE OR REPLACE VIEW v_feature_importance_demo AS SELECT
  ts,
  y,
  exog_a
FROM v_random_data;

SELECT * FROM v_feature_importance_demo;

CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION anomaly_model_feature_importance_demo(
  INPUT_DATA => TABLE(v_feature_importance_demo),
  TIMESTAMP_COLNAME => 'ts',
  TARGET_COLNAME => 'y',
  LABEL_COLNAME => ''
);

CALL anomaly_model_feature_importance_demo!EXPLAIN_FEATURE_IMPORTANCE();

Output

Because this example uses random data, do not expect your output to match this exactly.

+--------+------+--------------------------------------+-------+-------------------------+
| SERIES | RANK | FEATURE_NAME                         | SCORE | FEATURE_TYPE            |
+--------+------+--------------------------------------+-------+-------------------------+
| NULL   |    1 | aggregated_endogenous_trend_features |  0.36 | derived_from_endogenous |
| NULL   |    2 | exog_a                               |  0.22 | user_provided           |
| NULL   |    3 | epoch_time                           |  0.15 | derived_from_timestamp  |
| NULL   |    4 | minute                               |  0.13 | derived_from_timestamp  |
| NULL   |    5 | lag60                                |  0.07 | derived_from_endogenous |
| NULL   |    6 | lag120                               |  0.06 | derived_from_endogenous |
| NULL   |    7 | hour                                 |  0.01 | derived_from_timestamp  |
+--------+------+--------------------------------------+-------+-------------------------+

Example¶

CREATE TABLE t_error(date TIMESTAMP_NTZ, sales FLOAT, series VARCHAR);
INSERT INTO t_error VALUES
  (TO_TIMESTAMP_NTZ('2019-12-20'), 1.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-21'), 2.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-22'), 3.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-23'), 2.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-24'), 1.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-25'), 2.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-26'), 3.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-27'), 2.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-28'), 1.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-29'), 2.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-30'), 3.0, 'A'),
  (TO_TIMESTAMP_NTZ('2019-12-31'), 2.0, 'A'),
  (TO_TIMESTAMP_NTZ('2020-01-01'), 2.0, 'A'),
  (TO_TIMESTAMP_NTZ('2020-01-02'), 3.0, 'A'),
  (TO_TIMESTAMP_NTZ('2020-01-03'), 3.0, 'A'),
  (TO_TIMESTAMP_NTZ('2020-01-04'), 7.0, 'A'),
  (TO_TIMESTAMP_NTZ('2020-01-06'), 10.0, 'B'), -- the same timestamp used again and again
  (TO_TIMESTAMP_NTZ('2020-01-06'), 13.0, 'B'),
  (TO_TIMESTAMP_NTZ('2020-01-06'), 12.0, 'B'),
  (TO_TIMESTAMP_NTZ('2020-01-06'), 15.0, 'B'),
  (TO_TIMESTAMP_NTZ('2020-01-06'), 14.0, 'B'),
  (TO_TIMESTAMP_NTZ('2020-01-06'), 18.0, 'B'),
  (TO_TIMESTAMP_NTZ('2020-01-06'), 12.0, 'B');

CREATE SNOWFLAKE.ML.ANOMALY_DETECTION model(
  INPUT_DATA => TABLE(SELECT date, sales, series FROM t_error),
  SERIES_COLNAME => 'series',
  TIMESTAMP_COLNAME => 'date',
  TARGET_COLNAME => 'sales',
  LABEL_COLNAME => '',
  CONFIG_OBJECT => {'ON_ERROR': 'SKIP'}
);

CALL model!SHOW_TRAINING_LOGS();

Output

+--------+--------------------------------------------------------------------------+
| SERIES | LOGS                                                                     |
+--------+--------------------------------------------------------------------------+
| "B"    | {   "Errors": [     "At least two unique timestamps are required."   ] } |
| "A"    | NULL                                                                     |
+--------+--------------------------------------------------------------------------+