Snowflake ML FORECAST: Training Models and Reading Predictions

1.Forecasting is a model you train, then call

Snowflake's built-in forecasting is not a scalar function you drop into a SELECT list. It is a class, SNOWFLAKE.ML.FORECAST. You create an instance of it, which trains a model on your history, and then you call methods on that instance to get predictions. The documentation's standard example is forecasting sales by item for the next two weeks.

Before you write any SQL, three things must be in place. First, select a database, schema and virtual warehouse. Second, you must own the schema or hold the CREATE SNOWFLAKE.ML.FORECAST privilege in it. Third, you need a table or view with at least one timestamp column and one numeric column. The timestamps should be at a fixed interval with few gaps. Training can still handle real-world data with missing, duplicate or misaligned time steps. You can run the whole process from the AI & ML Studio in Snowsight or from SQL.

CREATE SNOWFLAKE.ML.FORECAST model1(
  INPUT_DATA => TABLE(v1),
  TIMESTAMP_COLNAME => 'date',
  TARGET_COLNAME => 'sales'
);

Only three arguments are required. INPUT_DATA is a table or view reference wrapped in the TABLE keyword. TIMESTAMP_COLNAME names the time column, and TARGET_COLNAME names the numeric metric to predict. When training succeeds, Snowflake replies "Instance MODEL1 successfully created." From then on, model1 is an object you can call methods on.

Sources1

2.Calling FORECAST and reading the prediction interval

You get predictions from the instance's FORECAST method, using the model!METHOD syntax. For a model trained without extra features, FORECASTING_PERIODS says how many steps ahead to predict:

call model1!FORECAST(FORECASTING_PERIODS => 3);

Every forecast returns the same five columns. Dashboards and downstream queries are built on these columns:

LOWER_BOUND and UPPER_BOUND express the model's uncertainty. The default prediction_interval is 0.95, which means 95% of future points are expected to fall between the two bounds. To use a different level, pass prediction_interval in a CONFIG_OBJECT. In the documentation's toy example, the series is perfectly linear and the model makes zero errors, so both bounds equal the forecast.

CALL is convenient for interactive use. To filter, join or save the output, put the method call inside TABLE( ) in a FROM clause and leave out the CALL keyword:

CREATE TABLE my_forecasts AS
  SELECT * FROM TABLE(model1!FORECAST(FORECASTING_PERIODS => 3));

CALL model1!FORECAST(FORECASTING_PERIODS => 3, CONFIG_OBJECT => {' ? ': 0.8});

Sources21

3.Many series at once, and forecasting with features

Real forecasts rarely involve a single series. To forecast sales separately for every store/item combination, build one column that identifies each series and pass it as SERIES_COLNAME. The column can hold a value of any type, or an array built from several columns. The documentation's example uses [store_id, item] AS store_item. A single CREATE statement then trains a model for every series:

CREATE SNOWFLAKE.ML.FORECAST model2(
  INPUT_DATA => TABLE(v3),
  SERIES_COLNAME => 'store_item',
  TIMESTAMP_COLNAME => 'date',
  TARGET_COLNAME => 'sales'
);

Calling model2!FORECAST with only FORECASTING_PERIODS predicts every series, and the SERIES column shows which rows belong to which series. If you need just one series, name it with SERIES_VALUE. Only that series' forecast is generated, which is cheaper than forecasting everything and filtering afterwards:

CALL model2!FORECAST(SERIES_VALUE => [2,'umbrella'], FORECASTING_PERIODS => 2);

Features are additional factors you believe influence the metric, such as temperature, humidity or holidays. These are also called exogenous variables. No special argument is needed to declare them: any column in the training data other than the timestamp and the target is treated as a feature. The trade-off comes at prediction time. Because the model learned how sales respond to temperature, it needs future temperatures to make a prediction. You therefore supply a view of future feature values as INPUT_DATA, and FORECASTING_PERIODS is no longer used. The forecast timestamps come from that view:

CALL model3!FORECAST(
  INPUT_DATA => TABLE(v2_forecast),
  TIMESTAMP_COLNAME =>'date'
);

Sources1

4.Tuning training with CONFIG_OBJECT

CREATE SNOWFLAKE.ML.FORECAST also accepts a CONFIG_OBJECT. Its keys control how the model is trained. These are the keys that most often decide whether a forecasting job fits the business need:

Several of these keys trade accuracy against cost. With 'best', the default, several algorithms compete. 'fast' trains a single GBM, which is quicker but may be less accurate. Setting evaluate to FALSE skips the extra cross-validation training. frequency and the aggregation_* keys work together: you declare the grain you want, for example '1 day', and choose how values are combined at that grain. With on_error set to 'skip', you can find the series that failed with the model's SHOW_TRAINING_LOGS method.

For warehouse sizing, the documentation names two key factors: the number of rows and columns, and the number of distinct series. Finally, a trained model never changes. When new data arrives, you train a new model. Unused models still incur storage cost until you drop them.

Sources31

Exam traps

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

1. 1.To query forecast output, write SELECT * FROM TABLE(CALL model!FORECAST(...)).Why is that wrong?

   Inside a FROM clause you leave out CALL and wrap the method call in TABLE( ): SELECT * FROM TABLE(model1!FORECAST(FORECASTING_PERIODS => 3)).

   Covered in Calling FORECAST and reading the prediction interval

2. 2.A model trained with exogenous features can be forecast with FORECASTING_PERIODS alone, like a plain model.Why is that wrong?

   A feature model needs future values of each feature. You pass them as INPUT_DATA, and the forecast timestamps come from that data rather than from a period count.

   Covered in Many series at once, and forecasting with features

3. 3.By default, the forecast model trains a single fast GBM algorithm.Why is that wrong?

   The default method is 'best', an ensemble that picks the best algorithm for the data. 'fast' is the single-GBM option you have to request.

   Covered in Tuning training with CONFIG_OBJECT