DEA-C02 Performance Optimization Practice Question
A data engineer notices that a query performing a large GROUP BY on a high-cardinality column is slow. The Query Profile shows that the aggregation step is spilling to local disk. The engineer wants to reduce local spilling without changing the query logic. Which action is most appropriate?
⚠ Common exam trap
The trap here is thinking that sorting or reducing warehouse size can fix spilling, when the real issue is insufficient memory per node for a high-cardinality aggregation.
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 in a high-cardinality GROUP BY means the aggregation's hash table exceeds available memory on the nodes. Increasing the warehouse size adds memory and compute nodes, allowing the hash table to remain in memory and eliminating the spill. This is the most direct fix when the query logic cannot be changed and the profile clearly points to the aggregation step as the spill location.
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 an ORDER BY clause on the grouping column to sort data before aggregation.
Why it's wrong here
Adding ORDER BY does not reduce the memory required for the aggregation; it adds a sort step that can itself spill. Sorting before aggregation may sometimes help in other databases, but in Snowflake it introduces extra work and does not address the high-cardinality hash table that is causing the spill. The query logic would also change, which the engineer wants to avoid.
- ✗
Reduce the warehouse size to decrease contention among nodes.
Why it's wrong here
Reducing warehouse size decreases available memory per node, which would increase spilling rather than reduce it. The aggregation already spills to local disk because the working set exceeds memory. A smaller warehouse would make the problem worse and likely increase query time, so this action is counterproductive for the stated goal.
- ✓
Increase the warehouse size to provide more memory for the aggregation.
Why this is correct
A larger warehouse provides more memory per node and more nodes, allowing the aggregation to process higher-cardinality groups without exceeding memory limits. This directly reduces local spilling because the hash table can fit in memory. Scaling up is a standard response when the Query Profile shows local disk spilling in an aggregation step and the query logic cannot be changed.
- ✗
Set the MAX_CONCURRENCY_LEVEL parameter to 1 to give the query more resources.
Why it's wrong here
Snowflake does not expose a MAX_CONCURRENCY_LEVEL parameter for individual queries. Concurrency is managed by the warehouse configuration, such as multi-cluster settings. Setting a non-existent parameter would fail, and it would not allocate more memory to the aggregation operator. The correct lever for memory in a single query is warehouse size.
About these practice questions
One of 229 original DEA-C02 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.