ADA-C01 Question 297
Single answerAnalyze the components of the Query Profile:A Snowflake administrator is investigating why a dashboard query suddenly takes much longer during peak business hours. In Snowsight, the Query Profile for the slow execution shows that most elapsed time is concentrated in a node labeled "Remote Disk I/O," while the same query usually completes quickly at other times. The SQL text and warehouse size have not changed. Which conclusion is the MOST appropriate based on this Query Profile observation?
- A
The query is spending significant time reading data from remote storage rather than from the warehouse cache, so cache warmth is likely affecting performance.
- B
The query is blocked on transactional locks from concurrent DML, so the warehouse should be resized to reduce lock wait time.
- C
The query is primarily bottlenecked by compilation time, indicating that the optimizer is generating a more complex plan during peak hours.
- D
The query is failing to prune micro-partitions, which Query Profile would show specifically as Remote Disk I/O regardless of scan efficiency.
Show answer and explanation
Correct answer: A
Explanation
The key skill being tested is interpreting Query Profile components to identify the most likely performance cause. In Snowflake, Query Profile breaks execution into operators and highlights where elapsed time is spent. A large "Remote Disk I/O" share indicates the warehouse is reading data from remote/cloud storage instead of benefiting from local cache. This often explains why an unchanged query can run slower at some times and faster at others.
Administrators should distinguish this from other issues such as compilation overhead, lock contention, or poor pruning. Compilation is outside the operator-level execution path reflected by I/O nodes. Locking problems are not diagnosed by a Remote Disk I/O operator. And while poor pruning can increase scan work, the profile component named here points more directly to where the data was fetched from, not by itself to why so much data had to be scanned.
This aligns with Snowflake best practices for using Query Profile to determine whether a query is bottlenecked by scanning, joins, spilling, or remote I/O, and then deciding whether the issue is related to cache behavior, SQL design, clustering/pruning, or warehouse sizing.
- A. Correct.
Correct. In Query Profile, a dominant "Remote Disk I/O" component indicates time spent fetching data from remote/cloud storage rather than local SSD or memory cache on the virtual warehouse. If the same query is much faster at other times with no SQL or warehouse-size change, a practical explanation is that cached data is no longer warm or has been evicted, causing more remote reads. This is a common performance pattern when warehouses suspend/resume or when cache locality changes under different workloads.
- B. Incorrect.
Incorrect. Locking and transaction blocking are not inferred from a "Remote Disk I/O" hotspot in Query Profile. Resizing a warehouse does not resolve lock contention in the way suggested here. A candidate might choose this because peak-hour slowdown can be associated with concurrency, but the profile component named in the scenario points to storage read behavior, not lock wait.
- C. Incorrect.
Incorrect. Compilation time is a separate phase from execution and would not appear as most elapsed time in a Query Profile node labeled "Remote Disk I/O." Query Profile focuses on execution operators and their costs. Someone might select this if they confuse plan generation with runtime activity, but the scenario explicitly identifies an execution-time I/O bottleneck.
- D. Incorrect.
Incorrect. Poor micro-partition pruning can increase the amount of data scanned, but Query Profile does not make "Remote Disk I/O" a synonym for pruning failure. A scan with poor pruning may still show more bytes scanned and scan-related work, but the specific observation here supports that data was read remotely. The misconception is assuming every storage-heavy profile automatically means pruning failed.