DEA-C02 Performance Optimization Practice Question
A data engineer maintains a table that receives continuous small INSERT statements throughout the day. Query performance on this table has degraded even though total data volume is modest, and the Query Profile shows many very small micro-partitions being scanned. Which action best addresses the underlying cause?
⚠ Common exam trap
The trap here is reaching for reclustering or a larger warehouse when the real issue is that frequent small inserts are generating an excessive number of undersized micro-partitions.
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
✓
Batch the incoming rows and load them less frequently, or use a staging table and periodically merge into the target.
The profile evidence of many tiny micro-partitions points to ingestion pattern rather than query design or compute. Each small insert creates its own micro-partition, so the table becomes a patchwork of fragments that the optimizer must enumerate and scan. Batching or staging-then-merging reduces the number of partitions created, which lowers metadata and scan overhead at the source.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Schedule periodic reclustering of the table using a clustering key on the most selective filter column.
Why it's wrong here
Reclustering reorganizes existing micro-partitions to improve pruning, but it does not merge the many tiny partitions created by frequent small inserts. The table would still contain a large number of undersized micro-partitions, so reclustering spends credits while leaving the fragmentation that causes the overhead largely unaddressed.
- ✗
Increase the warehouse size so the larger compute pool can scan the many small micro-partitions in parallel.
Why it's wrong here
A bigger warehouse adds threads that can read more partitions concurrently, but the fundamental problem is the sheer number of undersized partitions and their metadata overhead. Scaling compute masks the symptom at higher credit cost and does not reduce the partition count, so performance degrades again as more small inserts accumulate.
- ✓
Batch the incoming rows and load them less frequently, or use a staging table and periodically merge into the target.
Why this is correct
Frequent single-row or tiny INSERT statements create many small micro-partitions, which inflates metadata overhead and forces the optimizer to scan numerous fragments. Accumulating rows and loading them in larger batches produces fewer, better-sized micro-partitions, directly reducing the fragmentation that the profile is revealing as the cause of the slowdown.
- ✗
Convert the table to a transient table so Snowflake stops maintaining the metadata for the small micro-partitions.
Why it's wrong here
Transient tables only affect Fail-safe retention, not micro-partition metadata or pruning behavior. Converting the table would not consolidate the small partitions and would additionally remove Fail-safe protection the workload may require, so it neither resolves the fragmentation nor is a safe change for a production table.
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.