Snowflake - Object Hierarchy
Organization
Account
Users Roles Database Warehouses
Schema
Tables Views Stage Stored Procedures UDF
Snowflake - Virtual warehouses
- Warehouse is a cluster of computing resources
- A warehouse is a combination of resources like CPU, memory, and temporary storage required to do multiple
operations
- The Operations include :
- Executing sql queries
- Data loading/unloading
- Warehouses come in sizes ranging from XS to 5XL
- Warehouse can be of 2 types :
- Single Cluster Warehouse
- Multi Cluster Warehouse
Snowflake - Virtual warehouse Configuration
- Max Cluster is the maximum number of warehouses that can be spun up during an operation
- Min Cluster is the default number of warehouses that Snowflake will spin up while creating a
warehouse
- Single Cluster Warehouse : Min Cluster = 1 , Max Cluster = 1
- Multi Cluster Warehouse : Min Cluster >= 1 , Max Cluster > 1
Snowflake - Multi Cluster warehouse modes
- Multi-cluster warehouse can be executed in two modes :
- Maximised : Min Clusters = 2 ,Max Cluster = 2
- AutoScale : Min Clusters = 1 ,Max Cluster = 2
- Scaling Policy :
- Standard (Default) : Prevents queuing of operations by starting additional warehouses
- Economy : Conserves credits by favoring keeping running warehouses fully-loaded
rather than starting additional warehouses
Multi Cluster Warehouse | Scaling Policy - Standard
Min Cluster = 1, Max Cluster = 3
SQL Query-1
WH1
X-Small SQL Query-2
Multi Cluster Warehouse | Scaling Policy - Standard
Min Cluster = 1, Max Cluster = 3
SQL Query-1
WH1
X-Small SQL Query-2
Wait 20 seconds
Complex SQL Query
Multi Cluster Warehouse | Scaling Policy - Standard
Min Cluster = 1, Max Cluster = 3
SQL Query
WH1
X-Small SQL Query
New WH
Complex SQL Query1
WH2
X-Small
Multi Cluster Warehouse | Scaling Policy - Standard
Min Cluster = 1, Max Cluster = 3
SQL Query
WH1
X-Small SQL Query
New WH
Complex SQL Query1
WH2
40 Seconds later
X-Small
Complex SQL Query2
Scaling Policy - Standard
Min Cluster = 1, Max Cluster = 3
SQL Query
WH1
X-Small SQL Query
Complex SQL Query1
WH2
X-Small
Complex SQL Query2
Wait 20 seconds
1 minute later
Huge Data ingestion
Pipeline
Scaling Policy - Standard
Min Cluster = 1, Max Cluster = 3
SQL Query
WH1
X-Small SQL Query
Complex SQL Query1
WH2
X-Small
Complex SQL Query2
New WH
WH3 1 minute later
Huge Data ingestion
X-Small Pipeline
Scaling Policy - Economy
Min Cluster = 1, Max Cluster = 3
SQL Query 1
WH1
SQL Query 2
X-Small
Will the warehouse be busy for at least
6 minutes?
Complex SQL Query1
Scaling Policy - Economy
Min Cluster = 1, Max Cluster = 3
SQL Query
WH1
X-Small SQL Query
Yes,warehouse will keep busy for at least 6 minutes
New WH
Complex SQL Query1
WH2
40 Seconds later
X-Small
Complex SQL Query2
Scaling Policy - Economy
Min Cluster = 1, Max Cluster = 3
SQL Query
WH1
X-Small SQL Query
Complex SQL Query1
WH2
40 Seconds later
X-Small
Complex SQL Query2
Will the warehouse be busy for at least
6 minutes? 1 minute later
Huge Data ingestion
Pipeline
Scaling Policy - Economy
Min Cluster = 1, Max Cluster = 3
SQL Query
WH1
X-Small SQL Query
Complex SQL Query1
WH2
40 Seconds later
X-Small
Complex SQL Query2
New WH Yes,the warehouse will be busy for at
WH3 least 6 minutes
1 minute later
Huge Data ingestion
X-Small Pipeline
