最新のDEA-C02問題集で試験を準備する

Snowflake DEA-C02 試験概要:

Snowflake DEA-C02 試験シラバストピック:

Snowflake SnowPro Advanced: Data Engineer (DEA-C02) 認定 DEA-C02 試験問題：

1. You are tasked with sharing a subset of a customer table (CUSTOMER DATA') residing in your organization's Snowflake account with a partner organization. You need to mask personally identifiable information (PII) while providing near real-time updates. You decide to use a secure view. Which of the following SQL statements is the MOST efficient and secure way to accomplish this, assuming the partner only needs 'customer id', 'masked_email', 'city', and 'state'? The email should be masked using SHA256.

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

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

3. You have a Snowflake Stream named 'ORDERS STREAM' on an 'ORDERS' table, which is used to incrementally load data into a historical orders table named 'HISTORICAL ORDERS'. The data pipeline involves a series of tasks: 1) Consume changes from the 'ORDERS STREAM', 2) Apply transformations and data quality checks, and 3) Merge the changes into 'HISTORICAL ORDERS' using a MERGE statement. After a recent data load, you notice that the 'HISTORICAL ORDERS' table contains duplicate records for certain 'ORDER values. The MERGE statement uses 'ORDER ID' as the matching key. You have confirmed that the transformation logic is correct and idempotent. Examine the MERGE statement below. What could be causing the duplicates, given the context of Streams and incremental loading?

A) The stream is not configured to capture DELETE operations from the ORDERS table, causing records that should have been removed in HISTORICAL ORDERS to remain.
B) The 'ORDERS STREAM' is retaining historical data beyond the data retention period, causing older records to be re-processed.
C) Multiple tasks are concurrently consuming from the same 'ORDERS STREAM' without proper coordination, causing records to be processed multiple times.
D) The stream's or 'BEFORE clause is being used incorrectly, potentially rewinding the stream to an earlier point in time.
E) The MERGE statement is not correctly handling updates and deletes from the stream. The 'WHEN NOT MATCHED' and 'WHEN MATCHED' clauses are not mutually exclusive, leading to potential insertions of duplicate rows.

4. A data engineer is working with a Snowpark DataFrame 'sales df containing sales data with columns 'product id', 'sale_date', and 'sale amount'. The engineer needs to calculate the cumulative sales amount for each product over time. Which of the following code snippets using window functions correctly calculates the cumulative sales amount, ordered by 'sale date'?

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

5. You are designing a data pipeline that involves unloading large amounts of data (hundreds of terabytes) from Snowflake to AWS S3 for archival purposes. To optimize cost and performance, which of the following strategies should you consider? (Select ALL that apply)

A) Partition the data during the unload operation based on a high-cardinality column to maximize parallelism in S3.
B) Utilize the 'MAX FILE SIZE parameter in the 'COPY INTO' command to control the size of individual files unloaded to S3. Smaller files generally improve query performance in S3.
C) Use a large Snowflake warehouse size to parallelize the unload operation and reduce the overall unload time.
D) Enable client-side encryption with KMS in S3 and specify the encryption key in the 'COPY INTO' command to enhance security.
E) Choose a file format such as Parquet or ORC with compression enabled to reduce storage costs and improve query performance in S3.

質問と回答：