Optimization: Snowflake warehouse design - sizing, auto-suspend, multi-cluster and query acceleration

#Billing in one paragraph

A running warehouse bills credits per second (with a 60-second minimum each time it starts) at a rate that doubles with each size step. Idle-but-running time costs the same as busy time. So the main levers are: run less idle time, use the right size, and don't run one oversized warehouse for every kind of work.

#1. See where credits go

SELECT warehouse_name, ROUND(SUM(credits_used), 1) AS credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time > DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 2 DESC;

-- idle share: hours billed vs hours with queries
SELECT warehouse_name,
       COUNT(DISTINCT DATE_TRUNC('hour', start_time)) AS hours_with_queries
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(day, -30, CURRENT_TIMESTAMP()) AND warehouse_name IS NOT NULL
GROUP BY 1;

#2. One warehouse per workload type

Separation lets you size and attribute cost per workload, and stops a heavy job slowing dashboards.

#3. Size by experiment

Run a representative job on two or three sizes:

When doubling the size roughly halves duration, the cost is about the same and the job finishes faster. When it doesn't, the bigger size is wasted. Spilling to remote storage (bytes_spilled_to_remote_storage in query history) is a sign the size is too small.

#4. Auto-suspend and auto-resume

ALTER WAREHOUSE wh_bi SET AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
ALTER WAREHOUSE wh_load SET AUTO_SUSPEND = 60;

60 seconds is a good default. Very short values on BI warehouses can drop the local disk cache too often; measure query times before going lower. Warehouses with AUTO_SUSPEND = 0 or NULL never stop. Find them:

SHOW WAREHOUSES;
SELECT "name", "auto_suspend" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "auto_suspend" IS NULL OR "auto_suspend" = 0 OR "auto_suspend" > 600;

#5. Multi-cluster for concurrency

For BI peaks with queuing (queued_overload_time), add clusters instead of a bigger size:

ALTER WAREHOUSE wh_bi SET MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 4 SCALING_POLICY = 'STANDARD';

ECONOMY favours cost (waits longer before adding clusters); STANDARD favours latency. Multi-cluster requires the appropriate Snowflake edition.

#6. Query acceleration for outliers

When most queries are fine but a few large scans dominate, Query Acceleration Service can offload parts of eligible queries to shared compute:

SELECT query_id, warehouse_name, eligible_query_acceleration_time
FROM snowflake.account_usage.query_acceleration_eligible
WHERE start_time > DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY eligible_query_acceleration_time DESC LIMIT 20;

ALTER WAREHOUSE wh_adhoc SET ENABLE_QUERY_ACCELERATION = TRUE QUERY_ACCELERATION_MAX_SCALE_FACTOR = 4;

Check eligibility first (the QUERY_ACCELERATION_ELIGIBLE view or SYSTEM$ESTIMATE_QUERY_ACCELERATION).

#7. Guardrails

CREATE OR REPLACE RESOURCE MONITOR rm_adhoc WITH CREDIT_QUOTA = 200 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 75 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND ON 110 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE wh_adhoc SET RESOURCE_MONITOR = rm_adhoc;
ALTER WAREHOUSE wh_adhoc SET STATEMENT_TIMEOUT_IN_SECONDS = 1800;

Also: limit who can create or resize warehouses (a role owning warehouses, with requests through the platform team).

Tip

Fix the expensive queries before resizing. A missing filter on a huge table makes every size look too small. Check the top queries by execution_time × warehouse size first.

#Checklist

• Credits per warehouse and idle time measured
• Separate warehouses per workload type
• Sizes chosen by experiment, spilling checked
• Auto-suspend ~60 s everywhere
• Multi-cluster for concurrency where queuing occurs
• Query acceleration for eligible outliers
• Resource monitors and statement timeouts