Snowflake DEA-C02 Exam Syllabus Topics:

Snowflake SnowPro Advanced: Data Engineer (DEA-C02) Sample Questions:

1. A Snowflake data warehouse contains a table named 'SALES TRANSACTIONS' with the following columns: 'TRANSACTION ID', 'PRODUCT D', 'CUSTOMER D', 'TRANSACTION DATE, and 'SALES AMOUNT'. You need to optimize a query that calculates the total sales amount per product for a given month. The 'SALES TRANSACTIONS' table is very large (billions of rows), and queries are slow. Given the following initial query: SELECT PRODUCT ID, SUM(SALES AMOUNT) AS TOTAL SALES FROM SALES TRANSACTIONS WHERE TRANSACTION DATE BETWEEN '2023-01-07' AND '2023-01-31' GäOUP BY PRODUCT ID; Which of the following actions, when combined, would MOST effectively improve the performance of this query?

A) Create a clustering key on 'PRODUCT_ID and 'TRANSACTION_DATE columns in the 'SALES_TRANSACTIONS' table.
B) Create a temporary table with the results of the query and query that table instead.
C) Convert the column to a VARCHAR data type.
D) Create a materialized view that pre-aggregates the total sales amount per product and month.
E) Increase the virtual warehouse size to the largest available size.

2. You have a large Snowflake table 'WEB EVENTS that stores website event data'. This table is clustered on the 'EVENT TIMESTAMP column. You've noticed that certain queries filtering on a specific 'USER ID' are slow, even though 'EVENT TIMESTAMP clustering should be helping. You decide to investigate further Which of the following actions would be MOST effective in diagnosing whether the clustering on 'EVENT TIMESTAMP is actually benefiting these slow queries?

A) Use the SYSTEM$CLUSTERING_INFORMATIOW function to get the 'average_overlaps' for the table and 'EVENT_TIMESTAMP' column. A low value indicates good clustering.
B) Run ' EXPLAIN' on the slow query and examine the 'partitionsTotal' and 'partitionsScanned' values. A significant difference indicates effective clustering.
C) Query the 'QUERY_HISTORY view to see the execution time of the slow query and compare it to the average execution time of similar queries without a 'USER filter.
D) Execute 'SHOW TABLES' and check the 'clustering_key' column to ensure that the table is indeed clustered on 'EVENT _ TIMESTAMP'.
E) Run 'SYSTEM$ESTIMATE QUERY COST to estimate the query cost to see if the clustering is impacting the cost.

3. You are monitoring a Snowpipe pipeline that loads data from an external stage into a Snowflake table. You observe the following error messages in the PIPE ERRORS view: 'Invalid UTF-8 detected in string'. The data files on the stage are encoded in UTF-8. Which of the following actions, taken individually or in combination, are MOST likely to resolve this issue? (Select TWO)

A) Ensure the file format definition explicitly specifies 'ENCODING = 'UTF8".
B) Convert the problematic files to UTF-16 encoding before loading them into the stage.
C) Modify the COPY INTO statement to include the 'ON ERROR = 'SKIP_FILE" option.
D) Verify the data files on the stage are actually valid UTF-8 and contain no corrupted characters.
E) Drop and recreate the external stage with 'TYPE = INTERNAL'.

4. You are tasked with building a Snowpipe to ingest JSON data from an AWS S3 bucket into a Snowflake table named 'SALES DATA'. The data is landing in the bucket frequently, and you want to use Snowpipe's auto-ingest feature. However, you are observing significant latency in data appearing in your Snowflake table after it lands in S3, despite verifying that S3 event notifications are correctly configured and the Snowflake event queue is receiving them. You've already checked that the pipe is enabled and has the necessary permissions. The Snowflake Pipe definition is as follows:

What is the MOST LIKELY reason for this delay, and what steps can you take to further troubleshoot?

A) The Snowflake virtual warehouse associated with the pipe is undersized. Increase the warehouse size to improve ingestion performance.
B) There is a backlog of files in the internal Snowflake queue waiting to be processed. Monitor the 'SYSTEM$PIPE STATUS' function and consider increasing the 'MAX CONCURRENCY' parameter (if applicable, based on underlying infrastructure considerations) on the pipe definition.
C) The S3 bucket is not in the same region as the Snowflake account. Ensure the S3 bucket and Snowflake account are in the same region to reduce network latency.
D) Snowpipe auto-ingest only supports CSV files. Convert your JSON data to CSV format before loading.
E) Snowflake's internal metadata cache is out of sync. Run 'ALTER PIPE SALES PIPE to refresh the cache.

5. You are developing a JavaScript UDF in Snowflake to perform complex data validation on incoming data'. The UDF needs to validate multiple fields against different criteria, including checking for null values, data type validation, and range checks. Furthermore, you need to return a JSON object containing the validation results for each field, indicating whether each field is valid or not and providing an error message if invalid. Which approach is the MOST efficient and maintainable way to structure your JavaScript UDF to achieve this?

A) Define a JavaScript object containing validation rules and corresponding validation functions. Iterate through the object and apply the rules to the input data, collecting the validation results in a JSON object. This object is returned as a string.
B) Utilize a JavaScript library like Lodash or Underscore.js within the UDF to perform data manipulation and validation. Return a JSON string containing the validation results.
C) Use a single, monolithic JavaScript function with nested if-else statements to handle all validation logic. Return a JSON string containing the validation results.
D) Create separate JavaScript functions for each validation check (e.g., 'isNull', 'isValidType', 'isWithinRange'). Call these functions from the main UDF and aggregate the results into a JSON object.
E) Directly embed SQL queries within the JavaScript UDF to perform data validation checks using Snowflake's built-in functions. Return a JSON string containing the validation results.

Solutions: