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

You are responsible for deploying a fraud detection model in Snowflake. The model needs to be validated rigorously before being put into production. Which of the following actions represent the MOST comprehensive approach to model validation within the Snowflake environment, focusing on both statistical performance and operational readiness, and using Snowflake features for validation?

• A. Conducting a comprehensive backtesting analysis using historical data, simulating real-world scenarios, and evaluating the model's performance under different conditions. Using Snowflake's time travel feature to access historical data snapshots for accurate backtesting. Monitoring model performance using Snowflake alerts triggered by custom SQL queries against model prediction logs.
• B. Calculating only the AUC (Area Under the Curve) metric on the entire dataset without performing any data splitting or cross-validation. Deploying the model if the AUC is above 0.7.
• C. Performing a single train/test split of the historical data and evaluating model performance metrics (e.g., accuracy, precision, recall) on the test set using standard Python libraries within a Snowflake Snowpark environment. Deploying the model directly if the metrics exceed a predefined threshold.
• D. Implementing K-fold cross-validation using Snowflake stored procedures and temporary tables to store and aggregate the results from each fold. Evaluating the model's performance across different data segments and time periods to assess its robustness. Using Snowflake streams and tasks to automate the validation process on new incoming data.
• E. Relying on a simple visual inspection of model outputs and comparing them to a small sample of known fraud cases. Skipping formal validation to accelerate the deployment process.

A data scientist needs to calculate the cumulative moving average of sales for each product in a table. The table contains columns: (INT), (DATE), and (NUMBER). The desired output should include the product_id', 'sale_date', and Which of the following Snowflake SQL statements correctly calculates the cumulative moving average for each product using window functions?

• A. SELECT product_id, sale_date, daily_sales, OVER (PARTITION BY product_id ORDER BY sale_date ASC) / OVER (PARTITION BY product_id ORDER BY sale_date ASC) AS cumulative_average FROM sales_by_day;
• B. SELECT product_id, sale_date, daily_sales, OVER (PARTITION BY product_id ORDER BY sale_date AS cumulative_average FROM sales_by_day;
• C. SELECT product_id, sale_date, daily_sales, AVG(daily_sales) OVER (PARTITION BY product_id) AS cumulative_average FROM sales_by_day;
• D. SELECT product_id, sale_date, daily_sales, OVER (PARTITION BY product_id ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_average FROM
• E. SELECT product_id, sale_date, daily_sales, AVG(daily_sales) OVER (ORDER BY sale_date ASC) AS cumulative_average FROM sales_by_day;

A data scientist is building a churn prediction model using Snowflake data'. They want to load a large dataset (50 million rows) from a Snowflake table 'customer_data' into a Pandas DataFrame for feature engineering. They are using the Snowflake Python connector. Given the code snippet below and considering performance and memory usage, which approach would be the most efficient for loading the data into the Pandas DataFrame? Assume you have a properly configured connection and cursor 'cur'. Furthermore, assume that the 'customer id' column is the primary key and uniquely identifies each customer. You are also aware that network bandwidth limitations exist within your environment. ```python import snowflake.connector import pandas as pd # Assume conn and cur are already initialized # conn = snowflake.connector.connect(...) # cur = conn.cursor() query = "SELECT FROM customer data```

• A. ```python with conn.cursor(snowflake.connector.DictCursor) as cur: cur.execute(query) df = pd.DataFrame(cur.fetchall())
• B. ```python import snowflake.connector import pandas as pd import pyarrow import pyarrow.parquet # Enable Arrow result format conn.cursor().execute("ALTER SESSION SET PYTHON USE ARROW RESULT FORMAT-TRUE") cur.execute(query) df =
• C. ```python cur.execute(query) df = pd.read_sql(query, conn)
• D. ```python cur.execute(query) df = pd.DataFrame(cur.fetchall(), columns=[col[0] for col in cur.description])
• E. ```python cur.execute(query) results = cur.fetchmany(size=1000000) df_list = 0 while results: df_list.append(pd.DataFrame(results, for col in cur.description])) results = cur.fetchmany(size=1000000) df = pd.concat(df_list, ignore_index=True)

You've developed a fraud detection model using Snowflake ML and want to estimate the expected payout (loss or gain) based on the model's predictions. The cost of investigating a potentially fraudulent transaction is $50. If a fraudulent transaction goes undetected, the average loss is $1000. The model's confusion matrix on a validation dataset is: Predicted Fraud Predicted Not Fraud Actual Fraud 150 50 Actual Not Fraud 20 780 Which of the following SQL queries in Snowflake, assuming you have a table 'FRAUD PREDICTIONS' with columns 'TRANSACTION ID', 'ACTUAL FRAUD', and 'PREDICTED FRAUD' (1 for Fraud, O for Not Fraud), provides the most accurate estimate of the expected payout for every 1000 transactions?

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

A pharmaceutical company is testing a new drug to lower blood pressure. They conduct a clinical trial with 200 patients. After treatment, the sample mean reduction in systolic blood pressure is 10 mmHg, with a sample standard deviation of 15 mmHg. You want to construct a 99% confidence interval for the true mean reduction in systolic blood pressure. Which of the following statements is most accurate concerning the appropriate distribution and critical value to use?

• A. Use a chi-squared distribution with 199 degrees of freedom.
• B. Use a t-distribution with 200 degrees of freedom, and the critical value is close to 2.576.
• C. Use a t-distribution with 199 degrees of freedom, and the critical value is slightly larger than 2.576.
• D. Use a z-distribution because the sample size is large (n > 30), and the critical value is approximately 2.576.
• E. Use a z-distribution because we are estimating mean, and use a critical value of 1.96.