DEA-C02 Performance Optimization Practice Question
A data engineer runs a nightly transformation that joins a 12 TB fact table to a 300 GB dimension table. The fact table is clustered by DATE_KEY, and the dimension is small enough to fit in memory. Query Profile shows the join operator building a hash table on the 12 TB side and spilling to remote disk. The engineer wants to eliminate the remote spill without increasing warehouse size. Which action should the engineer take?
⚠ Common exam trap
The trap here is assuming that a remote spill is always solved by a larger warehouse or by clustering, when the join's build-side choice is what determines whether the hash table fits in memory.
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 join so the 300 GB dimension table is the build side and the 12 TB fact table is the probe side.
Hash join performance depends on which input is used to build the in-memory hash table. Building on the smaller dimension and probing with the large fact table keeps the hash table resident in memory, eliminating remote disk spilling. Clustering, resizing, or materializing the join do not correct the build-side selection, so they leave the root cause of the spill unaddressed.
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 by one step so the join operator receives more memory per node.
Why it's wrong here
Scaling up adds memory, but the engineer explicitly wants to avoid that and the underlying inefficiency remains: the hash table is still built on the 12 TB input. A larger warehouse may mask the spill temporarily but will not eliminate the structural problem and increases credit consumption on every nightly run.
- ✗
Add a clustering key on the 12 TB fact table using the dimension's primary key column.
Why it's wrong here
Clustering improves micro-partition pruning for filters on the clustering column, but it does not change which side of the join is hashed. The join operator would still build its hash table on the large input, so the remote spill would persist. Clustering cannot substitute for correcting the build/probe orientation in this scenario.
- ✗
Convert the 12 TB fact table to a materialized view joined with the dimension table and refresh it nightly.
Why it's wrong here
A materialized view over a join of this size would require substantial storage and refresh compute, and Snowflake materialized views are not designed to pre-join very large fact tables with dimensions in this manner. This adds cost and complexity without targeting the hash-table build side that causes the spill.
- ✓
Rewrite the join so the 300 GB dimension table is the build side and the 12 TB fact table is the probe side.
Why this is correct
Hash joins build the hash table on the smaller input and stream the larger input as the probe side. With a 300 GB dimension as the build side and the 12 TB fact as the probe side, the hash table is far smaller and can be held in memory, avoiding remote disk spill. This directly addresses the spill without resizing the warehouse.
About these practice questions
This DEA-C02 question is part of Courseiva's 229-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 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.