Snowflake warehouse default auto-suspend time

urn:js:virtue:aspire:standard:9.1

TL;DR

The default warehouse auto-suspend time is 59 seconds.

Definition

Where Snowflake Warehouses have Auto-Suspend times set for longer than necessary, will occur whilst these warehouses sit idle waiting for the timeout to occur.

Set a blanket auto-suspend time on the warehouses to be the minimum of 59 seconds
Create XS, S and M ad-hoc warehouses and set XS as the default for all users

We apply a blanket change to all of the Warehouses in snowflake to reduce the auto-suspend times to the minimum values. Where use cases are shown to benefit from the local warehouse cache, then these specific cases can be assigned to warehouses with longer auto-suspend times configured. It may be worth setting up new warehouses that these specific workloads are directed against in this case. This prevents the longer auto-suspend configuration causing increased costs when the warehouse is used for other workloads within the team that do not benefit from the increaed auto-suspend time.

Good, because …

cost saving in reduced idle warehouse time when waiting for auto-suspend to kick in. On larger warehouses & clusters this cost can be significant. Bad, because…
Some queries are likely to cost more because they utilise the cache on the warehouse, however these should be the exception rather than the rule
Teams that have queries that fall into the above category will have to request a new Warehouse to run these queries against
So that the number of ‘exception’ warehouses does not get out of control, there should be a process of documenting and approving these so that we can check their use is valid, continued existance required etc…

Implications

Warehouses will suspend and clear their cache after 1 minute.
Some queries which heavily rely on the cache may take longer to run, however…
there are other cache-levels that can be utilized-the cache is only lost if there is a > 1 min gap between queries on the warehouse

Appendix

Migrated From Confluence

link Original Author : James, Goodhand