# Snowflake Warehouse Over-Provisioned Max Clusters

> Flags multi-cluster Snowflake warehouses with MAX_CLUSTER_COUNT above 2 that never averaged 2 running clusters in 14 days.

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

---

## The ceiling sets the worst-case hourly bill

A [multi-cluster warehouse](https://docs.snowflake.com/en/user-guide/warehouses-multicluster), 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

```sql
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](https://docs.snowflake.com/en/sql-reference/account-usage/query_history) records the `CLUSTER_NUMBER` each
statement ran on, and reading it takes the `GOVERNANCE_VIEWER` SNOWFLAKE database role:

```sql
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](https://zop.dev/integrations/snowflake/recommendations/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](https://docs.snowflake.com/en/sql-reference/account-usage/warehouse_load_history)
   (`AVG_QUEUED_LOAD`) for a week. Persistent queuing means the ceiling is now too low.