Snowflake Architecture and Pricing

State Management

• Cloud promises easy elasticity and scalability
• FaaS (e.g., AWS Lambda) comes close to stateless computation
• Load balancers and web servers scale easily and are stateless
• Scalable components often rely on stateful storage or database systems
• Stateful systems become synchronization and scaling points
• Scaling data management systems is challenging due to large volumes, high update rates, and durability requirements
• Database systems are key components in cloud services like OLTP for managing control plane state

Benchmarking

• Successful service use requires understanding functionality, cost, and performance (e.g., S3)
• S3 costs are $21/TB/month, $0.4/M GET, $5/M PUT
• Documentation often lacks performance details beyond SLAs
• Designing cost-efficient architectures requires knowing performance properties
• Validating and understanding performance of a service requires benchmarking
• Reference: Experiments from "Exploiting Cloud Object Storage for High-Performance Analytics, Durner et al., VLDB 2023"

S3 Latency

• Latency characteristics vary depending on size of the objects being accessed

Request Latency

• Request latency over time indirectly measures overall S3 utilization
• Latency patterns can show workday/weekend variations
• Artificial throttling may cause unnatural upper bounds

S3 Bandwidth

• Achieved high bandwidth by exploiting network bandwidth of the system
• Achieved by running many parallel requests

Request examples

• Seek latency is 30ms, scan speed is 50MB/s
• Achieving 80Gbit/s (=10GB/s) the request arrival rate (入) of 10GB/s : 16MB = 640/s, given 16MB requests
• Time in system for 16MB request (W): 30ms + 16MB/50MB/s = 350ms = 0.35s
• Little's law (the number of requests in the systems L): 640/s * 0.35s = 224

S3 GET Cost

• EC2 dominates S3 GET cost for request sizes over 16MB

S3 Performance Summary

• Each access has a latency of over 10ms and utilizes bandwidth similar to single disk disk (around 50 MB/s)
• Achieves high bandwidth with many disks available
• Request costs for small objects are high but are negligible for larger objects, especially when compared to EC2
• Similar cost and performance to other vendors

How to implement S3

• Load balancers, API servers, metadata storage, and object storage scale individually
• Additionally, it involves asynchronous/background storage management
• Object partitioning of data

S3 In The Real World (2023)

• Implemented using internal microservices
• A single large customer stores 600PB of data
• Handles 280 trillion objects and 100M requests per second overall

Cost efficient object store?

• S3 latencies and prices imply storage on disk other than SSD
• The cheapest disk instance in terms of GB/$ is d3en.12xlarge at $2,271/month for 335TB = $6.78/TB/month, calculating a discount
• S3 is S21/TB/month for comparison
• Needs more than one disk copy for redundancy

d3en requests

• Disks have around 100MB/s throughput
• Reading a 13,980GB disk takes 38 hours
• 24 * 100MB/s = 2.4 GB/s is the I/O bandwidth
• Disks typically have a 10ms latency
• 24 * 1s/10ms = 2400 IOPS

Object access

• Objects accessed more frequently than every 38 hours need duplication to handle the workload
• Workloads are skewed; less frequent access data, more is frequent
• Caching hot objects (on RAM, SSD, or both) makes sense
• Could be an additional service in front of the disk storage servers
• The objects could utilize API servers or metadata for caching

How To Achieve 11'9s?

• One approach involves three full copies across two or three AZs, with a cost of $6.78*3 = $20.34
• Use erasure coding as an alternative to 11'9s

Summary of durability

• S3 guarantees 11'9s durability, but only 3'9s availability

DBMS Market

• $80 Billion USD per year market size

OLTP vs OLAP

• Online Transaction Processing (OLTP) is simple and latency-critical with many inserts/deletes/updates and optimized for writing using row stores such as Aurora
• Online Analytical Processing (OLAP) focuses on reading, batch updates, and large table scans optimized with column stores over the last 5 years such as AWS Redshift

DynamoDB

• Multi-tenant distributed key/value store
• Supports Create, Update, Read, Delete (CRUD) operations
• It supports eventual, strong, and transactional consistency for reads
• Transactional functionality limited to read committed isolation, size, and one-shot operations
• Follows provisioned capacity and on-demand pricing models
• Write request unit: 1/write (up to 1 KB), 2/transactional write
• Read request unit: 1/strong, 2/transactional, 0.5/eventual consistency

Pricing

• The pricing is
  • $1.25 per million write request units
  • $0.25 per million read request units
  • $250 per TB/month

Classic DBMS Design for OLTP

• Data is organized as fixed-size pages (e.g., 8KB)
• Disk serves as the primary storage medium
• B-trees index rows
• Pages cached in RAM
• Inserts/updates/deletes are applied to pages in cache and logged in a write-ahead log (WAL)
• On commit, force WAL to disk (but not necessarily changed pages)
• Asynchronously write WAL to log archive and backup pages
• PostgreSQL, MySQL, and SQL Server work like this

Classic DBMS in the Cloud

• Classical design run on a cloud VM with instance storage
• Data is lost when the instance fails
• RAID will not help due to lack of physical disk access
• Recoverable from backup and a log archive
• Downtime will occur as a result
• Latest changes may be lost as a result
• Scalability, elasticity, and compute/storage disaggregation are a consideration

Remote Block Device & RBD details

• Improve durability when using virtual disks (e.g., , EBS) instead of instance storage
• Disk is attachable to a new instance if one shuts down
• Better durability is offered with virtual disks
• The RBD solution will cost more than standard solutions

Primary/Secondary Design

• Systems run on two identical nodes
• Primary node is the main node
• Write transactions handled by main node
• WAL shipped to the secondary
• Entries are applied eagerly
• Rapid switching to secondary node in the event of a failure
• Improved availability and durability are achieved

