DEA-C02 Performance Optimization Practice Question
A query is filtering a table based on a 'TRANSACTION_DATE' column using a range (e.g., BETWEEN '2023-01-01' AND '2023-01-31'). The table is 500GB and not explicitly clustered. Why might this query still perform well and show good partition pruning?
⚠ Common exam trap
Candidates often assume a table must have an explicit clustering key to be fast. They fail to understand that natural data insertion order can provide the same benefits as explicit clustering.
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 data was inserted in chronological order, creating natural clustering.
Snowflake micro-partitions are immutable and created in the order data is inserted. If the data is naturally loaded in chronological order, the 'TRANSACTION_DATE' values will be naturally clustered within the micro-partitions. This 'natural clustering' allows the metadata-driven pruning to skip partitions that fall outside the date range, even without an explicit clustering key.
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 Result Cache is automatically storing the range for all users.
Why it's wrong here
The Result Cache only helps if the exact same query with the exact same range was recently run. It does not explain why a new query on a specific date range would perform well initially. Pruning happens at the metadata level during the query execution phase, independent of the Result Cache.
- ✗
Snowflake automatically clusters all Date columns by default.
Why it's wrong here
Snowflake does not automatically apply clustering to Date columns. While Date columns are common candidates for clustering, the system only performs automatic clustering if a user explicitly defines a clustering key for the table. Otherwise, it relies on the natural order of the data as it was loaded.
- ✓
The data was inserted in chronological order, creating natural clustering.
Why this is correct
Most time-series data is loaded as it is generated, meaning rows with similar dates are grouped into the same micro-partitions. Snowflake’s metadata tracks the min/max values of every column in each partition, so a range filter on a naturally ordered column can effectively prune most of the table.
- ✗
The query is small enough to fit entirely in the warehouse's metadata cache.
Why it's wrong here
The metadata cache stores information about the table, not the table data itself. While the cache speeds up the pruning decision, the query performance depends on actually being able to skip the 500GB of data. If there were no pruning, the metadata cache would simply confirm that a full scan is needed.
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.