The Snowflake Elastic Data Warehouse

1 The Snowflake Elastic Data WarehouseBenoit Dageville, Thierry Cruanes, Marcin Zukowski, Vadim Antonov, Artin Avanes,Jon Bock, Jonathan Claybaugh, Daniel Engovatov, Martin Hentschel,Jiansheng Huang, Allison W. Lee, Ashish Motivala, Abdul Q. Munir, Steven Pelley,Peter Povinec, Greg Rahn, Spyridon Triantafyllis, Philipp UnterbrunnerSnowflake ComputingABSTRACTWe live in the golden age of distributed computing. Pub-lic cloud platforms now offer virtually unlimited computeand storage resources on demand. At the same time, theSoftware-as-a-Service (SaaS) model brings enterprise-classsystems to users who previously could not afford such sys-tems due to their cost and complexity. Alas, traditionaldata warehousing systems are struggling to fit into this newenvironment. For one thing, they have been designed forfixed resources and are thus unable to leverage the cloud selasticity. For another thing, their dependence on complexETL pipelines and physical tuning is at odds with the flex-ibility and freshness requirements of the cloud s new typesof semi-structured data and rapidly evolving decided a fundamental redesign was in order.

2 Ourmission was to build an enterprise-ready data warehousingsolution for the cloud. The result is the Snowflake ElasticData Warehouse , or Snowflake for short. Snowflake is amulti-tenant, transactional, secure, highly scalable and elas-tic system with full SQL support and built-in extensions forsemi-structured and schema-less data. The system is offeredas a pay-as-you-go service in the amazon cloud. Users up-load their data to the cloud and can immediately manageand query it using familiar tools and interfaces. Implemen-tation began in late 2012 and Snowflake has been generallyavailable since June 2015. Today, Snowflake is used in pro-duction by a growing number of small and large organiza-tions alike. The system runs several million queries per dayover multiple petabytes of this paper, we describe the design of Snowflake andits novel multi-cluster, shared-data architecture. The paperhighlights some of the key features of Snowflake: extremeelasticity and availability, semi-structured and schema-lessdata, time travel, and end-to-end security.

3 It concludes withlessons learned and an outlook on ongoing and Subject DescriptorsInformation systems [Data management systems]: Data-base management system enginesPermission to make digital or hard copies of part or all of this work for personal orclassroom use is granted without fee provided that copies are not made or distributedfor profit or commercial advantage and that copies bear this notice and the full citationon the first page. Copyrights for third-party components of this work must be all other uses, contact the owner/author(s).SIGMOD/PODS 16 June 26 - July 01, 2016, San Francisco, CA, USAc 2016 Copyright held by the owner/author(s).ACM ISBN 978-1-4503-3531-7/16 : warehousing, database as a service, multi-cluster shareddata architecture1. INTRODUCTIONThe advent of the cloud marks a move away from softwaredelivery and execution on local servers, and toward shareddata centers and software-as-a-service solutions hosted byplatform providers such as amazon , Google, or shared infrastructure of the cloud promises increasedeconomies of scale, extreme scalability and availability, anda pay-as-you-go cost model that adapts to unpredictable us-age demands.

4 But these advantages can only be capturedif thesoftwareitself is able to scale elastically over the poolof commodity resources that is the cloud. Traditional datawarehousing solutions pre-date the cloud. They were de-signed to run on small, static clusters of well-behaved ma-chines, making them a poor architectural not only the platform has changed. Data has changedas well. It used to be the case that most of the data in adata Warehouse came from sources within the organization:transactional systems, enterprise resource planning (ERP)applications, customer relationship management (CRM) ap-plications, and the like. The structure, volume, and rate ofthe data were all fairly predictable and well known. Butwith the cloud, a significant and rapidly growing share ofdata comes from less controllable or external sources: ap-plication logs, web applications, mobile devices, social me-dia, sensor data (Internet of Things).

5 In addition to thegrowing volume, this data frequently arrives in schema-less,semi-structured formats [3]. Traditional data warehousingsolutions are struggling with this new data. These solu-tions depend on deep ETL pipelines and physical tuningthat fundamentally assume predictable, slow-moving, andeasily categorized data from largely internal response to these shortcomings, parts of the data ware-housing community have turned to Big Data platformssuch as Hadoop or Spark [8, 11]. While these are indis-pensable tools for data center-scale processing tasks, and theopen source community continues to make big improvementssuch as the Stinger Initiative [48], they still lack much of theefficiency and feature set of established data warehousingtechnology. But most importantly, they require significantengineering effort to roll out and use [16].We believe that there is a large class of use cases andworkloads which can benefit from the economics, elasticity,and service aspects of the cloud, but which are not wellserved by either traditional data warehousing technology or215by Big Data platforms.

