ARA-C01 Performance Optimization Practice Question
A SnowPro Advanced Architect is analyzing a query that performs a large aggregation over a fact table. The query profile shows that the Aggregation operator is spilling to local disk. The architect wants to reduce spilling and improve performance. Which action is most likely to help?
⚠ Common exam trap
The trap here is assuming that clustering or query acceleration will fix aggregation spilling, when the issue is memory capacity for the aggregation state, not data pruning or offloading.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Increase the warehouse size to provide more memory per node.
Spilling to local disk during aggregation indicates that the aggregation state exceeds available memory. The most direct remedy is to increase memory per node by scaling up the warehouse. This allows the aggregation to be processed in memory, reducing or eliminating spilling. Other options either do not target memory usage or may worsen it. While scaling up increases cost, it is often the simplest and most effective fix for memory-bound aggregations.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Add a clustering key on the group-by columns to reduce the number of groups.
Why it's wrong here
Clustering can improve pruning and reduce the amount of data scanned, but it does not reduce the number of distinct groups in an aggregation. The spilling is caused by the size of the aggregation state, not by scanning too many micro-partitions. If the group-by columns have high cardinality, clustering may not help at all. Clustering is not a memory optimization for aggregation.
- ✗
Enable the Query Acceleration Service to offload the aggregation to shared compute.
Why it's wrong here
Query Acceleration Service is designed to offload portions of scans and filters for eligible queries, not to handle memory-intensive aggregation spilling. It does not provide additional memory for the aggregation operator. While it can help with I/O-bound queries, it is not a solution for local disk spilling in aggregations. The service has specific eligibility criteria and would not address the root cause.
- ✓
Increase the warehouse size to provide more memory per node.
Why this is correct
Aggregation spilling to local disk occurs when the aggregation state exceeds available memory. Scaling up the warehouse increases the memory available per node, which can allow the aggregation to complete in memory. This directly addresses the spilling symptom. While it may increase cost, it is a targeted fix for memory-intensive aggregations, especially when the query cannot be rewritten to reduce cardinality.
- ✗
Rewrite the query to use a window function instead of a GROUP BY.
Why it's wrong here
Replacing GROUP BY with a window function often increases memory usage because window functions may need to sort and retain more data. It does not inherently reduce aggregation state size. In many cases, window functions are more memory-intensive than simple aggregations. This rewrite would likely worsen spilling rather than alleviate it, and it may change the result set if not carefully constructed.
About these practice questions
This ARA-C01 question is part of Courseiva's 209-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This ARA-C01 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the ARA-C01 exam.