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

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 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.
• B. The consumer sets up a scheduled task in Snowflake to periodically query the 'SHOW VERSIONS command for the Listing.
• C. The data provider manually sends email notifications to each consumer whenever a new version is created.
• 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 have a Snowflake Stream named 'PRODUCT CHANGES' created on a table 'PRODUCTS'. A downstream task attempts to consume records from the stream, but occasionally fails with a 'Table PRODUCTS has been altered' error. The 'PRODUCTS' table undergoes DDL changes (e.g., adding/dropping columns) infrequently, but these changes are necessary for evolving business requirements. How can you design a more resilient data pipeline that minimizes disruptions caused by DDL changes to the 'PRODUCTS' table while still leveraging the 'PRODUCT CHANGES' stream?

• A. Create a new Stream on the 'PRODUCTS' table after each DDL change. The downstream task should dynamically switch to consuming from the new stream when the old stream encounters an error.
• B. Use a materialized view instead of a standard view as the source for the stream. Materialized views are less susceptible to issues when the underlying base table changes
• C. Create a task that monitors the 'PRODUCTS' table for DDL changes using 'INFORMATION SCHEMA. TABLES'. When a change is detected, pause the downstream task, execute the DDL change, and then resume the downstream task after a short delay.
• D. Implement error handling in the downstream task to automatically retry consuming records from the 'PRODUCT CHANGES' stream after a delay, assuming the DDL changes will be completed quickly.
• E. Before executing any DDL changes on the 'PRODUCTS' table, drop and recreate the 'PRODUCT CHANGES' stream. This ensures the stream definition is always in sync with the table structure.

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 is missing the 'ORDERS' table. Alter the replication group to include the 'ORDERS' table: 'ALTER REPLICATION GROUP ADD DATABASE SALES DB;'
• C. 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
• D. The network policy is blocking access to the ORDERS table. Update the network policy to allow access to the ORDERS table.
• E. The target Azure account does not have sufficient storage capacity. Increase the storage quota for the Azure account.

You are implementing row access policies on a 'SALES DATA table to restrict access based on the 'REGION' column. Different users are allowed to see data only for specific regions. You have a mapping table 'USER REGION MAP' with columns 'USERNAME' and 'REGION'. You want to create a row access policy that dynamically filters the 'SALES DATA' based on the user and their allowed region. Which of the following options represents a correct approach to create and apply this row access policy?

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

You're using Snowpipe Streaming to ingest JSON data into a Snowflake table. The JSON data contains nested objects and arrays. You're encountering errors related to data type mismatches during ingestion. The target table schema is defined with specific data types for each column. Which of the following approaches is MOST effective for handling this data type mismatch issue within the Snowpipe Streaming context, considering minimal transformation?

• A. Modify the client-side application to cast the data to the correct data type before sending it to Snowpipe Streaming.
• B. Create a Stored Procedure in Snowflake to transform the data after it has been ingested using Snowpipe Streaming.
• C. Use a variant column in the Snowflake table and perform data type casting during querying.
• D. Use a COPY INTO statement with a transformation function to cast the data during ingestion.
• E. Adjust the auto_ingest property of the Snowpipe object to force data type conversion.