The Snowflake Elastic Data Warehouse

In one breath — the whole paper, compressed

High-performance warehousing had settled on shared-nothing clusters where each node owns the rows on its local disks, which means every resize, node failure and upgrade forces a data reshuffle by the very nodes that are also running queries. Snowflake keeps a shared-nothing execution engine but moves the base data out to Amazon S3, cutting the system into three independently scalable layers: S3 storage, ephemeral compute clusters called virtual warehouses, and a heavily multi-tenant Cloud Services layer holding metadata, the optimizer, transactions and security. Tables are horizontally partitioned into large immutable files in a hybrid columnar (PAX) layout, so a query downloads only file headers and the columns it wants via S3 range GETs, while per-file min-max metadata prunes whole files with no index to build or maintain. Because files are immutable, a write produces a new table version by adding and removing whole files in a transactional key-value store, which yields snapshot isolation over MVCC almost for free, and with it time travel up to 90 days, UNDROP, and metadata-only CLONE. The authors report a working service, generally available since June 2015, running several million queries per day over multiple petabytes, upgraded weekly with no downtime, and exposing exactly one tuning knob: how much performance the user is willing to pay for.

Before this paper — the world it landed in

Through the 2000s, high-performance data warehousing converged on shared-nothing architectures: tables horizontally partitioned across nodes, each node responsible only for the rows on its local disks. That design won because star-schema joins need very little bandwidth and there is no contention for shared structures, so no expensive custom hardware is required. But it assumed a small, static, well-behaved cluster, in which resizing was rare, node failures were rare, and an upgrade could take a maintenance window. Meanwhile the data changed: a fast-growing share now arrived from application logs, web and mobile applications, social media and sensors, frequently in schema-less, semi-structured formats, while classic warehouses depended on deep ETL pipelines and physical tuning that assume predictable, slow-moving, largely internal data. Parts of the community answered by moving to Hadoop and Spark, but those platforms still lacked much of the efficiency and feature set of established warehousing technology and required significant engineering effort just to roll out, so when Snowflake began in late 2012 - with the database world fully focused on SQL on Hadoop - building a classic warehouse from scratch for the cloud looked like a contrarian and risky move.

The problem — what was actually breaking

A pure shared-nothing design couples compute to storage, so the hardware configuration ideal for bulk loading (high I/O bandwidth, light compute) is a poor fit for complex queries (low I/O bandwidth, heavy compute), forcing a compromise configuration with low average utilization.
Every membership change - a node failure or a user-requested resize - forces large amounts of data to be reshuffled by the same nodes that are simultaneously processing queries, so elasticity and availability are both limited, and in the cloud such changes are the norm rather than the exception because node failures are frequent and performance varies dramatically even among nodes of the same instance type.
Software and hardware upgrades eventually affect every node, and rolling them one node at a time with no downtime is very hard when everything is tightly coupled and expected to be homogeneous.
A growing share of warehouse data arrives from external, less controllable sources in schema-less, semi-structured formats such as JSON and Avro, which conventional ETL pipelines and physical tuning simply assume away.
S3 is a blob store with a PUT/GET/DELETE interface where objects can only be overwritten in full, the exact file size must be announced up-front in the PUT, and appending is impossible, so a file format and concurrency-control scheme built around mutable pages cannot be carried over.
Classic B+-tree indices are the wrong access method here because they depend on random access (bad for both S3 and compressed files), inflate data volume and load time, and must be explicitly created by the user, which contradicts a pure service with no physical design.

Core ideas — the contributions, and why they work

Separation of storage and compute

Snowflake handles compute and storage as two loosely coupled, independently scalable services: a proprietary shared-nothing execution engine over EC2, and Amazon S3 for the base data. This works because the cloud offers many node types and near-infinite storage, so taking advantage of the right hardware is just a matter of bringing the data to it rather than re-provisioning a fixed cluster. Crucially, local disk is no longer spent replicating base data that is large and mostly cold; it is used exclusively for temporary data and caches, both of which are hot and therefore worth putting on SSDs. Once caches are warm, performance approaches or even exceeds that of a pure shared-nothing system, so the elasticity is bought without giving up per-node efficiency.

Multi-cluster, shared-data architecture

