Snowflake Data Warehouse Architecture

┌────────────────────────────────────────────────────────────────────────────────────────────┐
│                                   CLOUD  SERVICES  LAYER                                   │
│   ┌────────────────────────────────────────────────────────────────────────────────────┐   │
│   │                          Authentication & Access Service                           │   │
│   └────────────────────────────────────────────────────────────────────────────────────┘   │
│                                                                                            │
│    ┌──────────────┐ ┌────────────┐ ┌────────────────┐ ┌────────────┐ ┌────────────────┐    │
│    │Infrastructure│ │ Optimizer  │ │    Metadata    │ │  Security  │ │ Availability & │    │
│    │   Manager    │ │            │ │    Manager     │ │            │ │  Transactions  │    │
│    └──────────────┘ └────────────┘ └────────────────┘ └────────────┘ └────────────────┘    │
└────────────────────────────────────────────────────────────────────────────────────────────┘
                                              │
                                              ▼
┌────────────────────────────────────────────────────────────────────────────────────────────┐
│                           COMPUTE  LAYER   (VIRTUAL  WAREHOUSES)                           │
│                                                                                            │
│    ┌──────────────────┐ ┌──────────────────┐ ┌──────────────────┐ ┌──────────────────┐     │
│    │     Cache &      │ │     Cache &      │ │     Cache &      │ │     Cache &      │     │
│    │ Micro-partitions │ │ Micro-partitions │ │ Micro-partitions │ │ Micro-partitions │     │
│    └──────────────────┘ └──────────────────┘ └──────────────────┘ └──────────────────┘     │
│                                                                                            │
│                     (Independent compute, auto-scaling, multi-cluster)                     │
└────────────────────────────────────────────────────────────────────────────────────────────┘
                                              │
                                              ▼
┌────────────────────────────────────────────────────────────────────────────────────────────┐
│                                  DATABASE  STORAGE  LAYER                                  │
│                                                                                            │
│            ┌────────────────────┐ ┌────────────────────┐ ┌────────────────────┐            │
│            │     ▒▒▒▒▒▒▒▒▒▒     │ │     ▒▒▒▒▒▒▒▒▒▒     │ │     ▒▒▒▒▒▒▒▒▒▒     │            │
│            │       Tables       │ │  Micro-partitions  │ │    Time Travel     │            │
│            │     (columnar)     │ │    (immutable)     │ │    & Fail-safe     │            │
│            └────────────────────┘ └────────────────────┘ └────────────────────┘            │
│                                                                                            │
│                (Centralized, encrypted at rest, auto-managed by Snowflake)                 │
└────────────────────────────────────────────────────────────────────────────────────────────┘

Snowflake is a cloud-based data warehouse platform that uses a unique multi-cluster, shared data architecture. It separates storage and compute resources, allowing them to scale independently.

Architecture Overview

Snowflake's architecture consists of three main layers that work together to provide a powerful, scalable, and flexible data warehousing solution:

1. Cloud Services/Client Layer (Top Layer)
2. Query Processing/Compute Layer (Middle Layer)
3. Database Storage Layer (Bottom Layer)

Architecture Diagram

┌──────────────────────────────────────────────────────────┐
│                  CLIENT/CLOUD SERVICES LAYER             │
│  ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌─────────────┐  │
│  │   Web    │ │   ODBC   │ │   JDBC   │ │   SnowSQL   │  │
│  │Interface │ │  Driver  │ │  Driver  │ │   CLI Tool  │  │
│  └──────────┘ └──────────┘ └──────────┘ └─────────────┘  │
│                                                          │
│  ┌────────────────────────────────────────────────────┐  │
│  │           Authentication & Access Control          │  │
│  │          Query Parsing & Optimization              │  │
│  │             Metadata Management                    │  │
│  └────────────────────────────────────────────────────┘  │
└──────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────┐
│                      COMPUTE LAYER                      │
│                                                         │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐   │
│  │   Virtual    │  │   Virtual    │  │   Virtual    │   │
│  │  Warehouse 1 │  │  Warehouse 2 │  │  Warehouse 3 │   │
│  │              │  │              │  │              │   │
│  │  ┌────────┐  │  │  ┌────────┐  │  │  ┌────────┐  │   │
│  │  │Compute │  │  │  │Compute │  │  │  │Compute │  │   │
│  │  │ Node 1 │  │  │  │ Node 1 │  │  │  │ Node 1 │  │   │
│  │  └────────┘  │  │  └────────┘  │  │  └────────┘  │   │
│  │  ┌────────┐  │  │  ┌────────┐  │  │  ┌────────┐  │   │
│  │  │Compute │  │  │  │Compute │  │  │  │Compute │  │   │
│  │  │ Node 2 │  │  │  │ Node 2 │  │  │  │ Node n │  │   │
│  │  └────────┘  │  │  └────────┘  │  │  └────────┘  │   │
│  └──────────────┘  └──────────────┘  └──────────────┘   │
│                                                         │
│              (Auto-scaling, Multi-cluster)              │
└─────────────────────────────────────────────────────────┘
                              │
                              ▼
┌──────────────────────────────────────────────────────────┐
│                      STORAGE LAYER                       │
│                                                          │
│  ┌────────────────────────────────────────────────────┐  │
│  │          Centralized Storage (Cloud Storage)       │  │
│  │                                                    │  │
│  │  ┌──────────┐  ┌──────────┐  ┌──────────────────┐  │  │
│  │  │  Tables  │  │  Schema  │  │    File Format   │  │  │
│  │  │   Data   │  │ Metadata │  │  (Compressed &   │  │  │
│  │  │          │  │          │  │   Columnar)      │  │  │
│  │  └──────────┘  └──────────┘  └──────────────────┘  │  │
│  │                                                    │  │
│  │         (Amazon S3, Azure Blob, Google Cloud)      │  │
│  └────────────────────────────────────────────────────┘  │
└──────────────────────────────────────────────────────────┘

1. Cloud Services Layer (Top Layer)

The cloud services layer coordinates and manages the entire Snowflake system, handling all user requests and system operations.

Key Components:

• Authentication and Access Control:
  • User authentication (username/password, SSO, MFA)
  • Role-based access control (RBAC)
  • Row-level and column-level security
• Query Parsing and Optimization:
  • SQL compilation and parsing
  • Query optimization and execution planning
  • Result caching management
• Metadata Management:
  • Catalog of all database objects
  • Table statistics and metadata
  • Query history and performance metrics
• Infrastructure Management:
  • Virtual warehouse provisioning
  • Resource monitoring and allocation
  • Automatic failover and recovery
• Transaction Management:
  • ACID compliance
  • Concurrency control
  • Data sharing coordination

2. Compute Layer (Middle Layer)

The compute layer consists of virtual warehouses that execute queries and perform data processing operations.

Key Features:

• Virtual Warehouses: Independent compute clusters that can be created, resized, or suspended on-demand
• Elastic Scaling: Warehouses can scale up (increase size) or scale out (add clusters) based on workload
• Size Options: Available in multiple sizes:
  1. (001) X-Small
  2. (002) Small
  3. (004) Medium
  4. (008) Large
  5. (016) X-Large
  6. (032) 2X-Large
  7. (064) 3X-Large
  8. (128) 4X-Large
• Multi-cluster Warehouses: Automatically spin up additional clusters during peak demand
• Auto-suspend and Auto-resume: Warehouses can automatically suspend when idle and resume when queries arrive
• Workload Isolation: Different warehouses can be dedicated to different workloads (ETL, analytics, reporting)
• Query Processing: Each warehouse has access to all data in the storage layer via shared metadata

3. Storage Layer (Bottom Layer)

The storage layer is the foundation of Snowflake's architecture, responsible for persistent data storage.

Key Features:

• Centralized Storage: All data is stored in a centralized location in cloud storage (AWS S3, Azure Blob Storage, or Google Cloud Storage)
• Automatic Management: Snowflake automatically handles all aspects of data storage including:
  • File size optimization
  • Data compression (automatic compression)
  • Data replication for durability
  • Data encryption at rest
• Columnar Format: Data is stored in a proprietary compressed columnar format for optimal query performance
• Micro-partitioning: Data is automatically divided into micro-partitions (50-500 MB of uncompressed data)
• Zero-copy Cloning: Creates instant copies of databases, schemas, or tables without duplicating storage
• Time Travel: Maintains historical data versions for up to 90 days

Key Benefits of This Architecture

1. Separation of Storage and Compute: Allows independent scaling of resources based on needs
2. Elastic Scalability: Can handle varying workloads without manual intervention
3. Pay-per-use Model: Only pay for storage used and compute time consumed
4. Zero Maintenance: No infrastructure management, tuning, or optimization required
5. Concurrent Workloads: Multiple virtual warehouses can operate simultaneously without resource contention
6. Instant Data Sharing: Share data across organizations without data movement
7. Built-in Security: End-to-end encryption, both in transit and at rest
8. High Availability: Built-in redundancy and automatic failover capabilities

Client Connectivity Options

Users and applications can connect to Snowflake through various interfaces:

• Web Interface: Browser-based UI for query execution and administration
• SnowSQL: Command-line client for executing SQL queries
• ODBC/JDBC Drivers: For connecting third-party tools and applications
• Python Connector: Native Python library for programmatic access
• Spark Connector: Integration with Apache Spark
• REST API: For custom integrations and automation
• Third-party Tools: Support for BI tools like Tableau, Power BI, Looker, etc.