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 compute 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. Its incredibly easy and low cost to setup 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 really high and its 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 setup correctly. Some Snowflake accounts have them setup but do not have them setup 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 to have 1 Resource Monitor set for Daily monitoring for each and every warehouse with auto suspend enabled once a credit limit is reached. This is currently the only real solution to having guard rails on your Snowflake consumption. Without doing this you are exposing your company and Snowflake account to significant cost risk.
Another anti-pattern related to Resource Monitors that we see too frequently is Administrators do not want to be responsible for stopping the compute so they setup 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 setup Resource monitors BUT do not attach them to a warehouse. This is the same as having a guard rail but its not activated. Ugh!
Finally, we also see Resource Monitors get setup 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 really 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 90 day time-travel set and you are changing a table with large amounts of rows/size every few minutes or hours than its going to add up as all of those immutable micro-partions for every changes 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 its 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 than this isn’t incredibly horrible but if its a larger size the costs can grow very fast and you lose all of the value of Snowflake’s consumption based pricing.
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.
Conclusion for Snowflake Cost Anti-patterns:
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 Optimizations by your team or a consulting provider especially setting up Resource Monitors IMMEDIATELY or at least in the same data as your Snowflake Account is provisioned.