Snowflake Architecture: Multi-Cluster Shared Data & Virtual Warehouses

Architecture Diagram 1: Complete Snowflake Architecture

Architecture Diagram 2: Micro-Partition & Pruning Strategy

Architecture Diagram 3: Virtual Warehouse Scaling & Multi-Cluster

Architecture Diagram 4: Query Execution Lifecycle

Architecture Diagram 5: Three-Layer Architecture Stack

Architecture Diagram 6: Query Execution Lifecycle

Architecture Diagram 7: Cost Model & Credit Consumption

Why Snowflake? The Industry Context

Snowflake was founded in 2012 by three data warehouse veterans who saw a fundamental flaw in existing architectures: compute and storage were tightly coupled, forcing organizations to over-provision one to scale the other. Traditional warehouses like Teradata, Netezza, and Oracle Exadata required buying bigger appliances to handle more queries — even if storage was fine.

The Problem Snowflake Solves

Who Uses Snowflake in Production?

• Capital One: Migrated from Teradata to Snowflake for fraud detection analytics — reduced query times from minutes to seconds
• Adobe: Processes 50+ billion customer experience events daily in Snowflake
• Sony: Consolidated 30+ data warehouses into a single Snowflake deployment
• Roche: Harmonized clinical trial data across 100+ countries with Snowflake's data sharing
• Instacart: Powers real-time delivery optimization with Snowflake as the analytical backbone

Snowflake vs. The Competition

Key Insight: Snowflake's differentiator isn't just the technology — it's the ecosystem. Data sharing, data exchange (Snowflake Marketplace), and zero-copy cloning create network effects that make Snowflake more valuable as more organizations adopt it.

Real-World Case Study: Retail Analytics Platform

A major retailer processes 2TB of daily transaction data across 500 stores. Before Snowflake:

• ETL took 6 hours — analysts waited until noon for morning data
• Concurrent query limits — only 5 analysts could run reports simultaneously
• Data freshness — decisions were based on yesterday's data

After migrating to Snowflake:

-- Before: Queries scanned full tables (10TB+), took 15+ minutes
SELECT store_id, SUM(revenue) 
FROM transactions 
WHERE date = CURRENT_DATE 
GROUP BY store_id;
-- Runtime: 14 minutes, scanned 10TB

-- After: Micro-partition pruning eliminates 95% of data
-- Partition pruning: only scans today's partitions (~200GB)
-- Runtime: 18 seconds

Architecture decisions made:

Results:

• Query performance: 14 minutes → 18 seconds (47x improvement)
• Data freshness: End-of-day → Near real-time (5-minute delay)
• Analyst productivity: 5 concurrent → 50+ concurrent users
• Annual cost: 95K (Snowflake) — 47% reduction

Detailed Explanation

What is Snowflake's Architecture?

Snowflake implements a multi-cluster shared data architecture that completely separates compute from persistent cloud storage. This enables independent scaling of each layer without the trade-offs of traditional data warehouses.

The Three-Layer Architecture

Cloud Services Layer

• Handles metadata operations, query parsing, optimization, and security
• Fully managed by Snowflake across all availability zones
• Maintains centralized metadata store (objects, access control, transactions)

Query Processing Layer

• Contains virtual warehouses for SQL/DML execution
• Each warehouse is independent (CPU, RAM, local SSD cache)
• Scalable up (node size) or out (cluster count) independently

Storage Layer

• Micro-partitioning: 50–500MB compressed columnar chunks
• Automatic management — no user-defined partitions required
• Rich metadata: min/max values, distinct counts, null counts per column

Micro-Partitioning and Automatic Clustering

Virtual Warehouse Architecture

• Elastic compute — dynamically scales based on workload demands
• Each node: CPU cores + RAM + local SSD cache for hot data
• Multi-cluster warehouses auto-start/suspend clusters based on queue depth
• True multi-tenancy: different workloads use isolated warehouses

Data Flow and Query Execution

1. Cloud services layer parses SQL and generates optimized query plan
2. Optimizer uses micro-partition metadata for partition pruning (80–95% data eliminated)
3. Virtual warehouse fetches required micro-partitions from cloud storage
4. Processes in parallel across all available nodes
5. Results cached at node, warehouse, and global levels

Key Takeaway: Snowflake's separation of compute and storage enables true multi-tenancy, elastic scaling, and cost-efficient query execution through micro-partition pruning.

Key Concepts Table

Code Examples

