COF-C03 Practice Question: Performance Optimization, Querying, and Transformation
A data engineer is optimizing a complex query that joins multiple large tables and includes several aggregations. The Query Profile shows a high number of rows spilled to local disk and remote disk. Which TWO actions are most likely to reduce spilling and improve performance? (Choose two.)
⚠ Common exam trap
The trap here is assuming that clustering or caching will fix spilling, when spilling is a memory issue best solved by more memory or less data.
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
✓
Rewrite the query to filter and aggregate data earlier in the pipeline.
Spilling indicates that intermediate results exceed available memory. Increasing warehouse size provides more memory, and rewriting the query to filter and aggregate earlier reduces the volume of intermediate data. Clustering, result caching, and temporary tables do not directly address the memory pressure causing the spilling.
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 join columns of the largest table.
Why it's wrong here
Clustering can improve pruning for filter predicates, but it does not directly reduce spilling during joins and aggregations. Spilling is caused by insufficient memory for intermediate results, not by poor pruning. While clustering might reduce the amount of data read, it does not address the memory-intensive operations that cause spilling. Therefore it is not a direct solution.
- ✗
Convert the query to use a temporary table to materialize intermediate results.
Why it's wrong here
Materializing intermediate results in a temporary table can sometimes help by breaking a complex query into steps, but it does not inherently reduce spilling. It may even increase I/O and storage usage. The spilling occurs during the execution of operations like joins and aggregations; a temporary table would just store the output, not reduce the memory required for those operations. Thus it is not a direct fix.
- ✓
Rewrite the query to filter and aggregate data earlier in the pipeline.
Why this is correct
By applying filters and aggregations as early as possible, the volume of data that needs to be joined and processed is reduced. This lowers the memory footprint of intermediate results, decreasing the likelihood of spilling. Early aggregation can also reduce the size of hash tables used in joins, further alleviating memory pressure.
- ✗
Enable the USE_CACHED_RESULT parameter to reuse previous results.
Why it's wrong here
Result caching only helps when the exact same query is rerun and the underlying data has not changed. It does not affect the execution of a new or modified query that is spilling. Since the engineer is optimizing a complex query, caching would not reduce spilling during its execution. It is irrelevant to the memory pressure issue.
- ✓
Increase the size of the virtual warehouse to provide more memory.
Why this is correct
Spilling occurs when the operations in a query require more memory than available on the virtual warehouse. Increasing the warehouse size provides more memory per node, which can allow the query to process larger intermediate results in memory, reducing both local and remote spilling. This directly addresses the resource constraint indicated by the Query Profile.
About these practice questions
One of 280 original COF-C03 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 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.