Snowflake Warehouse Sizing, Multi-Cluster Scaling and Credit Control

1.Scaling up: warehouse size, query complexity and cost

Virtual warehouses supply the compute that runs your queries. Snowflake lists several warehouse-side tuning strategies: reduce queues, resolve memory spillage, increase warehouse size, try query acceleration, optimize the warehouse cache, and limit concurrently running queries. The most direct of these is size. A larger warehouse has more compute resources for a query. Size also matters for spilling: a query runs substantially slower when a warehouse runs out of memory, because bytes spill onto storage.

A bigger warehouse does not help every query. Using a larger warehouse has the biggest impact on larger, more complex queries, and it may not improve small, basic queries at all. Before you resize, check the warehouse's load. If the warehouse is heavily loaded, concurrent queries are competing for its resources, and a bigger size may not give the boost you expect.

Each size doubles the hourly rate, so the useful question is never "is Large more expensive?" It is "does the query finish fast enough to offset the higher rate?" The documented test is simple. Upsize, re-run the query, and if the speed-up does not justify the cost, return the warehouse to its original size. Billing is per second, so you pay for the seconds a warehouse actually runs. Snowflake also recommends limiting who can resize warehouses. A user who upsizes for one query and forgets to size back down causes unexpected cost.

ALTER WAREHOUSE my_wh SET WAREHOUSE_SIZE = large;

Scale up versus scale out comes down to what is limiting the workload. Scaling up changes the size of the warehouse, so every cluster gets more compute and each individual query can run faster. Scaling out keeps the size and adds clusters, so more queries can run at the same time instead of queuing. Snowflake notes that multi-cluster warehouses are the way to scale capacity without changing the warehouse size.

On a standard single-cluster warehouse you have to handle growth by hand: either increase the size of the warehouse, or start additional warehouses and explicitly redirect users and queries to them. Then you must downsize or suspend again to conserve credits. A multi-cluster warehouse in Auto-scale mode removes that manual work for fluctuating concurrency. It does not make one slow query faster, though. For slow-running queries and data loading, resizing gives more benefit. Rule of thumb: slow query on a lightly loaded warehouse, scale up; many queries waiting in a queue, scale out. The next section covers scaling out in detail.

Sources123

2.Scaling out: multi-cluster warehouses

By default a warehouse is a single cluster. When there aren't enough resources for every submitted query, Snowflake queues the extra queries. A multi-cluster warehouse (Enterprise Edition) adds clusters of the same size. You define it with a maximum cluster count greater than 1 and a minimum that is equal to or less than the maximum. It keeps all the normal warehouse properties: size, resizing at any time, auto-suspend and auto-resume. Auto-suspend applies to the whole warehouse, not to individual clusters. Larger sizes allow fewer clusters: XSMALL through MEDIUM allow up to 300, LARGE 160, XLARGE 80, and 4XLARGE through 6XLARGE 10.

You run a multi-cluster warehouse in one of two modes. Maximized mode (min = max) starts every cluster when the warehouse starts. It suits large, steady concurrency. Auto-scale mode (min < max) starts clusters as queries begin to queue and shuts them down as load drops. Snowflake's advice is to start with Auto-scale and keep it small, for example max 2 or 3 and min 1, then widen the range as you learn the load pattern. In Auto-scale, the SCALING_POLICY property controls credit use. The Economy policy keeps running clusters fully loaded instead of starting new ones, which saves credits but can leave queries queued.

The maximum credit cost is size × maximum clusters. A Medium (4 credits/hour) with 3 clusters can burn at most 12 credits per hour. What you actually pay depends on how many clusters run and for how long. If you resize a multi-cluster warehouse, the new size applies to all of its clusters.

Sources34

3.Snowpark-optimized warehouses

Some workloads are limited by the memory on a single node, not by concurrency or total compute. Snowpark-optimized warehouses are built for this case. They let you configure memory and CPU architecture on a single-node instance. Snowflake recommends them for Snowpark code with large memory requirements or a dependency on a specific CPU architecture. A typical example is ML training in a stored procedure on one warehouse node. Snowpark UDFs and UDTFs may also benefit, but workloads that don't use Snowpark might not. The default configuration gives 16x the memory per node of a standard warehouse. You can request more with the RESOURCE_CONSTRAINT property on CREATE WAREHOUSE or ALTER WAREHOUSE. One trade-off: creating or resuming a Snowpark-optimized warehouse can take longer than a standard one.

Sources5

4.Measuring queues with ACCOUNT_USAGE

