Snowflake Architecture: Centralized Storage, Virtual Compute & Cloud Services

[!NOTE] Snowflake's true multi-tenant cloud-native architecture completely decouples compute from storage across three distinct layers:

1. Centralized Storage: Stores data in proprietary columnar encrypted micro-partitions across cloud object storage.
2. Multi-Cluster Virtual Compute: Independent, elastic compute clusters (Virtual Warehouses) that can be sized, suspended, and scaled without impacting running queries.
3. Cloud Services Layer: Manages authentication, metadata, query optimization, ACID transactions, and security.

1. Virtual Warehouse Scaling: Scale-Up vs Scale-Out

• Scale-Up (Vertical): Change warehouse size (X-Small to 6X-Large) to give large, complex queries more memory, CPU, and local SSD cache.
• Scale-Out (Horizontal / Multi-Cluster): Add warehouse clusters (min/max clusters) with Auto-Scaling policies (Standard vs Economy) to handle hundreds of concurrent BI users without query queueing.

Staff Architecture: Three-Layer Decoupled Cloud, Zero-Copy Cloning & Compute Scaling

• Storage vs Compute vs Cloud Services: Storage resides on compressed immutable micro-partitions in S3/ADLS/GCS (50–500 MB uncompressed converted to ~16 MB compressed columnar files); Compute (Virtual Warehouses) provides isolated stateless MPP clusters (XS -> 6XL) with local SSD query caches; Cloud Services handles auth, query optimizer/pruning, transaction coordination, and Result Cache (24-hour free hits!).
• Scaling Up vs Scaling Out (Multi-Cluster Warehouses): Scale UP (Medium -> X-Large) for single massive MERGE/sort workloads spilling to Remote Storage; Scale OUT (MIN_CLUSTER_COUNT=1, MAX_CLUSTER_COUNT=6, SCALING_POLICY=ECONOMY/STANDARD) for high BI concurrency so dashboards never wait in Queued state.
• Zero-Copy Cloning (CREATE SCHEMA dev CLONE prod): Clones copy zero storage bytes initially—metadata pointers reference shared immutable micro-partitions with copy-on-write deltas, making full production QA environments instant and practically free.