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

You accidentally truncated a large table named 'SALES DATA' in your 'REPORTING DB" database. You realize this happened 2 days ago, and your account has the default Time Travel retention of 1 day. You need to recover this table with minimal downtime. Analyze the situation and determine the best course of action, considering cost and recovery time.

• A. Create a clone of the table using the 'AT clause and a timestamp from 1 day ago. This would prevent any additional cost.
• B. Increase the account-level to 2 days and then use the UNDROP TABLE SALES_DATA' command.
• C. Raise a support ticket requesting data recovery from failsafe. Since data retention period has expired.
• D. Because the data retention period has expired, the table is unrecoverable using Snowflake's built-in features; you must restore from an external backup solution if available.
• E. Immediately contact Snowflake Support to initiate a restore from Fail-safe, understanding that this process may take several hours or even days.

You have a Snowpark Python application that performs complex calculations on a large dataset stored in Snowflake. The application is currently running slowly. After profiling, you've identified that the UDFs you're using are the bottleneck. These UDFs perform custom data transformations using a third-party Python library which has a significant initialization overhead. Which of the following strategies would be MOST effective to optimize performance, minimizing both runtime and resource consumption?

• A. Rewrite the UDFs in SQL using Snowflake's built-in functions to avoid the overhead of Python execution. If the library's functions aren't available, consider creating external functions using a cloud provider's serverless compute service.
• B. Implement UDF caching at the Snowflake level by setting the 'VOLATILE property to 'IMMUTABLE or 'STABLE' (if appropriate), and leverage the Snowflake query result cache.
• C. Increase the size of the Snowflake warehouse being used for the Snowpark workload. This will provide more CPU and memory resources.
• D. Convert the Snowpark Python application to a Snowpark Java application as Java generally offers better performance than Python.
• E. Use Snowpark's 'pandas_udf with 'vectorized=True' and pre-initialize the third-party library within the UDF's execution context using a closure or similar technique for reuse across batches.

You are designing a Snowpark Python application to process streaming data from a Kafka topic and land it into a Snowflake table 'STREAMED DATA. Due to the nature of streaming data, you want to achieve the following: 1. Minimize latency between data arrival and data availability in Snowflake. 2. Ensure exactly-once processing semantics to prevent data duplication. 3. Handle potential schema evolution in the Kafka topic without breaking the pipeline. Which combination of Snowpark and Snowflake features, applied with the correct configuration, would BEST satisfy these requirements? Select all that apply.

• A. Implement a Snowpark Python UDF that consumes data directly from the Kafka topic using a Kafka client library. Write data into 'STREAMED_DATX within a single transaction. Use a structured data type for the 'STREAMED DATA'.
• B. Use Snowflake Connector for Kafka to load data into a staging table. Then, use Snowpark Python to transform and load the data into 'STREAMED_DATR within a single transaction. Implement schema evolution logic in the Snowpark code to handle changes in the Kafka topic schema.
• C. Utilize Snowflake Streams on in conjunction with Snowpark to transform and cleanse the data after it has been ingested by Snowpipe. Apply a merge statement to update an external table of parquet files.
• D. Use Snowpipe with auto-ingest and configure it to trigger on Kafka topic events. Define a VARIANT column in 'STREAMED_DATX to handle schema evolution.
• E. Use Snowflake's native Kafka connector to load data into a staging table. Then, use a Task and Stream combination, using a Snowpark Python UDF, to transform and load the data into 'STREAMED DATA' within a single transaction, handling schema evolution by casting columns to their new types or dropping missing column data.

You are tasked with building a data pipeline using Snowpark to process sensor data from IoT devices. The data arrives in near real-time as JSON payloads, and you need to transform and load it into a Snowflake table named 'SENSOR DATA'. The transformation logic involves extracting specific fields, converting data types, and filtering out records based on a timestamp. Consider performance optimization for large data volumes. Which of the following approaches, in combination, would be MOST efficient for this scenario?

• A. Employing Snowpipe to ingest the raw JSON data into a VARIANT column in a staging table, followed by a Snowpark DataFrame operation using 'functions.get' to extract and transform the data, and finally loading into 'SENSOR DATA'
• B. Creating an external table pointing to the JSON data in cloud storage and using Snowpark DataFrames to read the external table, apply transformations, and load the result into 'SENSOR DATA'.
• C. Using a Snowpark Python UDF to parse JSON and perform transformations, loading the result into a temporary table, and then merging into 'SENSOR DATA'.
• D. Leveraging Snowflake's native JSON parsing functions within a SQL transformation step implemented as a Snowpark DataFrame operation, combined with a Snowpipe for initial data ingestion into a staging table.
• E. Using a stored procedure written in Java to parse the JSON data and insert directly into the "SENSOR DATA' table.

A daily process loads data into a Snowflake table named 'TRANSACTIONS using a COPY INTO statement. The table is clustered on 'TRANSACTION DATE'. Over time, you observe a significant degradation in query performance when querying data within specific date ranges. Analyzing the 'SYSTEM$CLUSTERING INFORMATION' function output for the 'TRANSACTIONS' table reveals a low 'effective clustering_ratio' and a high 'average_overlaps'. Which combination of actions below would BEST address the performance degradation and improve query efficiency?

• A. Drop the current clustered table and create a new table with partition by clauses
• B. Recluster the table using 'ALTER TABLE TRANSACTIONS RECLUSTER$ and adjust the virtual warehouse size to maximize resource allocation during the recluster operation.
• C. Create a new table with the desired clustering and load data using 'CREATE TABLE AS SELECT statement.
• D. Drop the existing clustering key on 'TRANSACTION_DATE, then recreate it with a different clustering key such as 'HASH(TRANSACTION_ID)'.
• E. Implement a data maintenance schedule that regularly reclusters the table using 'ALTER TABLE TRANSACTIONS RECLUSTER;' during off-peak hours and monitor the 'SYSTEM$CLUSTERING INFORMATION' function periodically.