Snowflake DSA-C03 Value Pack (Frequently Bought Together)

You are building an automated model retraining pipeline for a sales forecasting model in Snowflake using Snowflake Tasks and Stored Procedures. After retraining, you want to validate the new model against a champion model already deployed. You need to define a validation strategy using the following models: champion model deployed as UDF "FORECAST UDF , and contender model deployed as UDF 'FORECAST UDF NEW'. Given the following objectives: (1) Minimal impact on production latency, (2) Ability to compare predictions on a large volume of real-time data, (3) A statistically sound comparison metric. Which of the following SQL statements best represents how to efficiently compare the forecasts of the two models on a sample dataset and calculate the Root Mean Squared Error (RMSE) to validate the new model?

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

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. 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.
• C. 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);'
• 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. 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.

You've deployed a fraud detection model in Snowflake. The model is implemented as a Python UDF that uses a pre-trained scikit-learn model stored as a stage file. Your goal is to enable near real-time fraud detection on incoming transactions. Due to regulatory requirements, you need to maintain a detailed audit trail of all predictions, including the input features, model version, prediction scores, and any errors encountered during the prediction process. Which of the following approaches are valid and efficient for storing these audit logs and predictions in Snowflake?

• A. Store the audit logs as unstructured text files in an external stage (e.g., AWS S3) and periodically load them into a Snowflake table using COPY INTO command.
• B. Use Snowflake's 'SYSTEM$QUERY LOG' table to extract information about the UDF execution and join it with the transaction data to reconstruct the audit trail.
• C. Log the audit information to an external logging service (e.g., Splunk) using an external function called from within the UDF.
• D. Create a dedicated table with columns for transaction ID, input features (as a JSON VARIANT), model version, prediction score, error message (if any), and prediction timestamp. Use a Snowflake Sequence to generate unique log IDs.
• E. Utilize Snowflake's Streams and Tasks to automatically capture changes to the transaction table and trigger the prediction UDF, storing the audit logs in a separate table with similar structure as described in option A.

You're building a model to predict whether a user will click on an ad (binary classification: click or no-click) using Snowflake. The data is structured and includes features like user demographics, ad characteristics, and past user interactions. You've trained a logistic regression model using SNOWFLAKE.ML and are now evaluating its performance. You notice that while the overall accuracy is high (around 95%), the model performs poorly at predicting clicks (low recall for the 'click' class). Which of the following steps could you take to diagnose the issue and improve the model's ability to predict clicks, and how would you implement them using Snowflake SQL? SELECT ALL THAT APPLY.

• A. Reduce the amount of training data to avoid overfitting. Overfitting is known to produce low recall for the 'click' class.
• B. Increase the complexity of the model by switching to a non-linear algorithm like Random Forest or Gradient Boosting without performing hyperparameter tuning, as more complex models always perform better.
• C. Calculate precision, recall, F I-score, and AUC for the 'click' class using SQL queries to get a more detailed understanding of the model's performance on the minority class. Example:
  
• D. Generate a confusion matrix using SQL to visualize the model's performance across both classes. Example SQL:
  
• E. Implement feature engineering by creating interaction terms or polynomial features from existing features using SQL, to capture potentially non-linear relationships between features and the target variable. Example:
  

You are working with a Snowflake table 'CUSTOMER TRANSACTIONS containing customer IDs, transaction dates, and transaction amounts. You need to identify customers who are likely to churn (stop making transactions) in the next month using a supervised learning model. Which of the following strategies would be MOST appropriate to define the target variable (churned vs. not churned) and create features for this churn prediction problem, suitable for a Snowflake-based machine learning pipeline?

• A. Define churn based on a fixed threshold of total transaction value over a predefined period. Feature Engineering should purely consist of time series decomposition using Snowflake's built-in functions.
• B. Define churn as customers with zero transactions in the last month. Create features like average transaction amount over the past year, number of transactions in the past month, and recency (time since the last transaction).
• C. Define churn as customers with no transactions in the next month (the prediction target). Create features including: Recency (days since last transaction), Frequency (number of transactions in the past 3 months), Monetary Value (average transaction amount over the past 3 months), and trend of transaction amounts (using linear regression slope over the past 6 months).
• D. Define churn as customers with a significant decrease (e.g., 50%) in transaction amounts compared to the previous month. Create features based on demographic data and customer segmentation information, joined from other Snowflake tables.
• E. Define churn as customers who haven't made a transaction in the past 6 months. Create a single feature representing the total number of transactions the customer has ever made.