Snowflake SnowPro Advanced: Data Scientist Certification Sample Questions:

1. You have a Snowflake table called 'website visits' with columns 'user id', 'visit_date', and You need to identify users who consistently spend a large amount of time on specific page URLs. You want to calculate the average time spent per user on each page URL and then find the top 10 page URLs where users, on average, spend the most time. Which of the following approaches is the MOST efficient and accurate for achieving this in Snowflake?

A)

B)

C)

D)

E)

2. 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) Group by and 'product_id' , aggregate 'sum(profit)' , then use partitioned by ordered by 'sum(profit) DESC'.
B) Use 'rank()' partitioned by ordered by 'sum(profit) DESC' , after grouping by and 'product_id' , and aggregating 'sum(profity.
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.

3. A financial institution is analyzing transaction data in Snowflake to detect fraudulent activity. They have a 'Transaction_Amount' column. They want to binarize this feature, creating a new 'ls_High_Value' column. Transactions with amounts greater than $1000 should be marked as 1 (High Value), and all other transactions (including NULLs) should be marked as 0. Which of the following SQL statements would be the MOST efficient and correct way to achieve this in Snowflake?

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

4. You are working with a Snowflake table named 'sensor readingS containing IoT sensor data'. The table has columns 'sensor id' , 'timestamp' , and 'reading value'. You observe that the 'reading value' column contains a significant number of missing values (represented as NULL). To prepare this data for a time series analysis, you need to impute these missing values. You have decided to use the 'LOCF' (Last Observation Carried Forward) method, filling the NULL values with the most recent non-NULL value for each sensor. In addition to LOCF, you also want to handle the scenario where a sensor has NULL values at the beginning of its data stream (i.e., no previous observation to carry forward). For these initial NULLs, you want to use a fixed default value of 0. Which of the following approaches, using either Snowpark for Python or a combination of Snowpark and SQL, correctly implements this LOCF imputation with a default value?

A)

B) All of the above
C)

D)

E)

5. You are building a model to predict loan defaults using a dataset stored in Snowflake. After training your model and calculating residuals, you create a scatter plot of the residuals against the predicted values. The plot shows a cone-shaped pattern, with residuals spreading out more as the predicted values increase. Which of the following SQL queries, run within a Snowpark Python session, could be used to address the underlying issue indicated by this residual pattern, assuming the predicted values are stored in a column named and the residuals in a column named 'loan_default_residuar in a Snowflake table named 'loan_predictionds'?

A)

B)

C)

D)

E)

Solutions: