Snowflake Architecture — Virtual Warehouses and Stages — Data Warehouse & Analytics Engineering

Snowflake is a cloud-native data warehouse built on a unique multi-cluster shared data architecture that completely separates compute from storage. Unlike Redshift's node-based model where compute and storage are bundled together, Snowflake stores all data in a centralised cloud storage layer (S3, Azure Blob, or GCS depending on the cloud deployment) and connects it to independent, fully isolated compute clusters called Virtual Warehouses. This separation means storage scales independently of compute, and multiple workloads can query the same data simultaneously without competing for storage I/O.

Snowflake's architecture has three distinct layers. The Storage Layer holds all data in Snowflake's proprietary columnar micro-partition format — each micro-partition is a compressed 50–500MB columnar file stored in cloud object storage, with metadata recording the min/max values of every column in every micro-partition for pruning. The Compute Layer consists of Virtual Warehouses — independent clusters of compute nodes that execute queries against the storage layer. The Cloud Services Layer handles authentication, metadata management, query optimisation, and transaction coordination — shared infrastructure that Snowflake manages transparently.

Analogy🏏Cricket

🏏 Think of it like cricket: OLTP is the IPL's live ticketing counter — it handles thousands of simultaneous seat reservations, each requiring a precise single-seat record update with immediate confirmation. Speed per transaction and data consistency under concurrent updates are everything. OLAP is the IPL's season statistics department — it runs complex analytical queries across every ball bowled in every match of every season to produce the published rankings, economy rates, and historical comparisons. No one books a seat through the statistics department, and no broadcaster calls the ticketing counter for Bumrah's career economy rate. The two workloads demand completely different systems. Just as the ticketing counter is built for speed and correctness on one seat at a time and would buckle if asked to tally a decade of attendance mid-sale, an OLTP row-store excels at single-record writes but chokes on full-table aggregation; and just as the statistics department pores over millions of past deliveries but would be hopeless at booking a live seat under contention, the OLAP columnar engine sweeps billions of rows yet is the wrong tool for a fast single-row update. The physical design of each — row-oriented for the counter, columnar for the stats desk — is what makes it superb at its own job and unfit for the other's.