ARA-C01 Question 358
Single answerStreams and tasksA retail company loads order events continuously into RAW.ORDERS using Snowpipe. An architect needs to build an incremental ELT pipeline that runs every 5 minutes and inserts only newly arrived rows into CURATED.ORDERS_ENRICHED. The company wants to avoid consuming warehouse resources when no new data has arrived, and it must prevent reprocessing the same change records. Which design best meets these requirements?
- A
Create a stream on RAW.ORDERS and a triggered task that executes an INSERT INTO CURATED.ORDERS_ENRICHED ... SELECT ... FROM the stream, using WHEN SYSTEM$STREAM_HAS_DATA('RAW_ORDERS_STREAM').
- B
Create a regular view on RAW.ORDERS and schedule a task every 5 minutes to MERGE from the view into CURATED.ORDERS_ENRICHED; the view will return only new rows since the last task run.
- C
Create a stream on CURATED.ORDERS_ENRICHED and schedule a task every 5 minutes to INSERT into CURATED.ORDERS_ENRICHED from RAW.ORDERS; the target-table stream will prevent duplicate processing automatically.
- D
Create a materialized view on RAW.ORDERS and a triggered task that reads from the materialized view; Snowflake materialized views maintain change history so only unprocessed rows are returned.
- E
Create a stream on RAW.ORDERS and schedule a task every 5 minutes without a WHEN condition; streams do not advance their offset unless explicitly altered, so reruns will not reprocess rows.
Show answer and explanation
Correct answer: A
Explanation
The best design is to use a stream on the source table and a task gated by SYSTEM$STREAM_HAS_DATA. This pattern is a Snowflake best practice for incremental pipelines because streams provide CDC over tables by tracking an offset, and tasks automate downstream processing. Using WHEN SYSTEM$STREAM_HAS_DATA('stream_name') allows the task to run only when the stream contains change data, which helps minimize unnecessary compute consumption. To prevent reprocessing, the task should consume the stream in a DML statement such as INSERT, MERGE, CTAS, or COPY INTO where applicable; once successfully consumed in the transaction, the stream offset advances. Views and materialized views do not maintain consumer offsets for CDC. A stream must be created on the object where changes originate, which in this scenario is RAW.ORDERS. This aligns with Snowflake documentation on streams for change tracking and tasks with conditional execution using WHEN and SYSTEM$STREAM_HAS_DATA.
- A. Correct.
Correct. A stream on RAW.ORDERS provides change data capture semantics by exposing table changes since the stream offset. A task can be configured with a WHEN clause using SYSTEM$STREAM_HAS_DATA to avoid running when the stream is empty, which helps avoid unnecessary warehouse usage. When the task successfully consumes the stream in a DML statement such as INSERT ... SELECT FROM stream, the stream offset advances, preventing the same change records from being processed again.
- B. Incorrect.
Incorrect. A standard view does not track which rows are new since the last task run. It is just a stored query definition over the base table. Without additional state management, a task reading from a view would not inherently know which rows were already processed, so this design does not satisfy the requirement to prevent reprocessing.
- C. Incorrect.
Incorrect. A stream on the target table does not solve the core requirement, which is to capture new rows arriving in the source table RAW.ORDERS. Reading directly from RAW.ORDERS each run would require custom logic to identify newly arrived rows. Also, a stream on the target does not automatically deduplicate or prevent duplicate inserts from source-side reprocessing.
- D. Incorrect.
Incorrect. Materialized views improve query performance for certain repeated query patterns, but they are not a CDC mechanism and do not expose only unprocessed source changes the way streams do. A triggered task based on a materialized view would not provide offset-based change consumption.
- E. Incorrect.
Incorrect. While a stream does maintain an offset, the statement is wrong because a task scheduled without a WHEN condition will still start on its schedule, potentially consuming compute even when there is no data. Also, stream offsets are advanced when the stream is consumed by eligible DML in a transaction, not by explicit ALTER statements. The design therefore fails the requirement to avoid unnecessary warehouse usage.