SnowPro Advanced Data Engineer Exam

Exam SnowPro Advanced Data Engineer All QuestionsBrowse all questions from this exam

Question 54

A company is using Snowpipe to bring in millions of rows every day of Change Data Capture (CDC) into a Snowflake staging table on a real-time basis. The CDC needs to get processed and combined with other data in Snowflake and land in a final table as part of the full data pipeline.

How can a Data Engineer MOST efficiently process the incoming CDC on an ongoing basis?

Create a stream on the staging table and schedule a task that transforms data from the stream, only when the stream has data.

Transform the data during the data load with Snowpipe by modifying the related COPY INTO statement to include transformation steps such as CASE statements and JOINS.

Schedule a task that dynamically retrieves the last time the task was run from information_schema.task_history and use that timestamp to process the delta of the new rows since the last time the task was run.

Use a CREATE OR REPLACE TABLE AS statement that references the staging table and includes all the transformation SQL. Use a task to run the full CREATE OR REPLACE TABLE AS statement on a scheduled basis.

Correct Answer: A

To process the incoming CDC data efficiently, creating a stream on the staging table and scheduling a task to transform data from the stream only when there is data is the most effective approach. This method ensures that only the changes are processed, minimizing unnecessary computations and optimizing resource usage. The other options involve either complex transformations during the data load or processes that could result in inefficient handling of data updates or unnecessary full table operations, which are not ideal for ongoing, real-time data processing tasks.

Discussion

stopthisnowOption: A

A makes sense. D is definitely wrong. Can't drop and create table every time, it will also loose previous data. Needs to be incremental