# Snowflake Warehouse Over-Provisioned Min Clusters

> Flags multi-cluster Snowflake warehouses with a minimum cluster count above 1 whose average cluster use stayed under 1.5 for 14 days.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-over-provisioned-min-clusters

---

## 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](https://docs.snowflake.com/en/user-guide/warehouses-multicluster): 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

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

[SHOW WAREHOUSES](https://docs.snowflake.com/en/sql-reference/sql/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](https://docs.snowflake.com/en/sql-reference/account-usage/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](https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-over-provisioned-max-clusters).