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

1. 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) Execute 'SHOW TABLES' and check the 'clustering_key' column to ensure that the table is indeed clustered on 'EVENT _ TIMESTAMP'.
B) 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.
C) Run 'SYSTEM$ESTIMATE QUERY COST to estimate the query cost to see if the clustering is impacting the cost.
D) Run ' EXPLAIN' on the slow query and examine the 'partitionsTotal' and 'partitionsScanned' values. A significant difference indicates effective clustering.
E) Use the SYSTEM$CLUSTERING_INFORMATIOW function to get the 'average_overlaps' for the table and 'EVENT_TIMESTAMP' column. A low value indicates good clustering.

2. A data engineer is tasked with implementing a data governance strategy in Snowflake. They need to automatically apply a tag 'PII CLASSIFICATION' to all columns containing Personally Identifiable Information (PII). Given the following requirements: 1. The tag must be applied as close to data ingestion as possible. 2. The tagging process should be automated and scalable. 3. The tag value should be dynamically set based on a regular expression match against column names and data types. Which of the following approaches would be MOST effective and efficient in achieving these goals?

A) Create a Snowflake Task that runs daily, querying the INFORMATION SCHEMCOLUMNS view, identifying potential PII columns based on regular expressions, and then executing ALTER TABLE ... ALTER COLUMN ... SET TAG commands.
B) Manually tag each column containing PII using the Snowflake web UI or the 'ALTER TABLE ... ALTER COLUMN ... SET TAG' command. Train data stewards to identify and tag new columns.
C) Use Snowflake's Event Tables in conjunction with a stream and task. Configure the stream to capture DDL changes, and the task to evaluate new columns and apply the tag based on the column metadata using regular expressions.
D) Implement a custom application using the Snowflake JDBC driver to periodically scan table schemas, detect PII columns, and apply tags using dynamic SQL.
E) Implement a stored procedure that leverages external functions to call a Python script hosted on AWS Lambda, which uses a machine learning model to identify PII and apply Snowflake tags.

3. A data engineering team is building a real-time dashboard in Snowflake to monitor website traffic. The dashboard relies on a complex query that joins several large tables. The query execution time is consistently exceeding the acceptable threshold, impacting dashboard responsiveness. Historical data is stored in a separate table and rarely changes. You suspect caching is not being utilized effectively. Which of the following actions would BEST improve the performance of this dashboard and leverage Snowflake's caching features?

A) Replace the complex query with a series of simpler queries. This will reduce the amount of data that needs to be processed at any one time.
B) Use 'RESULT_SCAN' to cache the query result in the user session for subsequent queries. This is especially effective for large datasets that don't change frequently.
C) Create a materialized view that pre-computes the results of the complex query. Snowflake will automatically refresh the materialized view when the underlying data changes.
D) Increase the size of the virtual warehouse. A larger warehouse will have more resources to execute the query, and the results will be cached for a longer period.
E) Materialize the historical data into a separate table that utilizes clustering and indexing for faster query performance. Refresh this table periodically.

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 'EVENT TIMESTAMP' and periodically run 'OPTIMIZE TABLE EVENT DATA' using a small warehouse. Also, create a separate table clustered on 'APPLICATION
B) 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.
C) Cluster the table on 'USER ICY and rely solely on Snowflake's automatic reclustering feature, without running 'OPTIMIZE TABLES manually.
D) Create separate tables for each ' , each clustered on 'EVENT_TIMESTAMP'. Then, create a view that UNION ALLs these tables.
E) Create multiple materialized views: one filtering on common 'EVENT TIMESTAMP' ranges, and another filtering on common 'APPLICATION ID' values.

5. You have a large dataset stored in AWS S3 in Parquet format. The data is constantly updated by an external process, but you need to run read-only analytical queries against the most current data in Snowflake without ingesting it. Which approach is the MOST efficient and cost-effective way to achieve this, considering minimal latency for query results?

A) Create an external table in Snowflake pointing to the S3 location. Configure automatic data refresh using the 'AUTO REFRESH' parameter and a notification integration.
B) Use Snowpipe to continuously ingest the Parquet files from S3 into a Snowflake table.
C) Create a Snowflake internal stage, load the Parquet files into the stage periodically, and create a table from the staged files.
D) Create a Snowflake external table pointing to the S3 location. Manually refresh the external table metadata periodically using the 'ALTER EXTERNAL TABLE REFRESH' command.
E) Create a view on top of the S3 files using a custom Java UDF to read Parquet data directly. Then, query the view.

Solutions: