Snowflake Optimization Playbook: Warehouse Sizing & Spill Diagnostics

[!NOTE] Snowflake's Query Profile reveals the exact bottlenecks of every execution step. Diagnosing and eliminating Disk Spill is the single fastest way to accelerate slow warehouse queries.

1. Local Disk Spill vs Remote Disk Spill

• Local Disk Spill: Occurs when a query's working memory (hash joins, sorting) exceeds the warehouse's RAM. Data spills to local SSD storage. Query slows down moderately.
• Remote Disk Spill: Occurs when working memory exceeds both RAM AND local SSD. Data spills across the network to cloud storage (S3/Blob). Performance degrades catastrophically (10x to 50x slowdown).
• Remediation:
  1. Scale up the warehouse size (e.g. from Large to X-Large) to provide more RAM.
  2. Optimize SQL: eliminate cartesian joins, prune unneeded columns, and avoid high-cardinality ORDER BY.

Staff Architecture: Snowflake FinOps — AUTO_SUSPEND, Remote Spill Audits & Resource Monitors

• Killing the 60-Second Minimum Billing Trap: Snowflake charges a 60-second minimum every time a warehouse resumes, then per-second thereafter. Never set AUTO_SUSPEND = 1 on a busy BI warehouse receiving queries every 45 seconds (you pay 60s minimum repeatedly!), nor AUTO_SUSPEND = 3600 on a sporadic ETL warehouse. Rule of thumb: AUTO_SUSPEND = 60 (ETL), 120–300 (BI to preserve SSD Cache!), with AUTO_RESUME = TRUE.
• Spotting Remote Disk Spill (BYTES_SPILLED_TO_REMOTE_STORAGE): Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY for BYTES_SPILLED_TO_REMOTE_STORAGE > 0. Local SSD spill slows queries slightly; Remote Cloud Storage Spill destroys latency 5x–15x and means either the query needs pruning/pre-aggregation or a 1-step larger warehouse (M -> L) for that ETL task.
• Resource Monitors & Statement Timeouts: Attach Account & Warehouse Resource Monitors (NOTIFY AT 75%, SUSPEND AT 100%) plus STATEMENT_TIMEOUT_IN_SECONDS = 1800 on dev/analyst roles so a forgotten Cartesian join cannot burn weekend budgets.