Why Snowflake Costs Spiral Out of Control
If your Snowflake bill jumped 200% last quarter while data volume only grew 30%, you’re not alone. I’ve audited dozens of Snowflake environments where organizations unknowingly waste 40-60% of their spend on preventable issues.
The problem isn’t Snowflake’s pricing model—it’s how teams use the platform. Between warehouse sprawl, inefficient queries, and hidden serverless costs, most environments are bleeding credits. With the December 2025 Snowpipe pricing changes adding new cost dimensions, optimization is no longer optional.
This guide covers 12 actionable techniques I use to reduce Snowflake costs without sacrificing performance. Every recommendation includes production-grade SQL to implement immediately.
1. Audit Your Credit Consumption Patterns
Before optimizing anything, identify where credits actually go. Most teams guess wrong about their biggest cost drivers.
Find Your Top Credit-Consuming Warehouses
SELECT
warehouse_name,
SUM(credits_used) AS total_credits,
COUNT(DISTINCT query_id) AS query_count,
SUM(credits_used) / NULLIF(COUNT(DISTINCT query_id), 0) AS credits_per_query,
ROUND(SUM(credits_used) * 3.50, 2) AS estimated_cost_usd -- Adjust rate for your contract
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY warehouse_name
ORDER BY total_credits DESC
LIMIT 20;This reveals which warehouses drive costs. I’ve seen environments where a single analytics warehouse consumed 70% of credits because it never auto-suspended.
Identify Expensive Queries
SELECT
query_id,
query_text,
warehouse_name,
user_name,
execution_time / 1000 AS execution_seconds,
credits_used_cloud_services,
partitions_scanned,
bytes_scanned / POWER(1024, 3) AS gb_scanned,
start_time
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -7, CURRENT_DATE())
AND execution_time > 60000 -- Queries over 1 minute
ORDER BY credits_used_cloud_services DESC
LIMIT 50;Look for patterns: Are expensive queries scanning entire tables? Running repeatedly? Executing on oversized warehouses?
2. Right-Size Your Warehouses
The biggest waste I see is using XL or 2XL warehouses for workloads that run fine on Medium. Warehouse size determines cost per second, so oversizing is expensive.
The Warehouse Sizing Formula
Start small and scale up only when you hit these thresholds:
- X-Small (1 credit/hour): Ad-hoc queries, small data loads under 100MB
- Small (2 credits/hour): Standard BI dashboards, ETL jobs under 1GB
- Medium (4 credits/hour): Production ETL, dashboards with 100+ concurrent users
- Large+ (8+ credits/hour): Large batch processing, complex transformations over 10GB
Test Warehouse Performance vs Cost
-- Compare query performance across warehouse sizes
CREATE OR REPLACE PROCEDURE test_warehouse_sizing(query_text VARCHAR)
RETURNS TABLE (warehouse_size VARCHAR, execution_time NUMBER, cost_estimate NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
sizes ARRAY := ARRAY_CONSTRUCT('SMALL', 'MEDIUM', 'LARGE');
results RESULTSET;
BEGIN
FOR i IN 0 TO ARRAY_SIZE(:sizes) - 1 DO
LET size := GET(:sizes, :i);
EXECUTE IMMEDIATE 'USE WAREHOUSE ' || :size || '_WH';
LET start := CURRENT_TIMESTAMP();
EXECUTE IMMEDIATE :query_text;
LET elapsed := DATEDIFF(millisecond, :start, CURRENT_TIMESTAMP());
-- Calculate cost based on warehouse size
LET cost := CASE :size
WHEN 'SMALL' THEN :elapsed / 3600000.0 * 2 * 3.50
WHEN 'MEDIUM' THEN :elapsed / 3600000.0 * 4 * 3.50
WHEN 'LARGE' THEN :elapsed / 3600000.0 * 8 * 3.50
END;
INSERT INTO warehouse_test_results VALUES (:size, :elapsed, :cost);
END FOR;
results := (SELECT * FROM warehouse_test_results);
RETURN TABLE(results);
END;
$$;Run your critical queries through different warehouse sizes. If a Medium warehouse completes in 45 seconds vs 40 seconds on Large, use Medium—you’ll save 50% per query.
3. Implement Aggressive Auto-Suspend
Every second a warehouse runs idle costs money. Default auto-suspend of 10 minutes is too conservative for most use cases.
Optimal Auto-Suspend Settings by Use Case
-- For ad-hoc analytics (Looker, Tableau)
ALTER WAREHOUSE analytics_wh SET
AUTO_SUSPEND = 60 -- 1 minute
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;
-- For ETL jobs
ALTER WAREHOUSE etl_wh SET
AUTO_SUSPEND = 30 -- 30 seconds
AUTO_RESUME = TRUE;
-- For batch processing (dbt, Airflow)
ALTER WAREHOUSE batch_wh SET
AUTO_SUSPEND = 10 -- 10 seconds
AUTO_RESUME = TRUE;I set auto-suspend to 60 seconds for BI tools and 10-30 seconds for programmatic workloads. The “cold start” penalty is usually 2-5 seconds—negligible compared to idle costs.
Audit Current Auto-Suspend Settings
SELECT
name AS warehouse_name,
size,
auto_suspend / 60 AS auto_suspend_minutes,
auto_resume,
CASE
WHEN auto_suspend > 600 THEN 'TOO LONG - OPTIMIZE'
WHEN auto_suspend IS NULL THEN 'NEVER SUSPENDS - FIX IMMEDIATELY'
ELSE 'ACCEPTABLE'
END AS recommendation
FROM snowflake.account_usage.warehouses
WHERE deleted IS NULL
ORDER BY auto_suspend DESC NULLS FIRST;Any warehouse with auto-suspend over 10 minutes or NULL (never suspends) is a cost leak.
4. Eliminate Warehouse Sprawl
Most Snowflake accounts have 3-5x more warehouses than needed. Each warehouse increases management overhead and risks idle compute.
Identify Under-utilised Warehouses
SELECT
w.name AS warehouse_name,
w.size,
COUNT(DISTINCT qh.query_id) AS queries_last_30d,
SUM(wm.credits_used) AS credits_used,
MAX(qh.start_time) AS last_query_time,
DATEDIFF(day, MAX(qh.start_time), CURRENT_DATE()) AS days_since_last_use
FROM snowflake.account_usage.warehouses w
LEFT JOIN snowflake.account_usage.query_history qh
ON w.name = qh.warehouse_name
AND qh.start_time >= DATEADD(day, -30, CURRENT_DATE())
LEFT JOIN snowflake.account_usage.warehouse_metering_history wm
ON w.name = wm.warehouse_name
AND wm.start_time >= DATEADD(day, -30, CURRENT_DATE())
WHERE w.deleted IS NULL
GROUP BY w.name, w.size
HAVING queries_last_30d < 100 OR queries_last_30d IS NULL
ORDER BY credits_used DESC;Warehouses with under 100 queries per month should be consolidated or deleted. I typically consolidate down to 3-5 core warehouses:
- ETL warehouse (Medium, auto-suspend 30s)
- Analytics warehouse (Small, auto-suspend 60s)
- Data science warehouse (Large, auto-suspend 60s)
- Admin warehouse (X-Small, for account management)
5. Optimize Table Clustering
Poor clustering forces Snowflake to scan unnecessary micro-partitions. Proper clustering can reduce query costs by 70-90% for large tables.
Identify Tables That Need Clustering
SELECT
table_name,
table_schema,
row_count,
bytes / POWER(1024, 3) AS size_gb,
clustering_key,
CASE
WHEN avg_depth > 5 THEN 'POOR - CONSIDER RECLUSTERING'
WHEN avg_depth > 3 THEN 'MODERATE - MONITOR'
ELSE 'GOOD'
END AS clustering_health
FROM snowflake.account_usage.tables
WHERE deleted IS NULL
AND table_type = 'BASE TABLE'
AND row_count > 1000000 -- Focus on tables over 1M rows
ORDER BY bytes DESC
LIMIT 50;Tables with average clustering depth over 5 need attention. For time-series data, cluster on timestamp columns. For lookup tables, cluster on frequently filtered columns.
Implement Clustering Keys
-- For event data (most common pattern)
ALTER TABLE events
CLUSTER BY (DATE_TRUNC('day', event_timestamp));
-- For customer data with frequent filtering
ALTER TABLE customers
CLUSTER BY (customer_region, signup_date);
-- For multi-tenant architectures
ALTER TABLE tenant_data
CLUSTER BY (tenant_id, created_at);Monitor clustering cost vs query savings:
SELECT
table_name,
SUM(credits_used) AS reclustering_credits,
COUNT(*) AS recluster_operations
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY table_name
ORDER BY reclustering_credits DESC;If automatic clustering costs more than 10% of query credits on that table, consider manual clustering or adjusting the clustering key.
6. Reduce Data Storage Costs
Storage is cheap compared to compute, but multi-TB environments can still rack up $5,000-$10,000 monthly in storage fees alone.
Find Large Tables and Time Travel Waste
SELECT
table_schema,
table_name,
active_bytes / POWER(1024, 3) AS active_gb,
time_travel_bytes / POWER(1024, 3) AS time_travel_gb,
failsafe_bytes / POWER(1024, 3) AS failsafe_gb,
(active_bytes + time_travel_bytes + failsafe_bytes) / POWER(1024, 3) AS total_gb,
ROUND((time_travel_bytes + failsafe_bytes) / POWER(1024, 3) * 23, 2) AS time_travel_cost_usd -- $23/TB/month
FROM snowflake.account_usage.table_storage_metrics
WHERE active_bytes > 0
ORDER BY (time_travel_bytes + failsafe_bytes) DESC
LIMIT 50;Reduce Time Travel Retention
Default 1-day time travel is overkill for staging tables and logs.
-- For staging/temp tables
ALTER TABLE staging.raw_events
SET DATA_RETENTION_TIME_IN_DAYS = 0;
-- For production tables that don't need full 90 days
ALTER TABLE production.aggregated_metrics
SET DATA_RETENTION_TIME_IN_DAYS = 7;
-- Check current retention settings
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN ACCOUNT;Archive Old Partitions to Cold Storage
-- Unload old data to S3/Azure/GCS
COPY INTO @my_s3_stage/archive/events_2023/
FROM (
SELECT * FROM events
WHERE event_date < '2024-01-01'
)
FILE_FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY)
MAX_FILE_SIZE = 268435456; -- 256MB files
-- Drop archived data
DELETE FROM events WHERE event_date < '2024-01-01';Archiving to external storage costs $0.02-$0.03/GB/month vs Snowflake’s $23-$40/TB/month.
7. Optimize Materialized Views
Materialized views are powerful but expensive. Each refresh consumes credits, and Snowflake maintains them automatically.
Audit Materialized View Refresh Costs
SELECT
mv.table_name AS materialized_view,
mv.table_schema,
COUNT(mvr.refresh_id) AS refresh_count_30d,
SUM(mvr.credits_used) AS total_credits,
AVG(mvr.credits_used) AS avg_credits_per_refresh,
MAX(mvr.refresh_end_time) AS last_refresh
FROM snowflake.account_usage.materialized_view_refresh_history mvr
JOIN snowflake.account_usage.tables mv
ON mvr.database_name = mv.table_catalog
AND mvr.schema_name = mv.table_schema
AND mvr.table_name = mv.table_name
WHERE mvr.refresh_start_time >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY mv.table_name, mv.table_schema
ORDER BY total_credits DESC;Replace Expensive MVs with Scheduled Refreshes
If a materialized view refreshes 1,000+ times per day but queries only run 50 times, convert to a regular table with scheduled refreshes:
-- Drop materialized view
DROP MATERIALIZED VIEW expensive_mv;
-- Create regular table
CREATE TABLE scheduled_aggregation AS
SELECT
customer_id,
DATE_TRUNC('day', order_date) AS order_date,
SUM(amount) AS daily_revenue
FROM orders
GROUP BY customer_id, DATE_TRUNC('day', order_date);
-- Schedule refresh via dbt/Airflow (runs every 6 hours instead of constantly)
CREATE OR REPLACE TASK refresh_aggregation
WAREHOUSE = etl_wh
SCHEDULE = 'USING CRON 0 */6 * * * America/Los_Angeles'
AS
CREATE OR REPLACE TABLE scheduled_aggregation AS
SELECT
customer_id,
DATE_TRUNC('day', order_date) AS order_date,
SUM(amount) AS daily_revenue
FROM orders
GROUP BY customer_id, DATE_TRUNC('day', order_date);
ALTER TASK refresh_aggregation RESUME;8. Control Serverless Feature Costs
Snowpipe, Tasks, and Materialized Views use serverless compute—billed separately and easy to overlook.
Monitor Serverless Costs
SELECT
DATE_TRUNC('day', usage_date) AS date,
service_type,
SUM(credits_used) AS credits,
ROUND(SUM(credits_used) * 3.50, 2) AS cost_usd
FROM snowflake.account_usage.metering_daily_history
WHERE usage_date >= DATEADD(day, -30, CURRENT_DATE())
AND service_type IN ('SNOWPIPE', 'MATERIALIZED_VIEW', 'TASK')
GROUP BY DATE_TRUNC('day', usage_date), service_type
ORDER BY date DESC, credits DESC;Optimize Snowpipe Ingestion
Post-December 2025, Snowpipe pricing changed to charge per file processed. Batch small files before ingestion:
-- Bad: 10,000 files of 1KB each = high Snowpipe cost
-- Good: 100 files of 100KB each = 99% lower Snowpipe cost
-- Configure Snowpipe with longer refresh intervals
CREATE OR REPLACE PIPE events_pipe
AUTO_INGEST = TRUE
AWS_SNS_TOPIC = 'arn:aws:sns:us-east-1:123456789:snowpipe'
AS
COPY INTO events
FROM @s3_stage
FILE_FORMAT = (TYPE = JSON)
PATTERN = '.*.json'
-- Add SIZE_LIMIT to batch files
SIZE_LIMIT = 104857600; -- 100MB batches9. Implement Query Result Caching
Snowflake caches query results for 24 hours. Identical queries cost zero credits when cached.
Check Cache Hit Rates
SELECT
DATE_TRUNC('day', start_time) AS query_date,
COUNT(*) AS total_queries,
SUM(CASE WHEN query_result_cache = 'USED' THEN 1 ELSE 0 END) AS cache_hits,
ROUND(100.0 * cache_hits / total_queries, 2) AS cache_hit_rate,
SUM(execution_time) / 1000 AS total_execution_seconds
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -7, CURRENT_DATE())
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY query_date DESC;Target 30%+ cache hit rates for BI workloads. If under 20%, investigate:
- Are users running parameterized queries that prevent caching?
- Are dashboards adding random ORDER BY clauses?
- Are CURRENT_TIMESTAMP() calls making queries unique?
Force Result Reuse in BI Tools
-- In Tableau/Looker, standardize date filters
-- Bad (prevents caching):
SELECT * FROM sales WHERE sale_date = CURRENT_DATE();
-- Good (enables caching):
SELECT * FROM sales WHERE sale_date = '2025-01-02';10. Optimize Data Loading
COPY INTO operations can be expensive when misconfigured. Small files and frequent loads waste credits.
Batch Load Operations
-- Calculate optimal batch size
SELECT
pipe_name,
AVG(file_size / 1024) AS avg_file_kb,
COUNT(*) AS files_loaded,
SUM(credits_used) AS total_credits,
ROUND(SUM(credits_used) / COUNT(*), 4) AS credits_per_file
FROM snowflake.account_usage.copy_history
WHERE start_time >= DATEADD(day, -7, CURRENT_DATE())
GROUP BY pipe_name
ORDER BY credits_per_file DESC;If credits per file exceed 0.001, your files are too small. Batch before loading:
# Batch small JSON files in S3 before Snowpipe
aws s3 ls s3://bucket/raw/ | \
while read -r line; do
file=$(echo $line | awk '{print $4}')
cat "$file" >> batch_$(date +%s).json
# Process in 50MB batches
doneUse COPY INTO With File Pruning
-- Expensive: scans all files
COPY INTO events FROM @s3_stage;
-- Optimized: scans only new files
COPY INTO events
FROM @s3_stage
PATTERN = '.*2025-01-02.*json'
FILES = ('events_20250102_batch1.json', 'events_20250102_batch2.json');11. Monitor and Alert on Cost Anomalies
Set up automated alerts before runaway costs happen.
Create Cost Spike Alerts
CREATE OR REPLACE TASK cost_alert_task
WAREHOUSE = admin_wh
SCHEDULE = 'USING CRON 0 8 * * * America/Los_Angeles' -- Daily at 8 AM
AS
BEGIN
LET credits_today := (
SELECT SUM(credits_used)
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= CURRENT_DATE()
);
LET credits_avg := (
SELECT AVG(daily_credits)
FROM (
SELECT DATE_TRUNC('day', start_time) AS day,
SUM(credits_used) AS daily_credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY day
)
);
IF (:credits_today > :credits_avg * 1.5) THEN
CALL system$send_email(
'[email protected]',
'Snowflake Cost Alert',
'Credits used today: ' || :credits_today ||
' (50% above 30-day average of ' || :credits_avg || ')'
);
END IF;
END;
ALTER TASK cost_alert_task RESUME;Set Resource Monitors
-- Warehouse-level limit
CREATE RESOURCE MONITOR analytics_limit WITH
CREDIT_QUOTA = 1000 -- Monthly limit
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 75 PERCENT DO NOTIFY
ON 90 PERCENT DO SUSPEND
ON 100 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE analytics_wh SET RESOURCE_MONITOR = analytics_limit;
-- Account-level limit
CREATE RESOURCE MONITOR account_limit WITH
CREDIT_QUOTA = 10000
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 80 PERCENT DO NOTIFY
ON 95 PERCENT DO SUSPEND;
ALTER ACCOUNT SET RESOURCE_MONITOR = account_limit;12. Leverage Zero-Copy Cloning for Dev/Test
Never copy data for development environments. Zero-copy cloning is instant and costs nothing until data diverges.
Clone Production for Testing
-- Clone entire database (instant, no storage cost initially)
CREATE DATABASE dev_database CLONE production_database;
-- Clone specific schema
CREATE SCHEMA dev_schema CLONE production.analytics;
-- Clone table for testing
CREATE TABLE test_orders CLONE production.orders;
-- Time travel clone (snapshot from 2 days ago)
CREATE TABLE orders_snapshot CLONE production.orders
AT(OFFSET => -172800); -- 48 hours agoCommon Cost Optimization Mistakes to Avoid
Mistake 1: Over-Optimizing Small Warehouses Don’t waste time optimizing X-Small warehouses consuming 20 credits/month. Focus on Large+ warehouses burning 500+ credits/day.
Mistake 2: Clustering Every Table Clustering costs credits. Only cluster tables over 1M rows with frequent range scans or filtering.
Mistake 3: Disabling Auto-Resume This forces manual warehouse management and creates downtime. Keep auto-resume enabled.
Mistake 4: Using Single-Cluster Warehouses for BI Tools BI tools with 50+ concurrent users need multi-cluster warehouses to avoid queuing. Undersizing causes poor user experience.
Mistake 5: Ignoring Query Optimization No amount of warehouse tuning fixes a query scanning 100GB when it only needs 10MB. Optimize queries first, then infrastructure.
Measuring Success: KPIs to Track
After implementing these optimizations, monitor these metrics monthly:
- Cost per TB scanned: Should be under $50/TB
- Warehouse idle time: Under 10% of total runtime
- Query cache hit rate: Above 30% for BI workloads
- Credits per 1M rows processed: Benchmark by workload type
- Storage cost per TB: Target $23-25/TB/month (depends on time travel settings)
Frequently Asked Questions
Q: What’s the fastest way to reduce Snowflake costs immediately? Set all warehouse auto-suspend values to 60 seconds or less. This single change typically reduces costs by 15-25% within 24 hours.
Q: How much should I spend on compute vs storage? Typical breakdown: 75-85% compute, 10-20% storage, 5-10% serverless features. If storage exceeds 25%, audit time travel retention and archive old data.
Q: Should I use multi-cluster warehouses? Only for BI tools with 50+ concurrent users or ETL jobs with unpredictable parallelism. Otherwise, single-cluster warehouses with appropriate size are more cost-effective.
Q: How do I calculate ROI of query optimization? Use: (Credits saved per day × 30 days × $3.50/credit) / (Engineer hours × $75/hour). Optimizing a single expensive query that runs 1,000 times daily often pays for a full day of engineering time.
Q: What’s a good cost per query benchmark?
- Simple BI queries: $0.001-0.01 per query
- Complex ETL: $0.10-1.00 per query
- Large batch processing: $5-50 per run
If you’re above these ranges, optimization is needed.
Next Steps: Start by running the credit consumption audit query in section 1. Identify your top 3 cost drivers and tackle those first. Small optimizations across many areas rarely succeed—focus on the biggest problems.
