SnowPro Advanced DSA-C03 Dumps Full Questions with Free PDF Questions to Pass [Q81-Q101]

NO.81 You are working with a large dataset of customer transactions in Snowflake. The dataset contains columns like ‘customer id’ , ‘transaction date’, ‘product category’ , and ‘transaction_amount’. Your task is to identify fraudulent transactions by detecting anomalies in spending patterns. You decide to use Snowpark for Python to perform time-series aggregation and feature engineering. Given the following Snowpark DataFrame ‘transactions_df , which of the following approaches would be MOST efficient for calculating a 7-day rolling average of for each customer, while also handling potential gaps in transaction dates?

Use a simple followed by a UDF to calculate the rolling average. Fill in missing dates manually within the UDF.

Use ‘window.partitionBy(‘customer_id’).orderBy(‘transaction_date’).rowsBetween(-6, Window.currentRow)’ within a ‘select’ statement and handle any missing dates using ‘fillna()’ after calculating the rolling average.

Use a stored procedure in SQL to iterate over each customer, calculate the rolling average using a cursor and conditional logic for handling missing dates.

Use a Snowpark Pandas UDF to calculate the rolling average for each customer after collecting all transactions for that customer into a Pandas DataFrame. Handle missing dates using Pandas functionality.

Use’window.partitionBy(‘customer_id’).orderBy(‘transaction_date’).rangeBetween(Window.unboundedPreceding, Window.currentRow)’ in conjunction with a date range table joined to the transactions, filling in missing days before calculating the rolling average with ‘transaction_amount’ set to 0 for the inserted days.

NO.82 A data scientist is analyzing website conversion rates for an e-commerce platform. They want to estimate the true conversion rate with 95% confidence. They have collected data on 10,000 website visitors, and found that 500 of them made a purchase. Given this information, and assuming a normal approximation for the binomial distribution (appropriate due to the large sample size), which of the following Python code snippets using scipy correctly calculates the 95% confidence interval for the conversion rate? (Assume standard imports like ‘import scipy.stats as St’ and ‘import numpy as np’).

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

NO.84 A data scientist is developing a model within a Snowpark Python environment to predict customer churn. They have established a Snowflake session and loaded data into a Snowpark DataFrame named ‘customer data’. The feature engineering pipeline requires a custom Python function, ‘calculate engagement_score’, to be applied to each row. This function takes several columns as input and returns a single score representing customer engagement. The data scientist wants to apply this function in parallel across the entire DataFrame using Snowpark’s UDF capabilities. The following code snippet is used to define and register the UDF:

When the UDF is called the above error is observed. What change needs to be applied to make the UDF work as expected?

Remove argument from ‘session.udf.register’ call. Snowpark can infer the input types automatically.

Add ‘@F.sproc’ decorator before the function definition.

Change the function call to use the Snowpark DataFrame’s ‘select’ function with column objects: ‘customer_data.select(engagement_score_udf(F.col(‘num_transactions’), F.col(‘avg_transaction_value’),

Wrap the Python function inside a stored procedure using @F.sproc’ and call that stored procedure instead of the plain python function.

Redefine the function to accept string arguments and cast them to the correct data types within the function.

NO.85 A data science team at a retail company is using Snowflake to store customer transaction data’. They want to segment customers based on their purchasing behavior using K-means clustering. Which of the following approaches is MOST efficient for performing K-means clustering on a very large customer dataset in Snowflake, minimizing data movement and leveraging Snowflake’s compute capabilities, and adhering to best practices for data security and governance?

Exporting the entire customer transaction dataset from Snowflake to an external Python environment, performing K-means clustering using scikit-learn, and then importing the cluster assignments back into Snowflake as a new table. This approach involves significant data egress and potential security risks.

Using a Snowflake User-Defined Function (UDF) written in Python that leverages the scikit-learn library within the UDF to perform K-means clustering directly on the data within Snowflake. Ensure the UDF is called with appropriate resource allocation (WAREHOUSE SIZE) and security context.

Implementing K-means clustering using SQL queries with iterative JOINs and aggregations to calculate centroids and assign data points to clusters. This approach is computationally expensive and not recommended for large datasets. Moreover, security considerations are minimal.

Using Snowflake’s Snowpark DataFrame API with a Python UDF to preprocess the data and execute the K-means algorithm within the Snowflake environment. This approach allows for scalable processing within Snowflake’s compute resources with data kept securely within the governance boundaries.

Employing only Snowflake’s SQL capabilities to perform approximate nearest neighbor searches without implementing the full K-means algorithm. This compromises the accuracy and effectiveness of the clustering results.

NO.86 You are tasked with building a predictive model in Snowflake to identify high-value customers based on their transaction history. The ‘CUSTOMER_TRANSACTIONS table contains a ‘TRANSACTION_AMOUNT column. You need to binarize this column, categorizing transactions as ‘High Value’ if the amount is above a dynamically calculated threshold (the 90th percentile of transaction amounts) and ‘Low Value’ otherwise. Which of the following Snowflake SQL queries correctly achieves this binarization, leveraging window functions for threshold calculation and resulting in a ‘CUSTOMER SEGMENT column?

Option A

Option B

Option C

Option D

Option E

NO.87 You are tasked with optimizing the hyperparameter tuning process for a complex deep learning model within Snowflake using Snowpark Python. The model is trained on a large dataset stored in Snowflake, and you need to efficiently explore a wide range of hyperparameter values to achieve optimal performance. Which of the following approaches would provide the MOST scalable and performant solution for hyperparameter tuning in this scenario, considering the constraints and capabilities of Snowflake?

Using a simple ‘for’ loop to iterate through a predefined set of hyperparameter combinations, training the model for each combination within a Snowpark Python UDF, and storing the results in a Snowflake table.

Leveraging a distributed hyperparameter tuning framework like Ray Tune or Dask-ML within Snowpark Python, utilizing Snowflake’s compute resources for parallel training and evaluation of different hyperparameter configurations.

Employing Scikit-learn’s ‘GridSearchCV’ or ‘RandomizedSearchCV’ within a single Snowpark Python UDF, processing the entire dataset and hyperparameter search sequentially.

Implementing a custom hyperparameter optimization algorithm using Snowpark Python UDFs, manually distributing the hyperparameter search across multiple Snowflake virtual warehouses, and aggregating the results in a central table.

Using ‘Hyperopt’ within a Snowpark Python UDF, relying on its sequential optimization approach without any form of parallelization or distribution of the workload.

NO.88 You are tasked with analyzing the ‘transaction amounts’ column in the ‘sales data’ table to understand its variability across different geographical regions. You need to calculate the variance of transaction amounts for each region. However, some regions have very few transactions, which can skew the variance calculation. Which of the following SQL statements correctly calculates the variance for each region, excluding regions with fewer than 10 transactions, using Snowflake’s native statistical functions?

Option A

Option B

Option C

Option D

Option E

NO.89 You are evaluating a binary classification model’s performance using the Area Under the ROC Curve (AUC). You have the following predictions and actual values. What steps can you take to reliably calculate this in Snowflake, and which snippet represents a crucial part of that calculation? (Assume tables ‘predictions’ with columns ‘predicted_probability’ (FLOAT) and ‘actual_value’ (BOOLEAN); TRUE indicates positive class, FALSE indicates negative class). Which of the below code snippet should be used to calculate the ‘True positive Rate’ and ‘False positive Rate’ for different thresholds

Calculate AUC directly within a Snowpark Python UDF using scikit-learn’s function. This avoids data transfer overhead, making it highly efficient for large datasets. No further SQL is needed beyond querying the predictions data.

The AUC cannot be reliably calculated within Snowflake due to limitations in SQL functionality for statistical analysis.

Using only SQL, Create a temporary table with calculated True Positive Rate (TPR) and False Positive Rate (FPR) at different probability thresholds. Then, approximate the AUC using the trapezoidal rule.

Export the ‘predicted_probability’ and ‘actual_value’ columns to a local Python environment and calculate the AUC using scikit-learn.

The best way to calculate AUC is to randomly guess the probabilities and see how it performs.

NO.90 You are using Snowpark for Python to perform feature engineering on a large dataset stored in a Snowflake table named ‘transactions’. You need to create a new feature called ‘transaction_size category’ based on the ‘transaction_amount’ column. The categories are defined as follows: Small (amount < 10), Medium (10 <= amount < 100), and Large (amount 100). You want to optimize performance by leveraging Snowflake’s parallel processing capabilities. Which of the following Snowpark for Python code snippets is the MOST efficient and Pythonic way to achieve this?

NO.91 You have trained a logistic regression model in Python using scikit-learn and plan to deploy it as a Python stored procedure in Snowflake. You need to serialize the model for deployment. Consider the following code snippet:

The code will execute successfully. The model serialization and deserialization using pickle are correctly implemented within the stored procedure.

The code will fail because the ‘model_bytes’ variable is not accessible within the ‘predict’ function’s scope.

The code will fail because it does not handle potential security vulnerabilities associated with deserializing pickled objects from untrusted sources.

The code will fail because Snowflake stages cannot be used to store model objects.

NO.92 You are tasked with developing a multi-class image classification model to categorize product images stored in Snowflake external stage. The categories are ‘Electronics’, ‘Clothing’, ‘Furniture’, ‘Books’, and ‘Food’. You plan to use a pre-trained Convolutional Neural Network (CNN) model and fine-tune it using your dataset. However, you’re facing challenges in efficiently loading and preprocessing the image data within the Snowflake environment before feeding it to your model. Which of the following approaches would be MOST efficient for image data loading and preprocessing in Snowflake, minimizing data movement and leveraging Snowflake’s scalability, for a large dataset exceeding 1 TB of images?

Write a Python User-Defined Function (UDF) that loads each image from the external stage directly into memory, performs preprocessing (resizing, normalization), and returns the processed image data. The UDF is then called in a SQL query to process the image data.

Use Snowflake’s Snowpark to read images from the external stage into a Snowpark DataFrame. Then, implement image preprocessing using Snowpark DataFrame operations, such as resizing and normalization, within the DataFrame transformations before sending the data to the model.

Download all the images from the external stage to a local machine, preprocess them using a standard Python library like OpenCV, and then upload the processed data back into Snowflake as a table for model training.

Create a Snowflake Stream to continuously ingest new images into a Snowflake table. Use a task to periodically trigger a Python UDF that preprocesses the newly ingested images and stores them in another table for model training.

Utilize Snowflake’s external function integration with AWS Lambda to preprocess images as they are uploaded to S3, storing the preprocessed data back in S3 and creating an external table pointing to the preprocessed data.

NO.93 You are building a machine learning pipeline in Snowflake using Snowpark Python. You have completed the data preparation and feature engineering steps and now need to train a model. You want to track the performance of different model versions and hyperparameters using MLflow. You are considering these deployment strategies. Which of the deployment strategies allows automatic logging of metrics, parameters, and model artifacts to MLflow for each training run without requiring explicit MLflow logging code?

Train the model within a Snowpark Python UDF. Use a Snowflake stage to store MLflow artifacts.

Train the model within a Snowpark Python stored procedure. Use a Snowflake stage to store MLflow artifacts.

Train the model locally on your development machine and manually log metrics and artifacts to MLflow using the MLflow API. Then, deploy the trained model to Snowflake as a UDF or stored procedure.

Use the Snowpark MLAPI and its integration with MLflow’s autologging feature. Enable autologging before starting the training run. Deploy the model to Snowflake as a UDF.

Train the model using Snowpark’s DataFrame API directly in a Snowflake worksheet. Manually create a log file with metrics and model parameters and upload it to a Snowflake stage.

NO.94 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)

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).

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.

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.

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.

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.

NO.95 You are building a data science pipeline in Snowflake to predict customer churn. The pipeline involves extracting data, transforming it using Dynamic Tables, training a model using Snowpark ML, and deploying the model for inference. The raw data arrives in a Snowflake stage daily as Parquet files. You want to optimize the pipeline for cost and performance. Which of the following strategies are MOST effective, considering resource utilization and potential data staleness?

Use a single, large Dynamic Table to perform all transformations in one step, relying on Snowflake’s optimization to handle dependencies and incremental updates.

Implement a series of smaller Dynamic Tables, each responsible for a specific transformation step, with well-defined refresh intervals tailored to the data’s volatility and the downstream model’s requirements.

Load all data into traditional Snowflake tables and use scheduled tasks with stored procedures written in Python to perform the transformations and model training.

Use a combination of Dynamic Tables for feature engineering and Snowpark ML for model training and deployment, ensuring proper dependency management and refresh intervals for each Dynamic Table based on data freshness requirements.

Schedule all data transformations and model training as a single large Snowpark Python script executed by a Snowflake task, ignoring data freshness requirements.

NO.96 A marketing analyst at ‘NovaRetail’ suspects that a new advertising campaign has increased the average purchase amount. They have historical purchase data in a Snowflake table called ‘purchase_historf. To validate their hypothesis using the Central Limit Theorem (CLT), they perform the following steps: 1. Calculate the population mean (?) of purchase amounts from the historical data’. 2. Draw 500 random samples of size 50 from the table. 3. Calculate the sample mean (x?) for each sample. Which of the following steps are essential for correctly applying the Central Limit Theorem to perform a z-test to determine whether the new advertising campaign has significantly increased the average purchase amount?

Calculate the standard deviation of the population (?) from the historical data and estimate the standard error of the mean as ? / sqrt(50).

Check if the original population distribution (purchase amounts) is approximately normally distributed.

Ensure that the samples are drawn independently and randomly.

Calculate the standard deviation of the sample means and use it as an estimate for the standard error of the mean.

Verify that the sample size (n=50) is sufficiently large to approximate normality of the sample mean distribution based on the CLT. This implicitly assumes population size is significantly larger than the sample size.

