optimization Archives - app.dataengineerhub.blog

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 batches

9. 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
done

Use 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 ago

Common 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.

Tag: optimization

Snowflake Cost Optimization: 12 Proven Techniques to Cut Your Bill by 40% in 2026