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

A data scientist is analyzing website click-through rates (CTR) for two different ad campaigns. Campaign A ran for two weeks and had 10,000 impressions with 500 clicks. Campaign B also ran for two weeks with 12,000 impressions and 660 clicks. The data scientist wants to determine if there's a statistically significant difference in CTR between the two campaigns. Assume the population standard deviation is unknown and unequal for the two campaigns. Which statistical test is most appropriate to use, and what Snowflake SQL code would be used to approximate the p-value for this test (assume 'clicks_b' , and are already defined Snowflake variables)?

• A. A paired t-test, because we are comparing two related samples over time. Snowflake code: 'SELECT t_test_ind(clicks_a/impressions_a, 'VAR EQUAL-TRUE')
• B. An independent samples t-test, because we are comparing the means of two independent samples. Snowflake code: SELECT
  
• C. An independent samples t-test (Welch's t-test), because we are comparing the means of two independent samples with unequal variances. Snowflake code (approximation using UDF - assuming UDF 'p_value_from_t_stat' exists that calculates p-value from t-statistic and degrees of freedom):
  
• D. A one-sample t-test, because we are comparing the sample mean of campaign A to the sample mean of campaign Snowflake code: 'SELECT t_test_lsamp(clicks_a/impressions_a - clicks_b/impressions_b, 0)'
• E. Az-test, because we know the population standard deviation. Snowflake code: 'SELECT normcdf(clicks_a/impressions_a - clicks_b/impressions_b, O, 1)'

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. 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.
• C. 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.
• 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. 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.

You are training a regression model to predict house prices using a Snowflake dataset. The dataset contains various features, including 'number of_bedrooms', , and You want to use time-based partitioning for your training, validation, and holdout sets. However, you also need to ensure that the dataset is properly shuffled within each time partition to mitigate potential bias introduced by the order of data entry. Which of the following strategies is MOST EFFECTIVE and EFFICIENT for partitioning your data into train, validation, and holdout sets in Snowflake, while also ensuring random shuffling within each partition, and addressing potential data leakage issues?

• A. Create a user-defined function (UDF) in Python that takes a 'sale_date' as input and returns either 'train', 'validation', or 'holdout' based on pre-defined date ranges. Apply this UDF to each row, creating a 'split_group' column. Then, create temporary tables for each split using 'CREATE TABLE AS SELECT ... FROM . WHERE split_group = ... ORDER BY RANDOM()'. UDF overhead and global RANDOM sort make it very slow.
• B. Use Snowflake's SAMPLE clause with a 'REPEATABLE seed for each split (train, validation, holdout), filtering by 'sale_date'. Add an 'ORDER BY RANDOM()' clause within each 'SAMPLE query to shuffle the data within each split. This approach does not guarantee non-overlapping sets and can introduce sampling bias.
• C. Create a new column 'split_group' using a CASE statement based on 'sale_date' to assign each row to 'train', 'validation', or 'holdout'. Calculate a random number within each 'split_group' by using OVER (PARTITION BY split_group ORDER BY RANDOM())'. Then create temporary tables for each split using 'CREATE TABLE AS SELECT FROM WHERE split_group = QUALIFY ROW NUMBER() OVER (ORDER BY RANDOM()) (SELECT COUNT( ) FROM transactions WHERE split_group -- ...) (respective split percentage);'
• D. Create a new column 'split_group' using a CASE statement based on 'sale_date' to assign each row to 'train', 'validation', or 'holdout'. Then, create temporary tables for each split using 'CREATE TABLE AS SELECT FROM WHERE split_group = ORDER BY RANDOM()'. This can be very slow because of global RANDOM sort and leakage issues with using full dataset for randomness.
• E. Create separate views for train, validation, and holdout sets, filtering by 'sale_date' . Shuffle the entire dataset using 'ORDER BY RANDOM()' before creating the views to ensure randomness across all sets. This does not address shuffling within parition.

You are developing a model to predict house prices based on structured data including size, number of bedrooms, location, and age. You have built a linear regression model within Snowflake. During the evaluation, you observe that the residuals exhibit heteroscedasticity. Which of the following actions is the LEAST appropriate to address heteroscedasticity in this scenario, considering you want to implement the solution primarily using Snowflake's built-in features and capabilities?

• A. Implement Weighted Least Squares (WLS) regression by calculating weights inversely proportional to the variance of the residuals for each data point. This involves creating a UDF to calculate weights and modifying the linear regression model fitting process. (Assume direct modification of the fitting process is possible within Snowflake).
• B. Apply a logarithmic transformation to the target variable ('SALES_PRICE) using the 'LOG' function within Snowflake before training the linear regression model.
• C. Use robust standard errors in the linear regression analysis, even though Snowflake doesn't directly support calculating them. You decide to export model coefficients to an external statistics package (e.g., Python with Statsmodels) to compute robust standard errors and then bring insights back to Snowflake.
• D. Transform independent variables using Box-Cox transformation and include in Snowflake Linear Regression Model Training
• E. Include interaction terms between the independent variables in your linear regression model.

You have deployed a vectorized Python UDF in Snowflake to perform sentiment analysis on customer reviews. The UDF uses a pre-trained transformer model loaded from a Stage. The model consumes a significant amount of memory (e.g., 5GB). Users are reporting intermittent 'Out of Memory' errors when calling the UDF, especially during peak usage. Which of the following strategies, used IN COMBINATION, would MOST effectively mitigate these errors and optimize resource utilization?

• A. Implement lazy loading of the model within the UDF, ensuring it's only loaded once per warehouse node and reused across multiple invocations within that node.
• B. Partition the input data into smaller chunks using SQL queries and call the UDF on each partition separately.
• C. Reduce the value of 'MAX for the UDF to process smaller batches of data.
• D. Increase the value of 'MAX BATCH_ROWS' for the UDF to process larger batches of data at once.
• E. Increase the warehouse size to provide more memory per node.