How Snowflake separates storage and compute

The Interview Question

Interviewer: "Your data team uses Snowflake and says they can spin up a warehouse in seconds, run a query on 5 TB of data, and shut it down. Walk me through how Snowflake actually makes that possible. What happens internally when a query runs, and how does it separate storage from compute?"

This question tests whether you understand the three-layer architecture, micro-partition storage format, virtual warehouse lifecycle, and the caching layers that make Snowflake feel fast. The interviewer wants you to go beyond "it separates storage and compute" and explain the actual mechanics. A strong answer traces a single query from SQL text to result set, showing how each layer contributes.

What to Clarify Before Answering

You: "Before I dive into the architecture, a few scoping questions..."

• "Should I focus on the query execution path, or also cover data ingestion (Snowpipe, COPY INTO)?"
• "Are you interested in the cost model too, or just the technical architecture?"
• "Should I cover multi-cluster warehouses and auto-scaling, or is a single warehouse sufficient for this discussion?"
• "Do you want me to touch on Time Travel and zero-copy cloning, or stay focused on the query hot path?"

Why this matters: Snowflake is a broad platform. The query execution path alone involves micro-partition pruning, result caching, virtual warehouse scheduling, and cloud services coordination. Adding data sharing, Snowpipe, and governance doubles the scope. Scoping shows you know the breadth.

The 30-Second Answer

Snowflake's architecture has three independent layers: cloud services (brain), compute (virtual warehouses), and storage (micro-partitions on cloud object storage). Data is stored as micro-partitions, columnar compressed files of 50-500 MB on S3/Azure Blob/GCS. Virtual warehouses are ephemeral compute clusters that spin up in seconds, read micro-partitions from object storage, process the query, and can shut down immediately. A metadata catalog in the cloud services layer tracks min/max values per micro-partition per column, enabling partition pruning that skips irrelevant data without scanning. Three layers of result caching (metadata cache, result cache, local SSD cache) mean many queries return instantly without touching storage at all.

The bottom line: Snowflake is fast because it avoids work at every level. Partition pruning avoids reading irrelevant data. Result caching avoids recomputing results. Storage-compute separation avoids paying for compute you do not need. The system is designed so that the most common path (re-running a dashboard query on unchanged data) costs zero compute.

The Architecture Overview

I will walk through the three layers from bottom to top. Storage is the foundation: all data lives as immutable micro-partitions on cloud object storage (S3, Azure Blob, or GCS). This is cheap, durable, and shared across all compute. The compute layer consists of virtual warehouses, clusters of EC2/VM instances that spin up on demand, read data from storage, process it, and can suspend when idle. The cloud services layer is the brain: it handles authentication, query optimization, metadata management, transaction coordination, and result caching. It runs 24/7 regardless of whether any warehouse is active.

The key insight is that these layers scale independently. You can have 100 TB of storage and zero compute (cost: just storage). You can spin up 10 warehouses simultaneously, each reading from the same data without copying it. This decoupling is what makes Snowflake's pricing model work and what makes it so different from traditional databases where storage and compute live on the same machine.

Micro-Partitions: The Storage Format

Snowflake does not store data in traditional database pages or files that you manage. It automatically organizes data into micro-partitions: immutable, compressed, columnar files between 50 and 500 MB (roughly 50-500 MB compressed, representing much more raw data).

Every micro-partition stores its data column by column, similar to Apache Parquet. Each column within a micro-partition is independently compressed. Snowflake chooses compression algorithms automatically based on the data type and distribution.

When data is loaded (via INSERT, COPY INTO, or Snowpipe), Snowflake decides how to partition it. You do not choose partition boundaries. The system analyzes the data and creates micro-partitions that overlap somewhat in their value ranges (this is called "natural clustering"). For most workloads, the ingestion order provides reasonable clustering on time-based columns.

Immutability is the key design choice. Micro-partitions are never modified in place. An UPDATE statement does not change bytes in an existing file. Instead, Snowflake writes new micro-partitions with the updated rows and marks the old ones as deleted (but keeps them for Time Travel). This append-only design eliminates locking, simplifies concurrency, and enables Time Travel.

Partition Pruning

