Snowflake System Design Interview: Building a Cloud Data Warehouse That Separates Storage from Compute
Snowflake decoupled the two things every older warehouse had welded together: the disks that hold data and the CPUs that query it. Storage lives once in cloud object storage such as S3, and any number of independent compute clusters read from it at the same time. The company has reported handling billions of queries per day across its customer base, and single tables can hold petabytes spread over hundreds of millions of immutable micro-partition files. Treat specific throughput figures here as estimates unless a number is tied to a published source.
A cloud data warehouse has to serve fast analytical queries over enormous, mostly cold datasets while many teams load and read at once. Snowflake's answer is a three-layer split. Data sits once in object storage as immutable, compressed, columnar micro-partitions. Compute happens in virtual warehouses, which are elastic clusters you spin up, resize, and suspend independently, so an ingest job on one warehouse never fights a dashboard on another. A stateless cloud services layer holds all the brains: the optimizer, transaction manager, security, and the metadata catalog that records which micro-partitions make up each table version. Queries go fast not through indexes but through pruning, where per-column min and max statistics let the planner skip files that cannot match a predicate. Because files are immutable and tables are just a versioned list of files, you get ACID transactions with snapshot isolation, time travel to any point in a retention window, and zero-copy clones that duplicate a table by copying metadata rather than bytes. The design's core bet is that separating storage from compute buys near-infinite elasticity and workload isolation, and that object storage plus smart metadata can replace the tightly coupled shared-nothing clusters that came before.
Asked at: Asked at Snowflake, Databricks, AWS (Redshift), Google (BigQuery), and Microsoft for data platform and database engineering roles. It also shows up in senior system design loops at any company that wants to test whether you understand OLAP versus OLTP and the storage-compute separation that defines modern cloud warehouses.
Why this question is asked
This problem separates candidates who have only built request-response web services from those who understand analytical data systems. It forces you to reason about columnar storage, why indexes matter less than partition pruning at warehouse scale, and how you get ACID guarantees when your storage layer is an eventually consistent object store you do not control. Interviewers like it because the good answer keeps coming back to one architectural decision, the separation of storage and compute, and everything else (elasticity, isolation, cloning, time travel) falls out of it. It also rewards candidates who can talk about cost, since compute is the expensive part and the design is built to let you pay for compute only while queries run.
Requirements
Always clarify these in the first 5 minutes of the interview. Do not start drawing boxes until both lists are agreed.
Functional requirements
- Store structured and semi-structured data (JSON, Avro, Parquet) and query it with standard SQL
- Run analytical queries with joins, aggregations, and window functions over tables up to petabyte scale
- Provision independent compute clusters (virtual warehouses) that read the same data concurrently
- Scale a warehouse up (bigger nodes) and out (more clusters) without moving or repartitioning data
- Support ACID transactions with snapshot isolation across concurrent readers and writers
- Offer time travel to query or restore a table as of an earlier timestamp within a retention window
- Support zero-copy cloning of tables, schemas, and databases for dev, test, and backup
- Load data in bulk and continuously, and let queries see committed data without manual index maintenance
- Enforce fine-grained access control, encryption at rest and in transit, and secure data sharing across accounts
Non-functional requirements
- Elasticity: add or remove compute in seconds, and suspend idle warehouses so customers stop paying for them
- Workload isolation: heavy ETL on one warehouse must not degrade interactive queries on another
- Durability: data must survive node and availability zone loss, backed by object storage replication
- High availability of the metadata and services layer, since it sits in the path of every query
- Low query latency for well-pruned scans, and predictable performance as data grows
- Cost efficiency: storage is cheap and always on, compute is metered per second and off by default
- Strong security and multi-tenancy isolation, since one deployment serves thousands of separate customers
Back-of-envelope scale estimates
Show your math. Pulling numbers from thin air signals you have not thought about the load.
Micro-partition size
50 to 500 MB uncompressed
Snowflake docs state each micro-partition holds 50 to 500 MB of uncompressed data, stored compressed and columnar. This is the unit of pruning, so a 1 TB table becomes roughly 2,000 to 20,000 files (1 TB divided by 50 to 500 MB). Published figure, not derived.
Files in a large table
~100M+ micro-partitions for a PB table
Derived. A 1 PB table at an effective 100 MB per micro-partition is about 10 million files (1 PB / 100 MB). Snowflake has described tables with hundreds of millions of micro-partitions, so multi-PB tables push into the 100M+ range. Estimate.
Metadata read/write pattern
sub-millisecond tiny reads and writes
Snowflake's engineering blog describes the metadata workload as OLTP-like: very high frequency of tiny key-value operations at sub-millisecond latency, which is why they use FoundationDB rather than the object store for the catalog. Published characterization.
Metadata replication factor
3x across availability zones
Snowflake states metadata in FoundationDB is triple replicated and spread across multiple cloud availability zones for high availability, with sensitive fields encrypted. Published figure.
Pruning efficiency
scan ~10% of files for a 10% selective predicate
Snowflake docs give the target that a filter matching 10% of a value range should ideally scan only about 10% of micro-partitions. Actual pruning depends on data clustering. Published target, real numbers vary.
Warehouse sizing
X-Small to 6X-Large, doubling per step
Derived from public sizing: each warehouse size step roughly doubles the number of compute nodes and therefore credits burned per hour. An X-Small is 1 node worth of compute; a 6X-Large is hundreds. Scaling out adds identical clusters for concurrency. Estimate on exact node counts.
High-level architecture
A client sends SQL over a driver to the cloud services layer, which is a stateless, multi-tenant tier that authenticates the request, checks access control, and hands the query to the optimizer. The optimizer consults metadata in FoundationDB to learn which micro-partitions belong to the target table at the current transaction version, and it uses per-column min and max statistics to prune the file list down to only those that could satisfy the query's predicates. It then builds a distributed plan and dispatches it to the user's virtual warehouse, an independent cluster of compute nodes. Each node reads its assigned micro-partitions directly from object storage such as S3, decompresses only the columns the query touches, and caches hot files on local SSD so repeat scans avoid the object store round trip. Nodes run a vectorized, push-based execution engine, exchange intermediate data for joins and aggregations, and stream results back through cloud services. Writes follow the same spine in reverse: a warehouse produces new immutable micro-partitions, uploads them to object storage, and then commits a transaction by atomically updating the table's file list in the metadata store. Because the table is just a versioned set of file pointers, the commit is a small metadata change, older versions remain readable for time travel, and a clone is simply a new pointer set that shares the same underlying files.
In a real interview, sketch this on the whiteboard before diving into any single box.
Core components
Walk through each service. The interviewer wants to hear what each one owns, not just the names.
Cloud services layer
The stateless brain of the system: authentication, access control, the query optimizer, transaction management, and the metadata catalog live here. It is shared across all customers and horizontally scaled, and it holds no user table data itself, only the metadata and coordination state. If a services node dies, another picks up the work because the durable state is in FoundationDB, not on the node.
Virtual warehouses (compute)
Elastic clusters of compute nodes that execute queries over data they do not own. You can start, resize, suspend, and clone them independently, and several can read the same tables at once. Isolation is the point: a warehouse is dedicated to a workload, so a large batch load cannot slow a separate BI warehouse pointed at the same data.
Centralized object storage
The single durable home for table data, backed by S3, Google Cloud Storage, or Azure Blob. Data is written as immutable, compressed, columnar micro-partition files, and durability and replication come from the object store itself. Compute is stateless with respect to this layer, which is what lets you kill and recreate warehouses freely.
Micro-partitions and pruning metadata
Tables are physically a large set of immutable micro-partition files, each 50 to 500 MB uncompressed and stored column by column. For every file Snowflake records the min and max value of each column, distinct counts, and other statistics. The optimizer uses these to skip files that cannot match a predicate, which replaces the role traditional databases give to secondary indexes.
Metadata store (FoundationDB)
A transactional, ordered key-value store that holds catalog definitions, the list of micro-partitions per table version, per-partition statistics, transaction state, and pointers from logical tables to physical files. Its access pattern is OLTP-like, tiny sub-millisecond reads and writes, so it is triple replicated across availability zones. This layer is what makes ACID commits, time travel, and cloning cheap metadata operations.
Caching tiers
Two caches cut object-store latency and cost. Each warehouse node keeps hot micro-partitions on local SSD, so repeated scans of the same data stay local. The services layer also keeps a result cache, so an identical query over unchanged data can return instantly without spinning up any compute at all.
Query execution engine
A columnar, vectorized, push-based engine that processes batches of column values at a time rather than row by row. It handles distributed joins and aggregations by shuffling intermediate data between nodes in a warehouse, and it operates directly on compressed columnar data where possible. It also understands semi-structured VARIANT columns, so JSON can be queried with SQL without a rigid up-front schema.
Data model
Pick the right store per table. Justify each choice with the access pattern, not by reflex.
table_versionstable_idversion_idcommitted_attransaction_idadded_partition_idsremoved_partition_idsA table is a sequence of versions. Each version records which micro-partitions were added and removed by the committing transaction. Reading as-of a timestamp means selecting the version whose committed_at is at or before that time, which is how time travel works.
micro_partitionspartition_idobject_store_urlrow_countuncompressed_sizecompressioncreated_by_txnOne row per immutable file in object storage. Files are never mutated in place; an update writes new partitions and marks old ones removed at the next version. The url points at the physical S3/GCS/Blob object.
partition_column_statspartition_idcolumn_idmin_valuemax_valuedistinct_countnull_countThe pruning index. For each column in each micro-partition, min and max let the optimizer decide whether the file can possibly match a predicate before reading any data. This is the substitute for traditional B-tree indexes.
tables_catalogtable_idschema_idnamecolumn_definitionsclustering_keyscurrent_version_idLogical definition of a table: its columns, optional clustering keys, and a pointer to the current version. Semi-structured columns are typed as VARIANT. current_version_id is the anchor a fresh query resolves against.
transactionstransaction_idwarehouse_idstart_versionstatestarted_atcommitted_atTracks each transaction's snapshot (start_version) and outcome. Snapshot isolation means readers see the version as of start_version; a commit atomically advances the affected tables to a new version in one metadata write.
clonesclone_idsource_object_idsource_version_idcreated_atownerA zero-copy clone records a new logical object that initially shares the source's micro-partition set at a given version. No data is copied. Divergence only creates new files for the rows that actually change afterward.
Deep dives
These are the conversations the interviewer is steering you toward. Practice each one until you can talk through it without notes.
Why separate storage and compute at all
Older warehouses used shared-nothing architectures such as Teradata, where each node owns a slice of the data on its local disk. That couples the two resources: to add compute you must move data, and to store more you must add nodes you may not need for querying. It also makes elasticity painful, because resizing means repartitioning terabytes. Snowflake's insight for the cloud was that object storage is cheap, durable, and effectively infinite, so data can live there once while compute becomes disposable. Storage scales on its own, compute scales on its own, and you can run many compute clusters against the same bytes. Shared-disk architectures also decouple storage but bottleneck on a single set of disks and on lock contention; Snowflake avoids that by making files immutable and pushing coordination into a separate metadata service rather than fighting over shared blocks.
Micro-partitions and pruning instead of indexes
At warehouse scale, a secondary index over billions of rows is expensive to maintain and often not selective enough to help an analytical scan. Snowflake replaces indexes with pruning. Data is stored as immutable micro-partitions of 50 to 500 MB, columnar within each file, and for every column in every file the metadata records the min and max value plus distinct and null counts. When a query filters on, say, order_date between two values, the optimizer reads only the file-level statistics and drops every micro-partition whose min-max range cannot overlap the predicate. A query touching 10% of a value range should scan close to 10% of the files. Pruning works best when related values are physically close together, so Snowflake orders data on load and offers automatic clustering to re-sort files in the background for tables where the natural load order drifts away from the common filter columns.
Virtual warehouses and workload isolation
A virtual warehouse is a compute cluster that owns no data. You size it up for more power per query, or scale it out into multiple identical clusters to handle more concurrent queries, and Snowflake routes queries across those clusters. Because every warehouse reads the same shared storage, you can give each workload its own warehouse: one for nightly ETL, one for BI dashboards, one for ad hoc data science. None of them contend for the others' CPU or memory. Warehouses auto-suspend after an idle period and auto-resume on the next query, so you pay for compute by the second only while work is running. The tradeoff is a cold warehouse loses its local SSD cache on suspend, so the first queries after resume are slower until the cache refills from object storage.
The cloud services layer and metadata in FoundationDB
Everything stateful about coordination lives in the services layer: the catalog, the optimizer's statistics, transaction state, and access control. The hard part is that this metadata has an OLTP access pattern, a flood of tiny sub-millisecond reads and writes, which the analytical object store is terrible at. Snowflake stores it in FoundationDB, a transactional ordered key-value store, triple replicated across availability zones with sensitive fields encrypted. Keeping metadata separate from data is what makes the expensive-sounding features cheap. A commit is a small transactional write to the file list, not a rewrite of data. Pruning is a metadata scan, not a data scan. The services layer is stateless in front of FoundationDB, so any node can serve any request and node failures do not lose committed state.
ACID and snapshot isolation over immutable files
Object stores do not offer multi-object transactions, so Snowflake does not try to make S3 transactional. Instead a table is a versioned list of immutable files held in the transactional metadata store, and that list is where atomicity lives. A writing transaction produces new micro-partitions, uploads them, and then commits by atomically swapping the table's file set to a new version in FoundationDB. Readers run under snapshot isolation: a query resolves the table version at its start and sees a consistent snapshot for its whole life, unaffected by concurrent writers. Since old file versions are never overwritten, concurrent readers of the previous version keep working, and there is no read-write lock contention on the data itself. Conflicts between concurrent writers to the same table are resolved at commit time against the version they started from.
Time travel and zero-copy cloning
Both features are almost free once tables are versioned lists of immutable files. Time travel lets you query or restore a table as of any point within its retention window, because the metadata still points at the micro-partitions that made up each past version and those files were never deleted or mutated. Restoring a dropped table or an accidental mass update is a metadata operation, not a recovery from backup. Zero-copy cloning creates a new table, schema, or whole database that initially shares the exact same micro-partition files as the source at a chosen version. No bytes are copied, so a clone of a petabyte table is instantaneous and adds no storage. Only when the clone or the original is modified do new files get written for the changed rows, and storage grows just for that delta. This is what makes cheap dev and test copies of production data practical.
Caching, semi-structured data, and the execution engine
Performance leans on two caches. Each warehouse node caches hot micro-partitions on local SSD, so a working set that fits locally avoids object-store latency on repeat scans. The services layer keeps a result cache keyed on the query and the data version, so an identical query over unchanged data returns without touching a warehouse at all. On top of that, the execution engine is columnar, vectorized, and push-based: it processes batches of column values, reads only the columns a query references, and often works on compressed data directly. Semi-structured data is handled through the VARIANT type, which stores JSON or similar formats and still lets the engine extract and index paths so SQL can query nested fields with the same pruning and columnar benefits as flat columns. The design keeps the fast path in memory and on SSD, and only falls back to the object store for cold data.
Trade-offs to discuss
Every senior interviewer expects you to surface at least 3 of these. Pick the decisions, state the alternatives, and justify your choice.
Separated storage and compute versus shared-nothing (Teradata style)
Separation gives independent scaling, elastic compute, and many clusters on one dataset, at the cost of network round trips to object storage that shared-nothing local disks avoid. Snowflake hides the latency with SSD and result caches, and for cloud economics the flexibility and isolation win.
Pruning on micro-partition statistics versus secondary indexes
Min-max pruning needs no index maintenance and suits scan-heavy analytics, but it only helps when data is clustered so files have tight, non-overlapping ranges. Traditional indexes give pinpoint lookups for OLTP but are costly to maintain at warehouse scale, so Snowflake picks pruning plus optional clustering.
Immutable files versus in-place updates
Immutability makes snapshot isolation, time travel, and zero-copy cloning almost free and removes read-write lock contention. The price is write amplification, since an update rewrites whole micro-partitions rather than changing rows in place, and background work is needed to reclaim old files after the retention window.
FoundationDB for metadata versus storing metadata in the object store
Metadata has an OLTP pattern of tiny frequent transactional writes that object stores cannot serve at sub-millisecond latency. A dedicated transactional key-value store handles it well but adds an operational component to run and replicate. Snowflake accepts that cost because the metadata layer is in the path of every query.
Auto-suspend warehouses versus keeping compute always warm
Suspending idle compute saves customers money and matches metered pricing, but it drops the local SSD cache, so queries right after resume are slower. Always-warm compute gives steady latency at higher cost. Snowflake defaults to suspend and lets customers tune the idle timeout.
Multi-cluster shared-data multi-tenancy versus single-tenant deployments
A shared services layer serving thousands of customers is efficient and elastic but demands strong isolation and encryption to keep tenants apart. Single-tenant would simplify isolation but waste capacity and lose elasticity. Snowflake invests in strict access control and encryption to make shared multi-tenancy safe.
How Snowflake actually does it
The architecture here follows the design Snowflake's founders published in the 2016 SIGMOD paper The Snowflake Elastic Data Warehouse, which introduced the multi-cluster shared-data model with separated storage, virtual warehouses, and a cloud services layer. The three-layer split, immutable micro-partitions, min-max pruning, MVCC with snapshot isolation, and time travel are all described there. Snowflake's own engineering blog details how the cloud services layer keeps metadata in FoundationDB, triple replicated across availability zones, because the metadata workload is OLTP-like with tiny sub-millisecond operations, and how that metadata tracks the micro-partitions of each table version to power pruning, cloning, and time travel. The micro-partition size range of 50 to 500 MB and the pruning behavior come from Snowflake's public documentation. Where exact node counts or per-day query volumes are stated above, treat them as estimates unless tied to one of these sources.
Lessons to study before this interview
If any of these topics are fuzzy, the interviewer will catch it. Each lesson is 15 to 60 minutes with diagrams, code, and a quiz.