3. Decrease the sync frequency of your data ingestion tool
Previously, I would only run data syncs once every morning using a data ingestion tool like Airbyte, when my data pipeline was automated to run. Then, I had a need for semi real-time data within Snowflake. After increasing data syncs in Airbyte to once every 15 minutes, Snowflake costs skyrocketed. Because data was being ingested every 15 minutes, the data warehouse was almost always running. Rather than it running for an hour or so each day, it was running for 24 hours a day.
It’s important to assess how frequently you need fresh data and data models in your data warehouse. If your business is depending on them at a certain frequency, it is probably worth the extra cost. However, if you are running these syncs multiple times a day when nobody is using fresh data, you are quite literally throwing dollars out the door. Look at your most important dashboards and see how often they are refreshed or checked by business teams. Then talk to your analyst and ask them about the data they work with on a daily basis. Is it necessary to have hourly or multiple data syncs per day?
4. Decrease the warehouse’s auto-suspend period
Decreasing the warehouse’s auto suspend period is the tip that has had the biggest impact on my Snowflake costs. Like I mentioned earlier, I have multiple warehouses running at a time, each for different processes. Because of this, every minute counts. When you create a new virtual warehouse, you have the option to set an auto-suspend period. The lowest default value they provide for you on the UI is 5 minutes. This is actually a long time if you have multiple processes running.
Every minute is valuable to optimize your Snowflake costs! If you create your warehouse manually, rather than in the UI, you can actually set your own auto-suspend time.
alter warehouse DBT_WH set auto_suspend=30;
I highly recommend setting it for 30 seconds. This is just long enough for back-to-back queries to execute without the warehouse being shut down but short enough so that you aren’t paying for extra downtime.
Now if you look at your warehouses on the UI, you can see that the “Auto Suspend” time is 30 seconds rather than 5 minutes.
5. Use materialized views
Optimizing the run time of your Snowflake queries is one way to reduce your Snowflake costs. Materialized views in Snowflake are only executed against all the data in a table once. After executing against all the available data on its initial run, it only runs against new data added. It does this by utilizing INSERT and UPDATE commands.
This is powerful because of the potential it has to save you in compute costs. Because the query isn’t being executed on all of the data, just new data, it takes a lot less time to run. Less run time means less time a warehouse needs to be active, saving you costs.
Materialized views can only be utilized on data models that use simple SELECT statements rather than those that use joins or aggregation. I highly recommend using them on your base or staging dbt models, since these are basic select statements from your raw data tables.
6. Change your Snowflake account plan
Lastly, you can always reevaluate the account plan that you are using. Depending on your company size and the amount of data you use, enterprise plans may not make sense for you. I’ve seen a lot of people get roped into long-term contracts, thinking they were getting a discount, but in reality, they didn’t use nearly as many credits as they were paying for. Before signing anything, make sure you have a clear understanding of your monthly and yearly usage. Sometimes it may be better to test Snowflake out for a year and then potentially sign a contract if that makes sense for you. If you have no idea how many credits you will be using each month, don’t lock yourself into anything for a small discount. You will probably end up losing money in the long run!
Conclusion
The best thing you can do to optimize costs in Snowflake is try new things! You definitely won’t save any money if you keep your structure the way things currently are. In this article we covered six ways to optimize your Snowflake costs:
Change your warehouse size
Decrease the number of warehouses running at the same time
Decrease the sync frequency of your data ingestion tool
Decrease the warehouse’s auto-resume period
Use materialized views
Change your Snowflake account plan
Pick a few of these Snowflake optimization tips that you can easily implement and keep track of how they perform over the course of a few days. It’s all a balancing game between cost and performance. Performance may suffer, or it may soar while saving hundreds of dollars a week. You don’t know unless you try!
The beauty of the modern data stack is that it’s so easy to try new things. You don’t have any physical location or hardware you have to deal with. With a simple click of a button you can change the compute power of your entire data stack. And, you can change it right back a few days later! Take advantage of this in order to save whatever costs you can. There’s nothing that a business loves more than hearing about money saved or money made. Good luck!
The data movement infrastructure for the modern data teams.