This is where the metadata catalog earns its keep. For every micro-partition, Snowflake stores min/max values, null count, and distinct count for every column. When a query includes WHERE event_date = '2026-01-05', the optimizer checks the metadata and immediately eliminates any micro-partition where the date range does not include January 5th.

For a table with 10,000 micro-partitions, a well-pruned query might read only 50 partitions. That is a 200x reduction in I/O before the query even starts executing. This is Snowflake's equivalent of an index scan, but it requires no index creation or maintenance.

For your interview: say "Snowflake uses micro-partition pruning instead of traditional indexing. The metadata catalog stores min/max per column per partition, and the optimizer skips partitions that cannot contain matching rows."

Clustering Keys

When the natural ordering of your data does not match your query patterns, pruning becomes ineffective. This is where clustering keys come in. A clustering key tells Snowflake to reorganize micro-partitions in the background so that rows with similar values for the clustering columns end up in the same micro-partitions.

-- Define a clustering key on the events table
ALTER TABLE events CLUSTER BY (event_date, region);

Snowflake runs automatic reclustering in the background, rewriting micro-partitions to improve clustering. This incurs compute cost (billed as Snowflake credits) but can dramatically improve pruning efficiency.

Virtual Warehouses: Elastic Compute

A virtual warehouse is a cluster of compute nodes that Snowflake provisions from the cloud provider. It is not a persistent server. It is an ephemeral resource that spins up when needed and suspends (releasing all compute) when idle.

Each doubling in warehouse size roughly doubles the number of compute nodes and doubles the cost per hour. The rule of thumb: doubling the warehouse size halves the query time for scan-heavy queries. This is because Snowflake distributes micro-partition reads across all nodes in the warehouse.

Multi-cluster warehouses add horizontal scaling. Instead of making a warehouse bigger (vertical scaling), you add more clusters of the same size. Snowflake automatically routes queries to available clusters and spins up new clusters when the queue gets too deep. This is ideal for workloads with many concurrent users (like BI dashboards).

The auto-suspend / auto-resume behavior is what makes Snowflake's cost model work. A warehouse running 8 hours a day costs one-third of a warehouse running 24/7. I recommend setting auto-suspend to 1-5 minutes for interactive workloads and 0 minutes (immediate suspend) for batch ETL warehouses that run scheduled jobs.

How Queries Execute on a Warehouse

When a query arrives at a running warehouse, the execution follows this path:

1. Cloud services compiles the query into an execution plan with partition pruning
2. The plan is sent to the warehouse's coordinator node
3. The coordinator distributes work to worker nodes based on the partitions each needs to read
4. Each worker reads assigned micro-partitions from object storage (or local SSD cache)
5. Workers process their portions using a vectorized columnar execution engine
6. Partial results flow back to the coordinator for final aggregation
7. Results return to the client through cloud services

Each worker node has local SSD storage that acts as a cache. If a micro-partition was recently read by this warehouse, the subsequent read comes from local SSD (~100 microseconds) instead of object storage (~50-100 milliseconds). This cache is warehouse-specific and persists across queries as long as the warehouse stays running.

Result Caching: Three Layers

Snowflake's caching architecture has three distinct layers, and understanding which one is serving your query explains why some queries return in milliseconds and others take minutes.

The hierarchy is important: each layer is checked in order, and a hit at any layer short-circuits the rest. This means the fastest queries never leave the cloud services layer.

Layer 1: Metadata cache. For queries that can be answered entirely from metadata (like SELECT COUNT(*) FROM events or SELECT MIN(event_date) FROM events), Snowflake returns the answer from the metadata catalog without starting a warehouse. This is free and instant. I see teams surprised by this: their COUNT(*) on a 10 TB table returns in 200ms with no warehouse running.

Layer 2: Result cache. If the exact same query (same SQL text, same role, same database context) was executed in the last 24 hours and the underlying data has not changed, Snowflake returns the cached result. No warehouse needed, no compute cost. The result cache is invalidated when any DML modifies the underlying table.

The result cache has a subtle but important rule: it matches on exact SQL text. SELECT * FROM events and select * from events are different cache keys. Adding a comment to the SQL also creates a cache miss. I have seen teams accidentally bypass the result cache by including dynamic timestamps in comments.

