1. Right-Size Your Warehouses
The most impactful lever. A query that takes 10 seconds on an XL warehouse runs in 5 seconds on a 2XL — but costs twice as much. Always benchmark before upsizing.
-- Check actual query execution time vs. warehouse size
SELECT query_id, warehouse_size, total_elapsed_time / 1000 AS seconds
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY total_elapsed_time DESC
LIMIT 20;
2. Set Aggressive Auto-Suspend
The default auto-suspend is 10 minutes. For dev/test warehouses, set it to 60 seconds:
ALTER WAREHOUSE DEV_WH SET AUTO_SUSPEND = 60;
A warehouse suspended for 9 minutes instead of 1 minute wastes 8 minutes of credits.
3. Use Multi-Cluster Only When Needed
Multi-cluster warehouses scale out for concurrent users, not for query speed. Enable it only for BI tools with many simultaneous users, not for ETL pipelines.
4. Leverage Query Result Cache
Snowflake caches results for 24 hours. If the same query runs twice with the same data, the second run is free. Structure your BI dashboards to hit the same queries.
5. Use Clustering Keys on Large Tables
For tables > 1TB that are frequently filtered on a specific column:
ALTER TABLE fact_orders CLUSTER BY (order_date);
Clustering reduces the data scanned per query, directly lowering compute cost.
6. Avoid SELECT * in Production
Always select only the columns you need. Snowflake is columnar — unused columns
are not scanned, but lazy developers still write SELECT * in production code.
7. Use Resource Monitors
Set hard credit limits on warehouses to prevent runaway queries:
CREATE RESOURCE MONITOR monthly_cap
WITH CREDIT_QUOTA = 500
TRIGGERS ON 80 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE COMPUTE_WH SET RESOURCE_MONITOR = monthly_cap;
8. Prune Unused Tables and Stages
Run a monthly audit of tables with zero query activity:
SELECT table_name, last_altered
FROM INFORMATION_SCHEMA.TABLES
WHERE last_altered < DATEADD('month', -3, CURRENT_TIMESTAMP())
ORDER BY bytes DESC;
9. Use External Tables for Cold Data
Data accessed less than once a month doesn't need to live in Snowflake storage. Put it in S3 and create an External Table — you pay only for the query compute, not storage.
10. Profile with Query History
Your single most powerful optimisation tool is SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY:
-- Top 10 most expensive queries this week
SELECT
query_text,
credits_used_cloud_services,
total_elapsed_time / 1000 AS seconds,
bytes_scanned / 1e9 AS gb_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('week', -1, CURRENT_TIMESTAMP())
ORDER BY credits_used_cloud_services DESC
LIMIT 10;
Conclusion
Cost optimisation in Snowflake is an ongoing practice, not a one-time fix. Build a monthly review into your team's workflow using the strategies above, and you can typically cut your bill by 20–40% without impacting performance.