Snowflake is still my favorite Analytical Database since the beginning of 2018 but as I often present in my live training sessions and webinars, WITH GREAT POWER (practically unlimited computing scale) comes GREAT RESPONSIBILITY.
In this article, I’ll cover the TOP 3 Snowflake Cost Anti-patterns my Snowflake Cost Optimization team and I have come across after 3 years of analyzing hundreds of Snowflake Accounts. I cannot begin to state how you should either invest in a PART or FULL-TIME Snowflake DBA focused on cost and organization or if you do not have that financial luxury then use our automated Snowflake Cost Optimization Service – Snoptimizer. It’s incredibly easy and low-cost to set up Snoptimizer compared to having these anti-patterns manifest (which I know happens on too many Snowflake Accounts based on our review of hundreds of them). If you do not have cost guardrails like Resource Monitors enabled your Snowflake Compute Consumption Risk is high and it’s honestly gross negligence as a data administration professional to allow this.
Let’s go through the TOP 3 Snowflake Cost Anti-patterns.
Top 3 Anti-Patterns
The first Snowflake anti-pattern is by far the worst and happens all too often.
Snowflake Cost Anti-pattern #1
Sadly, we all too often see that Resource Monitors are not set up correctly. Some Snowflake accounts have them set up but do not have them set up at an effective grain. One anti-pattern is that the administrator sets a large credit-sized resource manager for the overall account and no other resources managers. It is okay to have some Resource Monitors cover the account or multiple warehouses but we highly recommend having 1 Resource Monitor set for Daily monitoring for each warehouse with auto-suspend enabled once a credit limit is reached. This is currently the only real solution to having guardrails on your Snowflake consumption. Without doing this you are exposing your company and Snowflake account to significant cost risk.
An additional anti-pattern related to Resource Monitors that we see too frequently is Administrators do not want to be responsible for stopping the computer so they set up Resource Monitors with ONLY notifications. The problem with this is that notifications are just that…ONLY something to notify you. What if you only have 1-2 Snowflake Account Administrators and they are not monitoring the emails or web notifications frequently enough and a Large to 6XL warehouse comes online without auto suspend enabled?
Another problem as well is that Snowflake Administrators set up Resource monitors BUT do not attach them to a warehouse. This is the same as having a guardrail but it’s not activated. Ugh!
Finally, we also see Resource Monitors get set up but then Account Administrators who do not enable their email or notifications correctly.
Snowflake Cost Anti-pattern #2
Another major Snowflake Cost anti-pattern is related to storage. We do not see this nearly as often as #1 but it can also be a cost risk danger if you do not understand the impacts of enabling longer Time-Travel settings on Snowflake. If you have many of your tables with time-travel set to 30,60, or 90 days, but you don’t need that much time travel and will never use it, then you should change those configurations to lower time-travel settings.
There are similar potential problems with any table that is frequently updating and changing data. These types of data tables will challenge Snowflake’s architecture because every data change requires recreating micro-partitions. So if you have a 90-day time-travel set and you are changing a table with large amounts of rows/sizes every few minutes or hours then it’s going to add up as all of those immutable micro-partitions for every change are saved for 90 days. Also, remember by default Snowflake forces a 7-day fail-safe of storage. So if you have Time-Travel set to 90 days then it’s 97 days of storage you will pay for.
Snowflake Cost Anti-pattern #3
Setting a Warehouse to “Never” auto suspend or high auto suspend settings. If you set a Warehouse to never suspend then you are creating a never-ending spend on a warehouse until you manually or through code suspend it. If the warehouse size is only XS then this isn’t incredibly horrible but if it’s a larger size the costs can grow very fast and you lose all of the value of Snowflake’s consumption-based pricing.
Conclusion for Snowflake Cost Anti-patterns:
These are the top 3 most dangerous Snowflake Cost Anti-patterns we have come across. There are many others but they are typically not as severe as these. These Snowflake Cost Anti-patterns are real and introduce your company and yourself to sizable cost risks. This is why we recommend using Snoptimizer or enabling Snowflake Best Practice Cost Optimization by your team. Especially set up Resource Monitors IMMEDIATELY or at least in the same data as your Snowflake Account is provisioned.