NO.97 You are tasked with validating a regression model predicting customer lifetime value (CLTV). The model uses various customer attributes, including purchase history, demographics, and website activity, stored in a Snowflake table called ‘CUSTOMER DATA. You want to assess the model’s calibration specifically, whether the predicted CLTV values align with the actual observed CLTV values over time. Which of the following evaluation techniques would be MOST suitable for assessing the calibration of your CLTV regression model in Snowflake?

Calculate the Mean Absolute Error (MAE) and Root Mean Squared Error (RMSE) on a hold-out test set to quantify the overall prediction accuracy.

Create a calibration curve (also known as a reliability diagram) by binning the predicted CLTV values, calculating the average predicted CLTV and the average actual CLTV within each bin, and plotting these averages against each other.

Evaluate the model’s residuals by plotting them against the predicted values and checking for patterns or heteroscedasticity.

Calculate the R-squared score on a hold-out test set to assess the proportion of variance in the actual CLTV explained by the model.

Conduct a Kolmogorov-Smirnov test to check the distribution of predicted and actual value.

NO.98 You are using Snowpark for Python to build a feature engineering pipeline for a machine learning model that predicts customer churn. The data is stored in a Snowflake table called ‘CUSTOMER DATA’ , and you want to create new features based on time-series data within the table. You need to calculate the ‘Recency’ feature (days since the last transaction) and ‘Frequency’ feature (number of transactions in the last 3 months). Considering performance and best practices, which Snowpark approach would you choose?

Fetch the entire ‘CUSTOMER DATA table into a Pandas DataFrame using , then use Pandas’ time-series functions to calculate ‘Recency’ and ‘Frequency’. After feature engineering, load the Pandas DataFrame back into Snowflake.

Use Snowpark DataFrame API to perform window functions within Snowflake to calculate ‘Recency’ and ‘Frequency’ directly, leveraging Snowflake’s processing power without transferring data to the client.

Create a Python UDF using Pandas to calculate ‘Recency’ and ‘Frequency’. Apply this UDF to the ‘CUSTOMER DATA’ table through Snowpark, processing the data row by row.

Write a stored procedure in SQL that calculates ‘Recency’ and ‘Frequency’ using SQL window functions, and then call this stored procedure from your Snowpark Python code.

Write custom Python code in a Snowpark UDF to retrieve each transaction for a customer and calculate recency and frequency directly in Python without pandas.

NO.99 You’re working with a large dataset of user transactions in Snowflake. You need to identify potential outliers in transaction amounts C TRANSACTION AMOUNT) for each user CUSER ID’). Your goal is to flag transactions that are more than 3 standard deviations away from the mean transaction amount for that specific user. Which of the following approaches, utilizing Snowflake’s statistical functions and window functions, would be MOST efficient and accurate for achieving this?

Using a correlated subquery to calculate the mean and standard deviation for each user and then filtering the transactions.

Calculating the overall mean and standard deviation for all transactions and filtering transactions based on those global statistics.

Using window functions to calculate the mean and standard deviation for each user within the same query, and then comparing each transaction amount to the calculated range.

Exporting the data to a Python environment, performing the calculations using Pandas, and then re-importing the results to Snowflake.

Creating a stored procedure that iterates through each user and calculates the mean and standard deviation individually.

NO.100 You are analyzing website traffic data stored in a Snowflake table named ‘WEB EVENTS. This table contains a ‘TIMESTAMP’ column representing when the event occurred and a ‘PAGE VIEWS column indicating the number of page views for that event. You need to identify the day with the highest number of page views and also the day with lowest number of page views along with average number of page views. How can you accomplish this using Snowflake SQL?

Option A

Option B

Option C

Option D

Option E

NO.101 You are developing a machine learning model using scikit-learn within Visual Studio Code (VS Code) and connecting directly to Snowflake to access a large dataset. You need to authenticate to Snowflake using Key Pair Authentication, but want to avoid storing the private key directly within your VS Code project or environment variables for security reasons. Which of the following approaches offers the MOST secure way to manage and access the private key for Snowflake authentication from VS Code?

Store the encrypted private key in a configuration file within your VS Code project and decrypt it at runtime using a password-based encryption algorithm.

Store the private key in a secure vault (e.g., HashiCorp Vault, AWS Secrets Manager, Azure Key Vault) and retrieve it dynamically within your VS Code script using the appropriate API or SDK.

Store the private key in a password-protected ZIP archive and extract it during the Snowflake connection process.

Use the Snowflake CLI to generate a temporary access token and hardcode it into your VS Code script for authentication.

Store the private key in a secure database table within Snowflake and query it dynamically.