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
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
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(days, -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_credits DESC;
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;
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
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;
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
ALTER WAREHOUSE ETL_WH SET AUTO_SUSPEND = 60;
ALTER WAREHOUSE ANALYTICS_WH SET AUTO_SUSPEND = 300;
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;
Step 4: Right-Size Warehouses
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;
ALTER WAREHOUSE DEV_WH SET WAREHOUSE_SIZE = 'XSMALL';
Step 5: Reduce Storage Costs
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
AND DATEDIFF('day', last_altered, CURRENT_DATE()) > 90
ORDER BY bytes DESC;
ALTER TABLE staging.temp_data SET DATA_RETENTION_TIME_IN_DAYS = 1;
CREATE TRANSIENT TABLE staging.temp_load (
id INTEGER, data VARIANT
);
Step 6: Serverless Feature Cost Control
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;
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;
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
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
Next Steps
For architecture patterns, see snowflake-reference-architecture.
