TL;DR — Direct Answer
Most Snowflake bills hide 20–40% recoverable waste in four places: oversized warehouses idling on auto-suspend timers, clustering keys on tables that don't need them, serverless features (Search Optimization, QAS, materialized views) quietly metering, and per-query waste from full scans your role hierarchy lets anyone run. The audit below is the checklist we run in FinOps engagements — every item includes where to look in ACCOUNT_USAGE. Run it before your renewal negotiation, not after.
1 — Warehouse sizing and suspension (usually the biggest line)
- Auto-suspend over 60 seconds: the default habit of 5–10 minutes means you pay minutes of idle after every burst. For BI warehouses, 60s; for ETL, suspend immediately after the job. Check:
WAREHOUSE_METERING_HISTORYcredits vsQUERY_HISTORYactive seconds — the gap is pure idle spend. - One-size-fits-all sizing: a Large running dashboard queries that profile at <2s on a Small is paying 4x per query. Split workloads by profile: XS/S for BI, sized-up only for transform windows.
- Multi-cluster set to maximize: max cluster counts sized for Black Friday running all year. Set economy scaling policy and realistic maximums.
- Per-team warehouses without quotas: resource monitors with suspend thresholds on every non-prod warehouse. No monitor = unbounded experiment budget.
2 — Clustering key antipatterns
- Clustering small tables: under ~1TB, natural micro-partition pruning usually suffices; automatic reclustering on a churning small table burns credits to optimize nothing. Check
AUTOMATIC_CLUSTERING_HISTORYfor tables where reclustering credits exceed any query saving. - Clustering on high-cardinality columns (UUIDs): every insert scatters across partitions, reclustering runs forever. Cluster on the columns queries actually filter by — usually date plus one low-cardinality dimension.
- Clustering tables that are full-scanned anyway: if the workload aggregates everything daily, pruning buys nothing. Verify with
QUERY_HISTORYpartition-scanned ratios before paying for order.
3 — The quiet serverless meters
Each is valuable when deliberate, expensive when forgotten: Search Optimization Service (point-lookup acceleration — audit which tables still need it), materialized views (maintenance credits on every base-table change; a high-churn base table can cost more in maintenance than the MV saves), Query Acceleration Service (check it accelerates real workloads, not masking bad sizing), and tasks/Snowpipe error-retrying in loops. Reconcile all of them monthly in METERING_DAILY_HISTORY by service type.
4 — Query-level waste
- SELECT * from wide tables in BI tools scanning columns nobody renders.
- Spilling: queries spilling to local/remote storage (see
QUERY_HISTORYbytes_spilled) signal wrong warehouse size or missing pre-aggregation — both fixable, both billed. - Repeated identical queries defeating the result cache via non-deterministic functions or session settings — dashboards re-paying for the same answer every refresh.
- Dev queries on prod-sized warehouses: role-based default warehouses solve this in an afternoon.
What a disciplined audit recovers
Across our FinOps engagements the pattern is consistent: idle-time and sizing fixes recover 10–20% in week one (config changes, zero risk); clustering and serverless cleanup another 5–10%; query and workload hygiene a further 5–10% over a quarter. Our flagship warehouse-economics engagement (Redshift→BigQuery, same discipline different platform) cut total cost 62% — the methodology transfers; the meters differ. Run the audit quarterly: Snowflake waste regrows like a hedge.