ποΈ Introduction to Virtual Warehouses in Snowflake
Virtual Warehouses (VWs) are Snowflakeβs compute engines β responsible for executing SQL queries, ELT/ETL pipelines, transformations, BI reports, ML processing, and more.
They operate independently from Snowflake storage, enabling unlimited scalability, high concurrency, and workload isolation.
π Snowflake Architecture: Storage vs Compute
Snowflakeβs core innovation is the complete separation of storage and compute.
π High-Level Architectureβ
β One copy of data β Multiple warehouses accessing it β No contention, no duplication
πΎ Storage Layer β Where Data Lives
Snowflake storage is:
- Highly scalable
- Encrypted
- Columnar micro-partitioned
- Fully managed
- Cheap
- Shared across all warehouses
π Key Capabilitiesβ
| Feature | Description |
|---|---|
| Cloud-native | Uses S3 / Blob / GCS internally |
| Stores all data | Raw, staged, transformed |
| Supports formats | JSON, Parquet, Avro, CSV |
| Global availability | Accessible from any warehouse |
π Important: Storage never pauses β only compute does.
βοΈ Virtual Warehouses β Where Processing Happens
A Virtual Warehouse performs all compute work:
- Running queries
- Joining/aggregating data
- Loading files
- Materialized view maintenance
- ELT pipelines
- ML feature engineering
Each warehouse is isolated, providing dedicated compute power for its workload.
π§ Virtual Warehouse Architecture Diagram
π§© Warehouse Size β Node Count
Snowflake warehouses scale by adding more compute nodes, which increases parallelism.
| Warehouse Size | Nodes |
|---|---|
| X-Small | 1 |
| Small | 2 |
| Medium | 4 |
| Large | 8 |
| X-Large | 16 |
| 2XL | 32 |
| 3XL | 64 |
| 4XL | 128 |
β Warehouses scale by adding/removing compute nodes internally.
β You never manage nodes β Snowflake does.
π§© Storage & Compute Interaction (Lifecycle)
π§ Virtual Warehouse Architecture Diagram
π§© Warehouse Size β Node Count
Snowflake warehouses scale by adding more compute nodes, which increases parallelism.
| Warehouse Size | Nodes |
|---|---|
| X-Small | 1 |
| Small | 2 |
| Medium | 4 |
| Large | 8 |
| X-Large | 16 |
| 2XL | 32 |
| 3XL | 64 |
| 4XL | 128 |
π₯ Real-World Scenario (Team-Based Warehouse Setup)
| Team | Workload | Recommended Warehouse |
|---|---|---|
| Marketing | Ad performance analytics | Medium |
| Finance | Daily dashboards | Small |
| Data Engineering | Nightly ETL | Large |
| ML/Data Science | Training & Feature jobs | XL or Snowpark-Optimized |
Each team gets its own dedicated compute β no interference.
βοΈ Warehouse Scaling Models
1οΈβ£ Vertical Scalingβ
Increasing warehouse size:
XS β S β M β L β XL β 2XL β 3XL β 4XL β 5XL
β More CPU
β More memory
β Faster queries
2οΈβ£ Horizontal Scaling (Multi-Cluster)β
Automatically add clusters during concurrency spikes.
CREATE WAREHOUSE analytics_wh
WAREHOUSE_SIZE = 'LARGE'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 4
SCALING_POLICY = 'ECONOMY';
β Best for BI dashboards β Eliminates queueing β Ensures stable performance
π Auto-Suspend & Auto-Resume (Must-Use!)
Save cost by shutting down idle compute.
CREATE WAREHOUSE my_wh
WAREHOUSE_SIZE = 'MEDIUM'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;
β Never worry about forgetting to turn off compute
β Most cost-efficient setting
π Storage vs Compute β Quick Comparison
| Storage Layer | Compute Layer (Warehouse) |
|---|---|
| Stores data | Processes data |
| Always ON | On-demand |
| Cheap | Costly (compute-heavy) |
| Shared | Isolated |
| Infinite scale | Vertical + horizontal scaling |
π§ Best Practices for Virtual Warehouses
β 1. Always Enable Auto-Suspendβ
Use 30β120 seconds for most workloads.
β 2. Separate Warehouses by Workloadβ
Examples:
ETL_WHBI_WHDS_WHADHOC_WH
β 3. Use Multi-Cluster for High-Concurrency BIβ
Prevents dashboard queues.
β 4. Use Appropriate Sizesβ
Use XS/S for small queries
Use M/L for ETL pipelines
Use XL+ for ML workloads
β 5. Monitor Credit Usageβ
Use Resource Monitors to avoid surprise costs.
π― Final Takeaway
βοΈ Storage is centralized. Compute is independent. Warehouses are the engine powering everything you do in Snowflake.
Virtual Warehouses make Snowflake:
β Highly scalable
β Cost efficient
β Concurrency friendly
β Easy to manage
β Perfect for ELT/ETL, BI, and ML workloads
Mastering them is essential for every Data Engineer and Snowflake Architect.