Snowflake SnowPro Advanced: Data Scientist Certification Sample Questions:

1. You're tasked with building an image classification model on Snowflake to identify defective components on a manufacturing assembly line using images captured by high-resolution cameras. The images are stored in a Snowflake table named 'ASSEMBLY LINE IMAGES', with columns including 'image_id' (INT), 'image_data' (VARIANT containing binary image data), and 'timestamp' (TIMESTAMP NTZ). You have a pre-trained image classification model (TensorFlow/PyTorch) saved in Snowflake's internal stage. To improve inference speed and reduce data transfer overhead, which approach provides the MOST efficient way to classify these images using Snowpark Python and UDFs?

A) Use Snowflake's external function feature to offload the image classification task to a serverless function hosted on AWS Lambda, passing the and 'image_icf to the function for processing.
B) Create a Python UDF that takes a single 'image_id' as input, retrieves the corresponding 'image_data' from the table, preprocesses the image, loads the pre-trained model, performs classification, and returns the result. This UDF will be called for each image individually.
C) Create a Python UDF that loads the entire table into memory, preprocesses the images, loads the pre-trained model, and performs classification for all images in a single execution.
D) Create a vectorized Python UDF that takes a batch of 'image_id' values as input, retrieves the corresponding 'image_data' from the 'ASSEMBLY LINE IMAGES table using a JOIN, preprocesses the images in a vectorized manner, loads the pre-trained model once at the beginning, performs classification on the batch, and returns the results.
E) Create a Java UDF that loads the pre-trained model and preprocesses the images. Call this Java UDF from a Python UDF to perform the image classification. Since Java is faster than Python, this will optimize performance.

2. You are building a fraud detection model for an e-commerce platform. One of the features is 'purchase_amount', which ranges from $1 to $10,000. The data has a skewed distribution with many small purchases and a few very large ones. You need to normalize this feature for your model, which uses gradient descent. Which normalization technique(s) would be most suitable in Snowflake, considering the data characteristics and the need to handle potential future outliers?

A) Power Transformer (e.g., Yeo-Johnson) implemented with Snowpark Python:

B) Robust scaling using interquartile range (IQR) in a stored procedure with Python:

C) Unit Vector normalization (L2 Normalization) using SQL:

D) Min-Max scaling using the following SQL:

E) Z-score standardization using the following SQL:

3. You are using a Snowflake Notebook to build a churn prediction model. You have engineered several features, and now you want to visualize the relationship between two key features: and , segmented by the target variable 'churned' (boolean). Your goal is to create an interactive scatter plot that allows you to explore the data points and identify any potential patterns.
Which of the following approaches is most appropriate and efficient for creating this visualization within a Snowflake Notebook?

A) Leverage Snowflake's native support for Streamlit within the notebook to create an interactive application. Query the data directly from Snowflake within the Streamlit app and use Streamlit's plotting capabilities for visualization.
B) Use the Snowflake Connector for Python to fetch the data, then leverage a Python visualization library like Plotly or Bokeh to generate an interactive plot within the notebook.
C) Create a static scatter plot using Matplotlib directly within the Snowflake Notebook by converting the data to a Pandas DataFrame. This involves pulling all relevant data into the notebook's environment before plotting.
D) Write a stored procedure in Snowflake that generates the visualization data in a specific format (e.g., JSON) and then use a JavaScript library within the notebook to render the visualization.
E) Use the 'snowflake-connector-python' to pull the data and use 'seaborn' to create static plots.

4. You are building a multi-class classification model in Snowflake to predict the category of customer support tickets (e.g., 'Billing', 'Technical Support', 'Sales Inquiry', 'Account Management', 'Feature Request') based on the ticket's text content. The initial model evaluation shows an overall accuracy of 75%, but the 'Feature Request' category has a significantly lower precision and recall compared to other categories. Which of the following strategies would be MOST effective in addressing this issue, considering the limitations and advantages of Snowflake's data processing capabilities and typical machine learning practices?

A) Engineer new features specifically designed to improve the model's ability to distinguish 'Feature Request' tickets from other categories. This could involve creating sentiment scores for 'innovation' or using topic modeling to identify key themes related to feature requests.
B) Oversample the 'Feature Request' category in the training dataset before training the model. This involves creating synthetic data points or duplicating existing data to balance the class distribution. This can be done using SQL and Snowflake's internal stage for storing temporary data before training.
C) All of the above.
D) Apply a cost-sensitive learning approach during model training, assigning a higher misclassification cost to errors involving the 'Feature Request' category. This encourages the model to prioritize correctly classifying feature requests.
E) Increase the threshold for classifying a ticket as 'Feature Request' to improve precision, even if it further reduces recall. This prioritizes accurate identification of feature requests over capturing all of them.

5. 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 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);'
B) 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.
C) 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.
D) 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.
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.

Solutions: