Snowflake Certified DSA-C03 Dumps Questions Valid DSA-C03 Materials [Q108-Q130]

Q108. You are working with a dataset containing timestamps representing website user activity. The timestamps are stored as strings in the format ‘YYYY-MM-DD HH:MI:SS.SSSSSS’ in a Snowflake table named ‘website_activity’. You need to extract the hour of the day from these timestamps and encode it as a cyclical feature using sine and cosine transformations. This is to capture the cyclical nature of user activity throughout the day (e.g., 23:00 and 00:00 are close in time). Which of the following Snowflake SQL code snippets correctly implements this cyclical encoding and creates the ‘hour_sin’ and ‘hour_cos’ columns?

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

Q110. You are building a fraud detection model using transaction data stored in Snowflake. The dataset includes features like transaction amount, merchant category, location, and time. Due to regulatory requirements, you need to ensure personally identifiable information (PII) is handled securely and compliantly during the data collection and preprocessing phases. Which of the following combinations of Snowflake features and techniques would be MOST suitable for achieving this goal?

Use Snowflake’s masking policies to redact PII columns before any data is accessed for model training. Ensure role-based access control is configured so that only authorized personnel can access the unmasked data for specific purposes.

Create a view that selects only the non-PII columns for model training. Grant access to this view to the data science team.

Encrypt the entire database containing the transaction data to protect PII from unauthorized access.

Use Snowflake’s data sharing capabilities to share the transaction data with a third-party machine learning platform for model development, without any PII masking or redaction.

Apply differential privacy techniques on aggregated data derived from the transaction data, before using it for model training. Combine this with Snowflake’s row access policies to restrict access to sensitive transaction records based on user roles and data attributes.

Q111. A data scientist is analyzing sales data in Snowflake to identify seasonal trends. The ‘SALES TABLE’ contains columns ‘SALE DATE’ (DATE) and ‘SALE _ AMOUNT’ (NUMBER). They want to calculate the average daily sales amount for each month and year in the dataset. Which of the following SQL queries will correctly achieve this, while also handling potential NULL values in ‘SALE AMOUNT?

Option A

Option B

Option C

Option D

Option E

Q112. You are a data scientist working with a Snowflake table named ‘CUSTOMER DATA’ that contains a ‘PHONE NUMBER’ column stored as VARCHAR. The ‘PHONE NUMBER’ column sometimes contains non-numeric characters like hyphens and parentheses, and in some rows the data is missing. You need to create a new table ‘CLEANED CUSTOMER DATA’ with a column named ‘CLEANED PHONE NUMBER that contains only the numeric part of the phone number (as VARCHAR) and replaces missing or invalid phone numbers with NULL. Which of the following Snowpark Python code snippets achieves this most efficiently, ensuring no errors occur during the data transformation, and considers Snowflake’s performance best practices?

Option A

Option B

Option C

Option D

Option E

Q113. You are building a predictive model for customer churn using linear regression in Snowflake. You have identified several features, including ‘CUSTOMER AGE’, ‘MONTHLY SPEND’, and ‘NUM CALLS’. After performing an initial linear regression, you suspect that the relationship between ‘CUSTOMER AGE and churn is not linear and that older customers might churn at a different rate than younger customers. You want to introduce a polynomial feature of “CUSTOMER AGE (specifically, ‘CUSTOMER AGE SQUARED’) to your regression model within Snowflake SQL before further analysis with python and Snowpark. How can you BEST create this new feature in a robust and maintainable way directly within Snowflake?

Option A

Option B

Option C

Option D

Option E

Q114. You are developing a churn prediction model using Snowpark Python and Scikit-learn. After initial model training, you observe significant overfitting. Which of the following hyperparameter tuning strategies and code snippets, when implemented within a Snowflake Python UDF, would be MOST effective to address overfitting in a Ridge Regression model and how can you implement a reproducible model with minimal code?

Option A

Option B

Option C

Option D

Option E

Q115. You are performing exploratory data analysis on a large sales dataset in Snowflake using Snowpark. The dataset contains columns such as ‘order_id’, , and ‘profit’. You want to identify the top 5 most profitable products for each month. You have already created a Snowpark DataFrame named ‘sales_df. Which of the following Snowpark operations, when combined correctly, will efficiently achieve this?

