One of Snowflake’s key features is something called multi-cluster warehouses.
By default, a virtual warehouse consists of a single cluster of servers that determines the total resources available to the warehouse for executing queries. As queries are submitted to a warehouse, the warehouse allocates resources to each query and begins executing the queries. If sufficient resources are not available to execute all the queries submitted to the warehouse, Snowflake queues the additional queries until the necessary resources become available.
With multi-cluster warehouses, Snowflake supports allocating a larger pool of resources to each warehouse. As the number of concurrent user sessions and/or queries for the warehouse increases, and queries start to queue due to insufficient resources, Snowflake automatically starts additional clusters, up to the maximum number defined for the warehouse.
Similarly, as the load on the warehouse decreases, Snowflake automatically shuts down clusters to reduce the number of running servers and, correspondingly, the number of credits used by the warehouse.
As you can imagine, this capability is very useful to maintain a consistent response time for users in BI and reporting scenarios where varying user loads are common. Imagine that you normally have 5 concurrent users on your system but on Monday mornings, you have a spike of 20 concurrent users, all wanting to view reports for the weekend’s business. A multi-cluster warehouse can ensure that users during the peak load experience the same response times (from the DB that is… saturation of the BI server is a separate factor) as users during non-peak periods.
To show this capability in action, I created a dashboard that queries against about 57M records of Citibike data, hosted in a Snowflake DB. I published this dashboard to Tableau Server and then used TabJolt to simulate a) a single user baseline, and then b) a peak period with multiple concurrent users. Note that caching was turned off both in Tableau Server and in the Snowflake data source to ensure that all queries were actually run in the DB.
You can see this experiment and the results in the following video. Apologies for just providing a download link, but I tried posting this to Vimeo but the video quality was terrible. That’s most likely my fault, but right now this is an easier way to share: