Advanced Pattern: Separation of Storage and Compute

The Architectural Breakthrough: Traditional data warehouses bundled storage and compute in a single appliance. You bought a 100 TB system with 50 compute nodes. If you needed more storage, you upgraded the whole appliance. If you needed more compute for year end reporting, you were stuck. This coupling killed elasticity and forced massive upfront capital expenditure. Modern cloud warehouses separate storage from compute. Data lives in cheap, durable object storage (Amazon S3, Google Cloud Storage, Azure Blob). Compute clusters spin up on demand, read from storage, process queries, and write results back. Compute and storage scale independently. How It Works in Practice: Snowflake pioneered this architecture. Data is stored in micro partitions (typically 16 MB compressed) in S3, organized by table and partition keys. When you run a query, Snowflake spins up a virtual warehouse (a cluster of compute nodes), pulls relevant micro partitions from S3 into local SSD cache, executes the query, and streams results back. If the warehouse is idle for a few minutes, it suspends automatically, stopping compute charges while data remains in S3. The cache layer is critical. Reading from S3 has 10 to 50ms latency per request. For a query scanning 1,000 micro partitions, that is 10 to 50 seconds just in network time. The local SSD cache on compute nodes reduces this to under 1ms for cached data. Snowflake's query optimizer prunes partitions aggressively: if you query WHERE date = '2024-01-15', it skips 99% of partitions, reading only the relevant 10 GB instead of 1 TB. Elasticity and Cost Model: The power is elasticity. You can run 5 small warehouses for different teams, each auto scaling independently. The data science team runs a 2 node warehouse for exploration, costing $4 per hour when active. During quarter end, finance spins up a 64 node warehouse for 2 hours to process a massive report, costing $160. Both query the same data in S3 without copying or moving it. This changes the cost model from fixed CapEx to variable OpEx. A traditional warehouse might cost $500k upfront for hardware plus $50k annual maintenance. A cloud warehouse costs zero upfront, $2,000 per month for 100 TB storage, and $5,000 to $30,000 per month for compute depending on usage. Low usage teams save massively. High usage teams pay more but get infinite scale. Concurrency and Isolation: Separating storage from compute enables true workload isolation. You cannot create one warehouse for production dashboards and another for ad hoc queries. If someone runs a 10 minute query in the ad hoc warehouse, it does not steal resources from production dashboards. They are physically separate compute clusters reading the same data. This also enables fine grained cost attribution. Each warehouse has usage metrics: queries run, compute hours, data scanned. You can charge back data science teams for their warehouse usage while subsidizing critical business dashboards. This visibility drives optimization: teams see their $10,000 monthly bill and start writing better queries. The Trade-off: Network Becomes the Bottleneck: Separating storage from compute means every query pulls data over the network from object storage. For small queries scanning a few GB, this adds 100 to 500ms latency versus reading from local disk. For very large queries scanning 100 TB, network bandwidth (typically 10 to 25 Gbps per node) can become the bottleneck. To mitigate this, systems use aggressive caching, partition pruning, and predicate pushdown. Snowflake maintains a distributed cache across compute nodes: if your query scans the same partitions as a recent query, it hits cache and avoids S3 entirely. BigQuery uses a columnar format (Capacitor) and only reads the columns you need, reducing network transfer by 10x to 50x versus reading full rows. Durability and Failure Handling: Object storage like S3 provides eleven nines durability. Your data is replicated across multiple availability zones and can survive data center failures. If a compute node fails mid query, the coordinator detects it within seconds and reschedules affected tasks on other nodes. The query might slow down by 10% to 20% but completes successfully. For multi region disaster recovery, some teams replicate data to a secondary region. Snowflake supports cross region replication with automated failover. If the primary region goes down, you can switch to the replica region within minutes. This adds cost (2x storage, cross region bandwidth) but ensures business continuity for critical workloads.