Get Ready with DAA-C01 Exam Dumps (2025) [Q119-Q135]

NEW QUESTION 119
You have a Snowflake table ‘RAW DATA containing a ‘VARIANT column called ‘json_data’. This column stores JSON objects representing customer orders. The structure includes a nested array of items within each order. You need to create a flattened table called ‘ORDER ITEMS with the following columns: ‘order_id’, and However, the field is not directly present in the JSON data’. Instead, it needs to be derived by concatenating the ‘order_id’ with the index (ordinal position) of the item within the ‘items’ array. The structure looks like this: { “order_id”: “ORD-123”, “customer_id”: “CUST-456”, “items”: [ { “item_name”: “Laptop”, “item_price”: 1200 }, { “item_name”: “Mouse”, “item_price”: 25 } ] } Which of the following SQL statements correctly creates the ‘ORDER ITEMS table?

Option A

Option B

Option C

Option D

Option E

NEW QUESTION 120
You’re designing a data pipeline in Snowflake to process order data’. The raw order data, including customer information, is stored in a JSON format within a single ‘RAW ORDERS table. Due to privacy regulations, you need to mask the customer’s email addresses before loading the data into a ‘CLEANED ORDERS’ table, while maintaining referential integrity. Furthermore, you want to track the data lineage (which raw order resulted in which cleaned order) in a separate ‘ORDER LINEAGE’ table. Which of the following approaches achieves these requirements effectively and efficiently? (Select TWO)

Create a masking policy on the ’email’ column of the ‘RAW_ORDERS’ table. Then, create a task to copy all data from ‘RAW_ORDERS to ‘CLEANED_ORDERS. Finally, create a separate task that runs after the copying task to populate the ‘ORDER_LINEAGE table with relevant mappings.

Implement a stored procedure that reads data from ‘RAW ORDERS, masks the email using SHA256, inserts the cleaned data into ‘CLEANED ORDERS’, and simultaneously inserts lineage information into ‘ORDER LINEAGE. Schedule this stored procedure to run periodically.

Use a Snowpipe to load data into ‘RAW ORDERS. Create a stream on ‘RAW ORDERS’. Create a task chained to the stream that reads the new records from the stream, masks the email, inserts the cleaned data into ‘CLEANED_ORDERS’ table and inserts lineage information into the ‘ORDER _ LINEAGE table.

Create a masking policy on the ’email’ column in ‘CLEANED ORDERS’. Create a stream on RAW ORDERS, then create a task to insert data from ‘RAW ORDERS’ into ‘CLEANED ORDERS’ and to INSERT the order to ‘ORDER LINEAGE

Create a view on the ‘RAW ORDERS table that masks the email address. Load the raw order data into ‘RAW ORDERS table using Snowpipe and a stream. Use a task chained to the stream to ingest the view into ‘CLEANED_ORDERS and populate lineage information into ‘ORDER_LINEAGE table.

NEW QUESTION 121
Your team is building a data pipeline to ingest data from a REST API that returns JSON payloads. Due to API rate limits, you need to implement a backoff strategy to avoid overwhelming the API. You are using Python and the ‘requests’ library for data ingestion. Which code snippet BEST demonstrates a robust backoff strategy with exponential backoff and jitter?

NEW QUESTION 122
You have a CSV file loaded into a Snowflake table named ‘raw data’. The file contains customer order data, but some rows have missing values in the ‘order date’ column. You need to create a new table, ‘cleaned data’ , that contains only valid records and handles missing ‘order date’ values by substituting them with the date ‘1900-01-01’. Which of the following approaches is the MOST efficient and correct way to achieve this using Snowflake features?

NEW QUESTION 123
You are building a dashboard to monitor website traffic. You have the following requirements: 1. Display the number of unique visitors per day. 2. Allow users to filter the data by device type (desktop, mobile, tablet). 3. Show a trend line of unique visitors over time. 4. The dashboard must refresh every 15 minutes with the latest data,. 5. The dashboard must be performant even with a large volume of dat a. Given the following table definition:

Which of the following approaches would be the MOST efficient and scalable solution in Snowflake? Select all that apply.

Create a materialized view to pre-aggregate the number of unique visitors per day and device type. Set up a Snowflake task to refresh the materialized view every 15 minutes. The dashboard queries the materialized view.

Use a Snowflake stream to capture changes to the ‘website_traffic’ table. Create a task to process the stream every 15 minutes and update a summary table with the number of unique visitors per day and device type. The dashboard queries the summary table.

Create a standard Snowflake view that calculates the number of unique visitors per day and device type. The dashboard queries the view directly, filtering by device type. No task or stream is used.

Use the dashboard tool’s built-in data transformation capabilities to calculate the number of unique visitors per day and device type on the fly, directly from the ‘website traffic’ table.

Create a stored procedure to calculate the number of unique visitors per day and device type. Schedule the stored procedure to run every 15 minutes and update a table. The dashboard queries this table.

NEW QUESTION 124
You have a table named ‘event_data’ that tracks user activities. The table contains ‘event_id’ (INT), ‘user _ id’ (INT), (TIMESTAMP NTZ), ‘event_type’ (VARCHAR), and ‘event_details’ (VARIANT). The table is partitioned by Performance on queries filtering by both ‘event_type’ and a specific date range on is slow You suspect inefficient partition pruning and JSON parsing as potential bottlenecks. Which combination of actions will most effectively address these performance issues?

Create a view that extracts specific fields from the ‘event_details’ column into separate columns and add a secondary index on ‘event_type’ .

Create a materialized view partitioned by and clustered by ‘event_type’ , pre-extracting relevant fields from ‘event_detailS into separate columns.

Change the partition key to ‘event_type’ and create a table function to query sevent_detailss.

Add a masking policy on the ‘event_details’ column and recluster the table by ‘user_id’.

Create a temporary table containing the results and then performing a Merge operation.

NEW QUESTION 125
You are tasked with retrieving data from a source system that outputs a large stream of semi-structured JSON data’. The data contains nested arrays and deeply nested objects. The data needs to be transformed before being loaded into Snowflake to flatten the structure and extract relevant fields. Which approach is most efficient and scalable for retrieving and preparing this data?

Use a Snowpipe with a SnowSQL transformation script to flatten and extract the required fields during the COPY INTO process.

Load the raw JSON data into a VARIANT column in Snowflake using COPY INTO, then use Snowflake’s JSON functions to transform the data within a VIEW.

Implement a data pipeline using an external stream processing framework (e.g., Apache Kafka, AWS Kinesis) to transform and flatten the JSON data before loading it into Snowflake using Snowpipe.

Create a Snowpark Python UDF to parse and flatten the JSON data during the COPY INTO process. Pass each JSON record as input to the UDF.

Create an external table pointing to the source data and query it directly. Use Snowflake’s JSON functions in the query to extract the relevant fields.

NEW QUESTION 126
You have a stored procedure written in Python within Snowflake that needs to process a large dataset. The procedure’s performance is critical, and you want to optimize it for speed. Which of the following strategies would be MOST effective in improving the performance of your Python stored procedure when dealing with large datasets in Snowflake? (Select TWO)

Using the Snowflake Python Connector’s ‘execute_stream’ method to stream data in chunks instead of loading the entire dataset into memory at once. This reduces memory consumption and improves performance, especially for very large datasets.

Converting the Python stored procedure to a SQL stored procedure, as SQL is generally faster for data manipulation tasks within Snowflake due to its optimized query engine.

Leveraging Pandas DataFrames within the Python stored procedure to perform data manipulation tasks in a vectorized manner. Pandas provides efficient data structures and functions for data processing, which can significantly improve performance compared to using standard Python loops.

Increasing the warehouse size for the Snowflake account where the stored procedure is running. A larger warehouse provides more compute resources, which can speed up data processing tasks, including Python stored procedures.

Replacing all Python loops with recursive SQL queries to improve performance

NEW QUESTION 127
A financial institution uses Snowflake to store customer transaction data’. They need to create a dashboard that visualizes daily transaction volume and average transaction amount for fraud detection purposes. This dashboard needs to be automatically updated every hour. The current dashboard query performance is slow, especially during peak hours. Given that the ‘TRANSACTIONS table contains billions of rows, which of the following strategies would BEST optimize both the query performance and the automated update process?

Create a materialized view that pre-aggregates the daily transaction volume and average transaction amount. Schedule a Snowflake task to refresh the materialized view hourly.

Create a regular view that calculates daily transaction volume and average transaction amount. Use a Snowflake stored procedure to execute the query and update a separate reporting table hourly.

Increase the warehouse size to X-Large and rely on Snowflake’s query optimization engine without any changes to the data model or update process.

Create a temporary table that stores daily transaction summaries. Truncate and reload the temporary table hourly using a scheduled Snowflake task.

Implement caching within the dashboard application to store the query results and only refresh the data once a day to avoid performance issues

NEW QUESTION 128
You are tasked with analyzing website clickstream data stored in a Snowflake table named ‘clickstream_data’. The table contains a ‘variant’ column named ‘event data’ that holds JSON data representing various events like ‘page view’, ‘button click’, and ‘form submission’. You need to extract the ‘page_url’ from ‘page_view’ events, the ‘button id’ from ‘button click’ events, and the ‘form id’ from ‘form submission’ events. Design the most efficient Snowflake SQL query to achieve this, considering the performance implications of variant parsing and filtering. Assume the ‘event_type’ is directly available within the variant object as well. Which of the following queries would be most efficient?

Option A

Option B

Option C

Option D

Option E

NEW QUESTION 129
You are using Snowpipe to continuously load data from an external stage (AWS S3) into a Snowflake table named ‘RAW DATA. You notice that the pipe is frequently encountering errors due to invalid data formats in the incoming files. You need to implement a robust error handling mechanism that captures the problematic records for further analysis without halting the pipe’s operation. Which of the following approaches is the MOST effective and Snowflake-recommended method to achieve this?

Implement a custom error logging table and modify the Snowpipe’s COPY INTO statement to insert error records into this table using a stored procedure called upon failure.

Utilize Snowpipe’s ‘VALIDATION_MODE’ parameter set to to identify and handle invalid records. This requires modification of the COPY INTO statement to redirect errors to an error table.

Configure Snowpipe’s ‘ON_ERROR parameter to ‘CONTINUE’ and rely on the ‘SYSTEM$PIPE_STATUS’ function to identify files with errors. Then, manually query those files for problematic records.

Implement Snowpipe’s ‘ERROR _ INTEGRATION’ object, configuring it to automatically log error records to a designated stage location in JSON format for later analysis. This requires updating the pipe definition.

Disable the Snowpipe and manually load data using a COPY INTO statement with the ‘ON_ERROR = ‘SKIP_FILE” option, then manually inspect the skipped files.

