# Snowflake Warehouse Idle

> Flags Snowflake warehouses that consumed credits across 14 days with no queries, and prices suspension at the full warehouse cost.

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

---

## A resumed warehouse bills whether or not queries arrive

Snowflake meters a virtual warehouse for the time it is running, not for the queries it serves.
[Warehouse billing](https://docs.snowflake.com/en/user-guide/warehouses-overview) is per second
with a 60-second minimum each time the warehouse starts, and credit burn doubles with each size
step, from 1 credit an hour for an X-Small Gen1 warehouse upward. An X-Small left running around
the clock therefore uses 24 credits a day, or 336 across two weeks, and a Medium four times that.

Auto-suspend normally stops this. It is on by default, but
[setting `AUTO_SUSPEND` to 0 or NULL](https://docs.snowflake.com/en/sql-reference/sql/create-warehouse)
means the warehouse never suspends, which Snowflake itself warns can consume significant credits.
A warehouse held open that way, or resumed repeatedly by a tool that then sends nothing, is the
case this rule catches.

## Checking metering against query history yourself

Two ACCOUNT_USAGE views answer the question. This query returns warehouses with compute credits
in the last 14 days and no queries attributed to them:

```sql
WITH burn AS (
  SELECT warehouse_name, SUM(credits_used_compute) AS compute_credits
  FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
  WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
  HAVING SUM(credits_used_compute) > 0
), work AS (
  SELECT warehouse_name, COUNT(*) AS queries
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
    AND warehouse_name IS NOT NULL
  GROUP BY warehouse_name
)
SELECT b.warehouse_name, b.compute_credits
FROM burn b
LEFT JOIN work w ON w.warehouse_name = b.warehouse_name
WHERE COALESCE(w.queries, 0) = 0
ORDER BY b.compute_credits DESC;
```

[WAREHOUSE_METERING_HISTORY](https://docs.snowflake.com/en/sql-reference/account-usage/warehouse_metering_history)
can lag by up to 3 hours and [QUERY_HISTORY](https://docs.snowflake.com/en/sql-reference/account-usage/query_history)
by up to 45 minutes, so the most recent hours are always incomplete. A role needs the
`USAGE_VIEWER` and `GOVERNANCE_VIEWER` SNOWFLAKE database roles, or imported privileges on the
SNOWFLAKE database, to read them. Then run `SHOW WAREHOUSES LIKE '<name>';` and look at the
`auto_suspend` column for each result.

## Credits above zero and queries at zero across 14 days

The rule reads two hourly series for the warehouse: credits consumed and query count. Summed over
the trailing 14 days, credits must be greater than zero and queries must be exactly zero. A single
query anywhere in the window clears the warehouse. There is no size, edition or tag condition.

## Warehouses that are not flagged as idle

If either series is missing or has no datapoints in the window, the rule does not fire: a
Snowflake finding is never raised on absent evidence. A warehouse that ran real queries, even a
handful, is left to the utilisation rules such as
<a href="https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-under-utilized">Snowflake Warehouse Under-Utilized</a>.
Loose auto-suspend on its own, with no wasted credits yet, is covered by
<a href="https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-auto-suspend-not-tuned">Snowflake Warehouse Auto-Suspend Not Tuned</a>.

## The saving is the warehouse's whole monthly bill

Suspending an idle warehouse stops all of its compute, so the saving equals the warehouse's
monthly cost as ZopNight prices Snowflake: credits multiplied by the credit rate for the account's
edition.

```text
saving = monthly warehouse cost (credits x edition credit rate)
cost after suspend = 0 until the next real query resumes it
```

When no price is available for the warehouse, the saving would be zero and the finding is dropped
instead of being shown as a $0 recommendation.

## Suspending the warehouse and stopping a repeat

1. Suspend it: `ALTER WAREHOUSE idle_wh SUSPEND;` Running statements finish first, then the compute
   is released. The role needs OPERATE or MODIFY on the warehouse.
2. Leave `AUTO_RESUME` at its default of TRUE so a genuine query wakes the warehouse, paying only
   the 60-second minimum.
3. Put a limit on idle time: `ALTER WAREHOUSE idle_wh SET AUTO_SUSPEND = 60;`
4. Find whatever resumed it (a BI connection, an orchestrator step, a manual `RESUME`) and fix or
   remove it.

**Warning**
Dropping the warehouse instead of suspending it breaks any task, user default or tool
that still names it. Suspend first and wait a cycle before deciding to drop.