Snowflake SnowPro Advanced: Data Scientist Certification - DSA-C03

You are training a regression model to predict house prices using a Snowflake dataset. The dataset contains various features, including 'number of_bedrooms', , and You want to use time-based partitioning for your training, validation, and holdout sets. However, you also need to ensure that the dataset is properly shuffled within each time partition to mitigate potential bias introduced by the order of data entry. Which of the following strategies is MOST EFFECTIVE and EFFICIENT for partitioning your data into train, validation, and holdout sets in Snowflake, while also ensuring random shuffling within each partition, and addressing potential data leakage issues?

• A. Create a user-defined function (UDF) in Python that takes a 'sale_date' as input and returns either 'train', 'validation', or 'holdout' based on pre-defined date ranges. Apply this UDF to each row, creating a 'split_group' column. Then, create temporary tables for each split using 'CREATE TABLE AS SELECT ... FROM . WHERE split_group = ... ORDER BY RANDOM()'. UDF overhead and global RANDOM sort make it very slow.
• B. Create a new column 'split_group' using a CASE statement based on 'sale_date' to assign each row to 'train', 'validation', or 'holdout'. Calculate a random number within each 'split_group' by using OVER (PARTITION BY split_group ORDER BY RANDOM())'. Then create temporary tables for each split using 'CREATE TABLE AS SELECT FROM WHERE split_group = QUALIFY ROW NUMBER() OVER (ORDER BY RANDOM()) (SELECT COUNT( ) FROM transactions WHERE split_group -- ...) (respective split percentage);'
• C. Create separate views for train, validation, and holdout sets, filtering by 'sale_date' . Shuffle the entire dataset using 'ORDER BY RANDOM()' before creating the views to ensure randomness across all sets. This does not address shuffling within parition.
• D. Create a new column 'split_group' using a CASE statement based on 'sale_date' to assign each row to 'train', 'validation', or 'holdout'. Then, create temporary tables for each split using 'CREATE TABLE AS SELECT FROM WHERE split_group = ORDER BY RANDOM()'. This can be very slow because of global RANDOM sort and leakage issues with using full dataset for randomness.
• E. Use Snowflake's SAMPLE clause with a 'REPEATABLE seed for each split (train, validation, holdout), filtering by 'sale_date'. Add an 'ORDER BY RANDOM()' clause within each 'SAMPLE query to shuffle the data within each split. This approach does not guarantee non-overlapping sets and can introduce sampling bias.

You are developing a regression model in Snowflake to predict housing prices. You've trained a model using Snowflake ML functions and now need to rigorously validate its performance. You have a separate validation dataset stored in a table named 'HOUSING VALIDATION'. Which of the following SQL statements, when executed in Snowflake, would accurately calculate the Root Mean Squared Error (RMSE) of your model's predictions against the actual prices in the validation dataset, assuming your model is named 'HOUSING PRICE MODEL' and the prediction function generated by CREATE SNOWFLAKE.ML.FORECAST is called PREDICT?

• A. Option C
• B. Option B
• C. Option A
• D. Option D
• E. Option E

You've built a customer churn prediction model in Snowflake, and are using the AUC as your primary performance metric. You notice that your model consistently performs well (AUC > 0.85) on your validation set but significantly worse (AUC < 0.7) in production. What are the possible reasons for this discrepancy? (Select all that apply)

• A. Your training and validation sets are not representative of the real-world production data due to sampling bias.
• B. There's a temporal bias: the customer behavior patterns have changed since the training data was collected.
• C. Your model is overfitting to the validation data. This causes to give high performance on validation set but less accurate in the real world.
• D. The AUC metric is inherently unreliable and should not be used for model evaluation.
• E. The production environment has significantly more missing data compared to the training and validation environments.

You've built a model in Snowflake to predict the likelihood of a customer clicking on an advertisement. The model outputs a probability score between 0 and 1. You want to determine the optimal threshold to use for converting these probabilities into binary predictions (click/no-click). Your business stakeholders have provided the following information: Cost of showing an ad: $0.10; Revenue generated from a click: $1.00; You have access to a table 'AD_PREDICTIONS' with columns 'CUSTOMER_ID', 'PREDICTED_PROBABILITY' , and 'ACTUAL CLICK' (1 for click, 0 for no click). Which of the following approaches would be the MOST appropriate for selecting the optimal probability threshold to maximize profit, and why?

• A. Iterate through a range of probability thresholds (e.g., 0.01 to 0.99), and for each threshold, calculate the profit using SQL in Snowflake: 'SELECT SUM(CASE WHEN PREDICTED PROBABILITY threshold THEN CASE WHEN ACTUAL CLICK = 1 THEN 0.9 ELSE -0.1 END ELSE O END) AS Profit FROM AD_PREDICTIONS;' Choose the threshold that maximizes the profit.
• B. Calculate the point on the ROC curve closest to the top-left corner (perfect classification) and use the corresponding threshold. This optimizes for both sensitivity and specificity.
• C. Use the precision-recall curve to find the threshold that maximizes the F1 -score, balancing precision and recall.
• D. Select a threshold of 0.5, as this is a common default threshold for binary classification problems.
• E. Select a very high probability threshold (e.g., 0.9) to ensure that only the most likely clicks are targeted, minimizing wasted ad spend.

You are validating a time series forecasting model for daily sales using Snowflake and Snowpark. The residuals plot shows a clear sinusoidal pattern. Which of the following actions should you consider to improve your model? (Select all that apply)

• A. Incorporate lagged features representing previous sales values (e.g., sales from the previous day, week, or month).
• B. Apply a Box-Cox transformation to the target variable (sales) to stabilize the variance.
• C. Increase the regularization strength in your model.
• D. Change the algorithm to a linear regression model, since it is more likely to capture sinusoidal patterns
• E. Remove outlier data points to improve overall model performance.