ML model training in Snowflake - Mini Project: Build & Apply

ML Model Training in Snowflake

📖 Scenario: You work as a data engineer at a retail company. Your team wants to train a simple machine learning model inside Snowflake to predict customer churn based on historical data. You will create a table with customer data, configure model parameters, train the model using Snowflake's ML capabilities, and finalize the model for use.

🎯 Goal: Build a Snowflake ML model training pipeline step-by-step. You will create a customer data table, set model configuration, train a logistic regression model, and finalize the model for prediction.

📋 What You'll Learn

Create a table named customer_data with specific columns and data

Add a configuration variable model_params with logistic regression settings

Train a model named churn_model using Snowflake's CREATE MODEL syntax

Finalize the model by granting usage rights to a role

💡 Why This Matters

🌍 Real World

Training ML models directly inside Snowflake helps data teams avoid moving data out, speeding up workflows and improving security.

💼 Career

Data engineers and data scientists often build and manage ML models in cloud data platforms like Snowflake to support business decisions.

1

Create the customer data table

Create a table called customer_data with columns customer_id (integer), age (integer), monthly_spend (float), and churned (boolean). Insert these exact rows: (1, 25, 50.5, false), (2, 40, 80.0, true), (3, 30, 65.0, false).

Snowflake

-- Create the customer_data table and insert data
-- Your code here

Need a hint?

Use CREATE OR REPLACE TABLE to define the table and INSERT INTO to add rows.

2

Set model configuration parameters

Create a variable called model_params as a string containing JSON with these exact key-value pairs: "model_type": "logistic_regression", "max_iterations": 100, and "regularization": 0.1.

Snowflake

CREATE OR REPLACE TABLE customer_data (
  customer_id INT,
  age INT,
  monthly_spend FLOAT,
  churned BOOLEAN
);

INSERT INTO customer_data VALUES
  (1, 25, 50.5, FALSE),
  (2, 40, 80.0, TRUE),
  (3, 30, 65.0, FALSE);

-- Create model_params variable with JSON string
-- Your code here

Need a hint?

Use SET to create a session variable with a JSON string.

3

Train the logistic regression model

Use CREATE OR REPLACE MODEL churn_model to train a model on customer_data. Use churned as the label column. Use age and monthly_spend as input features. Include the model parameters from model_params in the WITH clause.

Snowflake

CREATE OR REPLACE TABLE customer_data (
  customer_id INT,
  age INT,
  monthly_spend FLOAT,
  churned BOOLEAN
);

INSERT INTO customer_data VALUES
  (1, 25, 50.5, FALSE),
  (2, 40, 80.0, TRUE),
  (3, 30, 65.0, FALSE);

SET model_params = '{"model_type": "logistic_regression", "max_iterations": 100, "regularization": 0.1}';

-- Create and train churn_model using customer_data
-- Your code here

Need a hint?

Use CREATE OR REPLACE MODEL with OPTIONS and a SELECT query for training.

4

Finalize the model by granting usage rights

Grant USAGE privilege on the model churn_model to the role DATA_SCIENTIST_ROLE so others can use it for predictions.

Snowflake

CREATE OR REPLACE TABLE customer_data (
  customer_id INT,
  age INT,
  monthly_spend FLOAT,
  churned BOOLEAN
);

INSERT INTO customer_data VALUES
  (1, 25, 50.5, FALSE),
  (2, 40, 80.0, TRUE),
  (3, 30, 65.0, FALSE);

SET model_params = '{"model_type": "logistic_regression", "max_iterations": 100, "regularization": 0.1}';

CREATE OR REPLACE MODEL churn_model
  OPTIONS (
    model_type = 'logistic_regression',
    max_iterations = 100,
    regularization = 0.1
  ) AS
SELECT age, monthly_spend, churned AS label
FROM customer_data;

-- Grant USAGE privilege on churn_model to DATA_SCIENTIST_ROLE
-- Your code here

Need a hint?

Use GRANT USAGE ON MODEL to allow the role to use the model.