Regression Functions, Forecast Evaluation and Classification in Snowflake

1.Linear regression with REGR_SLOPE and REGR_INTERCEPT

Not every prediction needs a trained model. Snowflake's aggregate functions, which compute values such as sums, averages and standard deviations across rows, include a Linear Regression family: REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY and REGR_SYY. Two of them define a straight line. REGR_SLOPE is computed as COVAR_POP(x,y) / VAR_POP(x). REGR_INTERCEPT is computed as AVG(y) - REGR_SLOPE(y,x) * AVG(x). Together they give the line y = intercept + slope × x, which you can extend to a new x.

SELECT k, REGR_SLOPE(v, v2) FROM aggr GROUP BY k;

In the documentation's sample data, group 1 has a single row with v2 NULL, and the result for that group is NULL. Group 2 has four rows, one of which has v2 NULL. That row is dropped, and the remaining three pairs give a slope of 0.831408776. The same data gives REGR_INTERCEPT(v, v2) = 1.154734411. Both functions use only non-null pairs. They return FLOAT unless an input is DECFLOAT, and they do not support DISTINCT. Each can also be used as a window function, with OVER (PARTITION BY ...) only. That means you cannot compute a rolling regression with a window frame.

Sources123

2.Judging a forecast: evaluation metrics and feature importance

A forecast is only useful if you know how well it performs. By default, every forecasting model is evaluated with cross-validation while it trains. Alongside the final model, which is trained on all your data, Snowflake trains extra models on subsets of the data. It then scores their predictions against the withheld actual values. The evaluation_config object controls the splits: n_splits (default 1), max_train_size, test_size and gap. If you don't need metrics, set evaluate to FALSE and the extra training is skipped.

You read the results with model!SHOW_EVALUATION_METRICS(). You can also pass that method new out-of-sample data later and get metrics on data the model has never seen.

With the default n_splits of 1, there is only one validation set, so STANDARD_DEVIATION is NULL. Very small datasets may produce no metrics at all.

model!EXPLAIN_FEATURE_IMPORTANCE() shows what drove the predictions. It counts how often the model used each feature, including features generated automatically such as rolling averages. The scores are normalized so that they sum to 1. Near-identical features split their importance between them: two identical columns can each score half of what one would score alone. model!SHOW_TRAINING_LOGS() is where you debug training errors.

Sources45

3.Predicting a category with CLASSIFICATION

Forecasting predicts a number at future timestamps. Classification predicts which class a row belongs to. Typical uses are churn prediction, fraud detection and spam detection. It supports both binary (two-class) and multi-class problems. It follows the same pattern as forecasting: you create a SNOWFLAKE.ML.CLASSIFICATION instance, which trains a gradient boosting machine, and then call its methods. The training data needs a target column holding each row's labeled class and at least one feature column. No timestamp column is required:

CREATE OR REPLACE SNOWFLAKE.ML.CLASSIFICATION model_binary(
    INPUT_DATA => SYSTEM$REFERENCE('view', 'binary_classification_view'),
    TARGET_COLNAME => 'label'
);

Inference uses SELECT model_name!PREDICT(...) on new rows. Those rows must have the same feature names and types as the training data. Columns the model never saw are ignored. To assess the model, call the evaluation methods:

CALL <model_name>!SHOW_EVALUATION_METRICS();
CALL <model_name>!SHOW_GLOBAL_EVALUATION_METRICS();
CALL <model_name>!SHOW_THRESHOLD_METRICS();
CALL <model_name>!SHOW_CONFUSION_MATRIX();

Data types decide how classification treats each feature. Numeric features are continuous. String and Boolean features are categorical, and high-cardinality strings are supported but free text is not. Timestamps must be TIMESTAMP_NTZ, and the model derives epoch, day, week and month features from them. The label must have more than one distinct value, fewer distinct values than the number of rows, and no more than 255 classes. You cannot choose the algorithm or set its parameters. Models are immutable, so to update one you retrain it with CREATE OR REPLACE.

Sources6

4.Matching the business question to the right tool

Snowflake's ML functions come with a model type already chosen for each task, so the analyst's job is to pick the right function. Forecasting and Anomaly Detection both work on time series. Forecasting projects a metric into the future. Anomaly Detection flags points in the data being checked that differ from what was expected. Classification and Top Insights don't require time series. Classification assigns rows to classes. Top Insights looks for dimensions and values that move a metric in surprising ways. Below all of these sit the REGR_* aggregates. When you only need a straight-line relationship between two numeric columns, they give you one with no model object and no training cost. Every ML function, by contrast, incurs storage cost for its model instances and compute cost for training and prediction.

Sources7

Exam traps

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

1. 1.REGR_SLOPE takes the independent variable first, as in REGR_SLOPE(x, y).Why is that wrong?

   Both REGR_SLOPE and REGR_INTERCEPT take the dependent variable y first and the independent variable x second. Reversing them silently returns a different line.

   Covered in Linear regression with REGR_SLOPE and REGR_INTERCEPT

2. 2.A classification model needs a timestamp column, just like forecasting.Why is that wrong?

   Classification needs only a labeled target column and at least one feature column. It is one of the ML functions that does not require time series data.

   Covered in Predicting a category with CLASSIFICATION

Practise it for real

Train a forecasting model on a daily metric, generate a forecast you can query, and check how trustworthy it is

1. 1.Create a view with one timestamp column at a fixed daily interval and one numeric metric column.

   Why: Forecasting needs at least a timestamp column and a numeric column.

   You should see: SELECT on the view returns one row per day with a numeric value.

2. 2.Run CREATE SNOWFLAKE.ML.FORECAST my_model(INPUT_DATA => TABLE(my_view), TIMESTAMP_COLNAME => 'my_timestamps', TARGET_COLNAME => 'my_metric');

   Why: Creating the instance trains the model, and the model infers the daily interval from the data.

   You should see: A message that the instance was successfully created.

3. 3.Run SELECT * FROM TABLE(my_model!FORECAST(FORECASTING_PERIODS => 7));

   Why: Calling the method in a FROM clause makes the output queryable, so it can be saved or joined.

   You should see: Seven rows with columns SERIES (NULL), TS, FORECAST, LOWER_BOUND and UPPER_BOUND.

4. 4.Run CALL my_model!SHOW_EVALUATION_METRICS();

   Why: Cross-validation metrics show how far the model's predictions fell from withheld actual values.

   You should see: Rows for MAE, MAPE, MDA, MSE, SMAPE and the interval metrics, or no metrics if the dataset is too small to split.

5. 5.Run CALL my_model!EXPLAIN_FEATURE_IMPORTANCE();

   Why: Shows which features, including auto-generated ones, drove the predictions.

   You should see: Feature scores between 0 and 1 that sum to 1.