snowflake-cost-tuning

Snowflake Cost Tuning

Overview

Optimize Snowflake costs through resource monitors, warehouse right-sizing, auto-suspend tuning, and credit consumption analysis.

Snowflake Pricing Model

Cost Component	What It Measures	Typical % of Bill
Compute (credits)	Warehouse running time	60-80%
Storage	Data at rest (compressed)	10-20%
Cloud services	Metadata ops, auth, compilation	5-10%
Data transfer	Egress between regions/clouds	0-5%
Serverless	Snowpipe, auto-clustering, MV refresh	Variable

Credit rates by warehouse size:

Size	Credits/Hour	Nodes
X-Small	1	1
Small	2	2
Medium	4	4
Large	8	8
X-Large	16	16
2X-Large	32	32

Instructions

Step 1: Analyze Current Credit Consumption

-- Credits by warehouse (last 30 days)
SELECT warehouse_name,
       SUM(credits_used) AS total_credits,
       SUM(credits_used_compute) AS compute_credits,
       SUM(credits_used_cloud_services) AS cloud_credits,
       ROUND(SUM(credits_used) * 3.0, 2) AS est_cost_usd  -- ~$3/credit standard
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(days, -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_credits DESC;

-- Daily credit trend
SELECT DATE_TRUNC('day', start_time) AS day,
       SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(days, -30, CURRENT_TIMESTAMP())
GROUP BY day
ORDER BY day;

-- Idle warehouse time (credits wasted while no queries running)
SELECT warehouse_name,
       SUM(credits_used) AS total_credits,
       COUNT(DISTINCT query_id) AS queries,
       CASE WHEN COUNT(DISTINCT query_id) = 0 THEN SUM(credits_used)
            ELSE 0 END AS idle_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY w
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
  ON w.warehouse_name = q.warehouse_name
  AND DATE_TRUNC('hour', w.start_time) = DATE_TRUNC('hour', q.start_time)
WHERE w.start_time >= DATEADD(days, -7, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY idle_credits DESC;

Step 2: Set Up Resource Monitors

-- Account-level resource monitor
CREATE OR REPLACE RESOURCE MONITOR account_monthly
  WITH CREDIT_QUOTA = 5000
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 50 PERCENT DO NOTIFY
    ON 75 PERCENT DO NOTIFY
    ON 90 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND
    ON 110 PERCENT DO SUSPEND_IMMEDIATE;

ALTER ACCOUNT SET RESOURCE_MONITOR = account_monthly;

-- Per-warehouse monitor for ETL
CREATE OR REPLACE RESOURCE MONITOR etl_daily
  WITH CREDIT_QUOTA = 100
  FREQUENCY = DAILY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 80 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND;

ALTER WAREHOUSE PROD_ETL_WH SET RESOURCE_MONITOR = etl_daily;

Step 3: Optimize Auto-Suspend

-- Short auto-suspend for bursty workloads (ETL)
ALTER WAREHOUSE ETL_WH SET AUTO_SUSPEND = 60;     -- 1 minute

-- Longer for interactive analytics (avoids constant resume)
ALTER WAREHOUSE ANALYTICS_WH SET AUTO_SUSPEND = 300;  -- 5 minutes

-- Check current auto-suspend settings
SELECT name, size, auto_suspend, auto_resume,
       CASE WHEN auto_suspend > 300 THEN 'REVIEW: high auto_suspend'
            ELSE 'OK' END AS recommendation
FROM INFORMATION_SCHEMA.WAREHOUSES;

-- Never set auto_suspend = 0 in production (warehouse runs forever)

Step 4: Right-Size Warehouses

-- Find oversized warehouses (low utilization)
SELECT warehouse_name, warehouse_size,
       AVG(avg_running) AS avg_queries_running,
       AVG(avg_queued_load) AS avg_queries_queued,
       CASE
         WHEN AVG(avg_queued_load) > 1 THEN 'SCALE UP or add clusters'
         WHEN AVG(avg_running) < 1 THEN 'Consider DOWNSIZE'
         ELSE 'RIGHT-SIZED'
       END AS recommendation
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
  DATE_RANGE_START => DATEADD(days, -7, CURRENT_TIMESTAMP())
))
GROUP BY warehouse_name, warehouse_size;

-- Downsize underutilized warehouses
ALTER WAREHOUSE DEV_WH SET WAREHOUSE_SIZE = 'XSMALL';

Step 5: Reduce Storage Costs

-- Find large unused tables
SELECT table_catalog, table_schema, table_name,
       bytes / 1e9 AS gb,
       row_count,
       last_altered,
       DATEDIFF('day', last_altered, CURRENT_DATE()) AS days_since_modified
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE bytes > 1e9  -- > 1 GB
  AND DATEDIFF('day', last_altered, CURRENT_DATE()) > 90
ORDER BY bytes DESC;

-- Reduce Time Travel retention for non-critical tables
ALTER TABLE staging.temp_data SET DATA_RETENTION_TIME_IN_DAYS = 1;

-- Use transient tables for staging (no Fail-safe storage)
CREATE TRANSIENT TABLE staging.temp_load (
  id INTEGER, data VARIANT
);

Step 6: Serverless Feature Cost Control

-- Monitor Snowpipe costs
SELECT pipe_name, SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY
WHERE start_time >= DATEADD(days, -30, CURRENT_TIMESTAMP())
GROUP BY pipe_name
ORDER BY credits DESC;

-- Monitor auto-clustering costs
SELECT table_name, SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY
WHERE start_time >= DATEADD(days, -30, CURRENT_TIMESTAMP())
GROUP BY table_name
ORDER BY credits DESC;

-- Monitor materialized view refresh costs
SELECT table_name, SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY
WHERE start_time >= DATEADD(days, -30, CURRENT_TIMESTAMP())
GROUP BY table_name
ORDER BY credits DESC;

Cost Reduction Checklist

• Resource monitors on all production warehouses
• Auto-suspend < 5 minutes on all warehouses
• No WAREHOUSE_SIZE > 'MEDIUM' without justification
• Transient tables for staging/temp data
• Time Travel retention minimized for non-critical tables
• Clustering keys only on tables > 1TB with frequent filter queries
• Review serverless feature costs monthly

Error Handling

Issue	Cause	Solution
Unexpected credit spike	Runaway query or always-on warehouse	Check QUERY_HISTORY, set auto-suspend
Resource monitor suspended warehouse	Exceeded quota	Increase quota or optimize workload
High cloud services cost	Many small metadata queries	Batch operations, reduce DDL frequency
Storage growing fast	No cleanup policy	Archive old data, use transient tables

Resources

• Cost Controls
• Resource Monitors
• CREATE RESOURCE MONITOR
• Budgets

Next Steps

For architecture patterns, see snowflake-reference-architecture.