-- Example 1: Create a multi-cluster warehouse with specific configuration
CREATE WAREHOUSE analytics_wh
    WAREHOUSE_SIZE = 'xlarge'
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 5
    SCALING_POLICY = 'ECONOMY'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = FALSE
    RESOURCE_MONITOR = 'analytics_monitor'
    COMMENT = 'Multi-cluster warehouse for analytics workloads';

-- Example 2: Configure warehouse with advanced settings
CREATE WAREHOUSE etl_wh
    WAREHOUSE_SIZE = '2xlarge'
    MIN_CLUSTER_COUNT = 2
    MAX_CLUSTER_COUNT = 4
    SCALING_POLICY = 'STANDARD'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 3600
    STATEMENT_TIMEOUT_IN_SECONDS = 86400
    COMMENT = 'ETL warehouse with aggressive scaling';

-- Example 3: Query micro-partition metadata
SELECT 
    partition_id,
    row_count,
    uncompressed_bytes,
    compressed_bytes,
    (uncompressed_bytes / compressed_bytes) as compression_ratio,
    created_time,
    last_modified_time
FROM TABLE(INFORMATION_SCHEMA.PARTITION_HISTORY(
    TABLE_NAME => 'sales_data',
    START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
))
ORDER BY created_time DESC;

-- Example 4: Analyze warehouse performance metrics
SELECT 
    warehouse_name,
    warehouse_size,
    cluster_number,
    AVG(queries_completed) as avg_queries,
    AVG(queued_overload_queries) as avg_queued,
    AVG(execution_time_ms) / 1000 as avg_exec_seconds,
    SUM(credits_used) as total_credits,
    AVG(bytes_scanned) / 1024 / 1024 / 1024 as avg_gb_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY total_credits DESC;

-- Example 5: Monitor auto-scaling events
SELECT 
    warehouse_name,
    event_name,
    event_timestamp,
    event_reason,
    cluster_number
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
    START_TIME => DATEADD(hour, -24, CURRENT_TIMESTAMP())
))
WHERE event_name IN ('RESUME', 'SUSPEND', 'ADD_CLUSTER', 'REMOVE_CLUSTER')
ORDER BY event_timestamp DESC;

-- Example 6: Analyze partition pruning effectiveness
SELECT 
    query_id,
    query_text,
    partitions_scanned,
    partitions_total,
    (partitions_scanned / partitions_total) * 100 as scan_percentage,
    bytes_scanned / 1024 / 1024 as mb_scanned,
    compilation_time_ms,
    execution_time_ms
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP())
  AND partitions_total > 0
ORDER BY scan_percentage DESC
LIMIT 100;

Performance Metrics

Best Practices

1. Right-size warehouses: Start with Medium for ad-hoc queries, scale up for batch ETL. Monitor queue depth to identify under-provisioned warehouses.

2. Use multi-cluster warehouses: Set MIN_CLUSTER_COUNT=1 and MAX_CLUSTER_COUNT=3-5 for concurrent workloads. Use SCALING_POLICY=ECONOMY for cost optimization.

3. Implement warehouse isolation: Create separate warehouses for different workloads (ETL, reporting, ad-hoc) to prevent resource contention and enable independent scaling.

4. Configure appropriate timeouts: Set STATEMENT_QUEUED_TIMEOUT for queries that shouldn't run indefinitely and STATEMENT_TIMEOUT to kill long-running queries.

5. Leverage result cache: Ensure identical queries can hit the cache by avoiding non-deterministic functions. Use QUERY_TAG to group similar queries.

6. Monitor partition pruning: Use QUERY_HISTORY to identify queries with poor pruning (< 60% data elimination). Consider adding clustering keys for frequently filtered columns.

7. Implement resource monitors: Set up alerts for credit consumption to prevent unexpected costs. Use MAX_CREDIT_QUOTA per warehouse for budget control.

8. Use automatic clustering: Define clustering keys on large tables (100GB+) that are frequently filtered. Start with low cardinality columns used in WHERE/JOIN clauses.

9. Optimize data placement: Distribute data across micro-partitions based on query patterns. Regularly review CLUSTERING_INFORMATION() for clustering depth and overlap metrics.

10. Cache management: Understand result cache invalidation rules (data modifications, DDL changes). Use PERSISTENT_RESULT_CACHE_VARCHAR for long-lived result sets.

See Also

• Time Travel & Data Cloning — Point-in-time queries and zero-copy cloning
• Snowpipe Ingestion — Automated data loading and streaming
• Warehouse Management — Multi-cluster scaling and auto-suspend
• Optimization Techniques — Clustering keys and materialized views
• Data Warehouse Concepts — Data warehouse design principles
• PySpark Iceberg Tables — Iceberg table format integration