Group by and ‘product_id’ , aggregate ‘sum(profit)’ , then use partitioned by ordered by ‘sum(profit) DESC’.

Use ‘rank()’ partitioned by ordered by ‘sum(profit) DESC’ , after grouping by and ‘product_id’ , and aggregating ‘sum(profity.

First, create a temporary table with aggregated monthly profit for each product using SQL. Then, use Snowpark to read the temporary table and apply a window function partitioned by ordered by ‘sum(profit) DESC’.

Group by ‘product_id’, aggregate ‘sum(profity, then use partitioned by ordered by ‘sum(profit) DESC’ within a UDF.

Use ‘ntile(5)’ partitioned by ordered by ‘sum(profit) DESC’ after grouping by and ‘product_id’, and aggregating ‘sum(profit)’.

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

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

Q118. A marketing analyst is building a propensity model to predict customer response to a new product launch. The dataset contains a ‘City’ column with a large number of unique city names. Applying one-hot encoding to this feature would result in a very high-dimensional dataset, potentially leading to the curse of dimensionality. To mitigate this, the analyst decides to combine Label Encoding followed by binarization techniques. Which of the following statements are TRUE regarding the benefits and challenges of this combined approach in Snowflake compared to simply label encoding?

Label encoding followed by binarization will reduce the memory required to store the ‘City’ feature compared to one-hot encoding, and Snowflake’s columnar storage optimizes storage for integer data types used in label encoding.

Label encoding introduces an arbitrary ordinal relationship between the cities, which may not be appropriate. Binarization alone cannot remove this artifact.

While label encoding itself adds an ordinal relationship, applying binarization techniques like binary encoding (converting the label to binary representation and splitting into multiple columns) after label encoding will remove the arbitrary ordinal relationship.

Binarizing a label encoded column using a simple threshold (e.g., creating a ‘high_city_id’ flag) addresses the curse of dimensionality by reducing the number of features to one, but it loses significant information about the individual cities.

Binarization following label encoding may enhance model performance if a specific split based on a defined threshold is meaningful for the target variable (e.g., distinguishing between cities above/below a certain average income level related to marketing success).

Q119. You are building a binary classification model in Snowflake to predict customer churn based on historical customer data, including demographics, purchase history, and engagement metrics. You are using the SNOWFLAKE.ML.ANOMALY package. You notice a significant class imbalance, with churn representing only 5% of your dataset. Which of the following techniques is LEAST appropriate to handle this class imbalance effectively within the SNOWFLAKE.ML framework for structured data and to improve the model’s performance on the minority (churn) class?

Using the ‘sample_weight’ parameter in the ‘SNOWFLAKE.ML.ANOMALY.FIT function to assign higher weights to the minority class instances during model training.

Applying a SMOTE (Synthetic Minority Over-sampling Technique) or similar oversampling technique to generate synthetic samples of the minority class before training the model outside of Snowflake, and then loading the augmented data into Snowflake for model training.

Adjusting the decision threshold of the trained model to optimize for a specific metric, such as precision or recall, using a validation set. This can be done by examining the probability outputs and choosing a threshold that maximizes the desired balance.

Downsampling the majority class to create a more balanced training dataset within Snowflake using SQL before feeding the data to the modeling function.

Using a clustering algorithm (e.g., K-Means) on the features and then training a separate binary classification model for each cluster to capture potentially different patterns of churn within different customer segments.

Q120. You are deploying a large language model (LLM) to Snowflake using a user-defined function (UDF). The LLM’s model file, ’11m model.pt’, is quite large (5GB). You’ve staged the file to Which of the following strategies should you employ to ensure successful deployment and efficient inference within Snowflake? Select all that apply.

Use the ‘PUT’ command with to compress the model file before staging it. Snowflake will automatically decompress it during UDF execution.

Increase the warehouse size to XLARGE or larger to provide sufficient memory for loading the large model into the UDF environment.

Leverage Snowflake’s Snowpark Container Services to deploy the LLM as a separate containerized application and expose it via a Snowpark API. Then call that endpoint from snowflake.

Use the ‘IMPORTS’ clause in the UDF definition to reference Ensure the UDF code loads the model lazily (i.e., only when it’s first needed) to minimize startup time and memory usage.

Split the large model file into smaller chunks and stage each chunk separately. Reassemble the model within the UDF code before inference.

Q121. You are tasked with preparing a Snowflake table named ‘PRODUCT REVIEWS’ for sentiment analysis. This table contains columns like ‘REVIEW ID, ‘PRODUCT ID’, ‘REVIEW TEXT’, ‘RATING’, and ‘TIMESTAMP’. Your goal is to remove irrelevant fields to optimize model training. Which of the following options represent valid and effective strategies, using Snowpark SQL, for identifying and removing irrelevant or problematic fields from the ‘PRODUCT REVIEWS’ table, considering both storage efficiency and model accuracy? Assume that the model only need review text and review id and the rating.

Using ‘ALTER TABLE DROP COLUMN’ to directly remove ‘TIMESTAMP column, which is deemed irrelevant for the sentiment analysis model. SQL: ‘ALTER TABLE PRODUCT REVIEWS DROP COLUMN TIMESTAMP;’

Creating a VIEW that only selects the ‘REVIEW _ TEXT , ‘REVIEW_ID’, and ‘RATING’ columns, effectively hiding the irrelevant columns from the model. SQL: ‘CREATE OR REPLACE VIEW REVIEWS FOR ANALYSIS AS SELECT REVIEW TEXT, REVIEW ID, RATING FROM PRODUCT REVIEWS;’

creating a new table ‘REVIEWS_CLEANED containing only the relevant columns CREVIEW_TEXT , ‘REVIEW_ID’ , and ‘RATING’) using ‘CREATE TABLE AS SELECT. SQL: ‘CREATE OR REPLACE TABLE REVIEWS CLEANED AS SELECT REVIEW TEXT, REVIEW ID, RATING FROM PRODUCT REVIEWS;’

Dropping rows with ‘NULL’ values in REVIEW_TEXT and then dropping the ‘PRODUCT_ID’ and ‘TIMESTAMP’ columns using ‘ALTER TABLE. SQL: ‘CREATE OR REPLACE TABLE PRODUCT REVIEWS AS SELECT FROM PRODUCT REVIEWS WHERE REVIEW TEXT IS NOT NULL; ALTER TABLE PRODUCT REVIEWS DROP COLUMN PRODUCT ID; ALTER TABLE PRODUCT REVIEWS DROP COLUMN TIMESTAMP;’

All of the above.

Q122. A data engineer is tasked with removing duplicates from a table named ‘USER ACTIVITY’ in Snowflake, which contains user activity logs. The table has columns: ‘ACTIVITY TIMESTAMP’, ‘ACTIVITY TYPE’, and ‘DEVICE_ID. The data engineer wants to remove duplicate rows, considering only ‘USER ID’, ‘ACTIVITY TYPE, and ‘DEVICE_ID’ columns. What is the most efficient and correct SQL query to achieve this while retaining only the earliest ‘ACTIVITY TIMESTAMP’ for each unique combination of the specified columns?

Option A

Option B

Option C

Option D

Option E

Q123. You are building a machine learning pipeline that uses data stored in Snowflake. You want to connect a Jupyter Notebook running on your local machine to Snowflake using Snowpark. You need to securely authenticate to Snowflake and ensure that you are using a dedicated compute resource for your Snowpark session. Which of the following approaches is the MOST secure and efficient way to achieve this?

Store your Snowflake username and password directly in the Jupyter Notebook and create a Snowpark session using these credentials and the default Snowflake warehouse.

Use the Snowflake Python connector with username and password and execute SQL commands to create a Snowpark DataFrame.

Configure OAuth authentication for your Snowflake account and use the OAuth token to establish a Snowpark session with a dedicated virtual warehouse.

Use key pair authentication to connect to Snowflake, storing the private key securely on your local machine. Specify a dedicated virtual warehouse during session creation.

Hardcode a role with ‘ACCOUNTADMIN’ privileges in your Jupyter Notebook using username and password.

Q124. A data scientist is exploring customer purchase data in Snowflake to identify high-value customer segments. They have a table named ‘CUSTOMER TRANSACTIONS with columns ‘CUSTOMER ID’, ‘TRANSACTION_DATE’, and ‘PURCHASE_AMOUNT’. They want to calculate the interquartile range (IQR) of ‘PURCHASE AMOUNT for each customer. Which SQL query using Snowsight is the most efficient and accurate way to calculate and display the IQR for each ‘CUSTOMER ID?

Option A

Option B

Option C

Option D

Option E

Q125. You are tasked with forecasting the daily sales of a specific product for the next 30 days using Snowflake. You have historical sales data for the past 3 years, stored in a Snowflake table named ‘SALES DATA’, with columns ‘SALE DATE (DATE type) and ‘SALES AMOUNT’ (NUMBER type). You want to use the Prophet library within a Snowflake User-Defined Function (UDF) for forecasting. The Prophet model requires the input data to have columns named ‘ds’ (for dates) and ‘y’ (for values). Which of the following code snippets demonstrates the CORRECT way to prepare and pass your data to the Prophet UDF in Snowflake, assuming you’ve already created the Python UDF ‘prophet_forecast’?

Q126. You are working with a large sales transaction dataset in Snowflake, stored in a table named ‘SALES DATA’. This table contains columns such as ‘TRANSACTION_ID (unique identifier), ‘CUSTOMER_ID’, ‘PRODUCT_ID, ‘TRANSACTION_DATE’ , and ‘AMOUNT’. Due to a system error, some transactions were duplicated in the table. Your goal is to remove these duplicates efficiently using Snowpark for Python. You want to use the ‘window.partitionBy()’ and functions. Which of the following code snippets correctly removes duplicates based on all columns, while also creating a new column ‘ROW NUM’ to indicate the row number within each partition?

Q127. You are developing a model to predict equipment failure in a factory using sensor data stored in Snowflake. The data is partitioned by ‘EQUIPMENT ID’ and ‘TIMESTAMP. After initial model training and cross-validation using the following code snippet:

You observe significant performance variations across different equipment groups when evaluating on out-of-sample data’. Which of the following strategies could you employ to address this issue within the Snowflake environment to improve the model’s generalization ability across all equipment?

Increase the overall size of the “TRAINING_DATR to include more historical data for all equipment, assuming this will balance the representation of each EQUIPMENT ID’

Implement a hyperparameter search using ‘SYSTEM$OPTIMIZE_MODEL’ with a wider range of parameters for each ‘EQUIPMENT_ID individually, creating a separate model for each ‘EQUIPMENT ID.

Retrain the model with additional feature engineering to create interaction terms between ‘EQUIPMENT_ID’ and other relevant sensor features to capture equipment-specific patterns. For instance, you can one hot encode and add to model and include in ‘INPUT DATA’.

Implement cross-validation at the partition level by splitting ‘TRAINING_DATX into train and test sets before creating the model, and then using the ‘FIT’ command to train on the train set and ‘PREDICT to evaluate on the test set, repeating for each partition.

Create seperate models per equipment ID. For each equipment ID, split data into training and testing data. For each equipment ID, use ‘SYSTEM$OPTIMIZE MODEL’ to perform hyper parameter search individually. Train and Deploy the model at equipement ID Level.

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

Q129. You are deploying a fraud detection model using Snowpark Container Services. The model requires a substantial amount of GPU memory. After deploying your service, you notice that it frequently crashes due to Out-Of-Memory (OOM) errors. You have verified that the container image itself is not the source of the problem. Which of the following strategies are most appropriate to mitigate these OOM errors when using Snowpark Container Services, assuming you want to minimize costs and complexity?

Increase the ‘container.resources.memory’ configuration setting in the service definition to a value significantly larger than the model’s memory footprint. Monitor memory utilization and adjust as needed.

Implement model parallelism across multiple containers, splitting the model’s workload and data across them. Configure each container with a smaller ‘container.resources.memory’ allocation.

Utilize CPU-based inference instead of GPU-based inference, as CPU inference is generally less memory-intensive. Convert the model to a format optimized for CPU inference (e.g., using ONNX). Reduce the ‘container.resources.cpu’ count.

Implement a mechanism within your model’s inference code to explicitly free up unused memory after each prediction. Use Python’s ‘gc.collect()’ and ensure proper cleanup of large data structures. Configure a smaller ‘container.resources.memory’ allocation.

Ignore OOM errors and rely on the container service to automatically restart the container. The model will eventually process all requests.

Q130. 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?