ML model training in Snowflake - Step-by-Step Execution

Process Flow - ML model training in Snowflake

Prepare Data

↓

Create Model

↓

Train Model

↓

Evaluate Model

↓

Use Model for Prediction

This flow shows the steps to train a machine learning model in Snowflake: prepare data, create the model, train it, evaluate, and then use it for predictions.

Execution Sample

Snowflake

CREATE OR REPLACE MODEL my_model
  OPTIONS (model_type='linear_regression') AS
SELECT feature1, feature2, target FROM training_data;

This code creates and trains a linear regression model named 'my_model' using features and target from 'training_data' table.

Process Table

Step	Action	SQL Command	Result
1	Prepare Data	SELECT feature1, feature2, target FROM training_data;	Data selected for training
2	Create and Train Model	CREATE OR REPLACE MODEL my_model OPTIONS (model_type='linear_regression') AS SELECT feature1, feature2, target FROM training_data;	Model 'my_model' created and trained
3	Evaluate Model	SELECT * FROM TABLE(EVALUATE_MODEL('my_model', 'training_data'));	Evaluation metrics returned
4	Use Model for Prediction	SELECT feature1, feature2, PREDICT('my_model', feature1, feature2) AS prediction FROM new_data;	Predictions generated for new data

💡 All steps completed successfully; model trained and predictions ready

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4
training_data	table with raw data	data selected with features and target	used to train model	used to evaluate model	unchanged
my_model	not created	not created	created and trained	evaluated	used for prediction
predictions	none	none	none	none	generated for new_data

Key Moments - 3 Insights

Why do we use SELECT inside CREATE MODEL?

The SELECT statement defines the data used to train the model. It tells Snowflake which features and target to use, as shown in execution_table step 2.

What happens if the model already exists?

Using CREATE OR REPLACE MODEL replaces the existing model with the new one, ensuring the latest training data and parameters are applied, as in step 2.

How do we check if the model is good?

We run EVALUATE_MODEL function (step 3) which returns metrics like accuracy or error to understand model performance.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after step 2?

A Model 'my_model' created and trained

B Data selected for training

C Predictions generated for new data

D Evaluation metrics returned

💡 Hint

Check the 'Result' column in execution_table row with Step 2

At which step do we generate predictions for new data?

A Step 1

B Step 3

C Step 4

D Step 2

💡 Hint

Look at the 'Action' and 'Result' columns in execution_table for prediction generation

If we change the model type to 'decision_tree', which step changes?

A Step 1 - Prepare Data

B Step 2 - Create and Train Model

C Step 3 - Evaluate Model

D Step 4 - Use Model for Prediction

💡 Hint

Model type is specified in the CREATE MODEL command in Step 2

Concept Snapshot

ML Model Training in Snowflake:
- Use CREATE MODEL with SELECT to define training data
- Specify model_type in OPTIONS
- Evaluate model with EVALUATE_MODEL function
- Use PREDICT function for new data
- CREATE OR REPLACE MODEL updates existing model

Full Transcript

This visual execution shows how to train a machine learning model in Snowflake. First, data is prepared by selecting features and target columns. Then, the CREATE OR REPLACE MODEL command creates and trains the model using that data. After training, the model is evaluated using the EVALUATE_MODEL function to get performance metrics. Finally, the trained model is used to generate predictions on new data with the PREDICT function. Variables like training_data and my_model change state through these steps, reflecting data selection, model creation, evaluation, and prediction. Key points include understanding the SELECT inside CREATE MODEL, replacing existing models, and checking model quality with evaluation. The quiz tests understanding of these steps and their results.