DEA-C02 Performance Optimization Practice Question
A data engineer is analyzing a slow-running query that performs a large aggregation over a table with many columns. The query profile shows that most time is spent in the 'Aggregate' operator, and there is significant data spilling to local disk. Which action is most likely to reduce the spilling and improve performance?
⚠ Common exam trap
The trap here is assuming that clustering or query rewriting will fix spilling, when the root cause is insufficient memory and scaling up is the most direct remedy.
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 for the aggregation.
Local disk spilling during aggregation indicates that the aggregation's working set exceeds the available memory on the warehouse nodes. Scaling up the warehouse provides more memory per node, which can allow the aggregation to be performed in-memory, reducing spilling and improving performance. This is a direct and effective solution for memory-bound operations.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Rewrite the query to use a window function instead of a GROUP BY aggregation.
Why it's wrong here
Window functions often require more memory and can also spill. They are not a direct solution to spilling in aggregation; they may even exacerbate the issue because they maintain a window frame. The problem is memory pressure during aggregation, so changing to a window function does not address the root cause and could worsen performance.
- ✓
Increase the warehouse size to provide more memory for the aggregation.
Why this is correct
Increasing the warehouse size provides more memory and compute resources per node, which can reduce or eliminate local disk spilling during aggregation. Spilling occurs when the aggregation's working set exceeds available memory. A larger warehouse has more memory, allowing the aggregation to be processed in-memory. This directly addresses the spilling issue and is a straightforward optimization.
- ✗
Add a clustering key on the column used in the GROUP BY clause.
Why it's wrong here
Clustering can improve pruning for filters, but it does not reduce the memory required for aggregation. The aggregation still needs to process all groups, and clustering may not reduce the cardinality of the grouping. While clustering can sometimes improve aggregation performance by co-locating similar values, it is not the primary fix for spilling caused by insufficient memory.
- ✗
Enable the USE_CACHED_RESULT parameter to reuse previous aggregation results.
Why it's wrong here
USE_CACHED_RESULT can help if the exact same query was run before and the underlying data hasn't changed, but it does not reduce spilling for a new or modified query. It is a caching mechanism, not a memory optimization. In this scenario, the query is slow due to spilling during execution, so caching is not applicable unless the query is repeated identically.
About these practice questions
Courseiva writes every DEA-C02 question from scratch — 229 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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 DEA-C02 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 DEA-C02 exam.