Cost Optimization: Managing Credits, Warehouses, and Storage

Snowflake's pay-as-you-go model is a double-edged sword. While it offers elasticity and near-zero maintenance, runaway costs are a common pain point for teams. A single misconfigured warehouse can burn thousands of dollars per month. This tutorial dives deep into the three pillars of Snowflake cost: credits, warehouses, and storage. You'll learn how to monitor usage, set guardrails, and optimize each component. By the end, you'll have a production-ready cost optimization playbook that can reduce your bill by 30-50% without sacrificing performance.

Understanding Snowflake's Pricing Model

Snowflake charges for compute (credits), storage (per TB per month), and cloud services (metadata, etc.). Compute credits are consumed by virtual warehouses (clusters) while they are running. Storage costs are for compressed data, including time travel and fail-safe. Cloud services are usually <10% of total. The key to optimization is minimizing active warehouse time and storage footprint. For example, a Medium warehouse (4 credits/hour) running 24/7 costs ~2,880 credits/month. At $2/credit, that's $5,760. With auto-suspend after 5 minutes of idle, you might only pay for 8 hours/day, reducing cost to ~$1,920.

-- View credit usage by warehouse
SELECT WAREHOUSE_NAME,
       SUM(CREDITS_USED) AS total_credits,
       SUM(CREDITS_USED_COMPUTE) AS compute_credits,
       SUM(CREDITS_USED_CLOUD_SERVICES) AS cloud_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATEADD('month', -1, CURRENT_TIMESTAMP)
GROUP BY WAREHOUSE_NAME
ORDER BY total_credits DESC;

Optimizing Warehouse Configuration

Warehouses are the main cost driver. Key settings: size (X-Small to 6X-Large), auto-suspend (seconds of idle before shutdown), auto-resume (start on query), and multi-cluster (up to 10 clusters). Best practices: Use X-Small for development, Small/Medium for ETL, Large+ for heavy analytics. Set auto-suspend to 300 seconds (5 min) for interactive, 60 seconds for automated. For multi-cluster, start with 1 and increase only if queuing occurs. Use resource monitors to cap credit usage per warehouse or account.

-- Create an optimized warehouse for ad-hoc queries
CREATE WAREHOUSE ad_hoc_wh
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 2
  SCALING_POLICY = 'ECONOMY'
  INITIALLY_SUSPENDED = TRUE;

-- Set a resource monitor to limit monthly credits
CREATE RESOURCE MONITOR monthly_cap
  WITH CREDIT_QUOTA = 500
  FREQUENCY = 'MONTHLY'
  START_TIMESTAMP = '2025-01-01 00:00:00'
  END_TIMESTAMP = '2025-12-31 23:59:59'
  NOTIFY_USERS = (admin@example.com)
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

Reducing Storage Costs

Storage costs are driven by compressed data size, time travel (7 days default, up to 90), and fail-safe (7 days). To reduce: compress data before loading (Snowflake does this automatically), drop unnecessary tables, reduce time travel retention for non-critical tables, and use clustering keys to improve pruning (which reduces storage overhead from micro-partitions). Also, consider using transient or temporary tables for intermediate data (no fail-safe).

-- Check storage usage by table
SELECT TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       ACTIVE_BYTES / (1024*1024*1024) AS active_gb,
       TIME_TRAVEL_BYTES / (1024*1024*1024) AS time_travel_gb,
       FAILSAFE_BYTES / (1024*1024*1024) AS failsafe_gb
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
ORDER BY active_gb DESC
LIMIT 10;

-- Reduce time travel retention for a staging table
ALTER TABLE staging.raw_events SET DATA_RETENTION_TIME_IN_DAYS = 1;

Leveraging Materialized Views and Clustering

Materialized views (MVs) pre-compute and store results, reducing query compute costs. They are ideal for aggregations on large tables. However, MVs incur storage and maintenance costs. Use them when queries are frequent and the underlying data changes slowly. Clustering keys physically order data in micro-partitions, improving query performance and reducing credits spent on scanning. Both features trade storage for compute savings.

-- Create a materialized view for daily sales summary
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT DATE_TRUNC('day', order_date) AS day,
       SUM(amount) AS total_sales,
       COUNT(*) AS order_count
FROM orders
GROUP BY day;

-- Set clustering key on orders table
ALTER TABLE orders CLUSTER BY (order_date);

-- Check clustering status
SELECT * FROM TABLE(INFORMATION_SCHEMA.CLUSTERING_INFORMATION('orders'));

Monitoring and Alerting with Resource Monitors

Resource monitors are the first line of defense against cost overruns. They can be set at account, warehouse, or user level. You can define credit quotas and actions (notify, suspend, abort) when thresholds are reached. Best practice: set a monthly account-level monitor with 80% notify and 100% suspend. Also, set warehouse-level monitors for critical warehouses. Use the ACCOUNT_USAGE views to build custom dashboards.

-- Create a resource monitor for a warehouse
CREATE RESOURCE MONITOR wh_monitor
  WITH CREDIT_QUOTA = 1000
  FREQUENCY = 'MONTHLY'
  START_TIMESTAMP = '2025-01-01 00:00:00'
  END_TIMESTAMP = '2025-12-31 23:59:59'
  NOTIFY_USERS = (dba@example.com)
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND_IMMEDIATE;

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

-- Check monitor history
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.RESOURCE_MONITORS;

Query Optimization to Reduce Compute

Poorly written queries waste credits. Use query profiling to identify heavy scans, missing filters, and inefficient joins. Enable result caching to avoid re-executing identical queries. Use automatic clustering and search optimization for faster scans. Also, consider using the 'USE_CACHED_RESULT' session parameter. For long-running queries, break them into smaller batches or use incremental materialization.

-- Profile a query to see scanned bytes
SELECT QUERY_ID,
       TOTAL_ELAPSED_TIME,
       BYTES_SCANNED,
       BYTES_WRITTEN,
       CREDITS_USED_CLOUD_SERVICES
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TEXT LIKE '%SELECT * FROM orders%'
ORDER BY START_TIME DESC
LIMIT 5;

-- Enable result caching (default on)
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

Automating Cost Governance with Tasks and Stored Procedures

Use Snowflake tasks and stored procedures to automate cost management. For example, schedule a task to suspend idle warehouses after hours, or to drop old partitions. You can also automate the creation of resource monitors for new warehouses. This reduces manual overhead and ensures consistent governance.

-- Create a task to suspend all warehouses at 10 PM daily
CREATE TASK suspend_warehouses
  WAREHOUSE = admin_wh
  SCHEDULE = 'USING CRON 0 22 * * * America/New_York'
AS
  ALTER WAREHOUSE my_wh SUSPEND;
  -- Add more ALTER statements for other warehouses

-- Create a stored procedure to drop old partitions
CREATE OR REPLACE PROCEDURE drop_old_partitions(table_name STRING, retention_days INT)
RETURNS STRING
LANGUAGE SQL
AS
$$
  DECLARE
    drop_stmt STRING;
  BEGIN
    drop_stmt := 'ALTER TABLE ' || table_name || ' DROP PARTITION IF EXISTS (date < DATEADD(day, -' || retention_days || ', CURRENT_DATE))';
    EXECUTE IMMEDIATE drop_stmt;
    RETURN 'Old partitions dropped';
  END;
$$;