Instantly download DEA-C02 updated real questions

Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

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

1. A data engineering team is responsible for an ELT pipeline that loads data into Snowflake. The pipeline has two distinct stages: a high- volume, low-complexity transformation stage using SQL on raw data, and a low-volume, high-complexity transformation stage using Python UDFs that leverages an external service for data enrichment. The team is experiencing significant queueing during peak hours, particularly impacting the high-volume stage. You need to optimize warehouse configuration to minimize queueing. Which combination of actions would be MOST effective?

A) Create two separate warehouses: a Large, multi-cluster warehouse configured for auto-scale for the high-volume, low-complexity transformations and a Small warehouse for the low-volume, high-complexity transformations.
B) Create a single, X-Small warehouse and rely on Snowflake's query acceleration service to handle the workload.
C) Create two separate warehouses: a Small warehouse configured for auto-suspend after 5 minutes for the high-volume, low-complexity transformations and a Large warehouse configured for auto-suspend after 60 minutes for the low-volume, high-complexity transformations.
D) Create two separate warehouses: a Medium warehouse for the high-volume, low-complexity transformations and an X-Small warehouse for the low-volume, high-complexity transformations.
E) Create a single, large (e.g., X-Large) warehouse and rely on Snowflake's automatic scaling to handle the workload.

2. You have a Snowflake table called 'RAW ORDERS that contains semi-structured JSON data in a column named 'ORDER DETAILS. You need to extract specific fields from the JSON data, perform some data type conversions, and then load the transformed data into a relational table named 'CLEAN ORDERS'. Your requirements are as follows: 1. Extract the (STRING) from the JSON and store it as 'ORDER ID (NUMBER). 2. Extract the (STRING) from the JSON and store it as 'CUSTOMER ID (NUMBER). 3. Extract the 'order_date' (STRING) from the JSON and store it as 'ORDER DATE' (DATE). 4. Extract (STRING) from the JSON and store it as 'TOTAL AMOUNT' (FLOAT). Which of the following Snowpark Python code snippets correctly transforms the data and loads it into the 'CLEAN ORDERS table using a combination of Snowpark DataFrame operations and SQL? Assume that session 'sp' is already initialized.

A) Option A
B) Option E
C) Option B
D) Option D
E) Option C

3. You have configured a Kafka Connector to load JSON data into a Snowflake table named 'ORDERS. The JSON data contains nested structures. However, Snowflake is only receiving the top- level fields, and the nested fields are being ignored. Which configuration option within the Kafka Connector needs to be adjusted to correctly flatten and load the nested JSON data into Snowflake?

A) Configure the 'snowflake.data.field.name' property to specify the column in the Snowflake table where the entire JSON should be loaded as a VARIANT.
B) Enable the 'snowflake.ingest.stage' property and set it to a Snowflake internal stage.
C) Apply the 'org.apache.kafka.connect.transforms.Flatten' transformation to the 'transforms' configuration.
D) Use the 'transforms' configuration with the 'org.apache.kafka.connect.transforms.ExtractField$Value' transformation to extract specific fields.
E) Set the 'value.converter.schemas.enable' property to 'true'.

4. Consider a table 'EVENT DATA' that stores events from various applications. The table has columns like 'EVENT ID, 'EVENT TIMESTAMP, 'APPLICATION ID', 'USER ID', and 'EVENT _ TYPE. A significant portion of queries filter on 'EVENT TIMESTAMP ranges AND 'APPLICATION ID. The data volume is substantial, and query performance is crucial. You observe high clustering depth after initial loading. Which combination of actions will provide the MOST effective performance optimization, addressing both clustering depth and query performance?

A) Cluster the table on 'USER ICY and rely solely on Snowflake's automatic reclustering feature, without running 'OPTIMIZE TABLES manually.
B) Create multiple materialized views: one filtering on common 'EVENT TIMESTAMP' ranges, and another filtering on common 'APPLICATION ID' values.
C) Cluster the table on '(EVENT TIMESTAMP, APPLICATION IDY and periodically run 'OPTIMIZE TABLE EVENT DATA' using a warehouse sized appropriately for the table size. Then, monitor clustering depth regularly.
D) Create separate tables for each ' , each clustered on 'EVENT_TIMESTAMP'. Then, create a view that UNION ALLs these tables.
E) Cluster the table on 'EVENT TIMESTAMP' and periodically run 'OPTIMIZE TABLE EVENT DATA' using a small warehouse. Also, create a separate table clustered on 'APPLICATION

5. You have an external table in Snowflake pointing to data in Azure Blob Storage. The data consists of customer transactions, and new files are added to the Blob Storage daily You want to ensure that Snowflake automatically picks up these new files and reflects them in the external table without manual intervention. However, you are observing delays in Snowflake detecting the new files. What are the potential reasons for this delay and how can you troubleshoot them? (Choose two)

A) The file format used for the external table is incompatible with the data files in Blob Storage.
B) Snowflake's internal cache is not properly configured; increasing the cache size will solve the problem.
C) The Azure Event Grid notification integration is not properly configured to notify Snowflake about new file arrivals in the Blob Storage.
D) The storage integration associated with the external table does not have sufficient permissions to access the Blob Storage.
E) The external table's 'AUTO_REFRESH' parameter is set to 'FALSE', which disables automatic metadata refresh.

Solutions: