ARA-C01 Question 227
Single answerStreams and tasksA retail company loads order events continuously into RAW_DB.PUBLIC.ORDERS using Snowpipe. An architect must design an incremental ELT pattern that meets these requirements: only newly arrived rows should be transformed into CURATED_DB.SALES.ORDERS_ENRICHED, duplicate processing must be avoided even if no data arrives for several hours, and compute cost should be minimized. Which design best meets these requirements?
- A
Create a stream on RAW_DB.PUBLIC.ORDERS and a triggered task that executes only when SYSTEM$STREAM_HAS_DATA returns TRUE; the task reads from the stream and merges changes into CURATED_DB.SALES.ORDERS_ENRICHED.
- B
Create a stream on CURATED_DB.SALES.ORDERS_ENRICHED and schedule a task every minute to insert all rows from RAW_DB.PUBLIC.ORDERS into the target table.
- C
Schedule a task every minute to query RAW_DB.PUBLIC.ORDERS directly with a timestamp filter based on CURRENT_TIMESTAMP and insert matching rows into CURATED_DB.SALES.ORDERS_ENRICHED; no stream is needed.
- D
Create a temporary stream on RAW_DB.PUBLIC.ORDERS and a scheduled task that runs hourly regardless of whether data exists, then truncates the source table after each run.
Show answer and explanation
Correct answer: A
Explanation
The best design is to use a stream on the source table and a task that only runs when the stream has data. Snowflake streams provide change data capture by tracking row-level inserts, deletes, and updates between transactional offsets. Tasks can be triggered conditionally using a WHEN clause such as SYSTEM$STREAM_HAS_DATA('<stream_name>'), which helps avoid waking compute when there is nothing to process. This aligns with Snowflake best practices for incremental ELT: capture source changes with streams, process them with tasks, and use MERGE into curated targets for idempotent upsert behavior. Compared with custom timestamp filtering, streams reduce the operational burden of watermark tracking and lower the risk of missed or duplicated processing. Relevant Snowflake documentation includes the sections on Streams for CDC, Tasks and task WHEN conditions, and the SYSTEM$STREAM_HAS_DATA function.
- A. Correct.
Correct. A stream records change data capture metadata for the source object, allowing downstream processing of only new or changed rows since the last consumption point. Using a triggered task with a WHEN condition based on SYSTEM$STREAM_HAS_DATA minimizes unnecessary task runs and therefore reduces compute consumption. Reading from the stream and applying a MERGE into the target is a standard Snowflake pattern for incremental processing while avoiding reprocessing of already consumed changes.
- B. Incorrect.
Incorrect. The stream should be created on the source object where changes originate, not on the target table that is being populated. In addition, inserting all rows from the raw table each minute is not incremental and would likely create duplicates or require unnecessary full-table processing. This option reflects a common misconception that streams are primarily for target-state tracking rather than source change capture.
- C. Incorrect.
Incorrect. Querying the base table directly with a timestamp predicate is less reliable for change data capture than using a stream. Timestamp-based logic can miss rows because of clock skew, late-arriving data, task timing gaps, or reruns, and it usually requires custom watermark management. It also does not inherently provide the same consumption semantics as a stream for tracking processed changes.
- D. Incorrect.
Incorrect. Temporary streams are session-scoped and are not appropriate for persistent production pipelines driven by tasks. Running the task hourly regardless of data existence increases unnecessary compute usage, which conflicts with the cost-minimization requirement. Truncating the source table after processing is also typically inappropriate for Snowpipe-loaded landing tables unless there is an explicit retention strategy and no other downstream dependencies.