ADA-C01 Question 537
Single answerStreams and tasksA data engineering team loads raw order events into ORDERS_RAW throughout the day. They created a standard stream ST_ORDERS_RAW on ORDERS_RAW and a scheduled task TASK_APPLY_ORDERS that runs every 5 minutes to MERGE changes from the stream into an ORDERS_CURATED table. The task currently runs on schedule even when there is no new data, which wastes warehouse credits. The team wants the task to execute only when the stream has unread change data, without missing any committed changes. What is the BEST approach?
- A
Modify TASK_APPLY_ORDERS to include a WHEN SYSTEM$STREAM_HAS_DATA('ST_ORDERS_RAW') condition.
- B
Replace the stream with a view on ORDERS_RAW and schedule the task to query the view every 5 minutes.
- C
Set the task schedule to USAGE_BASED instead of a CRON or interval schedule so Snowflake starts it only when the stream changes.
- D
Create a second task that resumes TASK_APPLY_ORDERS whenever rows are inserted into ORDERS_RAW.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to keep the scheduled task and add a WHEN SYSTEM$STREAM_HAS_DATA('ST_ORDERS_RAW') condition. In Snowflake, streams track table change data using an offset, and tasks can evaluate conditions before running SQL. This is a common best practice for minimizing compute consumption when a CDC pipeline is idle. The key point is that the schedule still exists, but the task only executes when the stream has unread records. Snowflake documentation for tasks describes the WHEN clause, and the SYSTEM$STREAM_HAS_DATA function is documented for use with streams to determine whether change data is currently available. Alternatives such as replacing the stream with a view or trying to emulate row-level triggers do not provide the same reliable CDC behavior and are not the recommended administrative design.
- A. Correct.
Correct. Snowflake tasks support a WHEN clause, and SYSTEM$STREAM_HAS_DATA is specifically designed to let a task evaluate whether a stream currently contains CDC records. With this pattern, the task is triggered on its schedule but only executes when the function returns TRUE, reducing unnecessary warehouse usage while preserving stream-based change processing. This is the recommended approach for conditional execution with streams and tasks.
- B. Incorrect.
Incorrect. A view does not provide CDC semantics or stream offsets. Replacing the stream with a view would remove the ability to reliably consume only new changes since the last offset. The task would still need to scan underlying data repeatedly, increasing cost and complexity, and it would not solve the requirement to process unread committed changes safely.
- C. Incorrect.
Incorrect. There is no task schedule type called USAGE_BASED for automatically firing tasks when a stream changes. Snowflake tasks run on defined schedules or as part of task graphs after predecessor tasks complete. Conditional execution for streams is implemented with a WHEN clause, not with an event-driven schedule type like this.
- D. Incorrect.
Incorrect. Snowflake does not support a native trigger that fires directly on table inserts to resume another task. Also, resuming a task is an administrative state change, not the right mechanism for conditional CDC processing. The common misconception is treating Snowflake tasks like database triggers; in Snowflake, streams plus task schedules and WHEN conditions are the intended pattern.