Snowflake is multi-tenant, transactional, secure, highly scalable and elastic system with full SQL support and built in extensions for semi-structured and schema-less data.
Multi-cluser, Software as a Service, shared-data architecture
Problem
- Traditional solutions depend on deep ETL pipelines and tuning. Big data Systems such as Hadoop require significant engineering effort to roll out and use and lack much of the efficiency and feature sets of data warehouse.
- shared-nothing architecture
- Every query processor node has its own local disks. Tables are horizontally partitioned and each node is only responsible for the rows on its local disks.
- Every node has the same respomsibilities and runs the same hardware
- Important Drawback of shared nothing →Tightly couples compute resources and storage resources.
- Membership changes → If a set of nodes changes due to failure or resize, large amount of data needs to be reshuffled. Since same nodes are responsible for data shuffling and query processing, a significant performance impact can be observed, thus limiting elasticity and availability.
- Heterogeneous workloads → While hardware is homogenous the workload is not. System configuration that is ideal for bulk loading (high bandwidth, low compute) is a poor fit for complex queries. (Low I/O , heavy compute)
Architecture
- Snowflake separates storage and compute. Compute is provided by shared-nothing engine and storage can work on any blob store.
- To reduce network traffic, each compite node cache soem table data on local disk.
- Service oriented architecture.
-
Data Storage →
- Blob store, e.g. S3. S3 is choosen due to very high durability and availability.
- Cons: Higher access latency compared to local storage, high CPU overhead with I/O, especially if HTTP is enabled
- Because S3 provides GET requests over parts of files, only the file header and required columns can be downloaded.
-
Virtual Warehouse (VW)
- Handles query execution with elastic clusters.
- Elasticity and Insulation
- Each query runs exactly on one VW
- Each user may have multiple VWs running at any given time, each VW in turn mayt be running multiple queries. Every VW has access to the shame shared tables.
- Local caching and File Streaming
- Cache holds file headers and individual columns of files, since queries download only the columns they need.
- Cache lives for the duration fo the VW worker and is shared among concurrent and subsequent worker processes.[LRU]
-
Cloud Services → Control plane, management for queries, transactinos, metadata, schemas, ACL, encryption keys, usage statistics
- Query optimizer assigns input files to worker nodes using consistent hashing and loads data lazily. Optimizer follows Cascade style approach with top down cpst based optimization.
- No indices.
- Postones many decisions until execution time.
- Pruning
- Small materialized aggregates, for each chunk of data the system maintains distribution information that can tell if the chunk matches a given predicate.
- Concurrency control
- Access pattern → large reads, bulk inserts, bulk updates.
- ACID transactions via Snapshot Insulation. (SI)
- Metadata
- catalog objects, which tables consist which S3 files, statistics, locks, tx logs is stored in TX Key/Value store. This is ✅ FoundationDB: A Distributed Unbundled Transactional Key [Apple, Snowflake, 2021]
- Query management
- Optimizer follows
- Query optimizer assigns input files to worker nodes using consistent hashing and loads data lazily. Optimizer follows Cascade style approach with top down cpst based optimization.
-
Execution Engine
- columnar, vectorized and push based.
- Snowflake avoids materialization of intermediate results. Uses Pipeline architecture, in batches of a few thousand rows. This saves I/O.
- Relational operators push their results downstream. Push improves efficiency because it remove control flow logic from tight loops.
- Queries are executed against a fixed set of immutable files.
-
- Deployments
- Backwards compatibility. New deployments contain the new version of the service along with the old one. Any in flight queries are let to complete on the old one.