Snowflake DEA-C02 real exam prep : SnowPro Advanced: Data Engineer (DEA-C02)

• A. The Snowflake external function is not correctly parsing the JSON response from the Lambda function. Implement a wrapper function in Snowflake to parse the JSON and extract the discount value before returning it.
• B. The Lambda function is returning a string instead of a number. Modify the Lambda function to return the discount as a number (e.g., 'discount = 0.15' instead of 'discount = '0.15")
• C. The data types in the Lambda function and Snowflake function definition do not match. Specifically, the Lambda function expects strings while Snowflake is sending numbers and vice versa. Modify the Lambda function to handle numeric inputs and ensure the Snowflake function definition aligns with the expected output data type (FLOAT).
• D. The 'RETURNS NULL ON NULL INPUT clause in the external function definition is causing the function to return NULL even when valid inputs are provided. Remove this clause.
• E. The Lambda function returns the discount within a nested JSON structure Tdata': [[discount]]}'. The Snowflake function is not designed to handle this. The lambda function should return '{'data':

You have a 'SALES table and a 'PRODUCTS table. The 'SALES table contains daily sales transactions, including 'SALE DATE , 'PRODUCT ID', and 'QUANTITY. The 'PRODUCTS table contains 'PRODUCT and 'CATEGORY. You need to create a materialized view to track the total quantity sold per category daily, optimized for fast query performance. You anticipate frequent updates to the 'SALES table but infrequent changes to the 'PRODUCTS table. Which of the following strategies would provide the MOST efficient materialized view implementation, considering both data freshness and query performance?

• A. Create a standard materialized view that joins 'SALES' and 'PRODUCTS' , grouping by 'SALE_DATE and 'CATEGORY, and defining a clustering key on 'SALE DATE' and 'CATEGORY.
• B. Create a standard materialized view that joins 'SALES' and 'PRODUCTS' , grouping by 'SALE DATE and 'CATEGORY without any specific clustering key.
• C. Create a standard materialized view that joins 'SALES' and 'PRODUCTS' , grouping by 'SALE_DATE and 'CATEGORY, and defining a clustering key on 'SALE DATE.
• D. Create two materialized views: one for daily sales by product and another joining the first with 'PRODUCTS' to aggregate by category. Cluster the first view by 'SALE DATE' and the second by 'CATEGORY'.
• E. Create a standard materialized view that joins 'SALES' and 'PRODUCTS' , grouping by 'SALE_DATE and 'CATEGORY, and defining a clustering key on ' CATEGORY.

You're designing a data pipeline in Snowflake that utilizes an external function to perform sentiment analysis on customer reviews using a third-party NLP service. This service charges per request. You need to minimize costs while ensuring timely processing of the reviews.
Which of the following strategies would be most effective in optimizing the cost and performance of your external function?

• A. Implement a caching mechanism (e.g., using a Snowflake table or an external cache) to store the sentiment analysis results for frequently occurring reviews or similar text patterns, avoiding redundant API calls.
• B. Pre-process the customer reviews in Snowflake to filter out irrelevant reviews (e.g., very short reviews or reviews with stop words) before sending them to the external function.
• C. Implement rate limiting and error handling in the external service (e.g., AWS Lambda or Azure Function) to gracefully handle API usage limits and prevent excessive charges due to errors.
• D. Set 'MAX BATCH_ROWS' to a very high value (e.g., 10000) to maximize the number of rows processed per API call, even if it increases latency for individual reviews.
• E. Bypass the external function completely and rely solely on Snowflake's built-in NLP functions for sentiment analysis.

You have a Snowflake table, 'CUSTOMER ORDERS', with columns like 'CUSTOMER ID', 'ORDER DATE', 'ORDER AMOUNT', and 'REGION'. A Bl dashboard relies on a query that aggregates data from this table, but the query performance is consistently slow. The query frequently filters by 'ORDER DATE and groups by 'REGION'. Based on the following 'EXPLAIN' output, which combination of techniques should be considered to improve the performance the most?

• A. Cluster the 'CUSTOMER ORDERS table on 'ORDER DATE' and 'REGION'.
• B. Increase the virtual warehouse size to 'LARGE or 'XLARGE.
• C. Create a materialized view that pre-aggregates the data by 'ORDER DATE and 'REGION'
• D. Redesign the dashboard to minimize the data being displayed at once to the user.
• E. Create an index on the 'ORDER DATE column.

You are building a data pipeline that involves ingesting data from AWS S3 into Snowflake using Snowpipe. The data arrives in small files frequently, and you are experiencing performance issues with delayed data availability. You need to optimize the pipeline for near real-time ingestion. Which combination of strategies will MOST effectively address this scenario?

• A. Enable auto-ingest on the Snowpipe and increase the frequency of S3 event notifications to Snowflake. Combine this with clustering the target table on a relevant column to optimize query performance after loading.
• B. Configure S3 event notifications to trigger Snowpipe only when a sufficient number of files have arrived in the S3 bucket, using a serverless function (like AWS Lambda) to manage the file count threshold.
• C. Implement a micro-batching process using a third-party tool (like Apache Spark) to aggregate the small files into larger batches before loading them into S3, then configure Snowpipe to ingest the larger files.
• D. Increase the warehouse size used for the Snowpipe load process and adjust the 'MAX FILE SIZE' parameter on the pipe definition to match the size of the incoming files.
• E. Manually refresh the Snowpipe using the 'ALTER PIPE ... REFRESH' command every few minutes to force ingestion of new data.