The system is a service-oriented architecture of three layers communicating through RESTful interfaces: Data Storage on S3, Virtual Warehouses as the muscle that executes queries, and Cloud Services as the brain that manages warehouses, queries, transactions, schemas, access control, encryption keys and usage statistics. Many independent compute clusters read the same shared tables with no physical copying, which simultaneously satisfies the two historically opposed goals of data warehousing and data marts: integrate all the data in one place, but give each organizational unit private compute that cannot interfere with anyone else's. Cloud Services is heavily multi-tenant and long-lived, which improves utilization and yields better economies of scale than giving every user a private system incarnation. Each service is replicated for availability, so a service-node failure costs at most some in-flight queries, which are transparently re-executed.

Virtual warehouses as ephemeral compute

A virtual warehouse is a cluster of EC2 worker nodes presented to a single user through abstract T-shirt sizes from X-Small to XX-Large, so users never know or care how many nodes they have and Snowflake can evolve service and pricing independently of the cloud platform. VWs are pure compute: they can be created, destroyed or resized at any point on demand, with no effect whatsoever on the state of the database, and users are actively encouraged to shut all of them down when idle. Because you pay for compute-hours, running a load on 32 nodes for 2 hours instead of 4 nodes for 15 hours costs roughly the same while transforming the user experience. This is the deepest consequence of the split: compute becomes a disposable, per-workload resource rather than the thing that owns your data.

Immutable hybrid columnar table files

Tables are horizontally partitioned into large immutable files that play the role blocks or pages play in a traditional database. Within each file, values of each column are grouped together and heavily compressed - the PAX or hybrid columnar scheme - and a header records the offset of each column inside the file. Because S3 supports range GETs, a query downloads the header plus only the columns it actually references, recovering columnar I/O behaviour on top of an object store that has no notion of columns. Immutability is not just a storage convenience: it is what makes worker processes side-effect free, removes transaction management from the execution engine, and turns concurrency control into a metadata problem.

Pruning instead of indices

Rather than B+-trees, Snowflake keeps min-max distribution metadata for every individual table file - the technique known as small materialized aggregates, zone maps or data skipping - and uses it at optimization time to eliminate files that cannot satisfy the predicate. If file f1 holds values 3..5 and f2 holds 4..6 in column x, then a predicate x >= 6 needs only f2. This metadata is orders of magnitude smaller than the data, costs almost nothing to maintain on load, suits sequential access to large chunks, and above all requires no user input, which is exactly what a system with no tuning knobs needs. The same idea extends to complex expressions such as WEEKDAY(orderdate) IN (6, 7), and to auto-detected columns inside semi-structured documents.

Snapshot isolation over versioned file sets

Snowflake implements ACID transactions via Snapshot Isolation on top of MVCC, which the paper argues is the natural choice precisely because table files are immutable - a direct consequence of using S3. A write (insert, update, delete, merge) produces a new table version by adding and removing whole files, and those additions and removals are tracked in the global transactional key-value store in a form that lets the file set of any specific table version be computed very efficiently. Since old versions are just retained files plus metadata, three user-visible features fall out of the same mechanism at almost no extra cost: time travel via AT and BEFORE, UNDROP, and CLONE, which copies only metadata so two tables initially share files and then diverge independently. The paper notes it considered deferring changes through a redo-undo log and a delta store, but declined on complexity and scalability grounds.

VARIANT and schema-later semi-structured data

Snowflake adds three types - VARIANT, ARRAY and OBJECT - sharing one self-describing, compact binary encoding that supports fast key-value lookup, type tests, comparison and hashing, so VARIANT columns can serve as join, grouping and ordering keys like any other column. Users load JSON, Avro or XML straight into a VARIANT column with no schema declaration, turning ETL into ELT and letting transformation happen later with the full power of parallel SQL, including joins, sorting and aggregation that conventional ETL toolchains do badly. The performance trick is that the system does not stop at a row-wise blob: it statistically analyses the documents inside each table file, infers types, and shreds frequently occurring typed paths out into real compressed columns with their own pruning metadata. That is what makes schema-less storage nearly as fast as relational storage without any user effort, which is the point - a schema-later system that is also slow would just be a document store.

How it works — the mechanism, concretely

Table files, headers and the S3 read path

Each table file is written once and never modified, since S3 requires an object to be overwritten in full with its size declared up-front in the PUT. The file header carries, among other metadata, the offset of every column within the file, so a scan issues a range GET for the header and then range GETs for exactly the columns the query touches. S3 is also used beyond base data: query operators spill temporary data there once local disk is exhausted, which lets arbitrarily large joins and aggregations complete without out-of-memory or out-of-disk failures, and large query results are stored there too, removing the need for server-side cursors. Everything else - catalog objects, the mapping from table to S3 files, statistics, locks and transaction logs - lives in a scalable transactional key-value store inside Cloud Services, not in S3.

