Snowflake warehouses active for fewer than 4 hours a day over 30 days

Many small warehouses each pay their own overhead

Every warehouse bills with a 60-second minimum 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 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 has one row per warehouse per hour with credits. Counting hours with compute credits gives a close proxy:

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. 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.