DEA-C02 Performance Optimization Practice Question
A data engineer notices that a daily aggregation query scans 4 TB of a 5 TB table even though it only needs the last seven days of data. The table has a DATE column but no clustering key, and the query filters with DATE >= CURRENT_DATE - 7. Query Profile shows almost no partition pruning. What is the most effective change to reduce bytes scanned?
⚠ Common exam trap
The trap here is believing that rewriting the date predicate or adding search optimization will improve pruning, when pruning is governed by the physical clustering of the filtered column.
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 DATE column and let Automatic Clustering maintain it.
Partition pruning depends on how well the filter column correlates with micro-partition boundaries. A DATE clustering key groups similar dates together, allowing the optimizer to skip most micro-partitions for a seven-day filter. Automatic Clustering preserves that layout over time. Predicate rewrites, materialized views, and search optimization do not change the physical clustering that governs pruning in this scenario.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Enable search optimization service on the DATE column to improve range filter performance.
Why it's wrong here
Search optimization is designed for highly selective point lookups and equality or IN predicates, not broad range scans over a large fraction of a table. Applying it to a seven-day range on a 5 TB table would not deliver the partition-level skipping that clustering provides and would add maintenance overhead without the expected scan reduction.
- ✗
Create a materialized view that selects only the last seven days and refresh it on a schedule.
Why it's wrong here
A materialized view with a moving date predicate is not a supported pattern for Snowflake materialized views, which require deterministic, non-time-relative definitions. Even if approximated with a table, maintaining it adds cost and staleness risk. Clustering the base table addresses the pruning problem at its source rather than duplicating data.
- ✗
Replace the filter with a BETWEEN clause using explicit literal dates instead of CURRENT_DATE arithmetic.
Why it's wrong here
The predicate form does not determine pruning; what matters is whether the DATE values in each micro-partition fall inside the filter range. Rewriting CURRENT_DATE arithmetic as literals produces the same scan behavior because the underlying data layout is unchanged. This is a cosmetic change that does not reduce the 4 TB scan.
- ✓
Add a clustering key on the DATE column and let Automatic Clustering maintain it.
Why this is correct
Without clustering, micro-partitions can contain rows spanning wide date ranges, so a seven-day filter cannot prune much. Clustering on DATE co-locates similar dates into the same micro-partitions, enabling the optimizer to skip partitions outside the range. Automatic Clustering keeps this ordering as new data arrives, directly reducing bytes scanned for the daily query.
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.