What is Cold and Hot virtual warehouses ?

                            The virtual warehouse in snowflake goes through various states. The states are cold warehouse, warm warehouse, and Hot warehouse. In this article, we will learn about what does each state of virtual warehouses in snowflake. We will also see what impact of states of virtual warehouses on query performance.

A) What is a virtual warehouse in snowflake?

                            The virtual warehouse in snowflake is a cluster of computing resources that are used to perform activities such as DML operations and SQL execution. The compute resources consist of temporary storage, memory, and CPU. The majority of expenses occur due to the use of a virtual warehouse or compute layer.

B) What are the states of the virtual warehouse in snowflake?

                             The virtual warehouses go through various states and these are -

                            a) Cold virtual warehouse 

                            b) Warm virtual warehouse 

                            c) Hot virtual warehouse 

                      In the next section, we will more about all these states of the virtual warehouse.

C) Cold virtual warehouse :

                             Starting a new virtual warehouse without using local disk cashing and executing the query is called a cold virtual warehouse. When our virtual warehouse is suspended or not active and if we run the query then it will start a new instance of the virtual warehouse.

D) Warm virtual warehouse : 

                             It is the state of the virtual warehouse during which the virtual warehouse is active and running for a while and processed queries. Assume that, the virtual warehouse is in a warm state and we disable the result cache and execute the query then it will use local disk caching. This caching is called warm caching.

E) Hot virtual warehouse :

                            The hot virtual warehouse is a state of virtual warehouse during which the virtual warehouse is active and running for a while and processed queries. However, in this case, the result cache is on. If we execute the query in this state, the query result is returned from the result cache. This is the most efficient operation.

F) Impact of virtual warehouse state on query performance 

                   1) Cold warehouse 

                                The query takes longer than a warm and hot virtual warehouse. It uses a remote disk. Local disk cache is not used. Result cache is not used.

                   2) Warm warehouse 

                               The query takes lesser than a cold warehouse but more than a hot virtual warehouse. It does not use a remote disk, however, it uses a local disk. It does not use result cache.

                  3) Hot virtual warehouse 

                               The query takes lesser time for execution than a cold virtual warehouse and a warm virtual warehouse. It does not use both remote disk and local disk cache. The result is returned using the result cache. from the cloud services layer. It is the most efficient way of getting the result of the query.

                         Learn more about snowflake here -