Worker processes, local caching, consistent hashing and file stealing

When a query arrives, each worker node in the VW (or a subset, if the optimizer detects a small query) spawns a worker process that lives only for the duration of that query and, because table files are immutable, can never produce externally visible effects, so failures are contained and routinely fixed by retry. Each worker node keeps an LRU cache on local disk of file headers and individual columns it has previously read; the cache outlives individual queries and is oblivious to them, just seeing a stream of file and column requests. To raise hit rates and avoid caching the same file on several nodes, the optimizer assigns input file sets to workers by consistent hashing over table file names, and that hashing is lazy: when nodes are added, lost or resized away, nothing is shuffled eagerly - LRU eventually rewrites the caches, which amortizes the cost, avoids a degraded mode, and is far more available than eager reshuffling. Skew is handled at scan level by file stealing: a worker that finishes its input set asks peers for more, a peer with many files left transfers ownership of one file for the duration and scope of the current query, and the requester then downloads it from S3 rather than from the peer, so the straggler is never given extra work.

Optimizer in Cloud Services and the execution engine

Every query passes through Cloud Services for parsing, object resolution, access control and plan optimization; the optimizer is Cascades-style with top-down cost-based search, and all statistics are maintained automatically on load and update. Because there are no indices the plan space is already smaller, and it is deliberately shrunk further by postponing decisions such as the join distribution method to execution time, which trades a small loss in peak performance for fewer bad plans and more predictable behaviour. The resulting plan is shipped to the participating worker nodes, and Cloud Services then tracks query state to collect performance counters and detect node failures, storing all query information for audit and analysis. The engine itself is columnar, vectorized (batches of a few thousand rows in columnar form, no materialization of intermediates, as pioneered by VectorWise/MonetDB-X100) and push-based, where operators push results downstream instead of being pulled Volcano-style, which removes control flow from tight loops and allows DAG-shaped rather than merely tree-shaped plans; there is no transaction management during execution and no buffer pool, but join, group by and sort can all spill to disk and recurse.

Static and dynamic pruning

During optimization, the per-file min-max metadata is checked against the query predicates to prune the input file set before any data is read, and this works not only for simple base-value comparisons but for derived expressions such as WEEKDAY(orderdate) IN (6, 7). Pruning metadata is kept for every individual table file and covers plain relational columns as well as a selection of auto-detected columns inside semi-structured data. At execution time Snowflake adds dynamic pruning: while building a hash join it collects statistics on the distribution of join keys on the build side, then pushes that information to the probe side to filter rows and skip entire probe-side files, on top of standard techniques such as bloom joins. Because the metadata is tiny relative to the data, this adds little overhead to loading, optimization or execution - the property that lets a no-tuning system rely on it as its only access-path mechanism.

Semi-structured data made columnar

For each table file independently, Snowflake statistically analyses the collection of documents it contains, performs automatic type inference, and determines which typed paths are frequently common; those columns are then removed from the documents and stored separately in the same compressed columnar format as native relational data, complete with materialized aggregates for pruning. On scan the pieces can be reassembled into a single VARIANT value, but since most queries want only a few paths, projection and cast expressions are pushed down into the scan operator so only the needed columns are read and cast directly into the target SQL type. Doing this per file keeps storage efficient under schema evolution but breaks pruning, because a path may exist in most files yet be frequent enough to warrant metadata in only some; the conservative fallback would be to scan every file lacking metadata, so Snowflake instead stores Bloom filters over all paths present in each file's documents (paths, not values) and probes them during optimization to skip files that cannot contain the required path. Separately, optimistic conversion handles values such as dates that arrive as strings: the system converts at write time but keeps both the converted value and the original string in separate columns unless the conversion is fully reversible, so nothing is lost, and since unused columns are never read the double storage costs almost nothing at query time.

Fault resilience and standby capacity

S3 is replicated across availability zones, and Snowflake matches that by distributing and replicating its metadata store across AZs as well, while the remaining Cloud Services run as stateless nodes across multiple AZs behind a load balancer. A single node failure or even a full AZ failure therefore causes no system-wide impact, at worst failing the queries of users currently connected to a lost node, who are redirected on their next query. Virtual warehouses are deliberately not spread across AZs, because distributed query execution needs the much higher network throughput available within one AZ; if a worker node dies mid-query the query fails but is transparently re-executed, either with the node immediately replaced from a small pool of standby nodes (also used for fast VW provisioning) or with a temporarily reduced node count. The one accepted gap is a full AZ outage, which kills all queries on VWs in that zone and requires the user to actively re-provision elsewhere.

Online upgrade and background rekeying

All services are effectively stateless, with hard state in the transactional key-value store accessed through a mapping layer that handles metadata versioning and schema evolution with guaranteed backward compatibility, which lets two versions of the whole system run side by side. An upgrade deploys the new version alongside the old, progressively switches user accounts so their new queries go to the new version, lets in-flight queries finish on the old version, then decommissions it; both versions share one metadata store, and VWs of different versions can even share worker nodes and their caches, so nothing has to be repopulated. All services are upgraded once per week, and because upgrade and downgrade are continuously exercised in a pre-production incarnation, a critical bug can be answered by a fast downgrade or an out-of-schedule fix. The same storage/compute split makes security maintenance invisible: file keys are derived cryptographically from the table key and the unique file name rather than being stored, so rekeying re-encrypts files on worker nodes separate from query nodes, atomically flips table metadata to the new files, and deletes the old ones once ongoing queries finish.

What the paper showed — measurements and proofs

On TPC-H-like data at SF100 (100 GB) and SF1000 (1 TB) loaded from plain JSON into both a relational schema and a schema-less schema (every table a single VARIANT column, no hints, no tuning), all 22 queries on a medium standard warehouse over three warm-cache runs showed schema-less overhead of around 10 percent for every query except Q9 and Q17 at SF1000, whose slowdown was traced to a sub-optimal join order caused by a known bug in distinct value estimation.
The service is not a prototype: implementation began in late 2012, Snowflake became generally available in June 2015, and at the time of writing it ran several million queries per day over multiple petabytes of data on a live system of hundreds of nodes.
The paper's elasticity illustration is that a data load taking 15 hours on 4 nodes might take only 2 hours on 32 nodes, and because users pay for compute-hours the overall cost is very similar while the user experience is dramatically different.
Fault tolerance rests on S3's replication across availability zones, which the paper cites as guaranteeing 99.99 percent data availability and 99.999999999 percent durability, with Snowflake's own metadata store distributed and replicated across AZs to match.
Online upgrade is a production practice, not a design aspiration: all services are upgraded once per week with no downtime, with two versions sharing one metadata store and VWs of different versions sharing worker nodes and caches so caches need no repopulation.
Security uses AES 256-bit encryption with a four-level key hierarchy (root, account, table, file) rooted in AWS CloudHSM, with keys rotated at regular intervals (for example monthly) and data rekeyed after a longer interval (for example yearly) to complete the NIST 800-57 key life cycle, with root keys never leaving the HSM devices.

Limits and trade-offs — conceded and discovered

Conceded by the paper: Snowflake does not perform partial retries, so a single worker failure aborts the whole query and it is re-executed from the beginning, which the authors call out as a concern for very large, long-running queries.
Conceded by the paper: virtual warehouses are not distributed across availability zones, a deliberate choice for network throughput, so a full AZ outage fails every query on the VWs in that zone and the user must actively re-provision elsewhere - an accepted scenario of partial unavailability.
Conceded by the paper: worker nodes are never shared across virtual warehouses, which buys strong performance isolation at the cost of utilization, and worker processes are ephemeral, entailing a per-query start-up cost; the authors name worker-node sharing and process recycling as future work.
Conceded by the paper: the optimizer deliberately defers decisions and accepts a small loss in peak performance for robustness, and the reported TPC-H numbers themselves expose a real optimizer defect - a distinct-value-estimation bug producing bad join orders on Q9 and Q17 at SF1000.
Exposed by later experience: with no indices and no user-specifiable physical design, pruning only helps to the degree that data happens to be clustered on the predicate columns, which is why Snowflake later had to add automatic clustering, materialized views and a search optimization service; and the paper's only measurements are self-run TPC-H-like experiments with warm caches, with no head-to-head comparison against Redshift, BigQuery or Azure SQL DW despite discussing all three.

What it became — the systems that inherited it

