How Your "Experts" Have Been Misleading You About Snowflake Auto Suspend Settings

Reducing excessively large Auto Suspend intervals in Snowflake is commonly recommended for cost savings. However, many internet experts advocate extremely low values, such as 10, 30, or 60 seconds, oversimplifying the nuanced relationship between Auto Suspend settings and cost management. This approach frequently leads to unintended higher costs.

Why Short Auto Suspend Intervals Can Backfire

At first glance, shorter intervals appear logical, promising reduced idle costs. Yet, excessively short intervals often result in frequent cache clearing, forcing queries to read from slower, more expensive cold storage.

The Hidden Costs of Cache Misses in Snowflake

Frequent cache loss significantly impacts performance and expenses. Aggressively suspending your warehouse might save some idle costs, but repeatedly reloading data from cold storage quickly negates those savings.

The Nuance Ignored in Typical FinOps Advice

Optimal Auto Suspend intervals fluctuate due to dynamic query patterns, batch processes, and user activity. Static intervals rarely align perfectly with varying workload demands, often leading to higher expenses.

Keebo’s AI-Driven Dynamic Optimization: The Optimal Solution

Keebo’s patented AI algorithm continuously calculates the ideal Auto Suspend interval based on real-time query data, optimizing for minimal cost and maximal performance. Below is a screenshot of how Keebo adjusts that in real-time based on AI. In this particular example, Keebo determined that at 6:10am the optimal time to suspend this particular warehouse was after 251 seconds but later it deemed it unnecessary and drastically reduced the auto suspend value.

Keebo using AI to optimize memory — Real-world example of how Keebo uses AI to optimize the memory (caching) by dynamically adjusting when to suspend a warehouse based on current load

Illustrative Example: Comparing Auto Suspend Strategies

Let’s assume we have three queries, Q1, Q2, and Q3, arriving at 9:01 AM, 9:03 AM, and 9:10 AM respectively. Each query takes 1 minute if its data is already in the local cache of the warehouse and takes 4 minutes if it must read data from cold storage.

Scenario 1: Fixed 5-Minute Interval

Query Q1 arrives at 9:00 AM and finishes at 9:01 AM (cached). Warehouse remains active for 5 minutes. Query Q2 arrives at 9:03 AM, hitting an active warehouse and finishing at 9:04 AM (cached). Warehouse remains active until 9:09 AM. Query Q3 arrives at 9:10 AM, hitting a suspended warehouse, finishing at 9:14 AM (cache miss). Warehouse remains active until 9:19 AM.

Total Cost: 18 minutes (9:00-9:09 and 9:10-9:19)

Cache Misses: 1 (Q3)

Avg Execution Time: (1+1+4)/3 = 2.0 mins/query

Scenario 2: Fixed 1-Minute Interval

Query Q1 arrives at 9:00 AM, finishes at 9:01 AM. Warehouse suspends at 9:02 AM. Query Q2 arrives at 9:03 AM, hits suspended warehouse, finishes at 9:07 AM (cache miss). Warehouse suspends at 9:08 AM. Query Q3 arrives at 9:10 AM, hits suspended warehouse, finishes at 9:14 AM (cache miss). Warehouse suspends at 9:15 AM.

Total Cost: 12 minutes (9:00-9:02, 9:03-9:08, 9:10-9:15)

Cache Misses: 2 (Q2, Q3)

Avg Execution Time: (1+4+4)/3 = 3.0 mins/query

Scenario 3: Keebo Dynamic Optimization

Query Q1 arrives at 9:00 AM, finishes at 9:01 AM (cached). Keebo predicts warehouse should stay active until shortly after 9:03 AM. Query Q2 arrives at 9:03 AM, hits an active warehouse, finishes at 9:04 AM (cached). Keebo suspends the warehouse immediately. Query Q3 arrives at 9:10 AM, hits suspended warehouse, finishes at 9:14 AM (cache miss). Keebo suspends immediately afterward.

Total Cost: 8 minutes (9:00-9:04 and 9:10-9:14)

Cache Misses: 1 (Q3)

Avg Execution Time: (1+1+4)/3 = 2.0 mins/query

Summary

The following picture visualizes these 3 scenarios for a better comparison:

Snowflake auto suspend — Summary comparison of static versus dynamic auto suspend strategies

The table below summarizes these 3 example scenarios in terms of total cost (number of minutes the warehouse is running or idle) and performance. For performance, the table is showing the number of queries that hit a suspended warehouse and thus could not benefit from the cache as well as the average execution time of the 3 queries.

Scenario	Total Cost (minutes)	# Cache Misses (Queries)	Avg Execution Time (minutes/query)
Fixed 5-Minute Interval	18	1 (Q3)	2.0
Fixed 1-Minute Interval	12	2 (Q2, Q3)	3.0
Keebo Dynamic Optimization	8	1 (Q3)	2.0

As you can see from this toy example, dynamically adjusting the auto suspend value beats any static strategy both in terms of overall cost and in terms of preserving performance. For example, you can see how using a very low auto suspend value throughout the day (Scenario 2) can actually increase the costs compared to the dynamic approach.

While this is a contrived example, the goal is to visualize why no static value (whether large or small) is optimal. The next question then is how can Keebo predict when the next query arrives and how can it calculate the optimal time to suspend the warehouse if the workload is unpredictable and changes every day. The short answer is AI. The long answer we will leave for another post. For now, if you want to try it out yourself and see how much Keebo’s AI can help you save and improve performance by dynamically adjusting your Snowflake throughout the day, feel free to reach out.