DEA-C02 Performance Optimization Practice Question
A data engineer runs a query that joins a 5 TB fact table to a 200 GB dimension table. The query profile shows a broadcast operation for the dimension table and a local spilling node on the fact table. The engineer wants to reduce local spilling without changing the query results. The warehouse is a multi-cluster warehouse with sufficient memory. Which action is most likely to reduce local spilling?
⚠ Common exam trap
The trap here is assuming that any data reduction technique will fix spilling, when the root cause is often insufficient memory per node for the 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.
Local spilling indicates that an operation is exceeding the memory available on a warehouse node. Increasing the warehouse size provides more memory per node, which can prevent or reduce spilling. While other actions like filtering or clustering might reduce data volume, they do not directly address the memory constraint of the operation. Disabling broadcast could change the join strategy but may introduce other bottlenecks. Therefore, scaling up the warehouse is the most direct solution.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Increase the warehouse size to provide more memory per node.
Why this is correct
Local spilling happens when an operation exceeds the memory allocated to a warehouse node. Increasing the warehouse size adds more compute resources and memory per node, which can accommodate larger intermediate results and reduce or eliminate local spilling. This directly addresses the memory constraint without altering the query logic. Other options, such as changing join order or disabling broadcast, may not resolve the underlying memory issue if the data volume per node remains high.
- ✗
Add a clustering key on the join column of the fact table.
Why it's wrong here
Clustering the fact table on the join column can improve pruning and reduce the amount of data scanned, but it does not directly reduce local spilling during the join operation. Local spilling is a memory issue during processing, not a data retrieval issue. Clustering might help if it reduces the data volume per node, but it is not the most direct or reliable fix for spilling, especially when the join itself is the memory-intensive step.
- ✗
Reduce the size of the fact table by filtering out rows before the join.
Why it's wrong here
Filtering the fact table before the join reduces the number of rows processed, but it does not directly address the memory pressure that causes local spilling. Local spilling occurs when the join or aggregation operation exceeds the memory available to the warehouse node, often due to data skew or insufficient memory for the operation. Filtering may help if it significantly reduces data volume, but it is not the most direct solution when the dimension table is already being broadcast and the fact table is large.
- ✗
Disable broadcast for the dimension table to force a shuffle join.
Why it's wrong here
Disabling broadcast would force a shuffle join, which redistributes both tables across nodes. This could increase network overhead and may not reduce local spilling because the fact table would still be processed in partitions that might exceed memory. Broadcast is often efficient for small tables; the dimension table at 200 GB might be too large for broadcast, but the spilling is on the fact table side, so changing the join strategy alone is unlikely to solve the memory issue.
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.