Warehouses that burned credits for 14 days without serving a single query

Credits with no queries behind them

Warehouse spend normally tracks work: queries arrive, credits burn, auto-suspend ends the meter. The failure mode this rule hunts is the meter running with no work at all: a warehouse resumed by a decommissioned pipeline’s last gasp, or pinned awake by its own configuration, billing hour after hour into a void. An X-Small in that state quietly consumes about 24 credits a day; at 14 days, the detection window, that is roughly 336 credits for nothing, and larger sizes scale the waste by their credit multiplier.

Two sums, one verdict

Over the 14-day window, the sum of snowflake.warehouse.credits_used_per_hour must be greater than 0 while the sum of snowflake.warehouse.query_count equals exactly 0. One attributed query anywhere in the window clears the warehouse. Both series come from ACCOUNT_USAGE, metering from WAREHOUSE_METERING_HISTORY and counts from QUERY_HISTORY with system-internal rows (those carrying no warehouse name) filtered out so cloud-services housekeeping never masks a genuinely idle warehouse.

Reproduce the cross-check

SELECT m.warehouse_name,
       SUM(m.credits_used) AS credits_14d
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY m
WHERE m.start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
GROUP BY 1
HAVING SUM(m.credits_used) > 0
   AND NOT EXISTS (
     SELECT 1 FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
     WHERE q.warehouse_name = m.warehouse_name
       AND q.start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
   );

Mind the lag: QUERY_HISTORY trails by up to 45 minutes and metering by up to 3 hours, so a warehouse that just started real work can look idle for a beat. The 14-day window makes that transient noise, not a false positive.

How a warehouse gets stuck hot

Auto-suspend usually prevents this state, which is why the rule usually finds nothing. The exceptions: AUTO_SUSPEND disabled or set to hours, and multi-cluster warehouses with MIN_CLUSTER_COUNT pinned above 1, which hold clusters resumed regardless of demand. Both configurations have their own dedicated ZopNight rules; this one catches the burning consequence directly.

Suspension is free to reverse

ALTER WAREHOUSE forgotten_wh SUSPEND;

Nothing is deleted and no state is lost beyond the local cache. With AUTO_RESUME on, the next real query, if one ever comes, wakes the warehouse in seconds, paying only the 60-second resume minimum. The saving is the entire remaining idle burn.