ARA-C01 Question 354
Single answerData processingA retail company loads clickstream files into a landing table every few minutes. A downstream fact table must be updated continuously with new events, but the architecture team also wants to minimize compute usage when no new files arrive. During testing, an architect notices that a task scheduled every minute spends most runs doing no work because the stream often has no new rows. Which design is the most appropriate to reduce unnecessary compute while keeping data latency low?
- A
Create a triggered task that runs when SYSTEM$STREAM_HAS_DATA returns TRUE for the stream on the landing table, and have the task merge changes into the fact table
- B
Keep the one-minute scheduled task, but increase the warehouse size so idle checks complete faster and reduce total cost
- C
Replace the stream with a materialized view on the landing table and schedule a task every minute to read from the materialized view
- D
Use a serverless alert to execute the merge statement directly whenever new rows are inserted into the landing table
Show answer and explanation
Correct answer: A
Explanation
The best design is to use a stream on the landing table together with a triggered task that runs only when the stream has data. In Snowflake, streams are the native mechanism for capturing change data on tables, and tasks orchestrate SQL execution. Rather than scheduling a task every minute and paying for many no-op runs, architects can use a WHEN condition with SYSTEM$STREAM_HAS_DATA to avoid unnecessary compute while preserving near-real-time processing. This approach aligns with Snowflake best practices for event-driven or micro-batch data pipelines. Materialized views are for query acceleration, not CDC consumption, and simply resizing a warehouse does not address wasted task executions. Snowflake documentation on streams and tasks specifically covers using SYSTEM$STREAM_HAS_DATA in task conditions to build efficient incremental processing pipelines.
- A. Correct.
Correct. A triggered task can evaluate a condition such as SYSTEM$STREAM_HAS_DATA on a stream and run only when the stream contains change data. This is a common pattern for low-latency ELT in Snowflake because it avoids spinning up compute for task runs that have nothing to process, while still processing changes soon after they arrive. Using a stream also preserves CDC semantics for inserts, updates, and deletes as needed.
- B. Incorrect.
Incorrect. Increasing warehouse size does not solve the fundamental inefficiency of running a task on a fixed schedule when there is no new data. In fact, a larger warehouse can increase cost. The issue is orchestration design, not warehouse performance.
- C. Incorrect.
Incorrect. Materialized views are designed to optimize query performance for repeated query patterns, not to replace streams for change data capture processing. A materialized view would not provide the same offset-based consumption behavior as a stream, and the task would still run every minute whether or not useful new changes exist.
- D. Incorrect.
Incorrect. Alerts in Snowflake are intended to periodically evaluate a condition and send notifications or invoke actions based on query results, but they are not the preferred mechanism for CDC-driven table processing in this scenario. The requirement is to process table changes efficiently with low latency, which is what streams plus triggered tasks are designed for.