COF-C03 Question 234
Single answerTasksA data engineering team uses a Snowflake task to run an hourly MERGE statement that loads new records from a staging table into a reporting table. The task was created with a user-managed warehouse. During a cost review, the team notices that the task is starting every hour even when no new data has arrived, causing unnecessary warehouse usage. The team wants the task to run only when new data is present in the staging table, while keeping the existing SQL transformation logic in the task body. Which approach should the engineer take?
- A
Modify the task to include a WHEN clause that checks SYSTEM$STREAM_HAS_DATA on a stream created for the staging table
- B
Replace the task with a materialized view on the staging table so the MERGE runs only when data changes
- C
Change the task to use a larger warehouse so it finishes faster and reduces total hourly cost
- D
Create a second task that truncates the staging table before the hourly task starts
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use conditional task execution with a WHEN clause and a stream on the staging table. In Snowflake, tasks can be scheduled and configured to run only if a Boolean expression evaluates to TRUE. For incremental pipelines, Snowflake best practice is often to create a stream on the source or staging table and use SYSTEM$STREAM_HAS_DATA('<stream_name>') in the task definition. This allows the task to skip execution when no change data is available, avoiding unnecessary warehouse consumption. This pattern is commonly used with MERGE-based ELT pipelines. Materialized views are not a substitute for task orchestration, and warehouse resizing does not address idle scheduled executions. See Snowflake documentation on Tasks, Streams, and SYSTEM$STREAM_HAS_DATA for this design pattern.
- A. Correct.
Correct. Snowflake tasks support a WHEN condition, and a common pattern is to evaluate SYSTEM$STREAM_HAS_DATA against a stream on the source table. The task is triggered on schedule, but it only executes when the WHEN condition evaluates to TRUE. This is the recommended approach for reducing unnecessary runs while preserving the existing task SQL logic, such as a MERGE statement.
- B. Incorrect.
Incorrect. Materialized views do not replace task-driven MERGE processing. A materialized view maintains query results for eligible SELECT logic, but it does not execute DML operations like MERGE into a target reporting table. This option confuses automatic maintenance of a view with orchestration of ETL logic.
- C. Incorrect.
Incorrect. A larger warehouse might reduce runtime for executions that do occur, but it does not solve the core problem: the task is still starting every hour even when there is no new data. In many cases, a larger warehouse could increase cost rather than reduce it.
- D. Incorrect.
Incorrect. Truncating the staging table before the load task runs would risk deleting unprocessed data and does not provide a reliable mechanism for determining whether new data exists. It also changes the data flow semantics instead of using Snowflake's intended conditional task execution features.