Snowflake SnowPro Advanced: Data Scientist Certification Sample Questions:

1. You are performing exploratory data analysis on a large sales dataset in Snowflake using Snowpark. The dataset contains columns such as 'order_id', , and 'profit'. You want to identify the top 5 most profitable products for each month. You have already created a Snowpark DataFrame named 'sales_df. Which of the following Snowpark operations, when combined correctly, will efficiently achieve this?

A) Use 'rank()' partitioned by ordered by 'sum(profit) DESC' , after grouping by and 'product_id' , and aggregating 'sum(profity.
B) Group by and 'product_id' , aggregate 'sum(profit)' , then use partitioned by ordered by 'sum(profit) DESC'.
C) First, create a temporary table with aggregated monthly profit for each product using SQL. Then, use Snowpark to read the temporary table and apply a window function partitioned by ordered by 'sum(profit) DESC'.
D) Use 'ntile(5)' partitioned by ordered by 'sum(profit) DESC' after grouping by and 'product_id', and aggregating 'sum(profit)'.
E) Group by 'product_id', aggregate 'sum(profity, then use partitioned by ordered by 'sum(profit) DESC' within a UDF.

2. Consider the following Python UDF intended to train a simple linear regression model using scikit-learn within Snowflake. The UDF takes feature columns and a target column as input and returns the model's coefficients and intercept as a JSON string. You are encountering an error during the CREATE OR REPLACE FUNCTION statement because of the incorrect deployment of the package during runtime. What would be the right way to fix this deployment and execute your model?

A) The package 'scikit-learn' needs to be included in the import statement and deployed while creation of the 'Create or Replace function' statement, by including parameter. Also the correct code is to ensure the model can be trained and return the coefficients and intercept of the model.
B) The package 'scikit-learn' needs to be included in the import statement and deployed while creation of the 'Create or Replace function' statement, by including parameter. Also the correct code is to ensure the model can be trained and return the coefficients and intercept of the model.
C) The package 'scikit-learn' needs to be included in the import statement and deployed while creation of the 'Create or Replace function' statement, by including parameter. Also the correct code is to ensure the model can be trained and return the coefficients and intercept of the model.
D) The code works seamlessly without modification as Snowflake automatically resolves all the dependencies and ensures the execution of code within the create or replace function statement.
E) The required packages 'scikit-learn' is not present. The correct way to create UDF is by including the import statement within the function along with the deployment.

3. You have a table 'PRODUCT SALES in Snowflake with columns: 'PRODUCT (INT), 'SALE_DATE (DATE), 'SALES_AMOUNT (FLOAT), and 'PROMOTION FLAG' (BOOLEAN). You need to perform the following data preparation steps using Snowpark SQLAPI:

A) Handling missing 'SALES_AMOUNT values by imputing them with the average 'SALES_AMOUNT' for the same 'PRODUCT_ID during the previous month. If there's no data for the previous month, use the overall average for that
B) Creating a new feature representing the percentage change in 'SALES_AMOUNT compared to the previous day for the same 'PRODUCT_ID. Handle the first day of each 'PRODUCT by setting 'SALES_GROWTH' to O.
C) All of the above.
D) Creating a feature that returns 1 if there is a PROMOTION_FLAG of True and SALES_AMOUNT > 1000, and zero otherwise
E) Converting 'SALE_DATE to a quarterly representation (e.g., '2023-QI').

4. You have developed a customer churn prediction model using Python and deployed it as a Snowflake UDE You are monitoring its performance and notice a significant drop in accuracy over time. To address this, you need to implement automated model retraining with regular validation. Which of the following steps and validation techniques are MOST critical for ensuring the retrained model is effective and avoids overfitting to recent data? (Select THREE)

A) Update the UDF in place using 'CREATE OR REPLACE FUNCTION' immediately after retraining completes, regardless of the validation results.
B) Retrain the model using the entire available dataset, as this will maximize the amount of data the model learns from.
C) Monitor the model's performance on a live dataset and trigger retraining only when the performance drops below a predefined threshold, using metrics like accuracy, precision, or recall. Save Model Performance to 'MODEL_PERFORMANCE.
D) Implement a data drift detection mechanism. Monitor the distribution of input features over time and trigger retraining if significant drift is detected using tools such as Snowflake's Anomaly Detection features or custom drift metrics calculated in SQL.
E) Use cross-validation techniques (e.g., k-fold cross-validation) during the retraining process to estimate the model's performance on unseen data and prevent overfitting. Evaluate on a held-out validation set.

5. You are building a customer support chatbot using Snowflake Cortex and a large language model (LLM). You want to use prompt engineering to improve the chatbot's ability to answer complex questions about product features. You have a table PRODUCT DETAILS with columns 'feature_name', Which of the following prompts, when used with the COMPLETE function in Snowflake Cortex, is MOST likely to yield the best results for answering user questions about specific product features, assuming you are aiming for concise and accurate responses focused solely on providing the requested feature description and avoiding extraneous chatbot-like conversation?

A) Option E
B) Option D
C) Option C
D) Option A
E) Option B

Solutions: