Snowflake cost and warehouse efficiency

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

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

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 <object-type>:<stable-object-name>:<issue-slug>, 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

• 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

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.