Amazon, Aurora

• Dominant cloud-native OLTP system that was introduced in 2014
• Storage and compute disaggregated
• Primary processing node (plus secondary available)
• Multi-tenant page and logging service

Details

• No writing of pages to disk
• WAL entries are written to 6 storage nodes in different AZs Log records replayed in the cloud
• Primary reads from of these three nodes
• Individual servers are fully functional without external
• Backups and log stored on S3

Pricing Standard

• The compute depends on instance size is $100/TB per month
• I/O is priced at $0.2/million

1/0-Optimized:

• Depends on compute, storage and instance size for billing
• $225/tb/month costs
• S3 Storage is free
• Network Architecture is an important component

Microsoft, Socrates

• This system's commercial name is SQL Database Hyperscale This design removes overhead, but the architecture has concerns Observations Regarding Aurora:
• Log service different from page
• 3 Copies is too expensive

Changes compared to Aurora

Key components can implement separated logging service

System types

• Classic
• HADR
• RBD
• Aurora-Like
• Socrates-Like

Properties

• In classic systems, logs reside on the same disk.
• Hot/Cold SSD can be deployed to accelerate recovery.
• Network infrastructure is an important component

Fully Distributed OLTP Systems

• Bottleneck with scalability for all system designs
• All writings go through systems memory
• Explicit provisioning may be required
• DynamoDB, Fully distributed available
• A major is the trade offs which can limit systems

OLAP Systems

• Optimized table scanning is available
• Large columnar storage is the major design consideration
• Must have a parallelism/distribution

Traditional OLAP Design

• Horizontal Partitioning is essential
• The system shares nothing
• Row are partitioned and not by column
• Specify each user partition for storage
• The original version of Amazon Redshift used horizontal partition

Modern OLAP Design

• Cloud Object Stores are available with S3 solutions
• This model allows for disaggregation and data transmission
• Snowflake used multi-tenant systems
• Snowflake: Multi tenant system that supported OLTP
• Query processing: virtual instances of an EC2 system Cloud object storage used for all processing

Snowflake Details

• Control plane: query OLTP systems for the latest blocks
• Blocks cache to disk and query
• Engine is in play

Snowflake Query Step-By-Step Example

1. control plane: -query OLTP systems for all current blocks
   • filter necessary blocks using lightweight per-block indexes/filters
   • send query plan and filtered blocks to query engine nodes
2. Query Engine

• request data from cache/storage
• execute query

3. Storage

• implemented as immutable blocks on S3 1,2,3- details

Cloud object stores and distributed store is supported over multi cloud

Features of Snowflake Details

• Cloud object store stores immutable data blocks, e.g., 10K tuples
• Blocks can be cached on query-processing nodes.
• Consistent hashing helps with elasticity.
• Updates/transactions:
• Update transactions create new objects.
• Coordinated in control plane using OLTP system's (FoundationDB) transaction functionality.
• Read queries will either see old or new objects.
• Elasticity: - Cluster size can be adjusted.

Summary continued...

• A pool of worker nodes is maintained to make this quick.
• Configurable auto-shutdown/startup (e.g., after 15 minutes of no queries). -One can launch several virtual warehouses for the same database as needed.

Snowflake Pricing

At least ~ $2/hour, $3-4 additional

The cost is multiplied by the cluster size: Small $2.00, medium $4.00

Hardware not specified and was C5d-2xlarge in ec2 = 8VCPU and 16G and .2 SSD

There is a $40 TB cost on storage and $24 dollars per month for capacity

Can be referenced online = https://ww.snowflakepricing.com

RedShift Pricing information

• Redshift has a traditional = data warehouse nothing share
• It operates on a $4-dollar model plus $24 dollars of storage
• Redshift does not perform storage measures and calculations from the same servers.
• Name and sizes are provided for all pricing calculations.
• RedShift managed storage is also available for users.
• Redshift does not perform storage measures and calculations from the same servers.
• Name and sizes are provided for all pricing calculations.

More specific examples:

• name vCPU Memory I/O Price Redshift (traditional = shared nothing):
• dc2.large 2 15 GiB 160 GB 0.60 GB/s $0.25/h
• dc2.8xlarge 32 244 GiB 2.7 TB 7.50 GB/s $4.80/h Redshift (managed storage):
• ra3.xlplus 4 32 GiB 0.65 GB/s $1.09/h
• ra3.4xlarge 12 96 GiB 2.00 GB/s $3.26/h
• ra3.16xlarge 48 384 GiB 8.00 GB/s $13.04/h

$24 Cost per TB per month

Query as a Service Pricing

• Pricing is often charged by cluster size
• Average utilization is often low as a result
• Google Redshift is often an excellent system for the reasons above. The cost scans $5 dollars\TB

No server administration necessary

How Are QaaS Systems Implemented?

• The implementation is dependent on the size and cluster itself.
• There are many ways to optimize code and store data efficiently. often best-effort per-query latency guarantee = the number of nodes used for one query is scaled with query size billing by scan size alone is exploitable-
• This is the way QaaS systems charge CPU or time limits may be available

QaaS systems

• The big query implementation details (https://www.vldb.org/pldb/vol13/p3461-melnik.pdf)
• Billing and scans are useful

Serverless FaaS analytic pricing

• This approach drives query latency to zero with many tenants

Idea- execute the query with server list functions

A prototype can be reviewed online - arxiv.org/pdf/1912.00937

This quiz covers Snowflake's architecture, including the control plane, query processing, and storage mechanisms. It also tests knowledge of Snowflake's data handling, pricing models, and advantages over other systems like Redshift. Assess your understanding of Snowflake's elasticity and cost structure.