Snowflake DEA-C02 Exam Syllabus Topics:

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

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

2. A data engineer is responsible for maintaining a Snowflake data warehouse. They notice a significant slowdown in the performance of a specific query that aggregates data from a table called 'SALES DATA', which contains billions of rows. The query is used for generating daily sales reports. The engineer suspects that the issue might be related to clustering. How would you diagnose the effectiveness of the clustering on the 'SALES DATA' table and identify potential improvements?

A) Use the SYSTEM$CLUSTERING_INFORMATION' function to analyze the clustering depth of the table. A high clustering depth indicates poor clustering.
B) Examine the query profile in the Snowflake web interface to identify stages that are scanning large amounts of data. Check if these stages are benefiting from clustering.
C) Use the 'SHOW TABLES command to view the clustering key defined on the table. Verify that the clustering key is appropriate for the query workload.
D) Use the 'VALIDATE table command. This command detects fragmentation in the data due to poor clustering.
E) Use the 'DESCRIBE TABLE SALES_DATA' command and check the 'clustering_key' property, then run 'SELECT SYSTEM$MEASURE CLUSTERING DEPTH('SALES to check the average depth of the table. Compare the clustering depth to the number of micro- partitions to assess clustering effectiveness. A depth closer to zero is best.

3. You are working with a Snowpark DataFrame named 'customer data' that contains sensitive Personally Identifiable Information (PII). The DataFrame has columns such as 'customer id', 'name', 'email' , and 'phone number'. Your task is to create a new DataFrame that only contains 'customer id' and a hash of the 'email' address for anonymization purposes, while also filtering out any customers whose 'customer id' starts with 'TEMP'. Which of the following approaches adheres to best practices for data security and efficiency in Snowpark, using secure hashing algorithms provided by Snowflake?

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

4. A data engineering team is implementing column-level security on a Snowflake table named 'CUSTOMER DATA containing sensitive PII. They want to mask the 'EMAIL' column for users in the 'ANALYST role but allow users in the 'DATA SCIENTIST role to view the unmasked email addresses. The 'ANALYST role already has SELECT privileges on the table. Which of the following steps are necessary to achieve this using a masking policy?

A) Create a masking policy that uses the CURRENT ROLE() function to return a masked value if the current role is 'ANALYST and the original value otherwise.
B) Create a masking policy that uses the CURRENT_USER() function to check if the current user belongs to the 'ANALYST' role.
C) Create a masking policy that uses the IS_ROLE_IN_SESSION('ANALYST') function to return a masked value if the analyst role is active in current session and the original value otherwise.
D) Create a dedicated view on 'CUSTOMER DATA' for analysts with the 'EMAIL' column masked using a CASE statement within the view's SELECT statement. Grant SELECT privilege to the ANALYST role on the view only.
E) Create a masking policy with a CASE statement that checks the CURRENT ROLE() function to see if it's 'ANALYST'. If true, mask the email; otherwise, return the original email.

5. 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) Manually refresh the Snowpipe using the 'ALTER PIPE ... REFRESH' command every few minutes to force ingestion of new data.
C) 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.
D) 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.
E) 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.

Solutions: