# Snowflake Warehouse Under-Utilized

> Flags Snowflake warehouses averaging under 4 active hours a day over 30 days as candidates to consolidate into a shared warehouse.

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

---

## Many small warehouses each pay their own overhead

Every warehouse bills with a [60-second minimum](https://docs.snowflake.com/en/user-guide/warehouses-overview) each time it
starts, plus whatever idle time passes before auto-suspend. An organization with a warehouse per
team or per tool pays those fixed costs once per warehouse, and each one keeps its own cache that
goes cold every time it suspends. Snowflake's
[warehouse guidance](https://docs.snowflake.com/en/user-guide/warehouses-considerations) is to match the warehouse to the
size and complexity of the queries it runs.

A warehouse that is up for only an hour or two a day is often a candidate to share with another
workload of a similar size, where the combined traffic keeps one warehouse warm instead of several
repeatedly resuming.

## Counting active hours yourself

[WAREHOUSE_METERING_HISTORY](https://docs.snowflake.com/en/sql-reference/account-usage/warehouse_metering_history) has one row
per warehouse per hour with credits. Counting hours with compute credits gives a close proxy:

```sql
SELECT warehouse_name,
       COUNT(DISTINCT start_time)            AS active_hours_30d,
       ROUND(COUNT(DISTINCT start_time) / 30, 1) AS active_hours_per_day,
       ROUND(SUM(credits_used_compute), 1)   AS compute_credits_30d
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
  AND credits_used_compute > 0
GROUP BY warehouse_name
ORDER BY active_hours_per_day;
```

An hour with any credits counts as a whole active hour here, so this overstates activity slightly
for bursty warehouses. The view needs the `USAGE_VIEWER` role and lags by up to 3 hours.

## Under 4 active hours a day, averaged over 30 days

ZopNight reads a daily active-hours series for the warehouse and averages it over the last 30 days.
The finding fires when that average is below 4 hours. There is no condition on size, cost or query
count, so both a lightly used X-Small and a lightly used 2X-Large qualify.

## Warehouses it does not raise

A warehouse without the active-hours series, or with no datapoints in the 30 days, is not flagged.
A warehouse that ran credits with no queries at all is the stronger case handled by
[Snowflake Warehouse Idle](https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-idle). The rule cannot see why a warehouse is
separate, so isolation chosen for security or chargeback reasons will still be flagged.

## Why consolidation is not priced

The finding carries no dollar figure. Merging workloads is a manual move whose saving depends on
how the combined traffic overlaps, which ZopNight does not simulate. The cost in play is each
warehouse's resume minimums and idle tails, plus the admin overhead of many near-identical
warehouses.

## Consolidating a lightly used warehouse

1. List what runs on it: users, roles, tasks and tools whose default warehouse it is.
2. Pick a shared warehouse of a similar size with spare capacity.
3. Point the workloads at it, for example `ALTER USER report_svc SET DEFAULT_WAREHOUSE = shared_bi_wh;`
   and grant USAGE on the shared warehouse to the roles involved.
4. Suspend the old one with `ALTER WAREHOUSE old_wh SUSPEND;` and drop it only after a full cycle of
   jobs has run without it.