Warehouse auto-suspend in Snowflake

Whether you’re just starting in Snowflake, or are an experienced administrator, you’ll likely seek tools and strategies to maintain a cost-effective and efficient infrastructure. One of the best practices for optimizing warehouses for cost and performance is setting the right auto-suspend time by paying attention to warehouse trends. Capital One has seen great cost benefits in reducing the default auto-suspend time for particular warehouses. First, users will need to learn how to optimize their warehouse settings.

Snowflake warehouses consume credits per second, and a warehouse runs with a minimum charge of one minute. The size and number of compute resources determine the amount of credits charged for a warehouse. This table from Snowflake represents how many total credits will be used for an hour based on the warehouse size–beginning with an X-Small warehouse consuming one credit up to a 2X-Large warehouse using 32 credits.

An idle warehouse consumes Snowflake credits while running, even without actively running workloads or processing queries. Ideally, you want to shut down additional charges unless you expect a steady workload of queries in the same warehouse, where you can take advantage of reusing cache.

To help minimize the cost of unused warehouses, Snowflake offers an auto-suspend setting. Warehouse auto-suspend automatically stops idle warehouses from consuming credits by temporarily shutting down all running servers if there’s no activity for a specified time. Servers will resume running when a new workload has been submitted to the warehouse and compute resources are needed.  

The default time setting for auto-suspend is 10 minutes and is enabled automatically. Given the default setting, even if you run a small query that takes less than one minute, you’ll be billed for 11 minutes of warehouse usage.

Snowflake administrators might need assistance understanding the ideal auto-suspend time, especially since there may be different optimal auto-suspend times throughout the day or week. This leads to unnecessary costs during periods where a warehouse may be idle.

Factors that might impact auto-suspend time: 

• Warehouse Idleness - How long, on average, is your warehouse sitting idle? 

• Warehouse Suspension Events - How frequently are idle periods getting resumed or suspended?

Faced with a similar challenge, we created Capital One Slingshot, a tool designed to optimize Snowflake investments, enhance efficiency and reduce costs. Slingshot’s Slingshot has an idleness widget showing the general trend of idleness and suspension events across your warehouses over time. 

Slingshot now factors in optimal auto-suspend time and provides recommendations based on optimal warehouse size and clusters. Furthermore, Slingshot can identify opportunities to reduce idle time in a warehouse for a given hour or set of hours in the week. In that case, a change in auto-suspend will be recommended for that hour in the overarching recommended schedule.

If you are paying for warehouses that are sitting idle, scheduling a warehouse with auto-spend times in smaller increments will decrease idleness, reduce Snowflake credit usage and ultimately save on costs. 

Capital One Slingshot can provide insights and recommendations to help Snowflake admins make informed decisions when optimizing warehouse usage.