Table of Contents
We all know Snowflake is a powerful cloud data platform but its costs must be kept in-check to ensure optimal results. Snowflake costs can spiral exponentially as your data grows and you may end up spending the quarter’s budget (or more) in just 1 month. Hence, it is critical for data engineers to understand how they can maintain balance between performance and Snowflake spend.
In this article, we’ll walk through the major contributors before diving into some practical and effective ways to optimize Snowflake cost.
Types of Snowflake Cost
It’s critical for optimization to figure out what you are paying for. Snowflake costs are divided into 3 primary categories – compute cost, storage cost, and data transfer cost.
Compute Cost
In most cases, they contribute the biggest portion of your total Snowflake bill. These charges are calculated by taking into account the size of your virtual warehouses and the duration for which they were run. Many people think that Snowflake cost is based on the running time of queries but that’s not true. In fact, a lot of unexpected spikes in Snowflake costs are triggered by this misconception.
Important:
You pay for the time the warehouse is running, not per query. So, if 3 queries ran in parallel each taking 20 mins. You pay for 20 mins of compute not 60 mins.
The size of your virtual warehouse is the other vital factor for determining your Snowflake bill. Sizes range from X-Small to 6X-Large, with costs increasing linearly as we go up the ladder. Snowflake cost is based on credits and the following table shows how the size of the warehouse can impact your credit consumption.
| Size of the Virtual Warehouse | Credits Consumed per Hour |
|---|---|
|
X-Small |
1 |
|
Small |
2 |
|
Medium |
4 |
|
Large |
8 |
|
X-Large |
16 |
|
2X-Large |
32 |
|
3X-Large |
64 |
|
4X-Large |
128 |
|
5X-Large |
256 |
|
6X-Large |
512 |
In addition to these, Snowflake also charges for cloud services, like user authentication, query optimization, and metadata management. Although these costs are relatively minimal, it’s important to keep them in consideration while calculating your Snowflake spend (particularly, if you have large workloads or frequent metadata operations).
The charges for cloud services apply ONLY if it exceeds 10% of your total virtual warehouse usage. For example, if you are using 150 Snowflake credits a day, you will get 15 cloud services credits for FREE. If you consume more than that, you will have to pay for the additional credits.
Storage Cost
Storing data on Snowflake is another substantial expense but its calculation is not very complex. They are much more sustainable than compute costs as Snowflake typically charges around $23-$25 per TB of storage.
However, you can experience higher rates if your account is located in certain parts of the world, like Washington ($40) and Zurich ($50.50). Similarly, you can incur some additional expenses for storing historical data for failure recoveries.
Data Transfer
Data ingress is completely free but you get charged for downloading data (egress) from Snowflake. This cost can come as a big surprise for new users and must be taken into consideration. It is calculated according to the pay-per-byte model and the cost per byte can vary by region.
NOTE: If the data is transferred within the same region, it is FREE.
Want a full breakdown with real-world examples? Check out our detailed guide on estimating
4 Best Ways to Optimize Snowflake Cost
Now that we know where the costs come from, let’s explore how to keep them in check. Snowflake gives you plenty of flexibility to control usage — if you know where to look.
Warehouse Optimization
Warehouses are the biggest contributor to your Snowflake bill. The key to optimization is using the right-sized warehouses and ensuring they don’t run longer than necessary. Here’s how we can achieve this goal.
Choose Appropriate Warehouse Size
Always start with the smallest possible warehouse (X-Small or Small) and monitor the performance of your system before shifting to a larger warehouse. Moving from Small to Large can multiply the cost exponentially and it must be done only when necessity. In many cases, small warehouses perform just as well for batch ETL or low concurrency workloads.
You should also analyze the performance metrics regularly to find the sweet spot between performance and cost. For example, you can use warehouse-level dashboards to monitor queueing, execution times, and credit consumption.
|
SELECT WAREHOUSE_NAME, AVG(AVG_RUNNING) AS AVG_RUNNING FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY GROUP BY WAREHOUSE_NAME; |
Use Auto-Suspend and Auto-Resume
An always-on warehouse is like leaving your car running overnight. If it’s not in use, you’re still burning fuel (credits in case of Snowflake). Setting up a trigger to enable auto-suspend after a short period of inactivity and let it resume again when needed can save a lot of money.
For example, a data science team with which I was working switched from a 24/7 Medium warehouse to an auto-suspend Small one. It reduced their monthly credits consumption by 60% with no impact on performance.
The default time for auto-suspend is 5 minutes but it’s a long duration in the world of Snowflake. Consider reducing it to 1-2 minutes if your queries don’t run for too long.
|
ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 120; — 2 minutes ALTER WAREHOUSE my_wh SET AUTO_RESUME = TRUE; |
Never set the auto-suspend time at less than 1 minute as it can result in double billing. Here’s how:
Snowflake charges for at least 1 minute even if your warehouse ran for a few seconds. Now, consider a situation where you have set the auto-suspend at 30 seconds and a query ran for 10 seconds. The warehouse will be turned off after 30 seconds and the cost of a minute will be charged.
Let’s say, the query ran again at the 35th second for 10 seconds. The warehouse will be activated again and you will again be charged for 1 minute. In this way, you will have to pay twice the cost.
Set Minimum Clusters to 1
Multi-cluster warehouses are used for handling high concurrency. They are great for enterprise systems but are extremely costly if not tuned properly. Therefore, it’s critical to always start with MIN_CLUSTER_COUNT = 1 and scale only when needed. Setting higher minimum clusters allocate compute whether needed or not and can make a HUGE hit to your Snowflake bill.
In this case, you can also monitor QUEUED_PROVISIONING_TIME and QUEUED_OVERLOAD_TIME to justify scaling up.
|
ALTER WAREHOUSE my_wh SET MIN_CLUSTER_COUNT = 1; |
Query Optimization
Snowflake charges for compute per second, which means inefficient queries can rack up bills fast. Optimizing query patterns can dramatically cut costs while improving performance. It’s about writing better SQL, leveraging caching, and ensuring compute is not wasted on unnecessary operations.
Reduce Query Frequency
If your dashboards are refreshing every few minutes (even without new data), you’re paying for unnecessary compute and it should be optimized. Very small intervals between refreshes can also affect the performance and may result in slower response times.
Implementing a sensible refresh rate, using materialized views, and caching repetitive results can save a lot of money. The following table can serve as an ideal example to understand their importance.
|
Run Frequency |
Monthly Cost |
|
Once a day for the entire week |
$700 |
|
Once at the start and once around the midday of the working day |
$1,000 |
|
Hourly during working hours and skip weekend |
$4,000 |
|
Hourly on weekdays and daily at the weekend |
$12,200 |
|
After Every Hour |
$16,800 |
Enable Query Timeouts
It is critical to set a maximum duration for queries, particularly if you are running resource-heavy ones. These timeouts prevent the queries from consuming too many compute resources and save the bill. They also notify you if any query is taking longer than expected to run and serve as a trigger for query optimization.
It is highly recommended to combine user-level and session-level timeouts for tight governance. It will ensure large queries don’t block warehouses indefinitely.
|
ALTER USER analyst_user SET STATEMENT_TIMEOUT_IN_SECONDS = 300; |
Filter Early and Avoid Select *
Scanning the entire table when you only need specific results is a huge NO to optimize Snowflake cost. Always make sure to apply filters early in the query to minimize the number of results being checked. Similarly, only select those columns in your queries that are vital for your system to work.
|
— Avoid: SELECT * FROM orders; |
|
— Do this: SELECT order_id, customer_id FROM orders WHERE order_date >= CURRENT_DATE – 30; |
Implement Wise Clustering of Tables (for Query Pruning)
We should cluster large tables by filter-friendly columns (like order_date) to reduce the volume of scanned data. It is very effective if we are working with frequently queried, append-only tables. However, excess of everything is bad. Don’t overuse it as it can lead to metadata maintenance cost.
|
ALTER TABLE sales_data CLUSTER BY (region, sale_date); |
You can also check the effectiveness of your clustering by using the following query:
|
SELECT * FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS WHERE TABLE_NAME = ‘SALES_DATA’; |
Storage Optimization
The cost of storage is often undermined while calculating Snowflake cost but these charges can definitely hit a punch. This predictable cost should be optimized to lower your baseline Snowflake bill. Some of the most obvious things to avoid are old tables, unnecessary clones, or long retention policies. The key is to retain only what’s needed, and eliminate what isn’t.
Drop Old/Unused Tables
Run regular audits to find tables that haven’t been accessed or updated recently. Once identified, they should be archived or dropped. Snowflake also offers lifecycle management policies to handle archiving automatically using Snowflake Tasks.
You can also schedule cleanup jobs (or alerts) for stale tables over 90 days to optimize your overall Snowflake cost.
|
SELECT TABLE_SCHEMA, TABLE_NAME, ROW_COUNT, LAST_ALTERED FROM INFORMATION_SCHEMA.TABLES WHERE LAST_ALTERED < DATEADD(MONTH, -3, CURRENT_DATE()); |
NOTE: Create a backup before deleting tables or ensure that they are no longer needed.
Use Transient Tables
These tables are used when the data is required for multiple sessions but long-term retention is not needed. For example, staging environments and intermediate data processing tasks are ideal use cases for transient tables. They are also quite useful for ETL processes where data is overwritten frequently and long-term recovery is not required.
Unlike permanent tables, these tables skip fail-safe storage and time travel which can induce additional costs. Here’s how you can create a transient table on Snowflake:
|
CREATE TRANSIENT TABLE temp_load AS SELECT * FROM staging_raw_data; |
Manage Time Travel Settings
The ‘Time Travel’ feature of Snowflake allows you to maintain copies of all modifications and changes to a table made over the retention period. In most cases, we don’t need these historical versions and their only contribution is to pump the bill.
The default value of time travel is 1 day for standard tables and 90 days for enterprise accounts. The following queries can help you to change these settings.
|
ALTER TABLE transactions SET DATA_RETENTION_TIME_IN_DAYS = 0; |
|
ALTER account SET DATA_RETENTION_TIME_IN_DAYS = 0; |
Avoid Frequent DML Operations
Snowflake is quite different from operational databases and we can’t make frequent calls for updating or deleting a single record. This is because Snowflake uses immutable micro-partitions and it will have to create the entire thing again even if you changed (added, deleted, etc.) a single entry. It increases compute (and storage) charges as more and more micro-partitions are being added.
To counter this, we need to either make changes in batches or use insert-overwrite patterns to minimize processing and storage cost. Here’s how it can be done:
|
CREATE TEMP TABLE updates AS SELECT * FROM new_data; DELETE FROM target WHERE id IN (SELECT id FROM updates); INSERT INTO target SELECT * FROM updates; |
Set up Proper Governance and Monitoring
A well-planned implementation of Snowflake’s monitoring tools and access control is a great tool to optimize Snowflake cost. You can enforce limits, track usage, and set up alarms to keep an eye on your monthly bill.
Enforce Role-Based Access
Limiting access to high-cost warehouses is an incredible tool to control and optimize Snowflake cost. Unfortunately, many users don’t utilize it and had to experience a lot of unexpected costs.
Imagine a new member on the team increase the warehouse size and let it run for several hours. You don’t want that, right? So, make sure to always use hierarchical roles (dev, analyst, admin) and assign warehouse usage accordingly. This prevents accidental misuse of powerful compute which can lead to massive Snowflake bills.
|
GRANT USAGE ON WAREHOUSE prod_large TO ROLE senior_analysts; REVOKE USAGE ON WAREHOUSE prod_large FROM PUBLIC; |
NOTE: Audit your GRANTS TO ROLE periodically for over-permissive access.
Configure Resource Monitors
Resource monitors are your first line of defense to prevent excessive bills on Snowflake. They track the usage of resources across all your warehouses and trigger alarms whenever a certain threshold is misbehaving. For example, you can set up a monitor to send an alert if 90% of your monthly credits has been consumed. Similarly, you can also auto-suspend the warehouses when thresholds are hit.
|
CREATE RESOURCE MONITOR budget_guard WITH CREDIT_QUOTA = 200 TRIGGERS ON 90 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND; ALTER WAREHOUSE marketing_wh SET RESOURCE_MONITOR = budget_guard; |
NOTE: It is highly recommended to set separate monitors for ETL, analytics, and dev to segment spending.
You can also use the Account Usage data (Resource Monitor History) to track the usage of your resources. Here’s how:
|
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.RESOURCE_MONITOR_HISTORY WHERE MONITOR_NAME = ‘BUDGET_GUARD’; |
Snowflake cost optimization is critical and the easiest way to implement an effective strategy is to have all around approach. Start early and keep monitoring your usage to optimize the resources as well as queries to get the best possible results. It will prevent the unnecessary charges and make your task to optimize Snowflake cost much easier.