Layer 3: Local SSD cache. Each warehouse node has local SSD storage. When micro-partitions are read from object storage, they are cached on local SSD. Subsequent queries that need the same partitions read from SSD instead of object storage, which is 100-1000x faster. This cache is tied to the warehouse instance and cleared when the warehouse suspends.

The SSD cache is why warehouse "warm-up" matters. The first query after a resume reads from object storage (cold). Subsequent queries that touch the same partitions benefit from SSD cache (warm). For latency-sensitive dashboards, keeping the warehouse running during business hours pays for itself through consistently fast queries.

Time Travel and Fail-safe

Because micro-partitions are immutable, Snowflake can keep old versions around. This enables Time Travel: the ability to query data as it existed at a point in the past.

-- Query the events table as it was 1 hour ago
SELECT * FROM events AT (OFFSET => -3600);

-- Query as of a specific timestamp
SELECT * FROM events AT (TIMESTAMP => '2026-04-11 14:30:00'::TIMESTAMP);

-- Query just before a specific query ID ran
SELECT * FROM events BEFORE (STATEMENT => '01b2c3d4-...');

-- Undrop a table that was dropped by mistake
UNDROP TABLE events;

Time Travel works by retaining the old micro-partitions that were replaced by DML operations. When you run an UPDATE, Snowflake writes new micro-partitions and marks the old ones for Time Travel retention instead of immediate deletion. The retention period is configurable (0-90 days, default 1 day for standard edition, up to 90 days for enterprise).

After the Time Travel period expires, data enters Fail-safe: a 7-day window where Snowflake support (not the user) can recover data. Fail-safe data is not queryable and exists purely as a disaster recovery mechanism.

The storage cost of Time Travel is the cost of retaining old micro-partitions. If you update 10% of a table daily, your Time Travel storage is roughly 10% of table size per day of retention. For a 10 TB table with 7-day retention, that is ~7 TB of Time Travel storage at object storage prices.

How Time Travel Interacts with DML

Different DML operations have different Time Travel storage costs. Understanding this prevents surprise bills.

INSERT: New micro-partitions are created. No old partitions are retained because nothing was replaced. Time Travel cost: zero for pure inserts.

UPDATE (MERGE INTO): Snowflake identifies micro-partitions containing rows that match the update predicate, writes new micro-partitions with the modified rows, and retains the old partitions for Time Travel. If an UPDATE touches rows in 500 out of 10,000 micro-partitions, Snowflake creates 500 new partitions and retains 500 old ones.

DELETE: Similar to UPDATE. Snowflake writes new micro-partitions that exclude the deleted rows and retains the old partitions.

TRUNCATE: Marks all micro-partitions as deleted. All data is retained for Time Travel. This is the cheapest DML operation (no new partitions created) but the most expensive for Time Travel storage (all old partitions retained).

Zero-Copy Cloning

This is one of my favorite Snowflake features. When you clone a table, database, or schema, Snowflake does not copy the data. It creates new metadata pointers to the same micro-partitions.

-- Clone a 10 TB table in seconds, using zero additional storage
CREATE TABLE events_staging CLONE events;

-- Clone an entire database
CREATE DATABASE analytics_dev CLONE analytics_prod;

The clone is nearly instant regardless of data size because no data moves. Both the original and the clone point to the same micro-partitions. When either table is modified, only the new or changed micro-partitions consume additional storage. This is copy-on-write at the micro-partition level.

I use zero-copy cloning for: (1) creating development copies of production data without doubling storage costs, (2) creating a "snapshot" before a risky data migration, (3) running A/B tests on different data transformations.

Data Sharing and Snowflake Marketplace

Snowflake enables sharing live data between Snowflake accounts without copying. A data provider creates a share, which is a set of metadata pointers to micro-partitions. The consumer account mounts this share as a read-only database and queries it using their own virtual warehouse.

No data moves between accounts. The consumer's warehouse reads directly from the provider's micro-partitions in object storage. The provider controls what is shared (down to specific rows and columns using secure views). Data updates by the provider are immediately visible to consumers.

This architecture is only possible because of the storage-compute separation. The provider's storage is accessible to other accounts' compute because both layers use the same cloud object storage infrastructure.

Query Compilation and Execution

When a query hits Snowflake, the cloud services layer does significant work before any warehouse node touches data. Understanding this pipeline explains why some queries are fast and others are not.

Step 1: Parsing and Optimization

The SQL text is parsed into an abstract syntax tree. Snowflake's cost-based optimizer evaluates multiple execution plans and picks the cheapest one. The optimizer considers:

• Partition pruning: which micro-partitions can be skipped based on WHERE filters and metadata
• Join ordering: which table to probe first in hash joins, based on estimated cardinality
• Predicate pushdown: pushing filters as close to the scan as possible
• Projection pushdown: only reading columns that the query actually needs

Step 2: Compilation to Native Code

Snowflake compiles the optimized plan into native machine code in the cloud services layer and sends that compiled plan to the warehouse. This adds a small planning overhead, usually tens to a few hundred milliseconds, but it makes execution much faster than an interpreted engine.

Step 3: Distributed Execution

The warehouse coordinator splits the work across worker nodes. Each worker is assigned a set of micro-partitions to scan. Workers execute the compiled plan on their assigned partitions in parallel, using a vectorized columnar execution engine similar (in principle) to ClickHouse's approach.

// Simplified execution model for a warehouse with 4 nodes:
// Query: SELECT region, SUM(revenue) FROM sales WHERE year = 2026 GROUP BY region

Node 1: Scan partitions 1-250    → local aggregate → partial result (region → sum)
Node 2: Scan partitions 251-500  → local aggregate → partial result (region → sum)
Node 3: Scan partitions 501-750  → local aggregate → partial result (region → sum)
Node 4: Scan partitions 751-1000 → local aggregate → partial result (region → sum)
Coordinator: merge 4 partial results → final result

For aggregation queries, each node computes local aggregates on its assigned partitions, then the coordinator merges partial results. This is why scaling up the warehouse size (adding more nodes) nearly linearly reduces scan time.

Step 4: Result Materialization

Large result sets are spilled to cloud object storage rather than held entirely in memory. If a query returns millions of rows, Snowflake writes temporary result data to storage and streams it back to the client. This avoids out-of-memory failures at the warehouse layer, but it adds latency for very large or very wide results.

Snowpipe: Streaming Ingestion

For continuous data loading, Snowpipe monitors a cloud storage location (S3 bucket, Azure container) and automatically loads new files into Snowflake tables within minutes of arrival.

The architecture: when a new file appears in the monitored location, an event notification (S3 SQS, Azure Event Grid) triggers Snowpipe. The cloud services layer queues the file for loading. Snowflake uses a shared, serverless compute pool (separate from your virtual warehouses) to process the load. No warehouse needs to be running.

Snowpipe billing is per-file, based on the compute needed to load and transform the data. For high-volume streams (thousands of small files per minute), consider batching files before Snowpipe picks them up, because per-file overhead adds up.

Snowpipe Streaming (Snowpipe API)

For even lower latency, Snowflake offers the Snowpipe Streaming API (Java SDK). Instead of writing files to object storage and triggering Snowpipe, applications insert rows directly via the API. Snowflake buffers these rows in the cloud services layer and flushes to micro-partitions within seconds.

This is the closest Snowflake gets to real-time ingestion. Typical end-to-end latency (from API call to queryable data) is 1-10 seconds, compared to 1-5 minutes for file-based Snowpipe. The trade-off is that the Streaming API requires application code changes and uses a different billing model (per-second of compute for the streaming channel).

COPY INTO: Bulk Loading

For large batch loads, COPY INTO is the most efficient method. It reads files from a stage (S3 bucket, Azure container, or Snowflake internal stage) and loads them into a table using the warehouse's compute resources.

-- Load from an S3 stage
COPY INTO events
FROM @my_s3_stage/events/2026/04/
FILE_FORMAT = (TYPE = 'PARQUET')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

COPY INTO parallelizes across warehouse nodes. An XL warehouse with 16 nodes can load 16 files simultaneously. For optimal throughput, split large datasets into files of 100-250 MB (compressed) so each warehouse node has roughly equal work. A single 10 GB file would only use one node while the other 15 sit idle.

What Happens When Things Break

Performance Characteristics

For your interview: emphasize that the first two rows (metadata and result cache) need no warehouse, which means zero compute cost. This is unique to Snowflake's architecture and catches many interviewers' attention.

How This Compares to Alternatives

