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

1. Consider the following scenario: You have a Snowflake task that refreshes a materialized view. The materialized view is based on a large base table that is constantly being updated. The refresh operation is taking longer than expected, impacting downstream reporting. Which of the following actions will likely NOT improve the performance of the materialized view refresh?

A) Using the 'AUTO_REFRESH' property of the materialized view when appropriate and feasible.
B) Analyzing the Query Profile of the Materialized View Refresh query to identify Bottlenecks and Optimization areas.
C) Adding more columns to the materialized view, even if they are not immediately required for reporting.
D) Increasing the warehouse size used by the task executing the refresh.
E) Ensuring the base table used by the materialized view has an appropriate clustering key.

2. You are designing a data governance strategy for a Snowflake data warehouse. One of the key requirements is to track data lineage for sensitive data, specifically Personally Identifiable Information (PII). You need to understand how PII data flows through various transformations and tables. Which Snowflake feature, when combined with appropriate tagging and metadata management practices, can BEST help you achieve this?

A) Snowflake Secure Data Sharing
B) Snowflake INFORMATION_SCHEMA views and Account Usage views
C) Snowflake Data Masking policies
D) Snowflake's Resource Monitors
E) Snowflake Cloning

3. You are a data engineer responsible for data governance in a Snowflake environment. Your company has implemented data classification using tags to identify sensitive data'. The compliance team has requested a report detailing all tables and columns that contain PII data, specifically including the tag name, tag value, the fully qualified name of the table, and the column name. You have the necessary privileges to access the Snowflake metadata views. Which of the following queries would provide the MOST comprehensive and accurate report, considering performance and ease of understanding?

A)

B)

C)

D)

E)

4. You are tasked with managing a large Snowflake table called 'TRANSACTIONS'. Due to compliance requirements, you need to archive data older than one year to long-term storage (AWS S3) while ensuring the queries against the current 'TRANSACTIONS' table remain performant. What is the MOST efficient strategy using Snowflake features and considering minimal impact on query performance?

A) Create an external table pointing to S3. Then create new table named 'TRANSACTIONS_ARCHIVE in Snowflake, copy the historical data from 'TRANSACTIONS' table into 'TRANSACTIONS ARCHIVE, and then delete the archived data from the 'TRANSACTIONS' table.
B) Partition the 'TRANSACTIONS table by date. Export the old partitions of the 'TRANSACTIONS' table to S3 using COPY INTO. Then, drop the old partitions from the 'TRANSACTIONS table and create an external table that points to the data in S3.
C) Create a new table 'TRANSACTIONS_ARCHIVE in Snowflake, copy the historical data, and then delete the archived data from the 'TRANSACTIONS table.
D) Export the historical data to S3 using COPY INTO, truncate the 'TRANSACTIONS' table, and then create an external table pointing to the archived data in S3.
E) Use Time Travel to clone the "TRANSACTIONS' table to a point in time one year ago. Then, export the cloned table to S3 and drop the cloned table. Delete the archived data from the 'TRANSACTIONS table.

5. You are tasked with designing a solution to ingest a continuous stream of unstructured log data from various sources into Snowflake. The log data includes text, JSON, and XML formats. The goal is to efficiently store the data, allow for flexible querying, and minimize storage costs. Which of the following approaches would BEST address these requirements? (Select TWO)

A) Use Snowflake's external functions to parse the log data during query execution.
B) Pre-process the log data to convert all formats into a standardized JSON format before ingestion.
C) Create separate tables for each log data format (text, JSON, XML).
D) Ingest all data 'as is' into a raw staging table. Then create a Task that use Python UDF to parse data and save it to different tables as required
E) Ingest all log data into a single VARIANT column in a Snowflake table.

Solutions: