Free Snowflake DEA-C02 Exam Dumps Questions & Answers

You are configuring cross-cloud replication for a Snowflake database named 'SALES DB' from an AWS (us-east-I) account to an Azure (eastus) account. You have already set up the necessary network policies and security integrations. However, replication is failing with the following error: 'Replication of database SALES DB failed due to insufficient privileges on object 'SALES DB.PUBLIC.ORDERS'.' What is the MOST LIKELY cause of this issue, and how would you resolve it? (Assume the replication group and target database exist).

• A.  The user account performing the replication does not have the 'ACCOUNTADMIN' role in the AWS account. Grant the 'ACCOUNTADMIN' role to the user.
• B.  The replication group does not have the necessary permissions to access the 'ORDERS' table in the AWS account. Grant the 'OWNERSHIP' privilege on the 'ORDERS table to the replication group: 'GRANT OWNERSHIP ON TABLE SALES DB.PUBLIC.ORDERS TO REPLICATION GROUP
• C.  The network policy is blocking access to the ORDERS table. Update the network policy to allow access to the ORDERS table.
• D.  The replication group is missing the 'ORDERS' table. Alter the replication group to include the 'ORDERS' table: 'ALTER REPLICATION GROUP ADD DATABASE SALES DB;'
• E.  The target Azure account does not have sufficient storage capacity. Increase the storage quota for the Azure account.

You are troubleshooting a slowly performing query in Snowflake that aggregates data from a large ORDERS table (10 billion rows) partitioned by ORDER DATE. The query execution plan shows significant 'Remote Spill to Disk'. Which of the following actions would be MOST effective in reducing the spill and improving query performance? Assume all statistics are up-to-date and the data is properly clustered by ORDER_DATE.

• A.  Reduce the number of columns selected in the query, only selecting those that are essential for the aggregation.
• B.  Optimize the query to leverage data pruning based on ORDER DATE by ensuring the query filters on a specific or limited range of ORDER DATE values.
• C.  Rewrite the query to use window functions instead of aggregate functions.
• D.  Increase the virtual warehouse size. This will provide more memory for the query to execute.
• E.  Increase the value of the parameter. This allows the warehouse to scale up further if needed.

You are tasked with processing streaming data in Snowflake using Snowpark Python. The raw data arrives in a DataFrame raw events' with the following schema: 'event id: string', 'event_time: timestamp', 'user id: string', and 'event data: string'. You need to perform the following data transformations: 1 . Extract a specific value from the JSON 'event_data' using the 'get' function to find the 'product_id' and create a new column named 'product id' of type STRING. 2. Filter the DataFrame to include only events where the is NOT NULL and the is within the last hour. 3. Aggregate the filtered data to count the number of events per 'product id'. Which of the following code snippets correctly performs these transformations in an efficient and performant manner?

• A.  Option A
• B.  Option B
• C.  Option D
• D.  Option C
• E.  Option E

A data engineering team is building a data pipeline in Snowflake. They are using tasks and streams to incrementally load data into a fact table. The team needs to monitor the pipeline's performance and ensure data lineage. What are the valid and most effective techniques to ensure that this pipeline adheres to compliance and governance rules?

• A.  Use a third-party data catalog to track lineage, monitor task performance via 'TASK_HISTORY, and ignore data masking and row-level security policies for simplicity in the initial implementation.
• B.  Leverage Snowflake's replication features for disaster recovery, monitor only the replication lag, and disable all security policies to improve performance since those tasks have already been validated during the initial deployment of the software.
• C.  Implement Snowflake's Data Lineage and Object Dependencies features to track data flow automatically, create Alerts based on 'TASK HISTORY to monitor task failures, and enforce data masking and row-level security policies at the table level. Use Snowflake's tags to categorise and classify objects.
• D.  Use Account Usage views like 'TASK HISTORY and 'STREAM_LAG' to track task execution and stream latency, create stored procedures to log metadata about each pipeline run to a separate metadata table, and rely on developers to manually document the pipeline's data flow and policy enforcement.
• E.  Enable Snowflake Horizon features, which include Data Lineage, Object Dependencies and Discovery and integrate it with the data lake and also tag the data pipeline.

You're designing a near real-time data pipeline for clickstream data using Snowpipe Streaming. The data volume is extremely high, with bursts exceeding 1 million events per second. Your team reports intermittent ingestion failures and latency spikes. Considering the constraints of Snowpipe Streaming, which of the following strategies would be MOST effective in mitigating these issues, assuming the data format is optimized and network latency is minimal?

• A.  Implement a message queue (e.g., Kafka) in front of Snowpipe Streaming to buffer incoming events and smooth out the traffic spikes.
• B.  Switch from Snowpipe Streaming to Classic Snowpipe, as it is more resilient to high data volumes.
• C.  Increase the number of Snowflake virtual warehouses to handle the increased load.
• D.  Implement client-side retry logic with exponential backoff and jitter to handle transient errors and avoid overwhelming the service.
• E.  Reduce the size of each micro-batch being sent to Snowpipe Streaming to minimize the impact of individual failures.