ML model training in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of Snowflake ML model training command

What is the output of the following Snowflake SQL command that trains a linear regression model?

Snowflake

CREATE OR REPLACE MODEL my_linear_model
  OPTIONS (model_type = 'linear_regression', input_label_cols = ('price')) AS
  SELECT sqft, bedrooms, bathrooms, price FROM housing_data;

A Model my_linear_model created successfully with linear_regression type.

B Model training failed due to missing input_label_cols option.

C Error: Model type linear_regression not supported in Snowflake.

D Syntax error: Missing FROM clause in SELECT statement.

💡 Hint

Check the syntax for CREATE MODEL in Snowflake and required options.

The command correctly creates a linear regression model named my_linear_model using the specified columns. Snowflake supports linear_regression as a model_type and requires input_label_cols to specify the target column.

❓ Model Choice

intermediate

Choosing the correct model type for classification in Snowflake

You want to train a model in Snowflake to predict if a customer will churn (yes/no). Which model_type option should you use in the CREATE MODEL statement?

A 'time_series_forecast'

B 'kmeans'

C 'linear_regression'

D 'logistic_regression'

💡 Hint

Classification problems with yes/no targets usually use logistic regression.

Logistic regression is used for binary classification tasks like predicting churn (yes/no). Linear regression is for continuous targets, kmeans is for clustering, and time_series_forecast is for forecasting time-based data.

❓ Hyperparameter

advanced

Effect of changing max_iterations in Snowflake model training

In Snowflake's CREATE MODEL statement, what is the effect of increasing the max_iterations option for a logistic regression model?

A It decreases the training time by limiting the number of features used.

B It increases the number of training cycles, potentially improving model accuracy but increasing training time.

C It changes the learning rate to speed up convergence.

D It automatically selects the best regularization parameter.

💡 Hint

max_iterations controls how many times the algorithm runs through the data.

max_iterations sets the maximum number of optimization steps during training. Increasing it allows the model more chances to improve but takes longer.

❓ Metrics

advanced

Interpreting model evaluation metrics in Snowflake

After training a binary classification model in Snowflake, you get these metrics: accuracy = 0.85, precision = 0.75, recall = 0.60. What does the recall value tell you about the model?

A The model correctly identifies 60% of all actual positive cases.

B The model correctly predicts 75% of its positive predictions.

C The model is 85% accurate overall on the test data.

D The model has 60% accuracy on negative cases.

💡 Hint

Recall measures how many actual positives were found.

Recall is the ratio of true positives found over all actual positives. A recall of 0.60 means the model finds 60% of the positive cases.

🔧 Debug

expert

Debugging Snowflake model training failure due to data type mismatch

You run this command to train a model but get an error: 'Data type mismatch for input_label_cols'. What is the likely cause? CREATE OR REPLACE MODEL churn_model OPTIONS (model_type = 'logistic_regression', input_label_cols = ('churn')) AS SELECT customer_id, churn FROM customer_data;

A The input_label_cols option should be a list of multiple columns.

B The model_type 'logistic_regression' is not supported for this data.

C The 'churn' column is not numeric or boolean, but a string type.

D The SELECT statement is missing required feature columns besides customer_id and churn.

💡 Hint

Check the data type of the label column used for training.

Snowflake requires the label column for classification to be numeric or boolean. If 'churn' is stored as text (e.g., 'yes'/'no'), it causes a data type mismatch error.

Practice

1. What is the main purpose of the CREATE MODEL statement in Snowflake's ML feature?

easy

A. To delete an existing machine learning model

B. To export model results to an external file

C. To make predictions using a trained model

D. To define and train a machine learning model using SQL

Solution

1. Step 1: Understand the role of CREATE MODEL

   This statement is used to define the model type, label column, and features, which means it sets up and trains the model.

2. Step 2: Differentiate from other commands

   PREDICT is used for predictions, not CREATE MODEL. Deleting or exporting models uses other commands.

3. Final Answer:

   To define and train a machine learning model using SQL -> Option D

4. Quick Check:

   CREATE MODEL = train model [OK]

Hint: Remember: CREATE MODEL means build and train [OK]

Common Mistakes:

• Confusing CREATE MODEL with PREDICT
• Thinking CREATE MODEL deletes models
• Assuming CREATE MODEL exports data

2. Which of the following is the correct syntax to create a logistic regression model named my_model in Snowflake?

easy

A. CREATE MODEL my_model AS logistic_regression WITH LABEL target AND FEATURES age, income;

B. CREATE MODEL my_model TYPE = 'logistic_regression' LABEL = target FEATURES = (age, income);

C. CREATE MODEL my_model USING logistic_regression LABEL target FEATURES (age, income);

