liveBook · Manning

WAREHOUSE_LOAD_HISTORY

As explained in Snowflake Data Engineering

WAREHOUSE_LOAD_HISTORY is a Snowflake usage-history table containing historical virtual-warehouse workload measurements, including query queuing activity, at five-minute intervals.

WAREHOUSE_LOAD_HISTORY is a Snowflake usage-history table that records virtual-warehouse workload in five-minute intervals. Its AVG_RUNNING and AVG_QUEUED_LOAD columns indicate the proportions of each interval spent running queries and queuing queries. section 9.4.1

Access may require ACCOUNTADMIN to grant the SNOWFLAKE.USAGE_VIEWER database role to SYSADMIN. After the grant, SYSADMIN can query the table for operational analysis. section 9.4.1

use role ACCOUNTADMIN;
grant database role SNOWFLAKE.USAGE_VIEWER to role SYSADMIN;
use role SYSADMIN;

Grant the required database role before querying warehouse load history. section 9.4.1

For a daily seven-day view, derive a date from START_TIME, sum AVG_RUNNING and AVG_QUEUED_LOAD, group by date and warehouse, and order the results for comparison. section 9.4.1

select 
  to_date(start_time) as start_date, 
  warehouse_name, 
  sum(avg_running) as total_running, 
  sum(avg_queued_load) as total_queued
from SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
where TO_DATE(start_time) > DATEADD(day,-7,TO_DATE(CURRENT_TIMESTAMP()))
group by all
order by 1, 2;

Run the SQL aggregation to compare running and queued load for each warehouse over the previous seven days. section 9.4.1

Interpreting queued load

A result with zero total queued load indicates no observed queuing during the measured intervals; simultaneous query activity in a production account may produce nonzero values. section 9.4.1

Use queued-load results to guide capacity changes:

  • Create additional warehouses and assign different warehouses to different groups when a single-cluster warehouse queues work. section 9.4.1
  • Convert a single-cluster warehouse to a multicluster warehouse or increase the maximum cluster count when concurrent demand requires more capacity. section 9.6

How Snowflake Data Engineering covers it

How deep it goes
Defines five-minute load metrics and demonstrates a seven-day aggregation, but does not detail the table schema beyond the queried columns.
Examples
Uses a seven-day warehouse summary and sample daily results comparing total running time with total queuing time.
What it assumes
Assumes Snowflake roles and warehouse familiarity while providing the required USAGE_VIEWER access procedure.
What you can do after
Diagnose query queuing with SQL and select warehouse-capacity responses such as additional or multicluster warehouses.
How it connects
Connects virtual warehouses, query queuing, multicluster warehouses, and Snowflake usage monitoring.
Cover of Snowflake Data Engineering
liveBookmanning.com
Read Snowflake Data Engineering on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Snowflake Data Engineering. The section links point to the parts of the book it draws on.