ADA-C01 Question 536
Single answerStreams and tasksA data engineering team loads raw order events into RAW_DB.PUBLIC.ORDERS every few minutes. They created a standard stream named ORDERS_STRM on the ORDERS table and a task named LOAD_CURATED_ORDERS that runs every 5 minutes to merge new rows into CURATED_DB.PUBLIC.ORDERS_CURATED. Recently, the task has been starting even when no new data has arrived, consuming warehouse credits unnecessarily. The team wants the task to run only when the stream has change data available, while still preserving transactional consistency for downstream processing. Which change should the administrator implement?
- A
Modify the task to include WHEN SYSTEM$STREAM_HAS_DATA('RAW_DB.PUBLIC.ORDERS_STRM') so the task is triggered only when the stream contains CDC records.
- B
Replace the stream with a materialized view on ORDERS and schedule the task against the view, because materialized views refresh only when underlying data changes.
- C
Change the task schedule from every 5 minutes to AFTER ORDERS_STRM so the task runs automatically after the stream is refreshed.
- D
Create a second task that checks INFORMATION_SCHEMA.TABLES for row count changes in ORDERS and resumes LOAD_CURATED_ORDERS only when the row count increases.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to keep the scheduled task and add a WHEN clause using SYSTEM$STREAM_HAS_DATA on the stream. This is a common Snowflake best practice for stream-and-task pipelines: the stream provides native change data capture semantics, and the task condition prevents unnecessary runs when there is nothing to process. In Snowflake, streams are designed to track table change data between offsets, and tasks are designed to execute SQL on a schedule or as part of a task graph. A task graph dependency can reference another task with AFTER, but not a stream. Materialized views and metadata polling are not substitutes for stream-based CDC. Relevant Snowflake documentation areas include Streams, CREATE TASK, task WHEN clauses, and the SYSTEM$STREAM_HAS_DATA function.
- A. Correct.
Correct. Snowflake tasks can use a WHEN clause with SYSTEM$STREAM_HAS_DATA('<stream_name>') to evaluate whether a stream currently has unconsumed change data. This is the recommended pattern for avoiding unnecessary task executions while continuing to use a stream as the CDC source. The task still runs on its schedule, but Snowflake skips execution when the condition evaluates to FALSE, which reduces unnecessary warehouse usage and preserves the stream-based processing model.
- B. Incorrect.
Incorrect. A materialized view is not a replacement for a stream in a CDC pipeline. Materialized views maintain query results for performance optimization; they do not expose change records for transactional consumption the way streams do. This option confuses query acceleration with change capture and would not address the requirement to process new changes incrementally from ORDERS.
- C. Incorrect.
Incorrect. Tasks can depend on other tasks by using AFTER <task_name>, but not AFTER a stream. Streams are queried objects that record table changes; they are not schedulable predecessors in a task graph. This distractor reflects a common misunderstanding between task orchestration dependencies and stream consumption.
- D. Incorrect.
Incorrect. Comparing row counts in INFORMATION_SCHEMA.TABLES is not a reliable or supported CDC mechanism. Row count changes would miss update patterns, could be affected by deletes, and would not provide transactional change tracking. It also adds operational complexity compared to the native stream metadata check provided by SYSTEM$STREAM_HAS_DATA.