DEA-C02 Question #54: Real Exam Question with Answer & Explanation

Question

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?

Options

• ACreate a stream on the staging table and schedule a task that transforms data from the stream,
• BTransform the data during the data load with Snowpipe by modifying the related COPY INTO
• CSchedule a task that dynamically retrieves the last time the task was run from
• DUse a CREATE OR REPLACE TABLE AS statement that references the staging table and

Explanation

Option A is correct because Snowflake Streams natively track the delta (inserts, updates, deletes) on a staging table as a DML changelog, and a Task can be scheduled to fire only when the stream contains new rows - making CDC processing event-driven, incremental, and efficient without re-scanning the full table. Option B is wrong because Snowpipe's COPY INTO is designed purely for ingestion, not transformation logic; adding complex transformation there would be unsupported and fragile. Option C is wrong because manually tracking the last run time with a task is a brittle, homegrown approach that duplicates what Streams already do automatically and atomically. Option D is wrong because CREATE OR REPLACE TABLE AS rebuilds the entire table from scratch on every execution, which is extremely expensive and not incremental - the opposite of efficient for millions of daily rows.

Memory tip: Think "Stream = bookmark, Task = trigger." A Stream holds your place in the change log; a Task acts on it only when something new arrives - together they form Snowflake's native CDC processing pattern.

No community discussion yet for this question.