# Snowflake cost and warehouse efficiency > Idle and oversized warehouses, auto-suspend settings, expensive query patterns, and storage that nobody meant to keep. Source: https://triagic.com/checkups/snowflake-cost ## Scope [#scope] A read-only cost review of one Snowflake account: where the credits go, which queries waste compute, and which storage is being paid for by accident. Read from `SNOWFLAKE.ACCOUNT_USAGE` (365-day retention; latency runs from 45 minutes to 3 hours depending on the view — about 45 minutes for `QUERY_HISTORY`, up to 90 for `TABLE_STORAGE_METRICS`, up to 180 for `WAREHOUSE_METERING_HISTORY` and `ACCESS_HISTORY`) and fall back to `INFORMATION_SCHEMA` table functions only when a view is not granted. Propose changes; never run `ALTER`, `SUSPEND`, `DROP`, or anything else that mutates the account. ## Procedure [#procedure] 1. **Establish the bill before explaining it.** Query `ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY` for the last 30 days grouped by `WAREHOUSE_NAME`: `SUM(CREDITS_USED_COMPUTE)` and `SUM(CREDITS_USED_CLOUD_SERVICES)`, ranked descending. Pull the preceding 30 days the same way for a month-over-month delta. The top three warehouses normally carry most of the bill, and that ranking decides where the remaining steps spend their effort. Cloud-services credits above \~10% of compute credits are themselves a finding — that usually means metadata-heavy work such as thousands of tiny `SHOW`/`INFORMATION_SCHEMA` calls or single-row DML. 2. **Idle burn.** For each warehouse, join metered hours against query activity in the same hour from `ACCOUNT_USAGE.QUERY_HISTORY`. Any hour with credits and no queries is being paid for by the auto-suspend gap. Read each warehouse's `AUTO_SUSPEND` and `AUTO_RESUME` (`SHOW WAREHOUSES`, or `ACCOUNT_USAGE.WAREHOUSES`) and compare it against the observed distribution of gaps between consecutive queries on that warehouse. A 600-second auto-suspend on a warehouse whose median inter-query gap is 20 minutes pays 10 idle minutes on every gap; a warehouse with `AUTO_SUSPEND` unset or set to `NULL` never suspends at all and should be treated as the worst case of this finding. 3. **Suspend/resume churn — the counter-finding to step 2.** Count `RESUME_WAREHOUSE` events per warehouse per day in `ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY`. Every resume bills a 60-second minimum, so a 60-second auto-suspend on a warehouse that resumes hundreds of times a day can cost more than a longer idle window would. Reconcile steps 2 and 3 before reporting either: recommend the auto-suspend value that minimises (idle seconds x rate) + (resumes x 60s), and say which of the two effects dominates for that warehouse. 4. **Sizing, in both directions.** From `QUERY_HISTORY` per warehouse, look at `BYTES_SPILLED_TO_LOCAL_STORAGE` and `BYTES_SPILLED_TO_REMOTE_STORAGE`. Remote spill is the strong signal that a warehouse is undersized for its workload — it is spilling past local SSD to object storage and every affected query pays for it in wall time. Local spill is the softer version of the same thing. In the other direction, a warehouse whose queries never spill, mostly finish in under a second, and show near-zero `QUEUED_OVERLOAD_TIME` is oversized: each size step doubles the credit rate, so halving it is the single largest lever in this checkup. Sustained `QUEUED_OVERLOAD_TIME` means concurrency pressure, not size — raise `MAX_CLUSTER_COUNT` on a multi-cluster warehouse instead of the size, and say so explicitly so nobody "fixes" queueing by doubling the rate. 5. **Expensive query patterns.** Over the same 30 days, rank `QUERY_HISTORY` by `SUM(TOTAL_ELAPSED_TIME)` and separately by `SUM(BYTES_SCANNED)`, grouped by `QUERY_PARAMETERIZED_HASH` (group by the first \~200 characters of `QUERY_TEXT` if that column is not available). For each top pattern check `PARTITIONS_SCANNED / PARTITIONS_TOTAL`: a ratio near 1.0 against a large table is a full scan, which usually means a missing or wrong cluster key, or a predicate that cannot prune (a function wrapped around the clustered column, an implicit cast, `OR` across unrelated columns). Separately, look for dashboard patterns that never reuse the **result** cache: a result-cache hit scans nothing at all — `BYTES_SCANNED = 0`, no warehouse attributed, near-zero elapsed time — so the signal is repeated executions of one `QUERY_PARAMETERIZED_HASH` where every run *does* scan and *does* bill a warehouse. Those normally differ only by an embedded `CURRENT_TIMESTAMP` literal or a non-deterministic function, either of which disables the result cache. Do not read `PERCENTAGE_SCANNED_FROM_CACHE` as that signal: it is the local-SSD data cache on the warehouse's own compute, so it measures warehouse warmth, not result reuse. Read it alongside step 2 instead — a warehouse with a consistently low percentage is re-fetching from remote storage after every suspend, which is the real cost of an aggressive `AUTO_SUSPEND` and belongs in the reconciliation there. Note which role or user submits each pattern — the fix has an owner. 6. **Storage.** From `ACCOUNT_USAGE.TABLE_STORAGE_METRICS`, compare `ACTIVE_BYTES` against `TIME_TRAVEL_BYTES` and `FAILSAFE_BYTES` per table. A high-churn table whose time-travel plus fail-safe bytes exceed its active bytes is paying several times over for data it does not need; check its `DATA_RETENTION_TIME_IN_DAYS` and whether a transient table (no fail-safe) would have been the right choice. Flag rows with `DELETED = TRUE` still holding fail-safe bytes, and tables growing steadily with no reads against them in `ACCOUNT_USAGE.ACCESS_HISTORY` for 90 days — that view is Enterprise Edition and above, so if it is missing, report an edition gap, not a coverage failure, and skip the no-reads test rather than treating silence as "unused". Check whether any large table is only ever queried with a narrow date predicate but is unclustered. 7. **Attribute and hedge honestly.** Every number in the report names the view and the window it came from. Where `ACCOUNT_USAGE` latency could matter (anything touching the last hour), say the window is not settled yet rather than present it as fact. If a view is not granted to the connected role, report that as a gap in coverage instead of silently narrowing the review. ## Finding keys [#finding-keys] A finding `key` identifies the *underlying issue*, not this run. The same idle warehouse must emit the same key next month so the ledger records "still open" instead of opening a second row — and so a resolved finding that comes back is detected as a regression. Use `::`, with the object name exactly as Snowflake spells it and a lowercase issue slug. Never put a date, a credit figure, a percentage, or a run id in the key; those belong in `detail` and `evidence`, which are refreshed on every run. * `warehouse:REPORTING_WH:idle` * `warehouse:ETL_WH:oversized` * `warehouse:BI_WH:suspend-thrash` * `query:a91f3c2e:full-scan` — the `QUERY_PARAMETERIZED_HASH`, never the text * `table:ANALYTICS.PUBLIC.EVENTS:time-travel-bloat` ## Severity rubric [#severity-rubric] * **critical** — one warehouse or query pattern burning more than 25% of the account's monthly credits with no owner able to justify it; or storage growth on a trajectory that doubles the storage bill within a quarter. * **high** — more than 10% of monthly credits recoverable by a single setting change (`AUTO_SUSPEND`, warehouse size, `DATA_RETENTION_TIME_IN_DAYS`); or remote spill on a warehouse that serves production or customer-facing workloads. * **medium** — a clear inefficiency worth a few percent of the bill: local spill, a full-scan pattern on a large table, an auto-suspend materially longer than the observed idle gaps, cloud-services credits above 10% of compute. * **low** — small recoverable waste, or a bad default with little current cost — a rarely-used warehouse with no auto-suspend, a transient-shaped table created as permanent. * **info** — an observation worth recording with no action attached: workload moved between warehouses, a large new table appeared, month-over-month spend flat. ## Output guidance [#output-guidance] Open with a one-paragraph executive summary in plain words: the 30-day credit total, the month-over-month direction, and the single largest recoverable item with its rough credit value. Then one section per area — Warehouses, Query patterns, Storage — each leading with the numbers and naming the `ACCOUNT_USAGE` view behind them. Close with a recommendations table (Recommendation | Object | Estimated monthly credits saved | Risk | Owner), most valuable first, and state plainly where an estimate is a rough order of magnitude rather than a computed figure. Say "nothing worth changing" outright if that is what the data shows.