I reach for Snowflake when the team wants a fully managed data warehouse with minimal operational overhead, strong governance features, and the ability to share data across organizations. I consider BigQuery when queries are sporadic and the per-TB pricing model makes more sense than always-on warehouses. I look at Databricks when the team needs a lakehouse architecture with open formats and heavy ML/data science workloads alongside SQL analytics.

Interview Cheat Sheet

• When asked about the architecture: "Three layers: cloud services (always-on brain), virtual warehouses (ephemeral compute), and micro-partitions on object storage (persistent data). Each scales independently."
• When asked about micro-partitions: "Immutable, columnar compressed files of 50-500 MB. Snowflake manages partitioning automatically. The metadata catalog stores min/max per column per partition for pruning. No user-managed indexes."
• When asked about virtual warehouses: "Ephemeral compute clusters that resume in 1-2 seconds. T-shirt sizing (XS to 6XL, each doubling doubles nodes and cost). Multi-cluster warehouses add horizontal scaling for concurrent users. Auto-suspend saves money."
• When asked about caching: "Three layers. Metadata cache: answers COUNT/MIN/MAX without a warehouse. Result cache: returns identical query results for 24 hours without compute. SSD cache: local disk on warehouse nodes for recently read partitions."
• When asked about Time Travel: "Possible because micro-partitions are immutable. Old partitions are retained for 0-90 days. Enables querying historical data, undropping tables, and rolling back mistakes."
• When asked about zero-copy cloning: "Creates metadata pointers to existing micro-partitions. Instant regardless of data size. Copy-on-write: only new/changed data consumes additional storage."
• When asked about cost optimization: "Set auto-suspend on every warehouse. Use resource monitors with credit alerts. Separate workloads into different warehouses. Review expensive queries in ACCOUNT_USAGE.QUERY_HISTORY. Add clustering keys only when pruning efficiency is demonstrably poor."
• When asked about limitations: "Proprietary storage format (no Parquet export without compute). Warehouse sizing is trial-and-error. No real-time streaming (Snowpipe has minute-level latency). Cross-region/cloud data sharing requires replication."

Test Your Understanding

These questions test whether you understand Snowflake's storage, compute, caching, recovery, and cost model well enough to reason about real production scenarios.

Quick Recap

1. Snowflake separates storage (micro-partitions on object storage), compute (virtual warehouses), and cloud services (metadata, optimization, caching) into three independently scalable layers.
2. Micro-partitions are immutable, columnar compressed files of 50-500 MB. Snowflake manages partitioning automatically and stores min/max metadata per column per partition for pruning.
3. Virtual warehouses are ephemeral compute clusters that resume in seconds. T-shirt sizing doubles nodes and cost at each step. Multi-cluster warehouses add horizontal scaling for concurrent users.
4. Three caching layers (metadata, result, local SSD) mean many queries return instantly with zero warehouse cost.
5. Time Travel enables querying historical data, undropping tables, and disaster recovery, all because micro-partitions are immutable and retained for a configurable period.
6. Zero-copy cloning creates instant copies of tables or databases using metadata pointers, with copy-on-write semantics for incremental storage cost.
7. Clustering keys improve partition pruning for query patterns that do not match the natural data ordering, but incur background reclustering costs.
8. The biggest cost risk is warehouses left running unnecessarily. Always set auto-suspend and resource monitors.

Related Concepts

• Micro-partition storage shares design principles with Apache Parquet and Delta Lake. All three use columnar, compressed, immutable file formats, but Snowflake's format is proprietary while Parquet and Delta are open.
• Virtual warehouses are similar to Kubernetes pods in concept: ephemeral compute that spins up on demand. The key difference is that Snowflake abstracts the infrastructure entirely.
• Result caching parallels HTTP caching (ETags, Cache-Control). The same principle applies: if the underlying data has not changed and the request is identical, serve from cache.
• Time Travel maps to MVCC (Multi-Version Concurrency Control) in PostgreSQL, but at the partition level instead of the row level. Both keep old versions for concurrent reads and recovery.
• Storage-compute separation is the foundational pattern also used by Databricks (Spark + Delta Lake), BigQuery (Dremel + Colossus), and modern data lake architectures.
• Data sharing is a form of the publish-subscribe pattern applied to datasets. The provider publishes a share, and consumers subscribe by mounting it as a read-only database.