Snowflake Cost Optimisation: A Practical Guide to Reducing Your Monthly Bill

Snowflake bills for compute (credits per second while a virtual warehouse is running) and storage ($23/TB/month for compressed data). Most cost optimisation effort should focus on compute — it is the dominant cost for active Snowflake environments, and the optimisation levers are large. Storage is rarely the primary driver but warrants attention for organisations with petabytes of data.

Compute Cost: Virtual Warehouse Management

**Auto-suspend is the most important setting.** Every virtual warehouse should have auto-suspend configured. The default is 10 minutes; for most development and ad-hoc warehouses, 1 minute is appropriate. For dedicated BI warehouse serving user queries, 5 minutes balances cold-start latency with cost. For batch ETL warehouses that run scheduled jobs, 1 minute auto-suspend combined with scheduled scale-up prevents idle billing between runs.

The impact: a warehouse left running without auto-suspend accumulates credits continuously. A medium (4 credits/hour) warehouse left running for 8 idle hours costs 32 credits — $64–128 depending on your rate. With 1-minute auto-suspend, the same warehouse costs less than 1 credit.

To set auto-suspend on all warehouses (run as ACCOUNTADMIN or with OPERATE privilege):

ALTER WAREHOUSE my_warehouse SET AUTO_SUSPEND = 60;

Audit warehouses that do not have auto-suspend configured:

SELECT warehouse_name, auto_suspend

FROM snowflake.account_usage.warehouses

WHERE auto_suspend IS NULL OR auto_suspend > 300;

**Warehouse sizing experiments.** The Snowflake credit cost scales linearly with warehouse size — an XL warehouse costs 8 credits/hour; a Medium costs 4 credits/hour. But query time scales sub-linearly — many queries do not run 2x faster on an XL than a Medium.

The correct warehouse size is determined empirically. Run your representative query workload on a Medium warehouse; then on a Large; measure total query execution time and total credits consumed. If queries on Large finish in 60% of the time with 100% more credits, Large is more expensive for the same work. If queries on Large finish in 40% of the time, Large may be cheaper (fewer credits consumed total despite the higher hourly rate) and faster.

Snowflake's Query Acceleration Service (QAS) is an alternative to warehouse sizing for query outliers — it supplements warehouse compute with shared Snowflake compute for the most expensive queries without requiring you to upsize the entire warehouse for those outliers. Enable QAS on a warehouse and set QUERY_ACCELERATION_MAX_SCALE_FACTOR to limit its usage.

**Separate workload warehouses.** A single shared warehouse for all workloads (BI dashboards, ad-hoc analysis, dbt runs, data loading) creates contention and over-provisioning. The BI warehouse runs continuously during business hours and needs responsive concurrency; the ETL warehouse runs in batch and needs bursty high compute; the data science warehouse runs occasionally and needs high compute on demand.

A typical multi-warehouse setup:

- bi_warehouse: Medium, auto-suspend 5 minutes, multi-cluster if concurrent users exceed single-cluster capacity

- dbt_warehouse: Small or Medium, auto-suspend 60 seconds, runs only during scheduled dbt runs

- loading_warehouse: X-Small or Small, auto-suspend 60 seconds, runs only during Fivetran/Airbyte loads

- adhoc_warehouse: Small, auto-suspend 60 seconds, for individual analyst queries

Each warehouse's credit consumption is attributable to a specific workload — you can see the cost of dbt runs separately from BI dashboard queries.

Query Performance and Credit Efficiency

Credits are consumed per second of warehouse uptime, not per query. A warehouse that runs 10 efficient queries in 5 minutes uses the same credits as one that runs one inefficient query in 5 minutes. Reducing query execution time directly reduces credit consumption.

**Result cache.** Snowflake caches the result of every query for 24 hours. If the same query (byte-for-byte identical) is executed again within 24 hours and the underlying tables have not changed, Snowflake returns the cached result and charges zero credits. BI tools that run the same aggregation queries repeatedly benefit from this — ensure queries are parameterised consistently so cache keys match.

To check result cache effectiveness:

SELECT query_id, execution_status, execution_time, bytes_scanned,

query_text, query_type

FROM snowflake.account_usage.query_history

WHERE execution_status = 'SUCCESS'

AND is_client_generated_statement = FALSE