Snowflake's separation of storage and compute became the default architecture of the cloud data platform, and the vocabulary of the paper - virtual warehouse, multi-cluster shared data, pay for what you run - became the vocabulary of the market; Amazon retrofitted the idea into Redshift with RA3 managed storage and later Redshift Serverless, and Azure Synapse, Firebolt, ClickHouse Cloud and the disaggregated modes of StarRocks and Apache Doris all follow the same shape. The immutable-file plus versioned-metadata design is the exact recipe later opened up by Apache Iceberg, Delta Lake and Apache Hudi, whose snapshots, manifest-level min-max statistics and zero-copy or shallow clones reproduce Snowflake's table-file model, time travel and CLONE in an open format; Snowflake's own marketing later renamed these table files micro-partitions, which is how most practitioners now know them. Databricks answered directly with the lakehouse, adopting the same split of cheap object storage from elastic compute while keeping open formats, and the resulting rivalry defined a decade of analytics purchasing. The VARIANT type and its shredding-into-columns implementation proved influential enough that a native variant type was subsequently adopted in Apache Spark and in open table formats, and semi-structured-as-a-first-class-citizen is now expected rather than remarkable. Operationally, the paper's pure-SaaS claims - weekly online upgrades, no tuning knobs, no physical design, no vacuuming - set the customer expectation that a database is something you use rather than something you administer, and Snowflake's 2020 IPO, then the largest software IPO ever, made that architectural argument a financial one. The one piece the industry pushed past is the paper's assumption that pruning alone suffices as an access method: automatic clustering, richer per-file statistics and secondary structures were all added afterward.

In the paper’s words — verbatim

“It is perfectly legal (and encouraged) that users shut down all their VWs when they have no queries.”

§3.2.1 Elasticity and Isolation

“MVCC is a natural choice given the fact that table files are immutable, a direct consequence of using S3 for storage.”

§3.3.2 Concurrency Control

“As a result, today, Snowflake has only one tuning parameter: how much performance the user wants (and is willing to pay for).”

§6 Lessons Learned and Outlook

Vocabulary — as this paper uses it

Multi-cluster, shared-data architecture: Snowflake's name for its design: multiple independent shared-nothing compute clusters that all read the same shared data in a blob store, with local disk used only for caches and temporary data. It preserves shared-nothing execution efficiency while decoupling it from data ownership.
Virtual warehouse (VW): A cluster of EC2 worker nodes presented to a single user in abstract T-shirt sizes from X-Small to XX-Large. It is pure compute, can be created, resized or destroyed at any time without touching database state, and each query runs on exactly one VW.
Table file: The unit of storage: a large, immutable file into which a table is horizontally partitioned, equivalent to a block or page in a traditional database. Writes never modify one; they produce a new table version by adding and removing whole files.
PAX / hybrid columnar: The layout inside a table file, in which the values of each column are grouped together and heavily compressed, with the header recording each column's offset. Combined with S3 range GETs, it lets a query download only the columns it references.
Pruning: Using per-file min-max metadata (also known as small materialized aggregates, zone maps or data skipping) to decide that a file cannot satisfy a predicate and skip it entirely. In Snowflake it replaces indices as the sole data-access-limiting mechanism, requiring no user input.
File stealing: The skew-handling technique in which a worker process that has finished scanning its input files asks peers for more, and a peer with many files left transfers ownership of one for the scope of the current query. The requester downloads that file from S3, not from the peer, so straggler nodes get no extra load.
VARIANT: A SQL type that can hold any native SQL value, a variable-length ARRAY, or an OBJECT map from strings to VARIANTs, all in one self-describing compact binary encoding. Because the encoding supports fast lookup, type tests, comparison and hashing, VARIANT columns work as join, grouping and ordering keys.
Optimistic conversion: Converting string-encoded values such as dates to their real SQL type at write time, while keeping the original string in a separate column unless the conversion is fully reversible. It buys read-time speed and pruning metadata for dates without risking information loss on values that only look like dates or numbers.
Time travel: Reading an earlier version of a table, schema or database using AT or BEFORE with an absolute time, a relative offset or a prior statement ID. It works because files removed by a new version are retained for a configurable period, currently up to 90 days, and it is also what makes UNDROP possible.

On the timeline — where this sits in the story

Year2014–2016

MilestoneSnowflake cloud data warehouse

Problem solvedTraditional warehouses coupled storage, compute, scaling, and workload management too tightly.

How it solved itSnowflake separated storage and compute with elastic scaling and multi-cluster workload isolation.

Why it matteredDefined a cloud-native warehouse architecture that reshaped enterprise analytics purchasing and design.

DDIACh. 1: Data Systems Trade-offs Ch. 4: Storage & Retrieval

View on the timeline