DEA-C02 Performance Optimization Practice Question
A data engineer is investigating a slow query that scans a large table. The Query Profile shows that the table scan is reading a very high number of micro-partitions compared to the total number of partitions in the table. The query filters on a column that is not the clustering key. What is the most likely explanation for the high number of partitions read?
⚠ Common exam trap
The trap here is assuming that a small warehouse or lack of indexes causes more partitions to be read, when pruning is purely a metadata and clustering concern.
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
✓
The table is not clustered on the filter column, so pruning is ineffective.
Pruning efficiency depends on how well data is clustered on the filter column. Without clustering, micro-partitions contain overlapping values, so the optimizer cannot skip many partitions. This results in a high number of partitions read. Warehouse size, indexing, and column count do not affect the number of partitions scanned in Snowflake.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The query is using a full table scan because the filter column is not indexed.
Why it's wrong here
Snowflake does not use traditional indexes; it relies on micro-partition metadata for pruning. There is no index on the filter column to create. The absence of an index is not the cause; the lack of clustering is. This option reflects a misunderstanding of Snowflake's architecture.
- ✗
The table has too many columns, causing the scan to read all partitions.
Why it's wrong here
The number of columns does not determine how many partitions are read. Columnar storage means only the columns referenced in the query are read, regardless of total column count. The high partition count is due to ineffective pruning on the filter column, not the table's width.
- ✓
The table is not clustered on the filter column, so pruning is ineffective.
Why this is correct
When a table is not clustered on the filter column, the micro-partition metadata may show wide ranges for that column, causing the optimizer to scan many partitions to find matching rows. Without clustering, data is organized by insertion order, which often leads to overlapping values and poor pruning. This directly explains the high number of partitions read.
- ✗
The warehouse is too small, causing the scan to read partitions multiple times.
Why it's wrong here
Warehouse size affects compute resources and memory, but it does not cause a scan to read more micro-partitions than necessary. The number of partitions read is determined by pruning, which is based on metadata. A smaller warehouse might run slower, but it will not increase the count of partitions scanned.
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.