DEA-C02 Performance Optimization Practice Question
A data engineer is optimizing a query that aggregates a large sales table by product category and region. The query currently uses a GROUP BY on two high-cardinality columns and produces a large intermediate result set. The query profile shows significant network traffic and remote spilling. The engineer wants to reduce remote spilling without changing the aggregation logic. Which approach is most likely to achieve this?
⚠ Common exam trap
The trap here is assuming that clustering or query acceleration will fix spilling, when the core issue is memory capacity for the aggregation operation.
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.
Remote spilling occurs when an operation's intermediate results exceed available memory and are written to remote storage. Increasing the warehouse size provides more memory per node, which can accommodate larger intermediate results and reduce or eliminate remote spilling. While clustering and query acceleration can improve performance in other ways, they do not directly address the memory pressure of the aggregation. Rewriting the query might help but is not as direct or reliable as scaling up.
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 product category column.
Why it's wrong here
Clustering on product category can improve pruning for queries that filter on that column, but this query aggregates all data without a filter. Clustering does not reduce the size of the intermediate aggregation result or the memory required for the GROUP BY operation. It may help with data retrieval, but remote spilling is a processing memory issue, not a scan issue. Therefore, clustering is unlikely to reduce remote spilling in this aggregation scenario.
- ✗
Rewrite the query to use a two-step aggregation with a temporary table.
Why it's wrong here
A two-step aggregation might reduce the size of intermediate results by pre-aggregating, but it introduces additional I/O and complexity. Remote spilling occurs when intermediate results are spilled to remote storage due to memory pressure; pre-aggregating could reduce data volume, but it may not eliminate spilling if the final aggregation still processes large partitions. This approach is not guaranteed to reduce remote spilling and could even increase overall execution time due to extra steps.
- ✗
Enable the query acceleration service on the warehouse.
Why it's wrong here
The query acceleration service is designed to offload portions of eligible queries to shared compute resources, but it primarily targets scans and filters, not aggregation operations with high-cardinality GROUP BY. It may not directly reduce remote spilling caused by the aggregation itself. While it can improve performance for certain workloads, it is not the most targeted solution for reducing remote spilling in this scenario, which is better addressed by optimizing the aggregation's data distribution.
- ✓
Increase the warehouse size to provide more memory per node.
Why this is correct
Remote spilling happens when intermediate results exceed local memory and are written to remote storage. Increasing the warehouse size allocates more memory per node, allowing the aggregation to process larger partitions in memory and reducing the need to spill remotely. This directly addresses the memory constraint without altering the query. While other options might have indirect benefits, scaling up the warehouse is the most straightforward way to mitigate remote spilling caused by memory-intensive aggregations.
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.