ARA-C01 Question 420
Single answerWarehouse monitoringA Snowflake architect is investigating complaints that dashboard queries are intermittently slow between 9:00 AM and 11:00 AM. The BI workload runs on a dedicated virtual warehouse named BI_WH. Users report that some queries complete quickly while others spend several minutes waiting before they start running. The architect needs to confirm whether warehouse saturation is the primary cause and identify the most appropriate monitoring source to prove it. Which approach should the architect take?
- A
Query the ACCOUNT_USAGE.QUERY_HISTORY view and compare QUEUED_OVERLOAD_TIME to execution times for queries running on BI_WH during the affected period.
- B
Query the ACCOUNT_USAGE.ACCESS_HISTORY view to determine whether table-level contention is causing warehouse-level queuing on BI_WH.
- C
Use SHOW WAREHOUSES to review warehouse size and auto-suspend settings, because these settings directly identify which queries were queued due to overload.
- D
Query the ACCOUNT_USAGE.LOGIN_HISTORY view to correlate user login spikes with warehouse queuing, because login concurrency is the best indicator of BI_WH saturation.
Show answer and explanation
Correct answer: A
Explanation
For warehouse monitoring, the architect should use query-level telemetry that directly exposes queuing behavior. In Snowflake, ACCOUNT_USAGE.QUERY_HISTORY contains queue timing fields, including QUEUED_OVERLOAD_TIME, which is specifically relevant when queries wait because the warehouse is overloaded or lacks available resources. This makes it the best source to confirm warehouse saturation as the root cause of intermittent slowness where queries are delayed before execution begins. In practice, architects often combine QUERY_HISTORY with warehouse-focused views such as WAREHOUSE_LOAD_HISTORY or WAREHOUSE_METERING_HISTORY for broader trend analysis, but the most direct proof for this scenario is the query queueing metric itself. This aligns with Snowflake best practices for monitoring warehouse performance: analyze queue times, concurrency behavior, and warehouse load before deciding whether to resize the warehouse or enable multi-cluster behavior for concurrent workloads.
- A. Correct.
Correct. ACCOUNT_USAGE.QUERY_HISTORY includes queue-related metrics such as QUEUED_OVERLOAD_TIME, which indicate time spent waiting because the warehouse lacked available compute resources. Filtering by WAREHOUSE_NAME = 'BI_WH' and the affected time window allows the architect to verify whether queries are delayed before execution due to warehouse saturation. This is the most direct monitoring evidence for the stated symptom: queries waiting several minutes before they start running.
- B. Incorrect.
Incorrect. ACCESS_HISTORY is used for data access auditing and lineage-style analysis, such as determining which objects were accessed by queries. It does not provide warehouse queuing or compute saturation metrics. A candidate might choose this if they confuse workload slowness with object-level contention, but warehouse queuing is monitored through query and warehouse usage metadata instead.
- C. Incorrect.
Incorrect. SHOW WAREHOUSES is useful for checking current warehouse configuration and state, such as size, auto-suspend, and auto-resume. However, it does not provide historical per-query queueing evidence for the 9:00 AM to 11:00 AM period. Someone might choose this because warehouse sizing is related to the problem, but configuration alone does not prove that queries were queued due to overload.
- D. Incorrect.
Incorrect. LOGIN_HISTORY can help analyze authentication activity and user access patterns, but login volume is not a reliable or direct measure of warehouse saturation. Users may remain connected while submitting many or few queries, so login spikes do not prove warehouse overload. The symptom described is specifically query waiting time on a warehouse, which is better validated in QUERY_HISTORY.