Study with DSA-C03 Actual Test Questions

You are exploring a large dataset of website user behavior in Snowflake to identify patterns and potential features for a machine learning model predicting user engagement. You want to create a visualization showing the distribution of 'session_duration' for different 'user_segments'. The 'user_segmentS column contains categorical values like 'New', 'Returning', and 'Power User'. Which Snowflake SQL query and subsequent data visualization technique would be most effective for this task?

• A. Query: 'SELECT user_segments, AVG(session_duration) FROM user_behavior GROUP BY Visualization: Bar chart showing average session duration for each user segment.
• B. Query: ' SELECT COUNT( ) ,user_segments FROM user_behavior GROUP BY user_segments;' Visualization: Pie chart showing proportion of each segment.
• C. Query: 'SELECT user_segments, APPROX 0.25), APPROX 0.5), APPROX_PERCENTlLE(session_duration, 0.75) FROM user_behavior GROUP BY user_segments;' Visualization: Scatter plot where each point represents a user segment and the x,y coordinates represent session duration at 25th and 75th percentiles respectively.
• D. Query: 'SELECT user_segments, MEDIAN(session_duration) FROM user_behavior GROUP BY user_segments;' Visualization: Box plot showing the distribution (quartiles, median, outliers) of session duration for each user segment.
• E. Query: 'SELECT session_duration FROM user_behavior WHERE user_segments = 'New';- (repeated for each user segment). Visualization: Overlayed histograms showing the distribution of session duration for each user segment on the same axes.

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?

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

You are tasked with identifying Personally Identifiable Information (PII) within a Snowflake table named 'customer data'. This table contains various columns, some of which may contain sensitive information like email addresses and phone numbers. You want to use Snowflake's data governance features to tag these columns appropriately. Which of the following approaches is the MOST effective and secure way to automatically identify and tag potential PII columns with the 'PII CLASSIFIED tag in your Snowflake environment, ensuring minimal manual intervention and optimal accuracy?

• A. Export the 'customer_data' to a staging area in cloud storage, use a third-party data discovery tool to scan for PII, and then manually apply the "PII_CLASSIFIED' tag to the corresponding columns in Snowflake based on the tool's findings.
• B. Create a custom Snowpark for Python UDF that uses regular expressions to analyze the data in each column and apply the 'PII_CLASSIFIED tag if a match is found. Schedule this UDF to run periodically using Snowflake Tasks.
• C. Use Snowflake's built-in classification feature with a pre-defined sensitivity category to identify potential PII columns. Associate a masking policy that redacts the data, and apply a tag 'PII_CLASSIFIED' via automated tagging to the columns identified as containing PII.
• D. Write a SQL script to query the 'INFORMATION SCHEMA.COLUMNS' view, identify columns with names containing keywords like 'email' or 'phone', and then apply the 'PII_CLASSIFIED tag to those columns.
• E. Manually inspect each column in the 'customer_data' table and apply the 'PII_CLASSIFIED' tag to columns that appear to contain PII based on their names and a small sample of data.

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?

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

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?

• A. 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.
• B. Use a simple followed by a UDF to calculate the rolling average. Fill in missing dates manually within the UDF.
• C. 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.
• D. 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.
• E. 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.