How does Snowflake do instantaneous resizes?

user_651174 0
Viewed 160

I was using Snowflake and I was surprised how it is able to do instantaneous resizes. Here is a very 10s video of how it instantly does a resize, and the query is still 'warm' the next time it is run (Note I have a CURRENT_TIMESTAMP in the query so it never returns from cache):

enter image description here

How is Snowflake able to do instantaneous resizes (completely different than something like Redshift)? Does this mean that it just has a fleet of servers that are always on, and a resize is just a virtual allocation of memory/cpu to run that task? Is the underlying data stored on a shared disk or in memory?

snowflake-cloud-data-platform data-warehouse
2 Answers

To answer your question about resizing in short: Yes, you are absolutely right.

As far as I know Snowflake manages a pool of running servers in the background. All customers can be assigned something from here. Consequence: A resize for you from S to XS is a reallocation of a server from that pool.

Most probably the Virtual Private Snowflake-Edition behaves differently as those accounts don't share resources (e.g. Virtual Warehouses) with other accounts (outside that VPS). More infos: https://docs.snowflake.com/en/user-guide/intro-editions.html#virtual-private-snowflake-vps

Regarding your storage-question: Snowflake's storage layer is basically a storage service, e.g. Amazon S3. In here Snowflake saves the data in columnar format, to be more presice in micro-partitions. More information regarding micro-partitions can be found here: https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions.html

Your virtual warehouse accesses this storage layer (remote disk) or - if the query was run before - a cache. There are a local disc cache (this is your virtual warehouse using SSD-storage) and a result cache (available across virtual warehouses for queries within the last 24 hours): https://community.snowflake.com/s/article/Caching-in-Snowflake-Data-Warehouse

user_124804310

To extend the existing answer, ALTER WAREHOUSE in standard setup is non-blocking statement, which means it returns control as soon as it is submitted.

ALTER WAREHOUSE

WAIT_FOR_COMPLETION = FALSE | TRUE

When resizing a warehouse, you can use this parameter to block the return of the ALTER WAREHOUSE command until the resize has finished provisioning all its servers. Blocking the return of the command when resizing to a larger warehouse serves to notify you that your servers have been fully provisioned and the warehouse is now ready to execute queries using all the new resources.

Valid values

  • FALSE: The ALTER WAREHOUSE command returns immediately, before the warehouse resize completes.

  • TRUE: The ALTER WAREHOUSE command will block until the warehouse resize completes.

Default: FALSE

For instance:

ALTER WAREHOUSE <warehouse_name> SET WAREHOUSE_SIZE = XLARGE WAIT_FOR_COMPLETION = TRUE;

EDIT:

The Snowflake Elastic Data Warehouse

3.2.1 Elasticity and Isolation VWs are pure compute resources.

They can be created,destroyed, or resized at any point, on demand. Creating or destroying a VW has no effect on the state of the database. It is perfectly legal (and encouraged) that users shut down all their VWs when they have no queries. This elasticity allows users to dynamically match their compute resources to usage demands, independent of the data volume.

user_50708790
Related