[Jul-2025] DSA-C03 Free PDF from PassLeaderVCE [Q52-Q68]

NO.52 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?

Increase the value of ‘MAX BATCH_ROWS’ for the UDF to process larger batches of data at once.

Reduce the value of ‘MAX for the UDF to process smaller batches of data.

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.

Partition the input data into smaller chunks using SQL queries and call the UDF on each partition separately.

Increase the warehouse size to provide more memory per node.

NO.53 A data scientist is performing exploratory data analysis on a table named ‘CUSTOMER TRANSACTIONS. They need to calculate the standard deviation of transaction amounts C TRANSACTION AMOUNT) for different customer segments CCUSTOMER SEGMENT). The ‘CUSTOMER SEGMENT column can contain NULL values. Which of the following SQL statements will correctly compute the standard deviation, excluding NULL transaction amounts, and handling NULL customer segments by treating them as a separate segment called ‘Unknown’? Consider using Snowflake-specific functions where appropriate.

Option A

Option B

Option C

Option D

Option E

NO.54 You have deployed a machine learning model in Snowflake to predict customer churn. The model was trained on data from the past year. After six months of deployment, you notice the model’s recall for identifying churned customers has dropped significantly. You suspect model decay. Which of the following Snowflake tasks and monitoring strategies would be MOST appropriate to diagnose and address this model decay?

Create a Snowflake Task that automatically retrains the model weekly with the most recent six months of data. Monitor the model’s performance metrics using Snowflake’s query history to track the accuracy of the predictions.

Establish a Snowflake pipe to continuously ingest feedback data (actual churn status) into a feedback table. Write a stored procedure to calculate performance metrics (e.g., recall, precision) on a sliding window of recent data. Create a Snowflake Alert that triggers when recall falls below a defined threshold.

Use Snowflake’s data sharing feature to share the model’s predictions with a separate analytics team. Let them monitor the overall customer churn rate and notify you if it changes significantly.

Back up the original training data to secure storage. Ingest all new data as it comes in. Retrain a new model and compare its performance with the backed-up training data.

Implement a Shadow Deployment strategy in Snowflake. Route a small percentage of incoming data to both the existing model and a newly trained model. Compare the predictions from both models using a UDF that calculates the difference in predicted probabilities. Trigger an alert if the differences exceed a certain threshold.

NO.55 A financial institution is analyzing transaction data in Snowflake to detect fraudulent activity. They have a ‘Transaction_Amount’ column. They want to binarize this feature, creating a new ‘ls_High_Value’ column. Transactions with amounts greater than $1000 should be marked as 1 (High Value), and all other transactions (including NULLs) should be marked as 0. Which of the following SQL statements would be the MOST efficient and correct way to achieve this in Snowflake?

Option A

Option B

Option C

Option D

Option E

NO.56 You’ve deployed a regression model in Snowflake to predict product sales. After a month, you observe that the RMSE on your validation dataset has increased significantly compared to the initial deployment. Analyzing the prediction errors, you notice a pattern: the model consistently underestimates sales for products with a recent surge in social media mentions. Which of the following actions would be MOST effective in addressing this issue and improving the model’s RMSE?

Increase the regularization strength of the model to prevent overfitting to the original training data.

Retrain the model using only the most recent data (e.g., last week) to adapt to the changing sales patterns.

Incorporate a feature representing the number of social media mentions for each product into the model and retrain.

Decrease the learning rate of the optimization algorithm during retraining to avoid overshooting the optimal weights.

Implement a moving average smoothing technique on the target variable (sales) before retraining the model.

NO.57 You’ve deployed a fraud detection model in Snowflake. The model is implemented as a Python UDF that uses a pre-trained scikit-learn model stored as a stage file. Your goal is to enable near real-time fraud detection on incoming transactions. Due to regulatory requirements, you need to maintain a detailed audit trail of all predictions, including the input features, model version, prediction scores, and any errors encountered during the prediction process. Which of the following approaches are valid and efficient for storing these audit logs and predictions in Snowflake?

Create a dedicated table with columns for transaction ID, input features (as a JSON VARIANT), model version, prediction score, error message (if any), and prediction timestamp. Use a Snowflake Sequence to generate unique log IDs.

Store the audit logs as unstructured text files in an external stage (e.g., AWS S3) and periodically load them into a Snowflake table using COPY INTO command.

Utilize Snowflake’s Streams and Tasks to automatically capture changes to the transaction table and trigger the prediction UDF, storing the audit logs in a separate table with similar structure as described in option A.

Log the audit information to an external logging service (e.g., Splunk) using an external function called from within the UDF.

Use Snowflake’s ‘SYSTEM$QUERY LOG’ table to extract information about the UDF execution and join it with the transaction data to reconstruct the audit trail.

NO.58 You are developing a real-time fraud detection system using Snowflake and an external function. The system involves scoring incoming transactions against a pre-trained TensorFlow model hosted on Google Cloud A1 Platform Prediction. The transaction data resides in a Snowflake stream. The goal is to minimize latency and cost. Which of the following strategies are most effective to optimize the interaction between Snowflake and the Google Cloud A1 Platform Prediction service via an external function, considering both performance and cost?

Invoke the external function for each individual transaction in the Snowflake stream, sending the transaction data as a single request to the Google Cloud A1 Platform Prediction service.

Implement a caching mechanism within the external function (e.g., using Redis on Google Cloud) to store frequently accessed model predictions, thereby reducing the number of calls to the Google Cloud A1 Platform Prediction service. This requires managing cache invalidation.

Batch multiple transactions from the Snowflake stream into a single request to the external function. The external function then sends the batched transactions to the Google Cloud A1 Platform Prediction service in a single request. This increases throughput but might introduce latency.

Use a Snowflake pipe to automatically ingest the data from the stream, and then trigger a scheduled task that periodically invokes a stored procedure to train the model externally.

Implement asynchronous invocation of the external function from Snowflake using Snowflake’s task functionality. This allows Snowflake to continue processing transactions without waiting for the response from the Google Cloud A1 Platform Prediction service, but requires careful monitoring and handling of asynchronous results.

NO.59 You’re analyzing the performance of two different AIB testing variants of an advertisement. You’ve collected the following data over a period of one week: Variant A: 1000 impressions, 50 conversions Variant B: 1100 impressions, 66 conversions Which of the following statements are TRUE regarding confidence intervals and statistical significance in this scenario?

Constructing a 95% confidence interval for the difference in conversion rates between Variant B and Variant A will allow you to assess if there is a statistically significant difference at the 5% significance level. If the confidence interval contains zero, there is no statistically significant difference.

A narrower confidence interval for the difference in conversion rates implies a higher degree of certainty about the estimated difference.

Increasing the sample size (number of impressions for each variant) will generally widen the confidence interval, making it more likely to contain zero.

If the 95% confidence interval for the conversion rate of Variant A is entirely above the 95% confidence interval for the conversion rate of Variant B, then Variant A is statistically better than Variant B.

Calculating separate confidence intervals for conversion rates A and B, and noting overlap, is an invalid method to infer statistical significance. One must construct confidence interval for the difference in means.

NO.60 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?

SELECT product_id, sale_date, daily_sales, AVG(daily_sales) OVER (ORDER BY sale_date ASC) AS cumulative_average FROM sales_by_day;

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

SELECT product_id, sale_date, daily_sales, OVER (PARTITION BY product_id ORDER BY sale_date AS cumulative_average FROM sales_by_day;

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;

SELECT product_id, sale_date, daily_sales, AVG(daily_sales) OVER (PARTITION BY product_id) AS cumulative_average FROM sales_by_day;

NO.61 You are deploying a pre-trained image classification model stored as a serialized file in an internal stage within Snowflake. You need to create a UDF to load this model and use it for inference on image data stored in a VARIANT column. The model was trained using Python’s scikit-learn library and uses OpenCV for image processing. Which of the following code snippets correctly outlines the steps required to create and deploy this UDF? Assume you have already created an internal stage named ‘MODEL STAGE and uploaded the model file into it. You also need to create a temporary directory that will be removed after the execution.

NO.62 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“`

“`python cur.execute(query) df = pd.DataFrame(cur.fetchall(), columns=[col[0] for col in cur.description])

“`python cur.execute(query) df = pd.read_sql(query, conn)

“`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)

“`python with conn.cursor(snowflake.connector.DictCursor) as cur: cur.execute(query) df = pd.DataFrame(cur.fetchall())

“`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 =

NO.63 You are tasked with training a logistic regression model in Snowflake using Snowpark Python to predict customer churn. Your data is stored in a table named ‘CUSTOMER DATA’ with columns like ‘CUSTOMER D’, ‘FEATURE 1’, ‘FEATURE 2’, ‘FEATURE 3’, and ‘CHURN FLAG’ (boolean representing churn). You plan to use stratified k-fold cross-validation to ensure each fold has a representative proportion of churned and non-churned customers. Which of the following code snippets demonstrates the correct way to perform stratified k-fold cross-validation with Snowpark ML? (Assume ‘snowpark_session’ is a valid Snowpark session object).

NO.64 A data scientist is tasked with identifying customer segments for a new marketing campaign using transaction data stored in Snowflake. The transaction data includes features like transaction amount, frequency, recency, and product category. Which unsupervised learning algorithm would be MOST appropriate for this task, considering scalability and Snowflake’s data processing capabilities, and what preprocessing steps are crucial before applying the algorithm?

K-Means clustering, after standardizing numerical features (transaction amount, frequency, recency) and using one-hot encoding for product category. This is highly scalable within Snowflake using UDFs and SQL.

Hierarchical clustering, using the complete linkage method and Euclidean distance. No preprocessing is necessary, as hierarchical clustering can handle raw data.

DBSCAN, using raw data without any scaling or encoding. The algorithm’s density-based nature will automatically handle the varying scales of the features.

Principal Component Analysis (PCA) followed by K-Means. This reduces dimensionality and then clusters, improving the visualization of the cluster.

K-Means clustering, after applying min-max scaling to numerical features and converting categorical features to numerical representation. The optimal ‘k’ (number of clusters) should be determined using the elbow method or silhouette analysis.

NO.65 You have a table in Snowflake named ‘CUSTOMER DATA’ with columns ‘CUSTOMER D’, ‘PURCHASE AMOUNT’, and ‘RECENCY’. You want to perform feature scaling on ‘PURCHASE AMOUNT’ using Min-Max scaling and store the scaled values in a new column named ‘SCALED PURCHASE _ AMOUNT’. Which of the following Snowflake SQL code snippets correctly implements this feature scaling? Note: Assume there are no NULL values in PURCHASE AMOUNT and you have privileges to create temporary tables and UDFs if necessary.

Option A

Option B

Option C

Option D

Option E

NO.66 You are using Snowflake ML to train a binary classification model. After training, you need to evaluate the model’s performance. Which of the following metrics are most appropriate to evaluate your trained model, and how do they differ in their interpretation, especially when dealing with imbalanced datasets?

Accuracy: It measures the overall correctness of the model. Precision: It measures the proportion of positive identifications that were actually correct. Recall: It measures the proportion of actual positives that were identified correctly. Fl-score: It is the harmonic mean of precision and recall.

Mean Squared Error (MSE): The average squared difference between the predicted and actual values. R-squared: Represents the proportion of variance in the dependent variable that is predictable from the independent variables. These are great for regression tasks.

AUC-ROC: Measures the ability of the model to distinguish between classes. It is less sensitive to class imbalance than accuracy. Log Loss: Measures the performance of a classification model where the prediction input is a probability value between 0 and 1.

Confusion Matrix: A table that describes the performance of a classification model by showing the counts of true positive, true negative, false positive, and false negative predictions. This isnt a metric but representation of the metrics.

Precision, Recall, F I-score, AUC-ROC, and Log Loss: Precision focuses on the accuracy of positive predictions; Recall focuses on the completeness of positive predictions; Fl-score balances Precision and Recall; AUC-ROC evaluates the separability of classes and Log Loss quantifies the accuracy of probabilities, especially valuable for imbalanced datasets because they provide a more nuanced view of performance than accuracy alone.

NO.67 You’ve developed a binary classification model using Snowpark ML to predict customer subscription renewal (0 for churn, 1 for renew). You want to visualize feature importance using a permutation importance technique calculated within Snowflake. You perform feature permutation and calculate the decrease in model performance (e.g., AUC) after each permutation. Suppose the following query represents the results of this process:

The ‘feature_importance_results’ table contains the following data:

Based on this output, which of the following statements are the MOST accurate interpretations regarding feature impact and model behavior?

The ‘contract_length’ feature is the most important feature for the model’s predictive performance; shuffling it causes the largest drop in AUC.

The ‘support_calls’ feature is the least important feature; removing it entirely from the model will have little impact on its AUC performance.

The ‘contract_length’ and ‘monthly_charges’ features are equally important.

Increasing the ‘contract_length’ for customers will always lead to a higher probability of renewal. However, there could be correlation between contract length and monthly charges.

Permutation importance only reveals the importance of features within the current model. Different models trained with different features or algorithms might have different feature rankings.

NO.68 You are developing a machine learning model within a Snowflake UDF (User-Defined Function) written in Python. This UDF needs to access external Python libraries not included in the default Snowflake Anaconda channel. You’ve created a stage and uploaded the necessary file. You’ve successfully used ‘conda create’ and ‘conda install –file requirements.txt’ to create your environment locally, and subsequently zipped the environment. Now, what steps are essential to configure the Snowflake UDF to correctly use these external libraries from the stage? Select all that apply.

Include the line ‘import sys; sys._xoptions[‘snowflake_home’] = at the top of your UDF to point to the environment stage location.

Create a ZIP file containing the Python environment and upload it to a Snowflake stage.

Specify the stage path containing the zipped environment in the ‘imports’ clause of the ‘CREATE OR REPLACE FUNCTION’ statement using the symbol and specifying the zip file e.g., ‘@snowflake_packages/myenv.zip’.

Set the ‘PYTHON_VERSION’ parameter of the ‘CREATE OR REPLACE FUNCTION’ statement to match the Python version used in your environment using e.g. ‘PYTHON_VERSION = ‘3.8″.

Install the packages directly into the Snowflake environment using ‘CREATE OR REPLACE FUNCTION RETURNS VARCHAR ..: and a pip install command within the function.

Leave a Reply Cancel reply