ARA-C01 Question 359
Single answerStreams and tasksA retail company loads order events continuously into RAW.ORDERS using Snowpipe. An architect must design a near-real-time transformation pipeline that meets these requirements: (1) only newly arrived rows should be processed on each run, (2) the pipeline should not consume warehouse resources when no new data has arrived, and (3) duplicate processing must be avoided if a previous task run failed before the transformed rows were committed. Which design best meets these requirements?
- A
Create a stream on RAW.ORDERS and a scheduled task that runs every minute with a WHEN SYSTEM$STREAM_HAS_DATA('orders_stream') condition, and have the task consume the stream within a single transaction while merging into the target table.
- B
Create a view over RAW.ORDERS filtered by LOAD_TIME > CURRENT_TIMESTAMP() - INTERVAL '1 MINUTE', and schedule a task every minute to insert from the view into the target table.
- C
Create a stream on the target transformed table and schedule a task every minute to read directly from RAW.ORDERS; if the task fails, re-run it manually because streams retain all history indefinitely.
- D
Create a triggered task on RAW.ORDERS without a stream, because tasks can detect row-level changes on base tables directly and only wake compute when new rows are inserted.
Show answer and explanation
Correct answer: A
Explanation
The best design is to pair a stream with a task and guard execution with SYSTEM$STREAM_HAS_DATA. This is the standard Snowflake pattern for incremental ELT pipelines. Streams provide change data capture semantics by exposing inserted, deleted, and updated row changes relative to an offset. A task can be scheduled frequently, but the WHEN clause prevents unnecessary task execution when the stream has no data, which helps reduce wasted compute. To avoid duplicate processing after failure, the task should consume the stream and write to the target in a single transactional statement or transaction, commonly using MERGE for idempotent upserts. If the transaction does not commit, the stream offset is not advanced, so the same source changes remain available for reprocessing. In contrast, time-window polling from the base table is error-prone and not truly change-data-driven. Snowflake documentation and best practices for streams and tasks emphasize using streams for CDC-style incremental processing, using tasks for orchestration, and using SYSTEM$STREAM_HAS_DATA to conditionally run work only when changes are available.
- A. Correct.
Correct. A stream records table change data so the task can process only new rows since the last successful consumption. Adding a WHEN SYSTEM$STREAM_HAS_DATA(...) condition allows Snowflake to evaluate whether data is available before starting the task, helping avoid unnecessary warehouse usage when no new data has arrived. Consuming the stream and applying a MERGE in a single transaction is the right pattern for idempotent incremental processing: if the transaction fails, the stream offset is not advanced, so the same changes remain available for the next successful run instead of being partially lost or duplicated.
- B. Incorrect.
Incorrect. Using a time-based filter on the base table is not a reliable CDC design. Rows can be missed or reprocessed due to clock skew, ingestion timing, retries, or task delays. This approach also causes the task to run on a schedule regardless of whether data arrived, which does not meet the requirement to avoid compute consumption when idle. It lacks the transactional offset behavior of streams, so failure handling is weaker and duplicate processing is more likely.
- C. Incorrect.
Incorrect. The stream should be created on the source object whose changes need to be tracked, not on the transformed target table for this use case. Also, the statement about streams retaining all history indefinitely is false. Streams rely on table versioning and have retention-related considerations; they are not an unlimited permanent replay log. Reading directly from RAW.ORDERS without using the source stream would not guarantee processing only new rows, and manual reruns are not an architectural solution to duplicate avoidance.
- D. Incorrect.
Incorrect. Tasks do not natively track row-level changes on base tables by themselves. To build event-driven incremental processing in Snowflake, the standard pattern is to use a stream to capture changes and a task to act on them. While triggered task patterns exist in conjunction with change data availability checks, a task alone cannot detect and process only changed rows from a table without an explicit mechanism such as a stream.