Snowflake DSA-C03 : SnowPro Advanced: Data Scientist Certification Exam

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. Creating a feature that returns 1 if there is a PROMOTION_FLAG of True and SALES_AMOUNT > 1000, and zero otherwise
• B. All of the above.
• C. 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
• D. 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.
• E. Converting 'SALE_DATE to a quarterly representation (e.g., '2023-QI').

You are tasked with building a machine learning model in Python using data stored in Snowflake. You need to efficiently load a large table (100GB+) into a Pandas DataFrame for model training, minimizing memory footprint and network transfer time. You are using the Snowflake Connector for Python. Which of the following approaches would be MOST efficient for loading the data, considering potential memory limitations on your client machine and the need for data transformations during the load process?

• A. Utilize the 'execute_stream' method of the Snowflake cursor to fetch data in chunks, apply transformations in each chunk, and append to a larger DataFrame or process iteratively without creating a large in-memory DataFrame.
• B. Load the entire table into a Pandas DataFrame using with a simple 'SELECT FROM my_table' query and then perform data transformations in Pandas.
• C. Create a Snowflake view with the necessary transformations, and then load the view into a Pandas DataFrame using 'pd.read_sql()'.
• D. Use the 'COPY INTO' command to unload the table to an Amazon S3 bucket and then use bot03 in your python script to fetch data from s3 and load into pandas dataframe.
• E. Use 'snowsql' to unload the table to a local CSV file, then load the CSV file into a Pandas DataFrame.

You are working with a large dataset of customer transactions in Snowflake. The dataset contains columns like 'customer id' , 'transaction date', 'product category' , and 'transaction_amount'. Your task is to identify fraudulent transactions by detecting anomalies in spending patterns. You decide to use Snowpark for Python to perform time-series aggregation and feature engineering. Given the following Snowpark DataFrame 'transactions_df , which of the following approaches would be MOST efficient for calculating a 7-day rolling average of for each customer, while also handling potential gaps in transaction dates?

• A. Use a Snowpark Pandas UDF to calculate the rolling average for each customer after collecting all transactions for that customer into a Pandas DataFrame. Handle missing dates using Pandas functionality.
• B. Use a simple followed by a UDF to calculate the rolling average. Fill in missing dates manually within the UDF.
• C. Use'window.partitionBy('customer_id').orderBy('transaction_date').rangeBetween(Window.unboundedPreceding, Window.currentRow)' in conjunction with a date range table joined to the transactions, filling in missing days before calculating the rolling average with 'transaction_amount' set to 0 for the inserted days.
• D. Use a stored procedure in SQL to iterate over each customer, calculate the rolling average using a cursor and conditional logic for handling missing dates.
• E. Use 'window.partitionBy('customer_id').orderBy('transaction_date').rowsBetween(-6, Window.currentRow)' within a 'select' statement and handle any missing dates using 'fillna()' after calculating the rolling average.

You are developing a churn prediction model using Snowpark Python and Scikit-learn. After initial model training, you observe significant overfitting. Which of the following hyperparameter tuning strategies and code snippets, when implemented within a Snowflake Python UDF, would be MOST effective to address overfitting in a Ridge Regression model and how can you implement a reproducible model with minimal code?

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

You've built a model in Snowflake to predict the likelihood of a customer clicking on an advertisement. The model outputs a probability score between 0 and 1. You want to determine the optimal threshold to use for converting these probabilities into binary predictions (click/no-click). Your business stakeholders have provided the following information: Cost of showing an ad: $0.10; Revenue generated from a click: $1.00; You have access to a table 'AD_PREDICTIONS' with columns 'CUSTOMER_ID', 'PREDICTED_PROBABILITY' , and 'ACTUAL CLICK' (1 for click, 0 for no click). Which of the following approaches would be the MOST appropriate for selecting the optimal probability threshold to maximize profit, and why?

• A. Select a threshold of 0.5, as this is a common default threshold for binary classification problems.
• B. Iterate through a range of probability thresholds (e.g., 0.01 to 0.99), and for each threshold, calculate the profit using SQL in Snowflake: 'SELECT SUM(CASE WHEN PREDICTED PROBABILITY threshold THEN CASE WHEN ACTUAL CLICK = 1 THEN 0.9 ELSE -0.1 END ELSE O END) AS Profit FROM AD_PREDICTIONS;' Choose the threshold that maximizes the profit.
• C. Select a very high probability threshold (e.g., 0.9) to ensure that only the most likely clicks are targeted, minimizing wasted ad spend.
• D. Use the precision-recall curve to find the threshold that maximizes the F1 -score, balancing precision and recall.
• E. Calculate the point on the ROC curve closest to the top-left corner (perfect classification) and use the corresponding threshold. This optimizes for both sensitivity and specificity.