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

You are designing a data sharing solution in Snowflake where a provider account shares a view with a consumer account. The view is based on a table that undergoes frequent DML operations (inserts, updates, deletes). The consumer account needs to see a consistent snapshot of the data, even during these DML operations. Which of the following strategies, or combination of strategies, would be MOST effective in ensuring data consistency from the consumer's perspective, and what considerations should be made?

• A. Creating a stream on the base table in the provider account and building a view on top of the stream. This way, changes are only reflected when the stream is consumed, allowing for batch processing and controlled updates in the consumer account.
• B. Creating a materialized view in the provider account and sharing that materialized view. This adds compute costs to the provider but ensures a consistent snapshot for the consumer account. The materialized view needs to be refreshed periodically, based on the rate of DML changes.
• C. Using Snowflake's Time Travel feature by querying the view with a specific 'AT' or 'BEFORE' clause in the consumer account. The provider account needs to inform the consumer account of a specific timestamp that guarantees consistency, adding administrative overhead.
• D. Creating a standard view in the provider account and relying on Snowflake's inherent transactional consistency. The consumer account will always see a consistent snapshot of the data as it existed at the beginning of their query execution. No additional configurations are necessary.
• E. A and B

You are tasked with loading a large dataset (50TB) of JSON files into Snowflake. The JSON files are complex, deeply nested, and irregularly structured. You want to maximize loading performance while minimizing storage costs and ensuring data integrity. You have a dedicated Snowflake virtual warehouse (X-Large).
Which combination of approaches would be MOST effective?

• A. Pre-process the JSON data using a Python script with Pandas to flatten the structure and convert it into a relational format like CSV. Then, load the CSV files using the COPY INTO command with gzip compression.
• B. Use Snowpipe with auto-ingest, create a raw VARIANT column alongside projected relational columns for frequently accessed fields, and use search optimization on those projected columns.
• C. Load the JSON data using the COPY INTO command with no pre-processing. Create a VIEW on top of the raw VARIANT column to flatten the data for querying.
• D. Use Snowpipe with auto-ingest, create a single VARIANT column in your target table, and rely solely on Snowflake's automatic schema detection.
• E. Load the JSON data using the COPY INTO command with gzip compression. Create a raw VARIANT column alongside projected relational columns for frequently accessed fields, and use materialized views to improve query performance.

You have a Snowflake Task that is designed to transform and load data into a target table. The task relies on a Stream to detect changes in a source table. However, you notice that the task is intermittently failing with a 'Stream STALE' error, even though the data in the source table is continuously updated. What are the most likely root causes and the best combination of solutions to prevent this issue? (Select TWO)

• A. The Stream has reached its maximum age (default 14 days) and expired. There is no way to recover data from an expired Stream. You need to recreate the Stream and reload the source table.
• B. DML operations (e.g., UPDATE, DELETE) being performed on the source table are affecting rows older than the Stream's retention period. Reduce the stream's 'DATA RETENTION TIME IN DAYS' to match the oldest DML operation on the source table.
• C. The source table is being modified with DDL operations (e.g., ALTER TABLE ADD COLUMN), which are not supported by Streams. Use Table History to track schema changes and manually adjust the Stream's query if needed. Use 'COPY GRANTS' during the DDL.
• D. The Stream is not configured with 'SHOW INITIAL ROWS = TRUE, causing initial changes to be missed and eventually leading to staleness. Recreate the stream with this parameter set to TRUE.
• E. The Task is not running frequently enough, causing the Stream to accumulate too many changes before being consumed, exceeding its retention period. Increase the task's execution frequency or increase the stream's 'DATA RETENTION TIME IN DAYS

A data engineering team has created a Snowflake Listing to share their company's sales data'. They want to allow consumers to access the Listing programmatically. The consumers need to know when new versions of the Listing are available. What is the MOST efficient method for consumers to be notified about new Listing versions without continuously polling Snowflake?

• A. The data provider manually sends email notifications to each consumer whenever a new version is created.
• B. The data consumer implements a custom webhook endpoint that the data provider calls whenever a new Listing version is published. The data provider will need to maintain a list of all consumers.
• C. The consumer sets up a scheduled task in Snowflake to periodically query the 'SHOW VERSIONS command for the Listing.
• D. Snowflake automatically notifies consumers via an event notification service (e.g., AWS SNS, Azure Event Grid) integrated with the Data Marketplace when a new version is available.

You're tasked with building a data pipeline using Snowpark Python to incrementally load data into a target table 'SALES SUMMARY from a source table 'RAW SALES. The pipeline needs to ensure that only new or updated records from 'RAW SALES are merged into 'SALES SUMMARY' based on a 'TRANSACTION ID'. You want to use Snowpark's 'MERGE' operation for this, but you also need to handle potential conflicts and log any rejected records to an error table 'SALES SUMMARY ERRORS'. Which of the following approaches offers the MOST robust and efficient solution for handling errors and ensuring data integrity within the MERGE statement?

• A. Use the 'WHEN MATCHED THEN UPDATE' clause to update existing records and the 'WHEN NOT MATCHED THEN INSERT clause to insert new records. Implement a separate process to periodically compare 'SALES_SUMMARY with 'RAW_SALES' to identify and log any inconsistencies.
• B. Incorporate an 'ELSE clause in the 'MERGE' statement to capture records that do not satisfy the update or insert conditions due to data quality issues. Use this 'ELSE clause to insert rejected records into 'SALES SUMMARY ERRORS'
• C. Use a single 'MERGE statement with 'WHEN MATCHED THEN UPDATE and 'WHEN NOT MATCHED THEN INSERT clauses. Capture rejected records by leveraging the ' SYSTEM$PIPE STATUS function after the 'MERGE operation to identify rows that failed during the merge.
• D. Utilize the 'WHEN MATCHED THEN UPDATE and 'WHEN NOT MATCHED THEN INSERT clauses with a 'WHERE' condition in each clause to filter out potentially problematic records. Log these filtered records to using a separate 'INSERT statement after the 'MERGE operation.
• E. Employ the 'MERGE statement with 'WHEN MATCHED THEN UPDATE' and 'WHEN NOT MATCHED THEN INSERT clauses, and use a stored procedure that executes the 'MERGE statement and then conditionally inserts rejected records into the 'SALES SUMMARY ERRORS' table based on criteria defined within the stored procedure. This will use the table function on the output.