Snowflake DEA-C02 Exam Syllabus Topics:

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

1. You have created an external table in Snowflake that points to a large dataset stored in Azure Blob Storage. The data consists of JSON files, and you've noticed that query performance is slow. Analyzing the query profile, you see that Snowflake is scanning a large number of unnecessary files. Which of the following strategies could you implement to significantly improve query performance against this external table?

A) Convert the JSON files to Parquet format and recreate the external table to point to the Parquet files.
B) Create an internal stage, copy all JSON Files, create and load the target table, and drop external table
C) Create a materialized view on top of the external table to pre-aggregate the data.
D) Partition the data in Azure Blob Storage based on a relevant column (e.g., date) and define partitioning metadata in the external table definition using PARTITION BY.
E) Increase the size of the Snowflake virtual warehouse to provide more processing power.

2. You are using Snowpipe to ingest data from Azure Blob Storage into a Snowflake table. You have successfully set up the pipe and configured the event notifications. However, you notice that duplicate records are appearing in your target table. After reviewing the logs, you determine that the same file is being processed multiple times by Snowpipe. Which of the following strategies can you implement to prevent duplicate data ingestion, assuming you cannot modify the source data in Azure Blob Storage to include a unique ID or timestamp?

A) Create a Snowflake stream on the target table and use it to incrementally load data into a separate, deduplicated table using a merge statement with conditional logic to insert or update records based on a combination of columns.
B) Configure the Snowpipe definition with the 'PURGE = TRUE parameter. This will ensure that each file is only processed once.
C) Implement idempotent logic within a Snowflake stored procedure that is triggered by a task after the data is loaded by Snowpipe. The stored procedure should identify and remove duplicate rows based on all other columns in the table.
D) Use a data masking policy with the 'MASK' function to obfuscate duplicate records based on their similarity, making them effectively invisible to downstream queries.
E) Modify the Azure Event Grid subscription configuration to filter events based on file size or creation time to avoid resending events for already processed files.

3. You have a large dataset of JSON documents stored in AWS S3, each document representing a customer order. You want to ingest these documents into Snowflake using Snowpipe and transform the nested 'address' field into separate columns in your target table. Considering data volume, complexity, and cost efficiency, which approach is MOST suitable?

A) Pre-process the JSON documents using an external compute service (e.g., AWS Lambda) to flatten the 'address' field before ingesting into Snowflake via Snowpipe.
B) Create an external table on the S3 bucket and then use CREATE TABLE AS SELECT (CTAS) to transform the data.
C) Use Snowpipe with a user-defined function (UDF) written in Python to parse the JSON and flatten the 'address' field.
D) Use a COPY INTO statement with a transform clause to flatten the 'address' field during ingestion.
E) Use Snowpipe to ingest the raw JSON data into a VARIANT column, then create a view that flattens the 'address' field.

4. 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) Use the 'transforms' configuration with the 'org.apache.kafka.connect.transforms.ExtractField$Value' transformation to extract specific fields.
C) Apply the 'org.apache.kafka.connect.transforms.Flatten' transformation to the 'transforms' configuration.
D) Set the 'value.converter.schemas.enable' property to 'true'.
E) Enable the 'snowflake.ingest.stage' property and set it to a Snowflake internal stage.

5. You have a complex data pipeline implemented using Snow park Python. The pipeline involves multiple Data Frame transformations, joins, aggregations, and window functions. To enhance the maintainability and readability of the code, you want to modularize the pipeline into reusable functions. You also need to handle potential errors and exceptions gracefully. Consider the following code snippet:

A)

B)

C)

D)

Solutions: