Snowflake Cost Optimization: Mastering Credits, Warehouses, and Storage

Snowflake's pay-as-you-go model is a double-edged sword: it offers elasticity and no upfront costs, but without careful management, your monthly bill can skyrocket. Credits are consumed by virtual warehouses (compute) and storage. Many teams unknowingly waste credits on oversized warehouses, idle compute, or inefficient queries. This tutorial dives deep into cost optimization strategies, from warehouse configuration to storage lifecycle management. You'll learn how to monitor usage, set resource monitors, and implement best practices that can cut costs by 30-50%. We'll also explore a real production incident where a misconfigured warehouse caused a $10k overnight spike. By the end, you'll be equipped to optimize Snowflake costs without sacrificing performance.

This tutorial aligns with the SnowPro Core certification objectives, helping you prepare for the SnowPro exam while building practical skills.

Understanding Snowflake's Pricing Model

Snowflake charges for compute (credits) and storage separately. Compute is consumed by virtual warehouses, which are clusters of compute resources. Storage costs are based on compressed data stored in Snowflake's cloud storage. Additionally, there are costs for cloud services (e.g., metadata operations) but these are usually minimal. Credits are billed per second, with a minimum of 60 seconds per warehouse start. The cost per credit varies by cloud provider and region. For example, on AWS US East, 1 credit = $2.00 (on-demand). Storage costs are typically $23 per terabyte per month (compressed). Understanding these components is the first step to optimization.

Right-Sizing Virtual Warehouses

Choosing the right warehouse size is critical. A common mistake is using a large warehouse for small queries, wasting credits. Use the following guidelines: For simple queries (e.g., point lookups), X-Small or Small is sufficient. For complex aggregations or joins on large tables, consider Medium or Large. For very large data loads or complex ETL, use X-Large or larger. Monitor warehouse load using the WAREHOUSE_LOAD_HISTORY view. If a warehouse is less than 50% utilized, consider downsizing. Also, use multi-cluster warehouses only when concurrency demands it; each additional cluster doubles credit consumption.

-- Check average load of a warehouse over the last 7 days
SELECT WAREHOUSE_NAME,
       AVG(AVG_RUNNING) AS avg_running,
       AVG(AVG_QUEUED) AS avg_queued
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE START_TIME > DATEADD('day', -7, CURRENT_TIMESTAMP)
GROUP BY 1
ORDER BY 2 DESC;

Auto-Suspend and Auto-Resume Best Practices

Auto-suspend stops a warehouse after a specified period of inactivity, saving credits. Auto-resume starts it automatically when a query is submitted. Set auto_suspend to 5-10 minutes for most warehouses. For development or ad-hoc warehouses, 1 minute is fine. For production warehouses that need to be always available, you might set it longer (e.g., 30 minutes) but be aware of the cost. Never set auto_suspend to NULL (never suspend) unless you have a specific reason. Also, ensure auto_resume is set to TRUE so that the warehouse starts on demand.

-- Set auto_suspend to 5 minutes and auto_resume to true
ALTER WAREHOUSE my_wh SET
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;

Using Resource Monitors to Cap Spending

Resource monitors allow you to set credit limits on warehouses or the entire account. You can define actions when a limit is reached: notify, suspend, or abort queries. Create separate monitors for different warehouses or groups. For example, set a monthly limit of 1000 credits for the BI warehouse, with an alert at 80% and suspend at 100%. This prevents surprise bills. Resource monitors can be set at the account level or warehouse level.

-- Create a resource monitor for a warehouse with 500 credit monthly limit
CREATE RESOURCE MONITOR my_monitor
  WITH CREDIT_QUOTA = 500
  FREQUENCY = MONTHLY
  START_TIMESTAMP = '2025-01-01 00:00:00'
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

-- Assign monitor to warehouse
ALTER WAREHOUSE my_wh SET RESOURCE_MONITOR = my_monitor;

Optimizing Storage Costs

Storage costs are based on compressed data. Snowflake automatically compresses data, but you can reduce storage by: 1) Dropping unused tables and schemas. 2) Reducing Time Travel retention (default 1 day, max 90 days). Each day of Time Travel adds to storage. 3) Using clustering keys to improve compression and query performance. 4) Archiving old data to cheaper storage (e.g., S3 Glacier). Monitor storage with TABLE_STORAGE_METRICS and STAGE_STORAGE_USAGE_HISTORY.

-- Find top 10 largest tables by active bytes
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
       ACTIVE_BYTES / 1024/1024/1024 AS ACTIVE_GB,
       TIME_TRAVEL_BYTES / 1024/1024/1024 AS TT_GB
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
ORDER BY ACTIVE_BYTES DESC
LIMIT 10;

Query Optimization to Reduce Compute

Inefficient queries waste credits. Use clustering keys to minimize data scanned. For large tables, define clustering keys on columns used in filters (e.g., date). Also use materialized views for pre-aggregated results. Avoid SELECT *; only select needed columns. Use query profiling (EXPLAIN) to identify full scans or large joins. Set up automatic clustering for tables that benefit from it. Monitor query performance with QUERY_HISTORY.

-- Add clustering key on date column
ALTER TABLE sales CLUSTER BY (sale_date);

-- Check clustering status
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'SALES' AND TABLE_SCHEMA = 'PUBLIC';

Monitoring and Alerting with Account Usage Views

Snowflake provides rich account usage views in the SNOWFLAKE database. Key views: WAREHOUSE_METERING_HISTORY (credit consumption), QUERY_HISTORY (query performance), TABLE_STORAGE_METRICS (storage), and RESOURCE_MONITORS (monitor status). Set up regular queries to generate cost reports. You can also use Snowsight dashboards to visualize usage. Consider setting up alerts for unusual spikes using tasks or external tools.

-- Daily credit usage by warehouse for the last 7 days
SELECT DATE(START_TIME) AS day,
       WAREHOUSE_NAME,
       SUM(CREDITS_USED) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME > DATEADD('day', -7, CURRENT_TIMESTAMP)
GROUP BY 1, 2
ORDER BY 1, 2;