ORDER BY execution_time DESC

LIMIT 100;

Filter for queries with bytes_scanned = 0 — those were served from the result cache at no compute cost.

**Clustering key effectiveness.** Poor clustering produces excessive micro-partition scans — queries scan a high proportion of the table's micro-partitions because the data is not ordered in a way that prunes effectively. The Query Profile's "Partitions Scanned / Partitions Total" metric reveals this.

If a critical query scans more than 30–40% of partitions despite having a selective WHERE clause, the table may need a clustering key. Automatic Clustering (ALTER TABLE ... CLUSTER BY) is a Snowflake service that continuously maintains clustering — it consumes credits but can significantly reduce query credit cost for large frequently-queried tables. The economics depend on whether the reduction in query credits exceeds the clustering service credits.

**Search Optimisation Service.** For point lookup queries (exact match on a specific value rather than range scans), the Search Optimisation Service creates secondary search access paths. It is useful for tables where queries filter on high-cardinality columns that are not the clustering key. Enable per-table, per-column:

ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_id, session_id);

Storage Cost

**Data compression audit.** Snowflake compresses columnar data automatically using its chosen algorithm. The actual compressed size determines storage billing. For tables with string-heavy data (free-text, long URLs, JSON), compression ratios may be lower. Identify the largest tables by compressed storage size:

SELECT table_name, table_schema,

ROUND(active_bytes / POWER(1024, 3), 2) as active_gb,

ROUND(time_travel_bytes / POWER(1024, 3), 2) as time_travel_gb,

ROUND(failsafe_bytes / POWER(1024, 3), 2) as failsafe_gb

FROM snowflake.account_usage.table_storage_metrics

WHERE active_bytes > 1e9

ORDER BY active_bytes DESC;

**Time Travel and Fail-safe storage.** Time Travel retains historical versions of data for up to 90 days (configurable). The default is 1 day for standard edition, up to 90 days for Enterprise. Every table modification stores the changed data for the Time Travel period. For tables with high write frequency (event tables receiving millions of inserts daily), long Time Travel periods create substantial storage cost.

Reduce Time Travel for tables where historical version access is unlikely:

ALTER TABLE high_volume_events SET DATA_RETENTION_TIME_IN_DAYS = 1;

Fail-safe is an additional 7 days of storage beyond Time Travel that Snowflake maintains for disaster recovery. Fail-safe cannot be configured or reduced — it is always 7 days. For very large tables, Fail-safe storage can exceed Time Travel storage.

**Transient and temporary tables.** Transient tables have no Fail-safe period and configurable Time Travel (0 or 1 day). Use transient tables for staging tables and intermediate pipeline outputs that do not require long Time Travel:

CREATE TRANSIENT TABLE staging.raw_events (...);

Temporary tables have no Fail-safe, no Time Travel, and exist only for the session duration. Use for within-session intermediate computation.

Cost Attribution and Monitoring

**ACCOUNT_USAGE views** provide per-warehouse, per-user, and per-query credit consumption:

SELECT warehouse_name,

SUM(credits_used) as total_credits,

SUM(credits_used) * [your_credit_price] as estimated_cost

FROM snowflake.account_usage.warehouse_metering_history

WHERE start_time >= CURRENT_DATE - 30

GROUP BY warehouse_name

ORDER BY total_credits DESC;

Build a cost dashboard from ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY, ACCOUNT_USAGE.QUERY_HISTORY, and ACCOUNT_USAGE.TABLE_STORAGE_METRICS. This gives per-warehouse credit consumption, per-user query costs, and per-table storage costs in a single view. Review weekly to surface unexpected cost growth before it accumulates.

**Snowflake resource monitors** set credit quotas and alerts on warehouses:

CREATE RESOURCE MONITOR monthly_limit

WITH CREDIT_QUOTA = 1000

TRIGGERS

ON 75 PERCENT DO NOTIFY

ON 90 PERCENT DO SUSPEND;

ALTER WAREHOUSE adhoc_warehouse SET RESOURCE_MONITOR = monthly_limit;

Resource monitors prevent runaway warehouse consumption from unconstrained ad-hoc queries.

For data teams looking to reduce Snowflake costs as part of a broader data platform review, our data architecture consulting practice conducts cost optimisation assessments — contact us to discuss your Snowflake environment.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.