DEA-C02 Performance Optimization Practice Question
A data engineer is analyzing a Query Profile for a query that joins three large tables. The profile shows that the optimizer chose a broadcast join for one of the joins, but the broadcasted table is 50 GB. The query is spilling to remote disk. Which action is most likely to improve performance by changing the join strategy?
⚠ Common exam trap
The trap here is assuming that a larger warehouse fixes a poor join strategy, when the real issue is likely inaccurate statistics driving the optimizer's choice.
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
✓
Collect statistics on the join columns and ensure the tables are not stale.
The optimizer chose a broadcast join for a 50 GB table, which is inefficient and causes spilling. This often happens when statistics are stale or missing, leading to underestimation of the table size. Collecting statistics on the join columns provides accurate cardinality estimates, enabling the optimizer to choose a better join strategy, such as a hash join with a smaller build side. This directly addresses the root cause.
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 all three tables.
Why it's wrong here
Clustering on join columns can improve pruning and reduce data scanned, but it does not directly change the join strategy chosen by the optimizer. If the optimizer still chooses a broadcast join for a 50 GB table, spilling may persist. Clustering is beneficial for pruning, but it is not the most direct way to influence the join strategy and eliminate remote spilling caused by broadcasting a large table.
- ✗
Increase the warehouse size to 4X-Large.
Why it's wrong here
A larger warehouse adds memory and compute, which may reduce spilling temporarily, but it does not correct the optimizer's choice of a broadcast join for a 50 GB table. The root cause is likely inaccurate statistics leading to a poor join strategy. Scaling up increases credit consumption and may not resolve the issue if the broadcast join remains inefficient.
- ✗
Rewrite the query to use a CROSS JOIN instead of an INNER JOIN.
Why it's wrong here
A CROSS JOIN produces a Cartesian product, which dramatically increases the number of rows and would worsen spilling. It does not change the join strategy to a more efficient one and is almost never appropriate for large tables. This would be a severe performance regression, not an improvement, and would not address the broadcast join issue.
- ✓
Collect statistics on the join columns and ensure the tables are not stale.
Why this is correct
The optimizer relies on statistics to estimate cardinality and choose join strategies. If statistics are stale or missing, it may incorrectly estimate the broadcasted table as small and choose a broadcast join. Refreshing statistics on the join columns gives the optimizer accurate row counts, allowing it to select a more appropriate join strategy, such as a hash join with a smaller build side, reducing spilling.
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.