To choose between scaling up and scaling out, you need evidence, and the SNOWFLAKE.ACCOUNT_USAGE schema provides it. By default only ACCOUNTADMIN can query it. The WAREHOUSE_LOAD_HISTORY view reports load in 5-minute intervals, and its latency can be up to 3 hours. Each load value is the total execution time of queries in a given state divided by the length of the interval. For example, 276 seconds of query time in a 300-second interval is a load of 0.92. The columns separate why work was waiting:

- AVG_RUNNING: load from queries that executed - AVG_QUEUED_LOAD: queued because the warehouse was overloaded - AVG_QUEUED_PROVISIONING: queued while the warehouse was being provisioned - AVG_BLOCKED: blocked by a transaction lock

In Snowsight, the Warehouse Activity chart shows the same Queued load. To see how long individual queries waited in the queue, query the QUERY_HISTORY view.

SELECT TO_DATE(start_time) AS date,
  warehouse_name,
  SUM(avg_running) AS sum_running,
  SUM(avg_queued_load) AS sum_queued
FROM snowflake. ? .warehouse_load_history
WHERE TO_DATE(start_time) >= DATEADD(month,-1,CURRENT_TIMESTAMP())
GROUP BY 1,2
HAVING SUM(avg_queued_load) >0;

Once you see sustained AVG_QUEUED_LOAD, there are documented fixes. For a regular warehouse, create more warehouses and spread the queries across them, starting with the queries that cause the spikes. You can also convert the warehouse to multi-cluster. If it is already multi-cluster, raise its maximum cluster count. On the other hand, if load is low and a single complex query is slow, that points to a larger size.

Sources64

5.Constraining warehouse credits with resource monitors

Scaling buys performance with credits, and resource monitors put a ceiling on what that can cost. Only ACCOUNTADMIN can create a monitor, though it can grant other roles the privileges to view and modify one. A monitor has a credit quota and a schedule. By default the schedule resets used credits monthly. You can choose daily, weekly, monthly, yearly or never, with a start date, and resets happen at 12:00 AM UTC. The quota counts credits from warehouses and from the cloud services that support them. It ignores the daily 10% cloud services adjustment, so cloud services usage that never gets billed still counts toward the limit.

You can set one account monitor per account. You can also create warehouse monitors, and each warehouse can be assigned to only one of them. If either the account monitor or a warehouse's own monitor reaches a suspend threshold, the warehouse is suspended. Triggers are percentages of the quota and may exceed 100. Each monitor can have one Suspend action (which waits for running statements to finish), one Suspend Immediate action (which cancels them), and up to five Notify actions. A monitor with no actions does nothing.

Sources7

6.Balancing optimization against credit consumption

Every optimization has a credit price, so tuning is a trade-off rather than a free win. The query acceleration service (QAS) offloads parts of a query to shared compute, which helps outlier queries with large scans and selective filters. But it can raise the credit consumption rate of a warehouse. The QUERY_ACCELERATION_MAX_SCALE_FACTOR property limits that rate. The scale factor is 8 when you explicitly set ENABLE_QUERY_ACCELERATION = TRUE, and 2 when Snowflake enables QAS automatically for Gen2 or multi-cluster warehouses at creation. The QUERY_ACCELERATION_ELIGIBLE view and the SYSTEM$ESTIMATE_QUERY_ACCELERATION function help you pick a scale factor before you commit.

Storage-side strategies such as Automatic Clustering, search optimization and materialized views use serverless compute, which consumes credits before you can test the benefit, and they add storage cost for search optimization and materialized views. The more a table changes, the higher the maintenance cost. Snowflake suggests starting small, tracking initial and ongoing cost, and comparing the cost of running a query before and after the optimization. The estimate functions SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS and SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS help with this.

The same balance applies to warehouse settings. A larger size is cost-neutral only if the query speeds up proportionally. Auto-scale with the Economy policy trades some queuing for fewer running clusters. Resource monitors cap what warehouses can spend, but they do not cover serverless features.

Sources89

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A multi-cluster warehouse will make a single slow query or a big data load run faster.Why is that wrong?

   Extra clusters add concurrency. For slow-running queries and data loading, resizing the warehouse helps more.

   Covered in Scaling out: multi-cluster warehouses

2. 2.Moving to the next warehouse size always doubles what a query costs.Why is that wrong?

   The hourly rate doubles, but with per-second billing a query that runs twice as fast costs the same in total.

   Covered in Scaling up: warehouse size, query complexity and cost

3. 3.An account-level resource monitor caps all spending, including Snowpipe, automatic clustering and materialized view maintenance.Why is that wrong?

   Resource monitors only cover warehouses. Serverless features need a budget.

   Covered in Constraining warehouse credits with resource monitors