D. CREATE MODEL my_model TYPE logistic_regression LABEL target FEATURES (age, income);

Solution

1. Step 1: Recall Snowflake's CREATE MODEL syntax

   The correct syntax uses TYPE = 'model_type', LABEL = label_column, and FEATURES = (feature_list) with equal signs and parentheses.

2. Step 2: Compare options

   CREATE MODEL my_model TYPE = 'logistic_regression' LABEL = target FEATURES = (age, income); matches the correct syntax exactly. Others have wrong keywords or missing equal signs or parentheses.

3. Final Answer:

   CREATE MODEL my_model TYPE = 'logistic_regression' LABEL = target FEATURES = (age, income); -> Option B

4. Quick Check:

   Correct syntax uses TYPE =, LABEL =, FEATURES = ( ) [OK]

Hint: Look for equal signs and parentheses in syntax [OK]

Common Mistakes:

• Omitting equal signs (=) in parameters
• Missing parentheses around features
• Using wrong keywords like AS or WITH

3. Given the following Snowflake SQL code:

CREATE MODEL churn_model TYPE = 'random_forest' LABEL = churn FEATURES = (age, balance, transactions);
SELECT * FROM PREDICT(churn_model, (SELECT age, balance, transactions FROM customers_test));

What will be the output of the PREDICT statement?

medium

A. The original customers_test table without predictions

B. An error because PREDICT cannot use SELECT statements

C. A table with predicted churn values for each row in customers_test

D. A summary report of model accuracy

Solution

1. Step 1: Understand the PREDICT statement

   PREDICT applies the trained model to new data and returns predicted values for each input row.

2. Step 2: Analyze the code

   The SELECT statement provides features from customers_test. PREDICT will output predictions for each row based on these features.

3. Final Answer:

   A table with predicted churn values for each row in customers_test -> Option C

4. Quick Check:

   PREDICT outputs predictions per input row [OK]

Hint: PREDICT returns predictions for each input row [OK]

Common Mistakes:

• Thinking PREDICT returns original data only
• Assuming PREDICT gives accuracy metrics
• Believing PREDICT cannot use SELECT

4. You run this Snowflake command:

CREATE MODEL sales_model TYPE = 'linear_regression' LABEL = sales_amount FEATURES = (store_size, location);

But get an error saying "Invalid feature column: location". What is the most likely cause?

medium

A. The column 'location' is not numeric or not suitable as a feature

B. The LABEL column 'sales_amount' does not exist

C. The model type 'linear_regression' is not supported

D. The FEATURES list must be empty for linear regression

Solution

1. Step 1: Understand feature requirements

   Features must be numeric or convertible to numeric for linear regression models.

2. Step 2: Analyze the error message

   "Invalid feature column" suggests 'location' is likely categorical or text, which linear regression cannot use directly.

3. Final Answer:

   The column 'location' is not numeric or not suitable as a feature -> Option A

4. Quick Check:

   Features must be numeric for linear regression [OK]

Hint: Check feature columns are numeric for regression models [OK]

Common Mistakes:

• Assuming LABEL column causes this error
• Thinking model type is unsupported
• Believing features list can be empty

5. You want to train a classification model in Snowflake to predict customer churn. You have a dataset with columns: customer_id, age, monthly_spend, churned (1 if churned, 0 otherwise), and signup_date. Which CREATE MODEL statement correctly trains a model ignoring customer_id and signup_date?

hard

A. CREATE MODEL churn_model TYPE = 'logistic_regression' LABEL = churned FEATURES = (age, monthly_spend);

B. CREATE MODEL churn_model TYPE = 'logistic_regression' LABEL = customer_id FEATURES = (age, monthly_spend, churned);

C. CREATE MODEL churn_model TYPE = 'logistic_regression' LABEL = churned FEATURES = (customer_id, age, monthly_spend, signup_date);

D. CREATE MODEL churn_model TYPE = 'linear_regression' LABEL = churned FEATURES = (age, monthly_spend);

Solution

1. Step 1: Identify correct label and features

   The label is churned because it indicates churn. Features should exclude customer_id and signup_date as they are identifiers or dates not useful for prediction.

2. Step 2: Choose appropriate model type

   Since churn is a binary outcome, logistic regression is suitable, not linear regression.

3. Final Answer:

   CREATE MODEL churn_model TYPE = 'logistic_regression' LABEL = churned FEATURES = (age, monthly_spend); -> Option A

4. Quick Check:

   Label = churned, features exclude IDs/dates, logistic regression for classification [OK]

Hint: Use label as target, exclude IDs/dates from features [OK]

Common Mistakes:

• Using ID or date columns as features
• Choosing wrong label column
• Using linear regression for classification