NEW QUESTION 130
Consider the following Snowflake table schema and data: ‘CREATE TABLE products (product_id INTEGER, product_name VARCHAR, properties VARIANT);’ Data: ‘INSERT INTO products VALUES (1, ‘Laptop’, “silver”, “storage”: “512GB”, “price”: 1200.00}’));’ ‘INSERT INTO products VALUES (2, ‘Mouse’, “wireless”, “dpi”: 1600, “price”: 25.00}’));’ ‘INSERT INTO products VALUES (3, ‘Keyboard’, PARSE JSON(‘{“layout”: “US”, “backlit”: true, “price”: Which of the following SQL queries will return the ‘product_name’ and ‘price’ for all products where the ‘price’ is greater than 50, ensuring that the ‘price’ is treated as a numeric value for comparison? Select all that apply

NEW QUESTION 131
You are working with a Snowflake table ‘ORDERS that contains order data in a VARIANT column named ‘ORDER DETAILS’. The ‘ORDER DETAILS column contains JSON objects with nested arrays of product information, including ‘product_id’, ‘quantity’, and ‘price’. You need to calculate the total revenue for each order. Which of the following SQL snippets correctly calculates the total revenue for each order using LATERAL FLATTEN and aggregation?

Option A

Option B

Option C

Option D

Option E

NEW QUESTION 132
You have a table named USER ACTIVITY containing user interaction data’. The ‘TIMESTAMP NTT column stores timestamps without time zone information, while the ‘USER ID column stores IDs as VARCHAR. You need to identify users who have been active between a specific UTC time range, converting the ‘TIMESTAMP NTT column to UTC. Furthermore, you want to categorize users based on the number of activities recorded. Which of the following SQL queries best achieves this, efficiently utilizing Snowflake’s casting and data transformation capabilities?

Option A

Option B

Option C

Option D

Option E

NEW QUESTION 133
You are tasked with creating a dashboard to monitor the performance of different marketing channels (e.g., email, social media, paid advertising). The data includes daily spend, impressions, clicks, and conversions for each channel. Which approach would BEST allow you to visualize the return on investment (ROI) for each channel over time, identify channels with diminishing returns, and enable stakeholders to easily compare channel performance?

Create separate line charts for each channel showing spend, impressions, clicks, and conversions over time, using a static reporting tool like SSRS.

Develop an interactive dashboard in Looker Studio, utilizing calculated fields to derive ROI for each channel (e.g., conversions / spend). Use a combination of line charts, bar charts (ROI per channel), and scatter plots (spend vs. conversions) with trendlines. Implement drill-down capabilities to view daily performance metrics.

Export the data to Excel and create a pivot table summarizing spend and conversions for each channel. Generate a simple bar chart showing total ROI for each channel.

Use Snowflake’s built-in charting capabilities to create a series of pie charts showing the percentage of total spend allocated to each channel.

Create a static report in Tableau using only aggregate measures to calculate the total ROI for each channel and display it in a table.

NEW QUESTION 134
You are building a dashboard in Power BI that connects to Snowflake. The dashboard needs to display the trend of daily active users (DAU) for the past year. The ‘USER_ACTIVITY table in Snowflake contains columns: ‘USER ONT), ‘ACTIVITY DATE (DATE), and ‘ACTIVITY TYPE (VARCHAR). Due to the large size of the ‘USER ACTIVITY table, query performance is critical. Which of the following strategies will BEST optimize the query executed by Power BI against Snowflake to calculate DAU?

Directly query the ‘USER_ACTIVITY table from Power BI using a DAX measure to calculate distinct user counts per day. Rely on Power BI’S query folding capabilities to optimize the query sent to Snowflake.

Create a Snowflake view that calculates the DAU for each day using ‘COUNT(DISTINCT USER_ID)’ and ‘GROUP BY ACTIVITY_DATE’ , then connect Power BI to this view.

Create a materialized view in Snowflake that pre-calculates the DAU for each day, then connect Power BI to this materialized view.

Import the entire ‘USER ACTIVITY table into Power BI using Power BI Desktop’s data import functionality and calculate DAU within Power BI’S data model.

Using a scheduled task in Snowflake, regularly create a summary table containing daily active users, then connect Power BI to that summary table.

NEW QUESTION 135
You are tasked with creating a dashboard to visualize sales performance across different product categories and regions. The data is stored in a Snowflake table named with columns: ‘SALE DATE (DATE), ‘PRODUCT CATEGORY (VARCHAR), ‘REGION’ (VARCHAR), ‘SALES_AMOUNT (NUMBER). The business stakeholders want to see a trend of monthly sales for the past year, a breakdown of sales by region, and a comparison of sales between product categories. Which of the following approaches would be MOST effective and efficient in Snowflake for generating the data needed for these visualizations, considering the need for dashboard responsiveness and minimal query cost?

Create a single, complex SQL query that joins the ‘SALES DATA’ table with itself multiple times, using window functions and subqueries to calculate all the required aggregations and breakdowns within the query. The dashboard will directly query this view.

Create multiple materialized views, each specifically designed for one of the dashboard visualizations (monthly sales trend, sales by region, sales by product category). Refresh these materialized views regularly.

Use Snowflake’s caching mechanism extensively to cache the results of individual queries within the dashboard, ensuring that subsequent requests for the same data are served from the cache. Rely on the dashboard’s internal caching capabilities.

Create a data pipeline that transforms the raw ‘SALES_DATX into summary tables using tasks and streams. These summary tables are optimized for the specific queries required by the dashboard visualizations.

Export the data to an external data warehouse for visualization purpose to avoid overhead of Snowflake visualization tool.

Leave a Reply Cancel reply