Study with Snowflake : DEA-C02 Exam Torrent as your best preparation materials

You are tasked with building a Snowpipe to ingest JSON data from an AWS S3 bucket into a Snowflake table named 'SALES DATA'. The data is landing in the bucket frequently, and you want to use Snowpipe's auto-ingest feature. However, you are observing significant latency in data appearing in your Snowflake table after it lands in S3, despite verifying that S3 event notifications are correctly configured and the Snowflake event queue is receiving them. You've already checked that the pipe is enabled and has the necessary permissions. The Snowflake Pipe definition is as follows:

What is the MOST LIKELY reason for this delay, and what steps can you take to further troubleshoot?

• A. Snowpipe auto-ingest only supports CSV files. Convert your JSON data to CSV format before loading.
• B. There is a backlog of files in the internal Snowflake queue waiting to be processed. Monitor the 'SYSTEM$PIPE STATUS' function and consider increasing the 'MAX CONCURRENCY' parameter (if applicable, based on underlying infrastructure considerations) on the pipe definition.
• C. The Snowflake virtual warehouse associated with the pipe is undersized. Increase the warehouse size to improve ingestion performance.
• D. Snowflake's internal metadata cache is out of sync. Run 'ALTER PIPE SALES PIPE to refresh the cache.
• E. The S3 bucket is not in the same region as the Snowflake account. Ensure the S3 bucket and Snowflake account are in the same region to reduce network latency.

A data engineering team has implemented a continuous data pipeline that loads data into a Snowflake table named 'SALES DATA' They notice that the pipeline intermittently experiences performance degradation, particularly during peak business hours. The team wants to implement alerts to proactively identify and address these performance issues. Which of the following approaches would be MOST effective for monitoring the pipeline and triggering alerts based on specific performance metrics related to data loading?

• A. create a custom Snowflake Alert that triggers when the function for the 'SALES_DATA' table indicates significant delay in data loading. Use a Snowflake Notification Integration to send alerts via email or Slack.
• B. Enable Snowflake's query acceleration service. This service automatically analyzes query performance and identifies opportunities for optimization, removing the need for manual monitoring and alerting. Use Snowflake's resource monitors to track credit usage.
• C. Create a Snowflake Alert based on a metric in the Account Usage views (e.g., that identifies when load durations for the data warehouse associated with the data pipeline exceed a specified threshold. Configure a Notification Integration to route alerts to a designated channel.
• D. Create a Snowflake Task that periodically queries the 'QUERY_HISTORY view, calculates the average load duration for 'SALES_DATX, and triggers an alert if the duration exceeds a predefined threshold. Use a Stored Procedure to handle the alert logic and send notifications.
• E. Implement a data streaming service that monitors the 'SALES_DATX table in real-time. The streaming service should track the number of rows inserted per minute and trigger an alert if the insertion rate drops below a predefined threshold. No Snowflake object or Alert required.

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 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.
• 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 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.
• D. 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
• E. 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.

Your company utilizes Snowflake Streams and Tasks for continuous data ingestion and transformation. A critical task, 'TRANSFORM DATA', consumes data from a stream 'RAW DATA STREAW on table 'RAW DATA' and loads it into a reporting table 'REPORTING TABLE. You observe that 'TRANSFORM DATA is failing intermittently with a 'Stream is stale' error. What steps can you take to diagnose and resolve this issue? Choose all that apply.

• A. Increase the parameter at the database level to ensure Time Travel data is available for a longer period.
• B. Use the "AT' or 'BEFORE clause when querying the stream to explicitly specify a point in time to consume data from.
• C. Modify the task definition to use the 'WHEN condition to prevent execution when the stream is empty.
• D. Ensure that the ' TRANSFORM DATA' task is consuming the stream data frequently enough to prevent the stream from becoming stale.
• E. Drop and recreate the stream and task to reset their states.

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. 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.
• 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. Recluster the table using 'ALTER TABLE TRANSACTIONS RECLUSTER$ and adjust the virtual warehouse size to maximize resource allocation during the recluster operation.