Snowflake SnowPro Advanced: Data Scientist Certification Sample Questions:

1. 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 B
D) Option C
E) Option A

2. You are analyzing customer churn for a telecommunications company. You have a Snowflake table called 'CUSTOMER ACTIVITY with columns 'CUSTOMER ID', 'CALL DURATION_SUM' (total call duration in minutes), 'DATA USAGE GB' (total data usage in GB), 'CONTRACT LENGTH MONTHS', and 'CHURNED' (boolean indicating whether the customer churned). You want to understand the relationship between these features and churn. Specifically, you want to visualize the distribution of 'CALL DURATION SUM' for churned and non-churned customers. Which of the following visualizations, combined with appropriate Snowflake SQL to prepare the data, would BEST illustrate the relationship between 'CALL DURATION SUM' and 'CHURNED'?

A) A scatter plot with on the x-axis and 'CHURNED' (0 or 1) on the y-axis, generated directly from the table using an external visualization tool connected to Snowflake.
B) A line chart plotting the average 'CALL DURATION SUM' over time, ignoring the 'CHURNED' status.
C) A pie chart showing the percentage of churned and non-churned customers, with no consideration of 'CALL DURATION SUM'
D) A box plot with 'CHURNED on the x-axis and "CALL DURATION SUM' on the y-axis, generated using an external visualization tool connected to Snowflake, after preparing the data using a CTE (Common Table Expression) in Snowflake to categorize customers by churn status.
E) A histogram of 'CALL DURATION SUM" for churned customers and a separate histogram of "CALL DURATION SUM' for non-churned customers, generated using an external visualization tool connected to Snowflake, after preparing the data using a CTE (Common Table Expression) in Snowflake to categorize customers by churn status.

3. A data scientist is performing exploratory data analysis on a table named 'CUSTOMER TRANSACTIONS. They need to calculate the standard deviation of transaction amounts C TRANSACTION AMOUNT) for different customer segments CCUSTOMER SEGMENT). The 'CUSTOMER SEGMENT column can contain NULL values. Which of the following SQL statements will correctly compute the standard deviation, excluding NULL transaction amounts, and handling NULL customer segments by treating them as a separate segment called 'Unknown'? Consider using Snowflake-specific functions where appropriate.

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

4. You are tasked with fine-tuning a Snowflake Cortex LLM model using your own labeled dataset to improve its performance on a specific sentiment analysis task related to customer reviews. You have already created a Snowflake stage 'my_stage' and uploaded your labeled data in CSV format to this stage. The labeled data contains two columns: 'review_text' and 'sentiment' (values: 'positive', 'negative', 'neutral'). Which of the following SQL commands, or sequences of commands, is MOST appropriate to initiate the fine-tuning process using the 'SNOWFLAKE.ML.FINETUNE LLM' function? Assume you have already set the necessary permissions for your role to access the model and stage.

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

5. A marketing team uses Snowflake to store customer purchase data'. They want to segment customers based on their spending habits using a derived feature called The 'PURCHASES' table has columns 'customer id' (IN T), 'purchase_date' (DATE), and 'purchase_amount' (NUMBER). The team needs a way to handle situations where a customer might have missing months (no purchases in a particular month). They want to impute a 0 spend for those months before calculating the average. Which approach provides the most accurate and robust calculation, especially when considering users with sparse purchase history?

A) Calculate the average spend only for customers with purchases in every month of the year. Ignore other customers in the analysis.
B) Use a window function to calculate the average spend over a fixed window of the last 3 months, ignoring missing months in the calculation.
C) Calculate the total spend for each customer and divide by the number of months since their first purchase: / DATEDlFF(month, CURRENT DATE()) GROUP BY customer_id'.
D) Create a view containing all months for each customer, left join with the 'PURCHASES' table, impute 0 for null 'purchase_amounts values, and then calculate the average spend. Requires creating a helper table for all the month.
E) Calculate the average monthly spend directly from the 'PURCHASES' table without accounting for missing months: 'AVG(purchase_amount) GROUP BY customer_id, date_trunc('month',

Solutions: