DEA-C02 Performance Optimization Practice Question
A table experiences performance degradation over time due to frequent DML operations (INSERT/UPDATE/DELETE). What is the most likely cause?
⚠ Common exam trap
Candidates often assume the issue is related to warehouse size or query complexity, failing to recognize that DML operations naturally degrade partition organization over time, necessitating maintenance or 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
✓
Micro-partition fragmentation.
Frequent DML operations lead to 'micro-partition fragmentation'. When rows are updated or deleted, the micro-partitions become sub-optimally filled or 'dirty'. This forces the query engine to scan more partitions than necessary because the data is no longer organized linearly. This fragmentation is a common performance bottleneck in write-heavy environments, and it requires periodic table maintenance or clustering to restore the efficiency of the partition pruning process for subsequent reads.
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 metadata cache is exceeding its capacity.
Why it's wrong here
The metadata cache is managed by Snowflake and does not have a capacity limit that affects user-level query performance. It is designed to handle massive amounts of table information efficiently, ensuring that query planning remains fast even as the number of tables or partitions grows over time.
- ✓
Micro-partition fragmentation.
Why this is correct
Frequent DML operations break the physical ordering within micro-partitions. As data is changed, the original clustering becomes scattered across many partitions, reducing the effectiveness of partition pruning. This forces the engine to scan more data than necessary to satisfy the same query, leading to significant performance degradation.
- ✗
The warehouse cache is becoming corrupted.
Why it's wrong here
Warehouse caches are not susceptible to corruption in the way that traditional database indexes might be. Snowflake's architecture ensures data integrity at all times. If performance degrades, it is almost always due to inefficient data organization or resource constraints rather than any form of cache corruption.
- ✗
The result cache is being constantly invalidated.
Why it's wrong here
The result cache is invalidated by any DML on the table, but this is expected behavior. Frequent invalidation is not the cause of slow queries; it just means the results must be recomputed. The actual slowdown is caused by the physical degradation of the data storage, not the invalidation itself.
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.