Instantly download DEA-C02 training test engine

Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

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

Question 1

A data engineer notices that a daily ETL job loading data into a Snowflake table 'TRANSACTIONS' is consistently taking longer than expected. The table is append-only and partitioned by 'TRANSACTION DATE. The engineer observes high 'Remote Spill' during the load process and suspect that micro-partition pruning isn't working effectively. Which of the following approaches would BEST address the performance issue, assuming you have already considered increasing warehouse size?

A. Re-create the 'TRANSACTIONS' table with a larger virtual warehouse and re-load the entire dataset.
B. Implement data skipping by creating a masking policy on the 'TRANSACTION_DATE column.
C. Partition the data in the source system by 'TRANSACTION DATE' and load data in parallel corresponding to each partition.
D. Examine the data load process to ensure the data is loaded in 'TRANSACTION_DATE order. If not, sort the data by 'TRANSACTION_DATE before loading.
E. Enable automatic clustering on the 'TRANSACTION_DATE column of the 'TRANSACTIONS table.

Question 2

A large e-commerce company is experiencing performance issues with its daily sales report queries. These queries aggregate data from a fact table 'SALES FACT (100 billion rows) and several dimension tables, including 'CUSTOMER DIM', 'PRODUCT DIM', and 'DATE DIM'. The queries are run every morning and are essential for business decision-making. The team has identified that the 'SALES FACT table's primary key is 'SALE ID, but the queries frequently filter and join on 'CUSTOMER and 'PRODUCT ID. You want to use query acceleration service for these reports without changing query logic. Which combination of actions will MOST effectively leverage query acceleration service, assuming sufficient credits?

A. Create materialized views that pre-aggregate the sales data based on 'CUSTOMER ID', 'PRODUCT ID, and 'DATE ID, then enable query acceleration on the virtual warehouse.
B. Enable search optimization on the columns 'CUSTOMER ID' and 'PRODUCT ID of the 'SALES FACT table, then enable query acceleration on the virtual warehouse. Set the QUERY_ACCELERATION_MAX_SCALE_FACTOR parameter to a reasonable value based on testing.
C. Increase the size of the virtual warehouse used for running the reports and enable query acceleration. Set the parameter to a high value.
D. Enable Automatic Clustering on the 'SALES FACT table based on 'CUSTOMER ID' and 'PRODUCT ID, then enable query acceleration on the virtual warehouse.
E. Enable clustering on the 'CUSTOMER DIM' and 'PRODUCT DIMS tables.

Question 3

A provider account is sharing a database named 'SHARED DB' through a share named 'MY SHARE. The consumer account has created a database named 'CONSUMER DB' from the share. The provider account revokes access to a table named 'SALES DATA within 'SHARED DB'. What will happen when a user in the consumer account attempts to query 'CONSUMER DB.SHARED SCHEMA.SALES DATA'?

A. The query will execute successfully, but only return data that existed before the access was revoked.
B. The query will execute successfully, but the user will receive an empty result set.
C. The query will be cached based on the initial access, so users can continue query previous result based on same SQL
D. The query will fail with an error message indicating that the table does not exist or the user does not have privileges.
E. The query will be automatically re-routed to another available share containing 'SALES DATA'.

Question 4

You are tasked with building a robust data quality monitoring system for a Snowflake data pipeline. The pipeline processes customer order data and loads it into a 'CUSTOMER ORDERS table. You need to implement checks to ensure that certain critical columns (e.g., 'ORDER ID, 'CUSTOMER ID', 'ORDER DATE, meet specific data quality requirements (e.g., not null, valid format, within acceptable range). You want to design a flexible and scalable solution that allows you to easily add, modify, and monitor data quality rules. Select the options to implement that and scale efficiently Assume there is a central Data Quality table for each metrics

A. Implement a Snowpark Python UDF that leverages a data quality library (e.g., Great Expectations) to define and execute data quality rules. The UDF takes a DataFrame representing the data to be checked and returns a DataFrame containing the data quality check results.
B. Utilize Snowflake's native Data Governance features, such as data masking and row-level security, to enforce data quality rules.
C. Create a series of individual SQL scripts, each checking a specific data quality rule for a specific column, and schedule these scripts to run using Snowflake tasks.
D. Build a set of custom Snowflake Native Apps to monitor and report on data quality. Each App will focus on one or more critical tables or data quality checks
E. Develop a parameterized stored procedure that accepts the table name, column name, data quality rule definition, and threshold values as input parameters. This procedure then dynamically constructs and executes the SQL query to check the data quality rule.

Question 5

A data engineer is investigating high credit consumption on a Snowflake warehouse due to frequent re-clustering operations on a large table named 'WEB EVENTS. This table is clustered on 'EVENT TIMESTAMP' and 'USER ID. The engineer suspects that the high frequency of data ingestion, especially out-of-order 'EVENT TIMESTAMP' values, contributes to the poor clustering. Choose the options that can lead to optimizing clustering and reducing credit consumption, assuming you have limited control over the ingestion process and data quality.

A. Increase the warehouse size to accelerate the re-clustering process.
B. Drop the clustering key altogether to avoid re-clustering costs.
C. Implement a maintenance task to periodically re-cluster the table less frequently, but at more strategically chosen times (e.g., during off-peak hours).
D. Implement a pre-processing stage to sort the incoming data by 'EVENT TIMESTAMP before loading it into the 'WEB EVENTS table, using a temporary table and then inserting into the final table.
E. Partition the table based on "EVENT _ TIMESTAMP' instead of clustering.

Solutions: