ARA-C01 Question 228
Single answerStreams and tasksA retail company loads order events into a raw table continuously throughout the day. An architect designs a downstream process that should transform only newly arrived rows every 5 minutes and merge them into a curated table. The company wants to avoid running compute when no new data has arrived. Which design best meets these requirements while preserving reliable incremental processing?
- A
Create a standard stream on the raw table and a scheduled task every 5 minutes with a WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream') condition that executes a MERGE from the stream into the curated table.
- B
Create a view over the raw table and schedule a task every 5 minutes to MERGE from the view into the curated table. Snowflake will automatically process only changed rows from the view.
- C
Create a temporary stream on the raw table and a serverless task every 5 minutes without a WHEN clause. The task can check the stream contents internally and exit if there is no data, which fully avoids compute usage.
- D
Create a stream on the curated table and a task that inserts new rows back into the curated table every 5 minutes. This ensures only committed changes are processed once.
Show answer and explanation
Correct answer: A
Explanation
The best design is to create a stream on the source raw table and use a task with a WHEN SYSTEM$STREAM_HAS_DATA condition. Streams in Snowflake provide change data capture by exposing inserts and, for standard tables, update/delete metadata since the stream offset. Tasks can be scheduled and conditionally triggered using a WHEN clause so execution is skipped when the stream has no data, reducing unnecessary compute usage. A MERGE statement is commonly used to apply incremental changes safely into the curated target table. This pattern aligns with Snowflake best practices for event-driven or micro-batch ELT pipelines using streams and tasks. Key documentation areas include Snowflake Streams, Tasks, task WHEN conditions, and the SYSTEM$STREAM_HAS_DATA function.
- A. Correct.
Correct. A standard stream records table change data capture metadata so downstream SQL can consume only rows changed since the last offset. Pairing the task with WHEN SYSTEM$STREAM_HAS_DATA(...) is the recommended pattern to avoid starting task compute when the stream has no unread change data. Running MERGE from the stream into the curated table supports idempotent incremental processing and is a common architecture for ELT pipelines in Snowflake.
- B. Incorrect.
Incorrect. A regular view does not provide stream-like change tracking semantics by itself for incremental consumption. Querying a view every 5 minutes would typically require re-evaluating the underlying data set, and Snowflake does not automatically limit processing to only newly changed rows simply because the source is a view. This option reflects a common misconception that views provide CDC behavior.
- C. Incorrect.
Incorrect. A temporary stream is generally unsuitable for a durable production pipeline because it is session-scoped and not intended for persistent orchestration. In addition, if the task starts and then exits after checking for data, compute has already been invoked. The requirement is to avoid running compute when no new data arrives, which is what a task WHEN condition helps accomplish before execution starts.
- D. Incorrect.
Incorrect. Streams are typically created on the source object whose changes need to be consumed, not on the target table to drive loading back into itself. Creating a stream on the curated table and writing back into that same table does not address the requirement to process new raw events incrementally and can create an incorrect or circular design.