Snowflake DSA-C03 Value Pack (Frequently Bought Together)

You are analyzing customer transaction data in Snowflake to identify fraudulent activities. The 'TRANSACTION AMOUNT' column exhibits a right-skewed distribution. Which of the following Snowflake queries is MOST effective in identifying outliers based on the Interquartile Range (IQR) method, specifically targeting unusually large transaction amounts? Assume IQR is already calculated as variable and QI as and Q3 as in snowflake session.

• A. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION_AMOUNT > (SELECT MEDIAN(TRANSACTION AMOUNT) FROM TRANSACTIONS);
• B. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION AMOUNT > q3 + (1.5 iqr);
• C. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION_AMOUNT > (SELECT + 3 FROM TRANSACTIONS);
• D. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION_AMOUNT > (SELECT WITHIN GROUP (ORDER BY TRANSACTION_AMOUNT) FROM TRANSACTIONS);
• E. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION_AMOUNT < qi - (1.5 iqr);

You're a data scientist analyzing sensor data from industrial equipment stored in a Snowflake table named 'SENSOR READINGS' The table includes 'TIMESTAMP' , 'SENSOR ID', 'TEMPERATURE', 'PRESSURE', and 'VIBRATION'. You need to identify malfunctioning sensors based on outlier readings in 'TEMPERATURE' , 'PRESSURE' , and 'VIBRATION'. You want to create a dashboard to visualize these outliers and present a business case to invest in predictive maintenance. Select ALL of the actions that are essential for both effectively identifying sensor outliers within Snowflake and visualizing the data for a business presentation. (Multiple Correct Answers)

• A. Create a Snowflake stored procedure to automatically flag outlier readings in a new column 'IS OUTLIER based on a predefined rule set (e.g., IQR method or Z-score threshold), and then use this column to filter data for visualization in a dashboard.
• B. Implement a clustering algorithm (e.g., DBSCAN) within Snowflake using Snowpark Python to group similar sensor readings, identifying outliers as points that do not belong to any cluster or belong to very small clusters.
• C. Calculate basic statistical summaries (mean, standard deviation, min, max) for each sensor and each variable C TEMPERATURE, 'PRESSURE, and 'VIBRATION') and use that information to filter down to the most important sensor, prior to using the other techniques.
• D. Calculate Z-scores for 'TEMPERATURE, 'PRESSURE, and 'VIBRATION' for each 'SENSOR_ID within a rolling window of the last 24 hours using Snowflake's window functions. Define outliers as readings with Z-scores exceeding a threshold (e.g., 3).
• E. Directly connect the 'SENSOR_READINGS' table to a visualization tool and create a 3D scatter plot with 'TEMPERATURE, 'PRESSURE, and 'VIBRATION' on the axes, without any pre-processing or outlier detection in Snowflake.

Consider the following Snowflake SQL query used to calculate the RMSE for a regression model's predictions, where 'actual_value' is the actual value and 'predicted value' is the model's prediction. However, you notice that the RMSE calculation is incorrect due to an error in the query. Identify the error in the query and provide the corrected query. The table name is 'sales_predictions'.

Which of the following options represents the corrected query that accurately calculates the RMSE?

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

You are building a customer churn prediction model in Snowflake using Snowflake ML. After training, you need to evaluate the model's performance and identify areas for improvement. Given the following table 'PREDICTIONS' contains predicted probabilities and actual churn labels, which SQL query effectively calculates both precision and recall for the churn class (where 'CHURN = 1')?

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

A financial services company wants to predict loan defaults. They have a table 'LOAN APPLICATIONS' with columns 'application_id', applicant_income', 'applicant_age' , and 'loan_amount'. You need to create several derived features to improve model performance.
Which of the following derived features, when used in combination, would provide the MOST comprehensive view of an applicant's financial stability and ability to repay the loan? Select all that apply

• A. Requires external data from a credit bureau to determine total debt, then calculated as 'total_debt / applicant_income' (Assume credit bureau integration is already in place)
• B. Calculated as 'applicant_age applicant_age'.
• C. Calculated as 'applicant_age / applicant_income'.
• D. Calculated as 'loan_amount I applicant_age' .
• E. Calculated as 'applicant_income I loan_amount'.