COF-C03 Question 232
Single answerStreamsA data engineering team loads new and changed customer records into a Snowflake table named CUSTOMER_STAGE throughout the day. Every 15 minutes, a task runs a MERGE statement to apply only the newly changed rows to a target table named CUSTOMER_DIM. The team wants to avoid rescanning the entire staging table each time and also wants to ensure that once a change has been successfully processed, it is not processed again in the next task run.
Which Snowflake object should the team use to meet this requirement most effectively?
- A
Create a standard stream on CUSTOMER_STAGE and have the MERGE read from the stream
- B
Create a materialized view on CUSTOMER_STAGE and have the MERGE read from the view
- C
Create a task on CUSTOMER_STAGE so Snowflake automatically tracks row-level changes for the MERGE
- D
Create a transient clone of CUSTOMER_STAGE before each task run and compare it to the previous clone
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a standard stream on the staging table and have the scheduled task consume that stream in the MERGE. Streams in Snowflake are designed for change data capture by storing an offset against the source object and exposing the net transactional changes since that offset. After a successful DML transaction consumes the stream, the offset advances, which supports incremental ELT patterns and avoids reprocessing the same rows. This is a common best practice for loading dimension and fact tables incrementally. Materialized views improve query performance but are not CDC objects. Tasks orchestrate scheduled execution but do not track data changes by themselves. Cloning is useful for environment isolation and point-in-time copies, not for routine incremental change consumption. See Snowflake documentation on Streams and Tasks for CDC-based pipelines and MERGE-based incremental processing.
- A. Correct.
Correct. A standard stream records change data capture (CDC) information for a table, including inserted and deleted rows, and exposes only the unconsumed changes since the stream offset. When the task uses the stream as the source in a DML transaction such as a MERGE and successfully commits, the stream offset advances so those processed changes are not returned again on the next run. This is the intended Snowflake pattern for incremental processing without rescanning the full source table.
- B. Incorrect.
Incorrect. A materialized view stores the results of a query for performance optimization, but it does not provide CDC semantics or track which row changes have already been consumed by a downstream process. Reading from a materialized view would not solve the requirement to process each change once and then advance an offset.
- C. Incorrect.
Incorrect. Tasks schedule SQL execution, but they do not themselves track row-level changes in source tables. A common misconception is that tasks provide change tracking because they are often used together with streams. In reality, the stream tracks the changes, and the task simply automates the SQL that consumes them.
- D. Incorrect.
Incorrect. Cloning can create point-in-time copies efficiently, but using transient clones every 15 minutes to detect deltas is unnecessarily complex and not the intended mechanism for incremental CDC processing. It would also require custom comparison logic and would not provide the simple consume-once behavior that streams offer.