Multi-cluster Snowflake warehouses whose max cluster count sits far above observed peak

The ceiling sets the worst-case hourly bill

A multi-cluster warehouse, an Enterprise Edition feature, runs between a minimum and a maximum number of clusters of the same size. In Auto-scale mode Snowflake starts clusters as load rises, up to the maximum, and shuts them down as it falls. The maximum credits per hour are the size’s rate multiplied by the maximum cluster count: Snowflake’s example is a Medium warehouse with 3 clusters topping out at 12 credits an hour.

Unused headroom costs nothing on its own. The risk is that a runaway dashboard or a flood of retries can use it, and Snowflake’s advice is to start small (for example maximum 2 or 3) and raise the limit as you learn the workload.

Comparing the setting with what actually ran

SHOW WAREHOUSES
  ->> SELECT "name", "size", "scaling_policy", "min_cluster_count", "max_cluster_count"
      FROM $1
      WHERE "max_cluster_count" > 2;

Then see how many clusters served queries over the same two weeks. QUERY_HISTORY records the CLUSTER_NUMBER each statement ran on, and reading it takes the GOVERNANCE_VIEWER SNOWFLAKE database role:

SELECT warehouse_name, MAX(cluster_number) AS highest_cluster_used
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
GROUP BY warehouse_name;

Over 2 allowed, never averaging 2 in use

ZopNight needs the warehouse’s maximum cluster count, and it must be greater than 2. It then reads the average number of running clusters over the last 14 days and takes the highest value in that window. The finding fires when even that peak is below 2, meaning the warehouse never averaged two clusters over any measured period.

When the maximum is left alone

Warehouses with a maximum of 1 or 2 are never flagged, and neither is a warehouse whose maximum could not be read. With no cluster-count series or no datapoints in the 14 days, nothing is raised. A single hour where the average reached 2 or more is enough to clear the warehouse.

Governance, not a computed saving

This finding carries no dollar figure, because a high maximum does not cost anything until it is used. What it prevents is surprise spend: each extra cluster that starts bills at the full rate of the warehouse size. The opposite problem, clusters held running all the time, is Snowflake Warehouse Over-Provisioned Min Clusters.

Lowering the maximum

1. Check for known peaks outside the 14-day window, such as quarter-end jobs.
2. Set the ceiling near what the workload has actually used: ALTER WAREHOUSE bi_wh SET MAX_CLUSTER_COUNT = 2; In Auto-scale mode the maximum must stay greater than the minimum.
3. Watch queued load in WAREHOUSE_LOAD_HISTORY (AVG_QUEUED_LOAD) for a week. Persistent queuing means the ceiling is now too low.