Multi-cluster Snowflake warehouses holding extra clusters that load never needed

A cluster floor runs whether or not queries need it

A multi-cluster warehouse’s minimum is the number of clusters Snowflake keeps running whenever the warehouse is up. When minimum and maximum are equal and above 1, the warehouse is in what Snowflake calls Maximized mode: all clusters start with the warehouse, which suits static, predictable concurrency. In Auto-scale mode, a minimum of 1 lets Snowflake add clusters only as load rises.

Each cluster bills at the rate for the warehouse size. A Large warehouse is 8 credits an hour on Gen1, so a minimum of 3 means 24 credits an hour from the moment it resumes, even if one cluster would have handled the traffic.

Finding raised minimums

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

SHOW WAREHOUSES reports min_cluster_count and max_cluster_count, always 1 for single-cluster warehouses. To see cluster activity over time, WAREHOUSE_EVENTS_HISTORY records events such as RESUME_CLUSTER with the CLUSTER_NUMBER they apply to.

Under 1.5 clusters at the busiest point

A warehouse whose minimum is already 1 is skipped. For the rest, ZopNight reads the average number of running clusters over the last 14 days and takes its highest value. The finding fires when that peak is below 1.5, which says the warehouse spent the whole fortnight effectively running on one cluster.

Warehouses it leaves as they are

A single measured period at 1.5 average clusters or more clears the warehouse, since that suggests concurrency the floor may be serving. If the cluster-count series is missing or has no datapoints, no finding is raised. The rule does not look at queueing, so it cannot tell you whether a lower floor would add wait time at warehouse start.

Why the saving is not priced

This is a governance finding with no dollar figure. How much a lower floor saves depends on how long the warehouse runs each day and how often Auto-scale would add clusters back, which ZopNight does not model here. The spend at issue is the extra clusters’ hourly rate for every hour the warehouse is running.

Dropping the floor to one cluster

1. Confirm nobody set Maximized mode deliberately for guaranteed capacity.
2. Lower it: ALTER WAREHOUSE bi_wh SET MIN_CLUSTER_COUNT = 1; The warehouse then runs in Auto-scale mode if its maximum is higher.
3. Keep the default SCALING_POLICY = STANDARD, which favours starting clusters over queuing, or use ECONOMY, which keeps running clusters fully loaded and may let queries queue.
4. Check AVG_QUEUED_LOAD in WAREHOUSE_LOAD_HISTORY after a week.

The mirror case, a ceiling set far above use, is Snowflake Warehouse Over-Provisioned Max Clusters.