DEA-C02 Performance Optimization Practice Question
A query is slow because it is scanning a massive table. The filters are on columns that are not currently clustered. What is the most immediate step to improve performance?
⚠ Common exam trap
Candidates often suggest scaling up the warehouse size immediately to speed up the scan, missing that clustering is the most effective way to avoid reading irrelevant data entirely.
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
✓
Add a clustering key on the filter columns.
When dealing with massive table scans, the most immediate improvement comes from reducing the volume of data read. If the filter columns aren't clustered, adding a clustering key is the most direct way to inform the engine which partitions can be skipped. This is a foundational performance technique in Snowflake, allowing the database to prune partitions based on the filter criteria, thereby reducing I/O and accelerating query execution without needing to change existing query SQL code.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use the SEARCH OPTIMIZATION service.
Why it's wrong here
Search Optimization is for point-lookups on specific values. If the query is scanning a massive table, it is likely doing range-based filtering or aggregation, where SOS would not be effective. Clustering keys are the correct choice for optimizing range filters and reducing the total number of partitions scanned.
- ✓
Add a clustering key on the filter columns.
Why this is correct
Clustering keys align the physical data storage with the query filter patterns. This allows Snowflake to skip micro-partitions that do not contain the data matching the filter criteria. This reduces I/O significantly, which is the primary bottleneck for massive table scans, leading to immediate performance improvements.
- ✗
Rewrite the query to use an INNER JOIN.
Why it's wrong here
Query rewriting does not change the amount of data that must be read from the table. If a full table scan is happening because the data is not partitioned correctly, changing the join type will not solve the underlying issue of excessive I/O during the scan process.
- ✗
Scale down the warehouse to a smaller size.
Why it's wrong here
Scaling down the warehouse reduces available compute power and memory. This would make the scan even slower, especially if the query is compute-intensive or requires memory for sorting and joining. Scaling down is the opposite of what is needed when struggling with slow table scans.
About these practice questions
One of 229 original DEA-C02 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 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.