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

You have built an external function to train a PyTorch model using SageMaker. The model training process requires a significant amount of CPU and memory. The training data is passed from Snowflake to the external function in batches. The external function code in AWS Lambda is as follows:

The Snowflake external function is defined as follows:

During testing, you encounter '500 Internal Server Error' from the external function consistently. Upon inspection of the Lambda logs, you find messages indicating 'PayloadTooLargeError'. What is the most likely cause and how do you mitigate it within the context of Snowflake and AWS Lambda?

• A. The Snowflake external function definition is incorrect. Change the 'RETURNS VARIANT clause to 'RETURNS VARCHAR as the Lambda function returns a JSON string.
• B. The Lambda function is timing out before the model training can complete. Increase the Lambda function's timeout setting to allow sufficient time for the training process.
• C. The size of the data being sent from Snowflake to the Lambda function exceeds the maximum payload size allowed by AWSAPI Gateway. Increase the maximum payload size limit in the API Gateway settings.
• D. The IAM role associated with the Lambda function lacks the necessary permissions to invoke the SageMaker training job. Grant the Lambda function's IAM role the appropriate SageMaker permissions.
• E. The size of the data being sent from Snowflake to the Lambda function exceeds the maximum payload size allowed by AWS API Gateway. Implement data partitioning in Snowflake and send smaller batches of data to the Lambda function, aggregating the results in a separate table.

You are working on a fraud detection model and need to prepare transaction data'. You have two tables: 'transactions' (transaction_id, customer_id, transaction_date, amount, merchant_id) and (merchant_id, city, state). You need to perform the following data cleaning and feature engineering steps using Snowpark: 1. Remove duplicate transactions based on 'transaction_id'. 2.
Join the 'transactions' table with the 'merchant_locations table to add city and state information to each transaction. 3. Create a new feature called 'amount_category' based on the transaction amount, categorized as 'Low', 'Medium', or 'High'. 4. The categorization thresholds are defined as follows: 'LoW: amount < 50 'Medium': 50 amount < 200 'High': amount >= 200 Which of the following statements about performing these operations using Snowpark are accurate?

• A. You can register SQL UDF to calculate the 'amount_category' using 'CASE WHEN' statement
• B. A LEFT JOIN should be used to join the 'transactions' and 'merchant_location' tables to ensure that all transactions are included, even if some merchant IDs are not present in the 'merchant_location' table.
• C. Removing duplicate transactions can be efficiently done using the method on the Snowpark DataFrame, specifying 'transaction_id' as the subset. Creating the amount categories can be completed using the 'when' clause with multiple 'otherwise' clauses.
• D. Removing duplicate transactions can be efficiently done using the method on the Snowpark DataFrame, specifying 'transaction_id' as the subset. Creating the amount categories requires use of a User-Defined Function (UDF) as the logic can't be efficiently embedded in a single 'when' clause.
• E. The construct in Snowpark can be used to create the 'amount_category' feature directly within the DataFrame transformation without needing a UDF

You are analyzing sales data in Snowflake using Snowpark to identify seasonality. You have a table named 'SALES DATA with columns 'SALE DATE (TIMESTAMP NTZ) and 'AMOUNT (NUMBER). You want to calculate the rolling average sales for each week over a period of 12 weeks using a Snowpark DataFrame. Which of the following Snowpark code snippets correctly implements this calculation?

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

You are building a customer churn prediction model for a telecommunications company. You have a 'CUSTOMER DATA' table with a 'MONTHLY SPENDING' column that represents the customer's monthly bill amount. You want to binarize this column to create a feature indicating whether a customer is a 'High Spender' or 'Low Spender'. You decide that customers spending more than $75 are 'High Spenders'. Which of the following Snowflake SQL statements is the most efficient and correct way to achieve this, considering performance and readability, while avoiding potential NULL values in the resulting binarized column?

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

You are analyzing a dataset of website traffic and conversions in Snowflake, aiming to understand the relationship between the number of pages visited CPAGES VISITED) and the conversion rate (CONVERSION_RATE). You perform a simple linear regression using the 'REGR SLOPE and 'REGR INTERCEPT functions. However, after plotting the data and the regression line, you observe significant heteroscedasticity (non-constant variance of errors). Which of the following actions, performed within Snowflake during the data preparation and feature engineering phase, are MOST appropriate to address this heteroscedasticity and improve the validity of your linear regression model? (Select all that apply)

• A. Apply a logarithmic transformation to the 'CONVERSION RATE' variable using the 'LN()' function. CREATE OR REPLACE VIEW TRANSFORMED_DATA AS SELECT PAGES VISITED, LN(CONVERSION RATE) AS LOG_CONVERSION RATE FROM ORIGINAL_DATA;
• B. Remove outlier data points from the dataset based on the Interquartile Range (IQR) of the residuals from the original linear regression model. This requires calculating the residuals first.
• C. Apply a Box-Cox transformation to the 'CONVERSION RATE' variable. This transformation will determine the optimal lambda value using some complex SQL statistical operations. This can be approximated to log tranformation in many real life scenarios.
• D. Calculate the weighted least squares regression by weighting each observation by the inverse of the squared predicted values from an initial OLS regression. This requires multiple SQL queries.
• E. Standardize the 'PAGES_VISITED' and 'CONVERSION_RATE variables using the and functions.Create OR REPLACE VIEW STANDARDIZED_DATA AS SELECT (PAGES_VISITED - OVER()) / OVER() AS Z PAGES_VISITED, (CONVERSION RATE -OVER()) / OVER() AS FROM ORIGINAL_DATA;