DEA-C02 Performance Optimization Practice Question
A data engineer notices that a dashboard query returns instantly on repeat executions but takes much longer the first time each morning. No data has changed overnight. Which Snowflake feature is most directly responsible for the fast repeat executions?
⚠ Common exam trap
The trap here is attributing the speedup to a warmed warehouse or disk cache, when an unchanged identical query returning instantly is the query result cache at work.
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 query result cache stores the result of the identical query and returns it without re-executing, as long as the underlying data and query text are unchanged.
The near-instant repeat execution with unchanged data is the signature of the query result cache, which persists results and serves identical queries without recomputation. The first run of the day must execute because the result is not yet cached or was invalidated, and any data change would also invalidate it.
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 holds micro-partition statistics that let the optimizer skip execution entirely and return a stored row set.
Why it's wrong here
Metadata caching stores statistics such as min and max values that help the optimizer plan and prune, but it does not store or return query results. The optimizer still executes the query against the data, so metadata alone cannot produce the instant responses seen on repeat runs.
- ✓
The query result cache stores the result of the identical query and returns it without re-executing, as long as the underlying data and query text are unchanged.
Why this is correct
When an identical query is re-run and the underlying tables have not changed, Snowflake returns the persisted result from the query result cache without recomputing it. That is why repeat executions are near-instant while the first execution after the cache expires or data changes must run fully, matching the observed morning pattern.
- ✗
The local disk cache on the warehouse nodes retains the scanned micro-partitions so the second execution reads them without remote I/O.
Why it's wrong here
The local disk cache speeds up re-reads of the same micro-partitions within a running warehouse, but it still requires executing the query. It cannot reduce a full aggregation to an instant response, and it is cleared when the warehouse suspends, which contradicts the morning-vs-repeat pattern described.
- ✗
The virtual warehouse remains suspended overnight, so the first execution warms it and later executions reuse the warmed compute.
Why it's wrong here
A suspended warehouse starts fresh on resume, and warming it affects startup latency rather than the dramatic difference seen between first and repeat runs. Warm compute does not by itself make an identical query return in milliseconds, so this does not explain the pattern the engineer observes.
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.