ARA-C01 Question 355
Single answerData processingA retail company loads clickstream files into Snowflake every 5 minutes and maintains a large FACT_CLICKS table with several billion rows. Downstream dashboards require session-level aggregates to be refreshed within 10 minutes of file arrival. The current process runs a scheduled task every 5 minutes that executes a full aggregation over the entire FACT_CLICKS table and MERGEs the results into a SESSION_SUMMARY table. This approach is missing SLAs and consuming excessive warehouse credits. As the architect, which solution would BEST reduce compute cost while meeting the near-real-time requirement?
- A
Create a stream on FACT_CLICKS and use a triggered task to process only newly arrived rows into SESSION_SUMMARY
- B
Increase the warehouse size for the scheduled task from Medium to 2XL so the full aggregation completes faster
- C
Cluster FACT_CLICKS by SESSION_ID and continue running the full-table aggregation every 5 minutes
- D
Replace the scheduled task with a materialized view on the full session aggregation and query the materialized view directly
Show answer and explanation
Correct answer: A
Explanation
The best answer is to redesign the pipeline for incremental processing. In Snowflake, streams provide change data capture semantics by tracking row-level changes between transactional offsets, and tasks can orchestrate SQL execution. A triggered task can run when new data is available rather than on a fixed schedule, making it appropriate for near-real-time ingestion patterns. This approach aligns with Snowflake best practices for reducing repeated scans of large tables and processing only deltas.
The current design is inefficient because it recomputes aggregates across the entire FACT_CLICKS table every 5 minutes, which is unnecessary when only a small subset of rows has changed. Simply increasing warehouse size addresses symptoms, not root cause, and usually increases spend. Clustering can help selective query pruning but does not materially solve repeated full-table aggregation. Materialized views can be useful for some precomputed query acceleration scenarios, but for high-frequency incremental ETL/ELT into a maintained summary table, streams plus tasks is generally the more appropriate and controllable architecture.
Relevant Snowflake documentation and best practices include using Streams for CDC-style incremental processing, Tasks for orchestration, and designing ELT pipelines to process changed data rather than reprocessing entire large datasets whenever possible.
- A. Correct.
Correct. A stream records change data capture information for the source table, allowing downstream SQL to consume only inserted or changed rows since the last offset. Pairing the stream with a triggered task enables event-driven processing rather than fixed-interval polling. This is well suited to frequent micro-batch ingestion where only recent rows need to be aggregated and merged, significantly reducing the amount of data scanned and helping meet the 10-minute SLA with lower compute usage.
- B. Incorrect.
Incorrect. Scaling up the warehouse may reduce runtime for the existing full-table aggregation, but it does not address the core inefficiency: repeatedly scanning and aggregating billions of historical rows every 5 minutes. This can improve performance at the expense of much higher cost and is usually not the best architectural fix for incremental processing requirements.
- C. Incorrect.
Incorrect. Clustering by SESSION_ID may improve pruning for queries that filter on SESSION_ID, but it does not make a full aggregation over the entire FACT_CLICKS table efficient enough for this use case. The job would still read and recompute aggregates for all historical data every run. Clustering helps specific access patterns; it is not a substitute for incremental processing design.
- D. Incorrect.
Incorrect. Materialized views can accelerate certain query patterns, but they are not a universal replacement for incremental sessionization pipelines. Large-scale aggregations over rapidly changing fact data can make materialized view maintenance expensive, and not all aggregation logic or downstream merge requirements are a good fit. In this scenario, the requirement is to incrementally refresh a target summary table with new data, which is better handled with streams and tasks.