6 So we decided to build a completelynew data warehousing system specifically for the cloud. Thesystem is called the Snowflake Elastic Data Warehouse , or Snowflake . In contrast to many other systems in the clouddata management space, Snowflake is not based on Hadoop,PostgreSQL or the like. The processing engine and most ofthe other parts have been developed from key features of Snowflake are as Software-as-a-Service (SaaS) ExperienceUsersneed not buy machines, hire database administrators,or install software. Users either already have their datain the cloud, or they upload (or mail [14]) it. They canthen immediately manipulate and query their data us-ing Snowflake s graphical interface or standardized in-terfaces such as ODBC. In contrast to other Database-as-a-Service (DBaaS) offerings, Snowflake s service as-pect extends to the whole user experience. There areno tuning knobs, no physical design, no storage groom-ing tasks on the part of has comprehensive support for ANSISQL and ACID transactions.

7 Most users are able tomigrate existing workloads with little to no offers built-in functions andSQL extensions for traversing, flattening, and nest-ing of semi-structured data, with support for popularformats such as JSON and Avro. Automatic schemadiscovery and columnar storage make operations onschema-less, semi-structured data nearly as fast as overplain relational data, without any user and compute resources can be scaled in-dependently and seamlessly, without impact on dataavailability or performance of concurrent AvailableSnowflake tolerates node, cluster, andeven full data center failures. There is no downtimeduring software or hardware is designed for extreme durability withextra safeguards against accidental data loss: cloning,undrop, and cross-region is highly compute-efficient and alltable data is compressed. Users pay only for whatstorage and compute resources they actually data including temporary files and network traf-fic is encrypted end-to-end.

8 No user data is ever ex-posed to the cloud platform. Additionally, role-basedaccess control gives users the ability to exercise fine-grained access control on the SQL currently runs on the amazon cloud (AmazonWeb Services, AWS), but we may port it to other cloudplatforms in the future. At the time of writing, Snowflakeexecutes millions of queries per day over multiple petabytesof data, serving a rapidly growing number of small and largeorganizations from various paper is structured as follows. Section 2 explains thekey design choice behind Snowflake: separation of storageand compute. Section 3 presents the resulting multi-cluster,shared-data architecture. Section 4 highlights differentiat-ing features: continuous availability, semi-structured andschema-less data, time travel and cloning, and end-to-endsecurity. Section 5 discusses related work. Section 6 con-cludes the paper with lessons learned and an outlook onongoing STORAGE VERSUS COMPUTES hared-nothing architectures have become the dominantsystem architecture in high-performance data warehousing,for two main reasons: scalability and commodity a shared-nothing architecture, every query processor nodehas its own local disks.

9 Tables are horizontally partitionedacross nodes and each node is only responsible for the rowson its local disks. This design scales well for star-schemaqueries, because very little bandwidth is required to join asmall (broadcast) dimension table with a large (partitioned)fact table. And because there is little contention for shareddata structures or hardware resources, there is no need forexpensive, custom hardware [25].In a pure shared-nothing architecture, every node has thesame responsibilities and runs on the same hardware. Thisapproach results in elegant software that is easy to reasonabout, with all the nice secondary effects. A pure shared-nothing architecture has an important drawback though:it tightly couples compute resources and storage resources,which leads to problems in certain WorkloadWhile the hardware is homo-geneous, the workload typically is not. A system con-figuration that is ideal for bulk loading (high I/O band-width, light compute) is a poor fit for complex queries(low I/O bandwidth, heavy compute) and vice , the hardware configuration needs to bea trade-off with low average ChangesIf the set of nodes changes; eitheras a result of node failures, or because the user choosesto resize the system; large amounts of data need to bereshuffled.

10 Since the very same nodes are responsiblefor both data shuffling and query processing, a sig-nificant performance impact can be observed, limitingelasticity and UpgradeWhile the effects of small membershipchanges can be mitigated to some degree using repli-cation, software and hardware upgrades eventually af-fecteverynode in the system. Implementing onlineupgrades such that one node after another is upgradedwithout any system downtime is possible in principle,but is made very hard by the fact that everything istightly coupled and expected to be an on-premise environment, these issues can usually betolerated. The workload may be heterogeneous, but there islittle one can do if there is only a small, fixed pool of nodeson which to run. Upgrades of nodes are rare, and so arenode failures and system situation is very different in the cloud. Platforms suchas amazon EC2 feature many different node types [4]. Tak-ing advantage of them is simply a matter of bringing the datato the right type of node.