DEA-C02 Performance Optimization Practice Question
Exhibit
SELECT * FROM TABLE(INFORMATION_SCHEMA.AUTOMATIC_CLUSTERING_HISTORY( TABLE_NAME => 'SALES_DATA', START_TIME => DATEADD(H, -12, CURRENT_TIMESTAMP())));
Refer to the exhibit. A data engineer runs this query to investigate clustering costs. The output shows high credit consumption but the 'Clustering Depth' of the table remains high. What is the most likely cause of this behavior?
⚠ Common exam trap
Candidates often blame the Automatic Clustering service for being broken. They fail to realize that constant, out-of-order DML updates effectively 'undo' the clustering, causing a loop of expensive, ineffective maintenance.
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 being continuously updated with data that overlaps existing ranges.
High credit consumption combined with high clustering depth usually indicates that the table is being frequently updated or appended with data that is significantly 'out of order' relative to the clustering key. This causes the Automatic Clustering service to continuously work to re-sort the data, but the constant influx of unsorted data prevents the depth from improving.
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 table is being continuously updated with data that overlaps existing ranges.
Why this is correct
When new data is inserted or existing data is updated in a way that creates overlapping ranges in the clustering key, the clustering service must work harder. If the rate of these changes is high, the service consumes many credits while struggling to keep the table well-organized.
- ✗
The clustering key is defined on a column with a 'Date' data type.
Why it's wrong here
Date columns are actually excellent clustering keys because they naturally provide a logical order for time-series data. Using a Date type would not inherently cause high costs or poor depth unless the data was being loaded in a completely random order across a very wide range of dates.
- ✗
The warehouse used for clustering is too small for the table size.
Why it's wrong here
Automatic Clustering does not use a user-defined virtual warehouse; it uses Snowflake-managed background resources. Therefore, the size of the user's warehouse has no impact on the clustering performance or credit consumption shown in the AUTOMATIC_CLUSTERING_HISTORY view, as these costs are billed separately as background services.
- ✗
The Search Optimization Service is conflicting with the clustering service.
Why it's wrong here
Search Optimization and Automatic Clustering are independent features that operate on different data structures. Search Optimization builds a separate persistent index-like structure, while clustering reorders the micro-partitions themselves. They do not conflict in a way that would cause high clustering costs or prevent the depth from decreasing.
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.