# Snowflake Warehouse Oversized

> Flags Snowflake warehouses with p95 load under 30% and negligible remote spill over 14 days; saving is half the monthly cost.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-oversized

---

## One size down halves the hourly rate

Snowflake [warehouse sizes](https://docs.snowflake.com/en/user-guide/warehouses-overview) double in credits per hour at each
step: 1 for X-Small on Gen1, then 2, 4, 8, 16 and upward. A warehouse that never works hard at its
current size is paying for compute its queries do not use. Snowflake notes that larger is not
necessarily faster for small, basic queries, and its
[sizing guidance](https://docs.snowflake.com/en/user-guide/warehouses-considerations) is to match size to the complexity of
the workload and to try the same queries on several sizes.

The catch is memory: a smaller warehouse has less of it, and queries that start spilling to remote
storage slow down sharply. That is why this check looks at spill as well as load.

## Looking at load and spill together

[WAREHOUSE_LOAD_HISTORY](https://docs.snowflake.com/en/sql-reference/account-usage/warehouse_load_history) gives query load in
5-minute intervals, and QUERY_HISTORY gives spill:

```sql
WITH load AS (
  SELECT warehouse_name,
         APPROX_PERCENTILE(avg_running, 0.95) AS p95_running
  FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
  WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
), spill AS (
  SELECT warehouse_name,
         MAX(bytes_spilled_to_remote_storage) AS max_remote_spill
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
)
SELECT l.warehouse_name, l.p95_running, COALESCE(s.max_remote_spill, 0) AS max_remote_spill
FROM load l LEFT JOIN spill s ON s.warehouse_name = l.warehouse_name
ORDER BY l.p95_running;
```

`AVG_RUNNING` is Snowflake's query load value for executing queries. It will not match ZopNight's
load percentage exactly, so treat it as a relative signal. The load view needs `USAGE_VIEWER`; QUERY_HISTORY needs `GOVERNANCE_VIEWER`.

## Low load and almost no spill over 14 days

ZopNight reads the warehouse's load percentage and its remote spill over the last 14 days and takes
the 95th percentile of each. The finding fires when p95 load is below 30% and p95 remote spill is
below 1 MiB (1,048,576 bytes). Both must hold: a lightly loaded warehouse that spills is not a
downsizing candidate.

## Warehouses kept at their size

If either series is missing or empty, no finding is raised. The finding is also dropped when
ZopNight has no monthly cost for the warehouse, so there is never a zero-dollar resize suggestion.
Warehouses that burn credits with no queries at all are handled by
[Snowflake Warehouse Idle](https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-idle) instead, and heavy spillers by
[Snowflake Warehouse Spilling to Remote Storage](https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-spilling-to-remote-storage).

## Half the monthly cost

Because each size step doubles the credit rate, one step down halves it for the same running time.
ZopNight takes the warehouse's monthly cost, priced from its credits at the edition's credit rate,
and counts half of it:

```text
saving = monthly warehouse cost x 0.5
```

If the smaller warehouse runs queries for longer, part of that saving is given back, which is why the
fix below includes a check.

## Stepping down one size

1. Resize: `ALTER WAREHOUSE bi_wh SET WAREHOUSE_SIZE = 'SMALL';` Queries already running keep their
   current resources; queued and new queries use the new size.
2. Watch spill and queuing (`AVG_QUEUED_LOAD`) for a few days.
3. If remote spill appears or queuing grows, go back up one size.