Snowflake Workload Management & ACCOUNT_USAGE Performance Views
Day 36 taught you to read a single query’s profile. Today you zoom out to the warehouse that runs all of them. The job is to put the right work on the right warehouse, then read the columns that show when a warehouse is overloaded. One trap repeats across Domain 4: when concurrency is the problem, you scale out, not up. Get that pairing right and most workload questions answer themselves.
Four phrases drive every workload question. Each has a plain meaning that points away from the answer.
| Phrase in the stem | What it sounds like | What it means for warehouses |
|---|---|---|
| Scale up | Make the thing bigger and stronger | Resize the warehouse to the next size. More memory and CPU per cluster. It speeds up large, complex queries. It is not the fix for concurrency. |
| Scale out | Spread the work wider | Add clusters to a multi-cluster warehouse. More queries run at once. It does not make any single query faster. |
| Concurrency | Many things happening together | The number of queries hitting one warehouse at the same time. High concurrency causes queuing, not slow individual queries. |
| Workload | How much work there is | A category of query traffic with one shape. ETL is heavy and scheduled. BI is many small concurrent reads. They do not belong on the same warehouse. |
Put Each Workload on Its Own Warehouse
Why one warehouse for everything fails
A warehouse has a fixed pool of compute at any moment. Every query on it draws from that pool. Run a heavy nightly load and a busy dashboard on the same warehouse and they compete. The load steals compute from the dashboard. The dashboard queues behind the load.
This is mutual contention. Both workloads run worse than they would alone. Splitting them onto separate warehouses removes the competition. Each workload gets a pool sized for its own shape.
The three common shapes
ETL is a small number of heavy, scheduled queries. Large joins, big sorts, and full-table reads. It wants raw compute per query, so it suits a larger single-cluster warehouse. Bigger size means more memory. That also cuts the spilling you saw on Day 36.
BI is the opposite. Many small queries from many dashboard users, all at once. The bottleneck is concurrency, not query size. A multi-cluster warehouse fits, because it adds clusters as users arrive. An XSMALL with room to scale out beats a single huge cluster here.
Ad-hoc and data science work is exploratory and intermittent. A small warehouse with auto-suspend keeps the cost low between bursts. Isolating it also stops a developer’s runaway query from stalling a production pipeline.
| Workload | Query shape | Warehouse choice |
|---|---|---|
| ETL / ELT | Few heavy scheduled jobs | Larger single-cluster, sized for the heaviest job |
| BI / dashboards | Many small concurrent reads | Smaller multi-cluster, set to scale out |
| Ad-hoc / data science | Bursty, unpredictable | Small warehouse with short auto-suspend |
The exam phrasing for this is workload isolation. When a stem describes a load job slowing a dashboard, the answer is almost always a second warehouse. This connects back to Day 27, where you read warehouse-level cost in ACCOUNT_USAGE. Separate warehouses also make that cost readable per workload.
Scale Up vs Scale Out: The Core Trap
This is the most-tested idea in the sub-objective. The two levers solve different problems. The exam writes stems that swap them.
Scale up (resize) fixes slow queries
Scaling up means resizing to the next warehouse size, the Day 8 lever. Each step doubles the compute in the cluster. More memory and CPU make a large, complex query finish faster. It also reduces spilling, because more data fits in memory.
Snowflake is explicit that resizing is for query performance. The docs state warehouse resizing is not intended for handling concurrency issues. If ten dashboard users queue, a bigger warehouse does not clear the queue. It just makes each query a little faster while they all still wait their turn.
Scale out (multi-cluster) fixes queuing
Scaling out means adding clusters to a multi-cluster warehouse. Snowflake starts extra clusters as concurrency rises, then shuts them down as it falls. More queries run at the same time, so the queue drains. It does not make any single query faster.
One hard fact gates this answer. Multi-cluster warehouses require Enterprise Edition or higher. If a stem says the account is on Standard edition, scale out is off the table. The answer there is a second warehouse, set up manually.
The pairing to memorise
Read the symptom, then match the lever. A single slow heavy query points up. A pile of queries waiting their turn points out. Among candidates I have trained, the swapped-lever distractor catches more people here than any other Domain 4 trap.
| Symptom | Real problem | Correct lever |
|---|---|---|
| One large query runs slowly, low concurrency | Not enough compute per query | Scale up: resize larger |
| Many queries queue at peak, each is small | Too much concurrency for one cluster | Scale out: multi-cluster (Enterprise) |
| Bytes spilled to remote storage | Query ran out of memory | Scale up: resize larger (Day 36) |
| Account is Standard edition and queuing | Multi-cluster unavailable | Add a second warehouse, route work to it |
Reading the Queuing Columns
Day 36 named queue time as one waiting bucket. QUERY_HISTORY splits that wait into three separate columns. Each names a different reason the query waited. The exam tests whether you know which is which.
QUEUED_OVERLOAD_TIME
Time the query waited because the warehouse was already full. Other queries held the compute, so this one sat in line. This is the common one. A non-zero overload time across many queries says the warehouse needs to scale out, or the workload needs its own warehouse.
QUEUED_PROVISIONING_TIME
Time the query waited for the warehouse to provision compute. This happens while a suspended warehouse resumes, or while a multi-cluster warehouse spins up another cluster. It is start-up wait, not contention. A short provisioning wait after an auto-resume is normal, not a problem.
QUEUED_REPAIR_TIME
Time the query waited while Snowflake repaired the warehouse, such as replacing a failed compute node. It is usually zero. It is in the exam pool mainly as a distractor against the two columns that matter.
Where to read history at scale
Two sources expose these columns. They answer different questions. Pick by the time range you need.
| Source | Time range | Freshness |
|---|---|---|
INFORMATION_SCHEMA.QUERY_HISTORY() function | Last 7 days | Near real-time |
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view | Last 365 days | Latency up to 45 minutes |
Use the function for recent debugging, where you need fresh rows from the last hour. Use the ACCOUNT_USAGE view for trend and cost analysis over weeks, where a 45-minute lag does not matter. Day 27 covered this view in depth.
Cheat Sheet
| Concept | What to remember | Exam keyword |
|---|---|---|
| Scale up | Resize larger. Fixes slow heavy queries. Not concurrency | “Resize” |
| Scale out | Add clusters. Fixes queuing. No faster single query | “Multi-cluster” |
| Multi-cluster edition | Enterprise Edition or higher, full stop | “Enterprise” |
| ETL warehouse | Larger single-cluster, sized for the heaviest job | “Heavy load” |
| BI warehouse | Smaller multi-cluster, scales out for users | “Concurrency” |
| Workload isolation | Separate warehouse per workload stops contention | “Separate warehouse” |
| QUEUED_OVERLOAD_TIME | Waited because warehouse was busy. Scale out | “Overload” |
| QUEUED_PROVISIONING_TIME | Waited for warehouse to start. Normal on resume | “Provisioning” |
| QUEUED_REPAIR_TIME | Waited for node repair. Usually zero | “Repair” |
| QUERY_HISTORY function | INFORMATION_SCHEMA, 7 days, fresh | “7 days” |
| QUERY_HISTORY view | ACCOUNT_USAGE, 365 days, up to 45 min lag | “365 days” |
Exam Tip
The stem tells you which lever to pull. The word “concurrency” means scale out, every time. If many users or many queries hit one warehouse and queue, the answer adds clusters or adds a warehouse. A bigger size does not clear a queue. If instead one heavy query is slow with nothing else running, the answer resizes larger.
Two distractor families repeat. The first says to scale up for high concurrency. It is wrong because resizing is not built for concurrency. The second offers multi-cluster as the answer on a Standard-edition account. It is wrong because multi-cluster requires Enterprise Edition. Check the edition named in the stem before you pick a multi-cluster option.
Hands-On Lab
SNOWFLAKE_SAMPLE_DATA database. This lab reads sample data only. It never touches day10_orders.Create one warehouse per workload. A small single-cluster warehouse stands in for ETL. A multi-cluster warehouse stands in for BI. The MAX_CLUSTER_COUNT of 2 needs Enterprise Edition.
CREATE WAREHOUSE etl_sim
WAREHOUSE_SIZE = 'SMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;
CREATE WAREHOUSE bi_sim
WAREHOUSE_SIZE = 'XSMALL'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 2
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;SHOW WAREHOUSES;. The bi_sim row shows a max cluster count of 2. If CREATE fails on a multi-cluster property, your account is not Enterprise Edition. That failure is itself the exam fact in action.Run a heavy job and a light job on separate warehouses. Open two worksheets. Start the heavy aggregation on etl_sim, then run the light count on bi_sim right after.
USE WAREHOUSE etl_sim;
SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), AVG(L_DISCOUNT)
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEM
GROUP BY L_RETURNFLAG, L_LINESTATUS;USE WAREHOUSE bi_sim;
SELECT COUNT(*) FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;Force contention on one warehouse. Point both queries at etl_sim and fire several at once. Re-run the heavy aggregation a few times in quick succession from one worksheet, then the count from another, all on etl_sim.
USE WAREHOUSE etl_sim;
-- Fire this several times back to back, from two worksheets,
-- so the single SMALL cluster has to queue some of them.
SELECT L_RETURNFLAG, SUM(L_QUANTITY)
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEM
GROUP BY L_RETURNFLAG;etl_sim. The Warehouse Activity chart shows a queued portion when the single cluster is saturated. That queued bar is overload, not slow queries.Read the queuing columns directly. The INFORMATION_SCHEMA function returns the queue breakdown for recent queries. Filter for the ones that actually waited on an overloaded warehouse.
SELECT QUERY_ID,
WAREHOUSE_NAME,
QUEUED_PROVISIONING_TIME,
QUEUED_OVERLOAD_TIME,
TOTAL_ELAPSED_TIME
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE QUEUED_OVERLOAD_TIME > 0
ORDER BY QUEUED_OVERLOAD_TIME DESC;QUEUED_OVERLOAD_TIME with a modest TOTAL_ELAPSED_TIME means the query was fine. The warehouse was the bottleneck.Cleanup. Drop both warehouses created for this lab. They are disposable simulations, not shared course assets.
DROP WAREHOUSE IF EXISTS etl_sim;
DROP WAREHOUSE IF EXISTS bi_sim;
-- lab_xs and day10_orders are untouched and remain available.Practice Questions
Options:
A. Resize the shared warehouse to the next larger size
B. Move the data load onto its own separate warehouse
C. Lower the AUTO_SUSPEND on the shared warehouse
D. Add a clustering key to the dashboard tables
Why B: The two workloads compete for one compute pool, so each slows the other. Putting the load on its own warehouse removes the contention. This is workload isolation, the standard fix for one workload starving another.
Why not A: A bigger shared warehouse still has both workloads competing on it. More compute split two ways does not stop the competition.
Why not C: Auto-suspend controls idle cost, not contention. It changes nothing while both workloads are active.
Why not D: Clustering helps pruning on a slow scan. It does not address two workloads sharing one warehouse.
Options:
A. Resize the warehouse to 2X-Large
B. Configure it as a multi-cluster warehouse and let it scale out
C. Add a clustering key to the most-queried table
D. Increase STATEMENT_TIMEOUT_IN_SECONDS
Why B: The problem is concurrency, since queries queue while each runs fast. Scaling out adds clusters, so more queries run at once and the queue drains. Multi-cluster is the lever built for this.
Why not A: Resizing speeds up individual queries. Those are already fast. Snowflake states resizing is not intended for concurrency, so the queue stays.
Why not C: Clustering targets slow scans from weak pruning. It does not change how many queries run at once.
Why not D: A longer timeout just lets queries wait longer before aborting. It does not reduce the wait.
Options:
A. Multi-cluster warehouses require Enterprise Edition or higher
B. Multi-cluster warehouses cannot use AUTO_SUSPEND
C. Multi-cluster warehouses only work for ETL, not BI
D. Multi-cluster warehouses double storage cost
Why A: Multi-cluster is an Enterprise Edition feature. On Standard edition the option does not exist. The fix there is to create a second warehouse and route some queries to it.
Why not B: Multi-cluster warehouses support auto-suspend and auto-resume like any warehouse. This invents a limit.
Why not C: Multi-cluster suits high-concurrency BI especially well. The claim is backwards.
Why not D: Clusters add compute credits, not storage cost. Storage is billed separately from warehouses.
Options:
A. QUEUED_OVERLOAD_TIME is time spent waiting because the warehouse was already busy
B. QUEUED_PROVISIONING_TIME is time spent waiting for the warehouse to start or add compute
C. QUEUED_OVERLOAD_TIME is the time the query spent executing
D. QUEUED_PROVISIONING_TIME measures bytes spilled to remote storage
Why A: Overload time is the wait caused by other queries holding the compute. A non-zero value across many queries signals the need to scale out.
Why B: Provisioning time is the start-up wait while a warehouse resumes or spins up another cluster. A short value after auto-resume is normal.
Why not C: Queue time is waiting, not executing. Execution time is a separate column. Re-read Day 36 if missed.
Why not D: Spilling is reported in the query profile, not in a queuing column. The two metrics are unrelated.
Options:
A. INFORMATION_SCHEMA.QUERY_HISTORY() table function
B. SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view
C. The Query Profile for each query
D. SHOW WAREHOUSES output
Why B: The ACCOUNT_USAGE view retains history for 365 days. That covers three months easily. Its latency of up to 45 minutes is fine for a trend review. This is the source from Day 27.
Why not A: The INFORMATION_SCHEMA function returns only the last 7 days. It cannot reach back three months.
Why not C: The profile inspects one query at a time. It does not aggregate trends across months.
Why not D: SHOW WAREHOUSES lists current warehouse properties. It holds no historical query data.
Snowflake Documentation
Official docs for today’s topics. The exam pulls directly from these.
Today you learned: put each workload on its own warehouse, because mixing ETL and BI causes mutual contention. ETL suits a larger single-cluster warehouse. BI suits a smaller multi-cluster warehouse that scales out. The core trap pairs a symptom with a lever: scale up for a slow heavy query, scale out for concurrency. Multi-cluster requires Enterprise Edition. The queuing columns split the wait into overload, provisioning, and repair. You read history from the 7-day function or the 365-day ACCOUNT_USAGE view.
Key takeaway: the word concurrency means scale out. The word slow on a single heavy query means scale up. The exam swaps these two. It also checks the edition before multi-cluster is on the table.
Tomorrow (Day 38): Query Acceleration and Search Optimization. You meet the Query Acceleration Service. It offloads heavy scans to a shared serverless pool. You add Search Optimization for point lookups and read its search access paths. Both are serverless add-ons that cost credits beyond the warehouse, so the exam tests when each one earns its keep.
