COF-C03 Question 235
Single answerTasksA data engineering team loads raw order files into a Snowflake table throughout the day using Snowpipe. They want a downstream transformation to run only when new rows have arrived, and they want to minimize compute cost when no data is available. The team is considering using a Snowflake task to execute a MERGE into a curated table. Which approach best meets these requirements?
- A
Create a scheduled task that uses a WHEN SYSTEM$STREAM_HAS_DATA('orders_stream') condition and runs the MERGE using a user-managed warehouse.
- B
Create a task that directly monitors the raw table for INSERT activity and automatically runs the MERGE without using a stream.
- C
Create a scheduled task that runs every minute and executes the MERGE unconditionally on a dedicated warehouse, because tasks automatically skip compute charges if the target table has no changes.
- D
Create a serverless task with no schedule so that Snowflake automatically detects DML changes in the source table and starts the task when rows arrive.
Show answer and explanation
Correct answer: A
Explanation
The best practice for running downstream processing only when new data is available is to combine a stream with a task condition. A stream records change data capture (CDC) information for a table, and a task can use WHEN SYSTEM$STREAM_HAS_DATA('<stream_name>') to determine whether work should run. This pattern is widely used for incremental ELT pipelines because it reduces unnecessary task executions and helps control compute usage. In Snowflake documentation, streams are the supported mechanism for tracking row-level changes, and tasks are used to schedule or orchestrate SQL work. Tasks do not natively monitor raw tables for DML without streams, and an unconditional frequent schedule can waste compute. For SnowPro Core, candidates should recognize the practical pattern: Snowpipe loads data, a stream tracks changes, and a task conditionally executes a MERGE only when the stream contains data.
- A. Correct.
Correct. A common and cost-conscious pattern is to create a stream on the source table and then define a task with a WHEN SYSTEM$STREAM_HAS_DATA(...) condition. The task can be scheduled to evaluate periodically, but the SQL body runs only when the stream reports unconsumed change data. This avoids unnecessary MERGE execution when no new rows have arrived. Using a user-managed warehouse is valid for tasks, although serverless tasks could also be used; the key requirement here is the stream-triggered conditional logic.
- B. Incorrect.
Incorrect. Snowflake tasks do not directly watch a table for DML activity in the way this option suggests. To process changes incrementally, Snowflake uses streams to track table change data, and tasks can use stream-aware logic such as SYSTEM$STREAM_HAS_DATA. Skipping the stream would miss the supported CDC pattern that Snowflake provides.
- C. Incorrect.
Incorrect. While a task can be scheduled every minute, running the MERGE unconditionally does not minimize compute cost. If the task executes SQL on a warehouse each minute regardless of whether data changed, warehouse resources can be consumed unnecessarily. Tasks do not automatically eliminate compute charges just because the target table has no new data; the task definition must avoid unnecessary execution.
- D. Incorrect.
Incorrect. A task cannot be created with no schedule and simply auto-detect table changes in the source table. Triggered task patterns in Snowflake rely on streams and task conditions rather than native direct table-event monitoring. Also, a task still needs a valid triggering mechanism such as a schedule or task graph relationship.