What you will be able to do
- Interpret running, queued, provisioning and blocked load, and choose between resizing, adding clusters or fixing locks
- Choose between the INFORMATION_SCHEMA table function and the ACCOUNT_USAGE view for load history
- Configure resource monitor quotas, schedules and actions, and know what they cannot see
- Use alerts with email notifications, and event tables for logging and tracing, as the monitoring and alerting layer
1.Reading warehouse load: running, queued, provisioning, blocked
If the data layout is fine, the next suspect is compute. Snowflake summarises warehouse activity as query load: the total execution time of all queries in an interval, divided by the length of that interval. In the documented example, four running queries in a 300-second interval add up to a running load of 0.92, and one queued query adds 0.08. Load is split by query state, and the WAREHOUSE_LOAD_HISTORY output gives each state its own column.
| Column | Description |
|---|---|
| AVG_RUNNING | Query load value for queries executed. |
| AVG_QUEUED_LOAD | Query load value for queries queued because the warehouse was overloaded. |
| AVG_QUEUED_PROVISIONING | Query load value for queries queued because the warehouse was being provisioned. |
| AVG_BLOCKED | Query load value for queries blocked by a transaction lock. |
Each state calls for a different response. High running load, or queuing caused by overload, means queries are competing for resources. Move the queued work to a separate warehouse, or change multi-cluster settings to add clusters for higher concurrency. Low load with slow queries is the case for resizing. A larger warehouse helps large, complex queries most and may not speed up small, basic ones. Running queries do not benefit from the new resources: restart the query after the resources are fully provisioned. Queued (Provisioning) load typically appears only in the first few minutes after a warehouse resumes, so it is not a capacity signal. Blocked load comes from transaction locks, and neither more clusters nor a bigger warehouse will clear it.
Check the opposite case too. If the warehouse runs and consumes credits for long stretches while total load stays below 1, it is used inefficiently. Consider a smaller size, which generally makes queries run longer, or a lower MIN_CLUSTER_COUNT for a multi-cluster warehouse. Recurring spikes are another pattern: move the peak workload to its own warehouse and run the rest on a smaller one.
Checkpoint 1 of 9· Check yourself
During a nightly batch, AVG_BLOCKED spikes while AVG_QUEUED_LOAD stays low. What does that indicate?
Blocked load counts queries waiting on a transaction lock. Lack of compute is reported as queued load, and resume delays are reported as queued provisioning.
“Queries that were blocked during the interval due to a transaction lock.”Source: docs.snowflake.com
Checkpoint 2 of 9· Put it in order
Put the documented resize-and-verify loop in order
- 1.If the gain does not justify the extra cost, return the warehouse to its original size
- 2.Re-run the query
- 3.Upsize the warehouse
The documentation describes upsizing as an experiment: upsize, re-run, and revert if the performance gain does not justify the cost.
“simply upsize the warehouse, re-run the query, and if the increased performance does not justify the increased cost”Source: docs.snowflake.com
Checkpoint 3 of 9· Exam question
A data engineer runs `SYSTEM$CLUSTERING_INFORMATION` against a 2 TB sales fact table clustered on `order_date` and sees `average_depth` of 46 with a right-skewed `partition_depth_histogram`. What should the engineer conclude?
Correct answer: A — The table is poorly clustered on its current key, so reclustering or picking a different key would reduce the partition overlap.
- A. A high average_depth means many micro-partitions overlap on the clustering column, so queries filtering on order_date scan far more partitions than necessary; reclustering or a different key narrows that overlap.
- B. SYSTEM$CLUSTERING_INFORMATION reports overlap on the existing key rather than whether the key still exists, and a dropped key would surface as a metadata error, not a depth value.
- C. average_depth is a storage-layout metric computed from micro-partition metadata, not a compute metric, so resizing the warehouse running the query has no effect on it.
- D. Table size alone does not justify a high depth value; a 2 TB table with an appropriate clustering key and healthy maintenance can still report a low, well-clustered depth.
Checkpoint 4 of 9· Exam question
A team is choosing a clustering key for a 5 TB events table that is almost always filtered by `event_date` and occasionally by `region`, and both columns are written in roughly chronological order. Which considerations should guide the decision? (Select all that apply)(Select 3)
Correct answers: A, B, D — Put the most frequently filtered column, `event_date`, first in the clustering key so range predicates on it prune the most partitions.; Check the existing natural insert order before adding a key, since data already loaded roughly by `event_date` may already prune well without one.; Weigh the ongoing reclustering credit cost against the query performance gain, since Automatic Clustering bills separately from warehouse compute.
- A. Correct — leading a clustering key with the column carrying the most selective, frequently used range filter maximizes pruning for the dominant query pattern.
- B. Correct — tables already loaded in an order that matches the query pattern can prune well without an explicit key, so checking current clustering avoids unnecessary reclustering cost.
- C. Incorrect — Snowflake supports only one clustering key made of a small number of expressions; stacking every filtered column dilutes ordering and can worsen overlap instead of improving it.
- D. Correct — Automatic Clustering consumes serverless credits tracked separately from warehouse compute, so the ongoing cost has to be weighed against the pruning benefit before committing to a key.
- E. Incorrect — leading with a low-cardinality column like region groups too few distinct values per partition and typically prunes worse than leading with a high-selectivity range column like a date.
- F. Incorrect — the decision to cluster depends on query patterns and pruning inefficiency, not on a fixed size threshold; some sub-10 TB tables benefit and some multi-TB tables do not.
2.INFORMATION_SCHEMA table function or ACCOUNT_USAGE view
You can get load history from two places, and the choice affects what you see. The INFORMATION_SCHEMA table function WAREHOUSE_LOAD_HISTORY covers a single warehouse and only the last 14 days. For ranges under 8 hours it reports load in 5-second intervals, and in 5-minute intervals otherwise. To call it, the session must have an INFORMATION_SCHEMA schema in use or you must fully qualify the function name. A role with MONITOR on a warehouse can query that warehouse's history.
Checkpoint 5 of 9· Fill the gap
Which schema completes this call, which returns the last hour of load in 5-second intervals for the current warehouse?
use warehouse mywarehouse; select * from table( ? .warehouse_load_history(date_range_start=>dateadd('hour',-1,current_timestamp())));The table function version of WAREHOUSE_LOAD_HISTORY is in INFORMATION_SCHEMA, and it is the only version that gives 5-second granularity for short ranges.
Source: docs.snowflake.comThe SNOWFLAKE.ACCOUNT_USAGE schema has a WAREHOUSE_LOAD_HISTORY view with the same load columns. It also follows the general differences between ACCOUNT_USAGE and INFORMATION_SCHEMA: it includes records for dropped objects, keeps historical usage data longer, and has data latency. For this view, latency can be up to 180 minutes, and load is shown in 5-minute intervals. In practice, use the table function to look at the last few minutes, and the view for trends across warehouses over longer periods, such as a month of queuing. The Account Usage QUERY_HISTORY view lets you set your own time window for evaluating warehouse performance, including throughput and latency by statement type.
SELECT TO_DATE(start_time) AS date,
warehouse_name,
SUM(avg_running) AS sum_running,
SUM(avg_queued_load) AS sum_queued
FROM snowflake.account_usage.warehouse_load_history
WHERE TO_DATE(start_time) >= DATEADD(month,-1,CURRENT_TIMESTAMP())
GROUP BY 1,2
HAVING SUM(avg_queued_load) >0;Checkpoint 6 of 9· Exam question
After adding a clustering key on `customer_id`, an analyst reruns `SYSTEM$CLUSTERING_INFORMATION` a week later and still sees a high `average_overlaps` value, even though Automatic Clustering shows active credit consumption in `ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY`. What is the most likely explanation?
Correct answer: A — The table receives DML fast enough that reclustering has not yet caught up, or `customer_id` is a poor fit for the actual filter pattern.
- A. Ongoing reclustering credit consumption combined with persistent overlap usually means either the write rate is outpacing the reclustering work, or the chosen key does not match how the table is actually queried.
- B. SYSTEM$CLUSTERING_INFORMATION is computed against current metadata each time it runs, so it reflects the table's present state rather than a snapshot frozen at creation.
- C. The function and the Snowsight clustering visualization both read from the same underlying micro-partition metadata, so they do not diverge in what they report.
- D. AUTOMATIC_CLUSTERING_HISTORY records clustering-specific credit usage in a dedicated view separate from Search Optimization Service maintenance, so the activity billed there is reclustering work, not SOS.
3.Resource monitors: credit quotas and actions
Fixing performance by resizing or adding clusters raises spend, and a resource monitor is the control on that spend. A resource monitor tracks credits used by warehouses, including the cloud services that support them, against a credit quota. When usage reaches a threshold, it acts. Only ACCOUNTADMIN can create one, though privileges to view and modify monitors can be granted to other roles. There are two scopes. An account monitor covers all warehouses, and an account can have only one. A warehouse monitor covers the warehouses assigned to it, and each warehouse can belong to only one. A monitor with neither scope set stays dormant. If either the account monitor or the warehouse monitor reaches a suspend threshold, the warehouse is suspended: the account monitor does not override the warehouse assignment.
By default, used credits reset to 0 at the start of each calendar month. A custom schedule sets a frequency (Daily, Weekly, Monthly, Yearly or Never) together with a start, and you cannot set one without the other. Resets happen at 12:00 AM UTC. Thresholds are percentages of the quota and can exceed 100. A monitor can have one Suspend action, one Suspend Immediate action and up to five Notify actions, and it needs at least one action to do anything.
| Action | Effect on standard warehouses |
|---|---|
| Notify | No action on warehouses; a notification is sent |
| Notify & Suspend | Notify, then suspend after running statements complete |
| Notify & Suspend Immediately | Notify and suspend immediately, cancelling running statements |
Checkpoint 7 of 9· Match them up
Match each resource monitor fact to its rule
Tap a term, then the definition that fits it.
These limits are stated in the resource monitor documentation. A monitor with no actions does nothing at its thresholds.
“if no actions have been defined, nothing happens when the used credits reach the threshold”Source: docs.snowflake.com
Sources6
4.Alerts and email notifications
Resource monitors only watch credits. For any other condition, such as rising queued load, use an alert. In CREATE ALERT, the IF( EXISTS( condition )) clause takes a SELECT, SHOW or CALL statement, and THEN takes the action. The action runs only when the condition returns one or more rows. To send an email or other notification, the action calls the SYSTEM$SEND_EMAIL or SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure. Notifications go through a notification integration, and alerts created from a template fail validation if the referenced integration does not exist or is the wrong type. Because the condition is an ordinary SELECT, it can query the same load and usage data described earlier on this page, so a troubleshooting query can become a standing alert.
Checkpoint 8 of 9· Check yourself
An alert's condition is a SELECT that returns warehouses whose queued load exceeded a limit in the last hour. On a quiet day the SELECT returns no rows. What happens?
An alert's action runs only when its condition returns one or more rows. Zero rows means no action.
“If the statement returns one or more rows, the action for the alert is executed.”Source: docs.snowflake.com
Sources7
5.Event tables: logging and tracing your own code
Load views and alerts describe warehouses and queries. They cannot show what happens inside your own functions and procedures. For that, Snowflake writes telemetry from your code, including log, trace and metrics data, to an event table that you query for analysis. You control how much is captured by setting a level for each type of data. Logs and traces answer different questions, as the table below shows.
| Characteristic | Log entries | Trace events |
|---|---|---|
| Intended use | Detailed but unstructured information about the state of your code | A brief but structured summary of each invocation of your code |
| Payload structure | None. A log entry is just a string. | Attributes (key-value pairs) that can be queried with SQL |
| Grouping | No. Each log entry is an independent event. | Yes. Trace events are organized into spans. |
| Quantity limits | Unlimited | Capped at 128 trace events per span |
Checkpoint 9 of 9· Check yourself
A team wants to aggregate per-invocation timing of a stored procedure across thousands of calls, filtering by key-value attributes in SQL. Which telemetry should the procedure emit to the event table?
Trace events give a structured summary of each invocation, with attributes you can query, which suits aggregation. Log entries are plain strings.
“Record a brief but structured summary of each invocation of your code.”Source: docs.snowflake.com
Sources8
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Any waiting load means the warehouse needs to be larger or needs more clusters.Why is that wrong?
Blocked load comes from transaction locks, and queued provisioning usually appears only just after a resume. Only queuing caused by overload points to adding capacity.
Covered in Reading warehouse load: running, queued, provisioning, blocked
2.The INFORMATION_SCHEMA WAREHOUSE_LOAD_HISTORY function can show a month of load history.Why is that wrong?
The table function returns only the last 14 days. For longer history, use the ACCOUNT_USAGE view, which can lag by up to 3 hours.
Covered in INFORMATION_SCHEMA table function or ACCOUNT_USAGE view
3.An account-level resource monitor will cap the credits spent on automatic reclustering after you add a clustering key.Why is that wrong?
Account-level resource monitors do not control credits used by serverless features, and automatic reclustering is one of them.
Covered in Resource monitors: credit quotas and actions
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“dividing the execution time (in seconds) of all queries in an interval by the total time (in seconds) for the interval”
↩︎ Reading warehouse load: running, queued, provisioning, blocked“if you are using multi-cluster warehouses, you could change your multi-cluster settings to add additional clusters to handle higher concurrency going forward.”
↩︎ Reading warehouse load: running, queued, provisioning, blocked“the total query load was less than 1 for substantial periods of time, the warehouse use is inefficient.”
↩︎ Reading warehouse load: running, queued, provisioning, blocked“Typically only occurs in the first few minutes after a warehouse resumes.”
↩︎ Exam trap 1“If the running query load is low and query performance is slow, you could resize the warehouse to provide more compute resources.”
↩︎ Prediction“Queries that were blocked during the interval due to a transaction lock.”
↩︎ Checkpoint - 2.
“Using a larger warehouse has the biggest impact on larger, more complex queries, and may not improve the performance of small, basic queries.”
↩︎ Reading warehouse load: running, queued, provisioning, blocked“simply upsize the warehouse, re-run the query, and if the increased performance does not justify the increased cost”
↩︎ Checkpoint - 3.
“Records for dropped objects are included in each view.”
↩︎ INFORMATION_SCHEMA table function or ACCOUNT_USAGE view“Longer retention time for historical usage data.”
↩︎ INFORMATION_SCHEMA table function or ACCOUNT_USAGE view - 4.
“Latency for the view may be up to 180 minutes (3 hours).”
↩︎ INFORMATION_SCHEMA table function or ACCOUNT_USAGE view - 5.
“When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name must be fully-qualified.”
↩︎ INFORMATION_SCHEMA table function or ACCOUNT_USAGE view“This function returns warehouse activity within the last 14 days.”
↩︎ Exam trap 2 - 6.
“Resource monitors work for warehouses only.”
↩︎ Resource monitors: credit quotas and actions“An account-level resource monitor does not override resource monitor assignment for individual warehouses.”
↩︎ Resource monitors: credit quotas and actions“Note that actions support thresholds greater than 100.”
↩︎ Resource monitors: credit quotas and actions“(for example, Snowpipe, automatic reclustering, and materialized views)”
↩︎ Exam trap 3“if no actions have been defined, nothing happens when the used credits reach the threshold”
↩︎ Checkpoint - 7.
“To send a notification, you can call the SYSTEM$SEND_EMAIL or SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure.”
↩︎ Alerts and email notifications“You can use the following commands: SELECT SHOW <objects> CALL”
↩︎ Alerts and email notifications“If the statement returns one or more rows, the action for the alert is executed.”
↩︎ Checkpoint - 8.
“Query the event table to analyze collected log and trace data.”
↩︎ Event tables: logging and tracing your own code“The number of trace events per span is capped at 128.”
↩︎ Event tables: logging and tracing your own code“Record a brief but structured summary of each invocation of your code.”
↩︎ Checkpoint