Snowflake SnowPro Advanced: Data Engineer (DEA-C02) : DEA-C02 Exam

A data pipeline ingests clickstream data from various sources into a raw Snowflake table CRAW CLICKS). A transformation job then processes this data and loads it into a more structured 'CLICK EVENTS table, performing filtering, cleaning, and data enrichment. The data engineering team notices significant performance bottlenecks during this transformation process, leading to data freshness issues.
The team wants to optimize this process, considering the following:

• A. Use a larger virtual warehouse for the transformation job and partition the 'RAW CLICKS table on the ingestion timestamp to improve data pruning and reduce the amount of data processed during the transformation.
• B. Optimize the transformation queries by identifying and rewriting inefficient SQL patterns, ensuring appropriate use of joins, filtering conditions, and data type conversions.
• C. Replace the transformation job with a series of smaller, more specialized jobs, each running on a separate virtual warehouse optimized for the specific task, and orchestrate these jobs using a data pipeline tool.
• D. Implement a change data capture (CDC) mechanism on the source systems to only ingest changed data into 'RAW CLICKS, reducing the overall data volume and the amount of data processed by the transformation job.
• E. Create a materialized view on top of 'RAW CLICKS' that pre-computes the necessary transformations and aggregations, allowing the 'CLICK EVENTS' table to be populated directly from the materialized view.

Explanation: Only visible for Pass4suresVCE members. You can sign-up / login (it's free).

A data engineering team uses Snowflake to analyze website clickstream data stored in AWS S3. The data is partitioned by year and month in the S3 bucket. They need to query the data frequently for reporting purposes but don't want to ingest the entire dataset into Snowflake due to storage costs and infrequent full dataset analysis. Which approach is the MOST efficient and cost-effective way to enable querying of this data in Snowflake?

• A. Create a Snowflake internal stage, copy the necessary files into the stage, and then load the data into a Snowflake table.
• B. Create a Snowflake external stage pointing to the S3 bucket, define an external table on the stage, and use partitioning metadata to optimize queries.
• C. Load all the data into a Snowflake table and create a materialized view on top of the table to pre-aggregate the data for reporting.
• D. Create a Snowpipe pointing to the S3 bucket and ingest the data continuously into a Snowflake table.
• E. Use Snowflake's COPY INTO command to ingest data directly from S3 into a Snowflake table on a scheduled basis.

Explanation: Only visible for Pass4suresVCE members. You can sign-up / login (it's free).

You have an external table in Snowflake pointing to data in Azure Blob Storage. The data consists of customer transactions, and new files are added to the Blob Storage daily You want to ensure that Snowflake automatically picks up these new files and reflects them in the external table without manual intervention. However, you are observing delays in Snowflake detecting the new files. What are the potential reasons for this delay and how can you troubleshoot them? (Choose two)

• A. The storage integration associated with the external table does not have sufficient permissions to access the Blob Storage.
• B. The external table's 'AUTO_REFRESH' parameter is set to 'FALSE', which disables automatic metadata refresh.
• C. The Azure Event Grid notification integration is not properly configured to notify Snowflake about new file arrivals in the Blob Storage.
• D. The file format used for the external table is incompatible with the data files in Blob Storage.
• E. Snowflake's internal cache is not properly configured; increasing the cache size will solve the problem.

Explanation: Only visible for Pass4suresVCE members. You can sign-up / login (it's free).

A data engineer accidentally truncated a critical table 'ORDERS' in the 'SALES DB' database. The table contained important historical order data, and the data retention period is set to the default. Which of the following options represents the MOST efficient and reliable way to recover the truncated table and its data, minimizing downtime and potential data loss?

• A. Contact Snowflake support and request them to restore the table from a system-level backup.
• B. Restore the entire Snowflake account to a previous point in time before the table was truncated.
• C. Use the UNDROP TABLE command to restore the table. If UNDROP fails, clone the entire SALES_DB database to a point in time before the truncation using Time Travel.
• D. Use Time Travel to create a clone of the truncated table from a point in time before the truncation. Then, swap the original table with the cloned table.
• E. Create a new table 'ORDERS' and manually re-insert the data from the application's logs and backups.

Explanation: Only visible for Pass4suresVCE members. You can sign-up / login (it's free).

A Snowflake table 'ORDERS' is clustered on the 'ORDER DATE column. After several months, you notice that many micro-partitions contain data from a wide range of 'ORDER DATE values, and query performance on date range filters is degrading. Which of the following actions could improve performance and reduce the overlap in micro-partitions?

• A. Change the clustering key to a composite key including ORDER DATE and another relevant column.
• B. Run 'OPTIMIZE TABLE ORDERS' to recluster the table.
• C. Change the ORDER DATE column to VARCHAR and then cluster.
• D. Drop and recreate the table with a different clustering key.
• E. Increase the virtual warehouse size used for loading data into the "ORDERS' table.

Explanation: Only visible for Pass4suresVCE members. You can sign-up / login (it's free).