Skip to main content

πŸ—οΈ 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​

FeatureDescription
Cloud-nativeUses S3 / Blob / GCS internally
Stores all dataRaw, staged, transformed
Supports formatsJSON, Parquet, Avro, CSV
Global availabilityAccessible 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 SizeNodes
X-Small1
Small2
Medium4
Large8
X-Large16
2XL32
3XL64
4XL128

βœ” 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 SizeNodes
X-Small1
Small2
Medium4
Large8
X-Large16
2XL32
3XL64
4XL128

πŸ‘₯ Real-World Scenario (Team-Based Warehouse Setup)

TeamWorkloadRecommended Warehouse
MarketingAd performance analyticsMedium
FinanceDaily dashboardsSmall
Data EngineeringNightly ETLLarge
ML/Data ScienceTraining & Feature jobsXL 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 LayerCompute Layer (Warehouse)
Stores dataProcesses data
Always ONOn-demand
CheapCostly (compute-heavy)
SharedIsolated
Infinite scaleVertical + 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_WH
  • BI_WH
  • DS_WH
  • ADHOC_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.