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). Maybe 97% of the time you will never run into trouble but if you don’t have cost guardrails like Resource Monitors enabled your Snowflake Cost 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, I do not know how many Snowflake Accounts our Performance and Cost Optimization Team are brought into that do NOT have Resource Monitors setup correctly. Some Snowflake accounts have them but do not have them setup appropriately at an effective grain. One anti-pattern is that the administrator sets a large resource manager for the account. 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 some type of 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….Something to notify you. What if you only have 1-2 ACCOUNT ADMINS and they are not monitoring the emails or web notifications frequently enough and a 6XL warehouse comes online.
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 ACCOUNTADMINs 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 tables that have lots of
There are similar potential problems with any table that is frequently updating and changing data. These types of data structures 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. 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 including all the re-created micro-partitions over that time.
Snowflake Cost Anti-pattern #3
Setting a Warehouse to “Never” auto suspend. We rarely see this anymore but we did see it more often than you would expect. If this is ever enabled you basically are creating a never ending spend on a warehouse until you manually either run ALTER WAREHOUSE XYZ_NAME SUSPEND; or do the equivalent from the Snowflake Web UI. 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 I do not know of a use case that really would justify this setting.
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.