DEA-C02 Performance Optimization Practice Question
A data engineer runs a query on a Snowflake virtual warehouse that scans a 5 TB table but returns only 100 rows. The Query Profile shows that the TableScan operator processed 5 TB of data even though a highly selective filter on a non-clustered column was applied. The warehouse is sized Medium. Which action will most effectively reduce the amount of data scanned by this query?
⚠ Common exam trap
The trap here is assuming that a larger warehouse reduces the volume of data scanned, when it only increases the speed at which the same bytes are processed.
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 filtered column and enable automatic clustering.
The TableScan reads 5 TB because the filter column is not clustered, so micro-partition pruning cannot eliminate irrelevant data. Adding a clustering key on the filtered column, with automatic clustering enabled, reorganizes micro-partitions so the optimizer can skip most of them. This reduces bytes scanned and directly targets the bottleneck shown in the Query Profile, rather than adding compute or duplicating data.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create a materialized view that selects all columns from the table.
Why it's wrong here
A materialized view that selects all columns essentially duplicates the base table and does not improve pruning on the filtered column. The optimizer may still scan the same volume of data unless the materialized view is clustered or narrowly projected. This approach adds storage cost and maintenance overhead without addressing the selective filter's inability to prune micro-partitions.
- ✓
Add a clustering key on the filtered column and enable automatic clustering.
Why this is correct
Clustering the table on the filtered column co-locates similar values in the same micro-partitions, so the optimizer can prune micro-partitions that cannot contain matching rows. This directly reduces bytes scanned by the TableScan, which is the reported bottleneck. Because the table is large and the filter is highly selective, clustering yields the greatest reduction in I/O and improves performance without resizing the warehouse.
- ✗
Increase the warehouse size from Medium to 2X-Large.
Why it's wrong here
Scaling up the warehouse adds compute resources and speeds up processing of the bytes that are read, but it does not reduce the 5 TB scanned. The TableScan still reads every micro-partition containing the filtered column because the data is not clustered. Larger warehouses cost more credits per second and will not address the root cause of excessive I/O from poor pruning.
- ✗
Add a search optimization service to the table.
Why it's wrong here
Search optimization service accelerates point lookups and selective equality predicates on columns, but it is designed for highly selective lookups, not for reducing a full 5 TB scan from a range or non-equality filter. It maintains a search access path but does not co-locate data by the filtered column, so the TableScan may still read a large volume of micro-partitions.
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.