COF-C03 Practice Question: Performance Optimization, Querying, and Transformation
A data engineer runs a query that performs a large aggregation over a table with billions of rows. The Query Profile shows that the Aggregate operator is spilling to local disk. The engineer wants to eliminate the spilling and improve performance. Which action is most likely to achieve this?
⚠ Common exam trap
The trap here is thinking that clustering or query rewriting can reduce memory usage for an aggregation, when only additional memory or reduced data volume can prevent spilling.
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.
Spilling to local disk indicates that the aggregation operator's memory footprint exceeds the available memory on the warehouse. Increasing the warehouse size provides more memory per node, allowing the aggregation to complete in memory. This is the most direct solution because it addresses the resource constraint. Clustering, query rewriting, and result caching do not increase memory and therefore do not resolve the spilling condition.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Enable the USE_CACHED_RESULT parameter to reuse previous aggregation results.
Why it's wrong here
USE_CACHED_RESULT controls whether Snowflake can reuse results from the result cache. It does not affect the memory available for the current aggregation or prevent spilling. If a previous identical query exists, it might return cached results, but the scenario describes a new aggregation. This parameter does not address the spilling issue.
- ✗
Add a cluster key on the group-by columns to reduce the number of groups.
Why it's wrong here
Clustering on group-by columns can improve pruning for filtered queries, but it does not reduce the number of distinct groups or the memory required for the aggregation. The aggregation still needs to process all rows and maintain state for each group. Clustering may help with I/O but will not eliminate spilling caused by a large number of groups.
- ✗
Rewrite the query to use a window function instead of a GROUP BY aggregation.
Why it's wrong here
Window functions also require memory to maintain partitions and can spill similarly to aggregations. Rewriting may change the execution plan but does not guarantee reduced memory usage. In many cases, window functions are more memory-intensive because they retain detailed rows. This action is unlikely to eliminate spilling and may worsen performance.
- ✓
Increase the warehouse size to provide more memory for the aggregation.
Why this is correct
Spilling to local disk occurs when the aggregation's working set exceeds the memory available on the warehouse nodes. Increasing the warehouse size adds more memory per node, which can allow the aggregation to be performed entirely in memory. This directly addresses the root cause of spilling and can eliminate the performance penalty associated with disk I/O.
Visual reference
About these practice questions
This COF-C03 question is part of Courseiva's 280-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 COF-C03 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 COF-C03 exam.