Interview Question

"Can you explain Snowflake's multi-cluster shared data architecture and how it differs from traditional data warehouse architectures? Walk me through the three layers and how they enable independent scaling."

The Three-Layer Architecture

Snowflake's architecture is fundamentally different from traditional data warehouses. It separates compute, storage, and services into independent layers that can scale independently.

1. Storage Layer

The storage layer manages all data, including table data, metadata, micro-partitions, and temporary files. Data is stored in Snowflake's proprietary compressed columnar format.

-- Examining storage metadata
SELECT 
    table_name,
    row_count,
    bytes / (1024 * 1024 * 1024) AS storage_gb,
    retention_time,
    clustering_key
FROM information_schema.tables
WHERE table_schema = 'PUBLIC'
ORDER BY bytes DESC;

-- Understanding micro-partition metadata
SELECT 
    table_name,
    partition_id,
    row_count,
    uncompressed_bytes,
    compressed_bytes,
    ROUND(compressed_bytes / NULLIF(uncompressed_bytes, 0) * 100, 2) AS compression_ratio_pct
FROM information_schema.partitions
WHERE table_name = 'CUSTOMERS';

2. Compute Layer (Virtual Warehouses)

Compute is handled by virtual warehouses — MPP compute clusters that execute queries. Each warehouse is independent and can be sized, paused, or resumed independently.

-- Create a warehouse with specific configuration
CREATE WAREHOUSE analytics_wh
    WAREHOUSE_SIZE = 'X-LARGE'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 5
    SCALING_POLICY = 'STANDARD'
    STATEMENT_TIMEOUT_IN_SECONDS = 3600
    STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 600;

-- Monitor warehouse utilization
SELECT 
    warehouse_name,
    warehouse_size,
    cluster_number,
    queries_executed,
    average_execution_time_ms,
    credits_used,
    start_time,
    end_time
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -7, CURRENT_DATE())
ORDER BY start_time DESC;

3. Services Layer

The services layer handles authentication, infrastructure management, metadata, query compilation, query optimization, and transaction management. This is the "brain" of Snowflake.

-- Services layer manages all metadata queries
SELECT 
    query_id,
    query_type,
    query_text,
    execution_status,
    compilation_time_ms,
    execution_time_ms,
    total_elapsed_time_ms,
    queue_time_ms,
    result_scan_time_ms
FROM snowflake.account_usage.query_history
WHERE query_type = 'SELECT'
  AND start_time >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
ORDER BY total_elapsed_time_ms DESC
LIMIT 10;

How the Layers Work Together

Multi-Cluster Warehouses

Multi-cluster warehouses automatically scale out (add clusters) or scale in (remove clusters) based on workload demands.

-- Create a multi-cluster warehouse for auto-scaling
CREATE WAREHOUSE elastic_wh
    WAREHOUSE_SIZE = 'LARGE'
    MIN_CLUSTER_COUNT = 2
    MAX_CLUSTER_COUNT = 6
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    SCALING_POLICY = 'ECONOMY';  -- or 'STANDARD'

-- Monitor cluster scaling events
SELECT 
    warehouse_name,
    event_timestamp,
    event_type,  -- CLUSTER_UPSCALED, CLUSTER_DOWNSCALED
    cluster_number,
    new_cluster_count,
    previous_cluster_count
FROM snowflake.account_usage.warehouse_events_history
WHERE event_timestamp >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
  AND event_type LIKE '%CLUSTER%'
ORDER BY event_timestamp DESC;

-- Check current scaling state
SHOW WAREHOUSES LIKE 'elastic_wh';

Real-World Scenario: Amazon

Question: "How would you architect a Snowflake solution for a petabyte-scale e-commerce analytics platform that needs to handle both batch reporting and real-time dashboards?"

Solution Architecture

-- Separate warehouses for different workload types
-- 1. BI/Reporting warehouse (auto-scales for dashboard users)
CREATE WAREHOUSE bi_reporting
    WAREHOUSE_SIZE = 'MEDIUM'
    MIN_CLUSTER_COUNT = 2
    MAX_CLUSTER_COUNT = 8
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    STATEMENT_TIMEOUT_IN_SECONDS = 900;

-- 2. ETL warehouse (burst compute for data loading)
CREATE WAREHOUSE etl_processing
    WAREHOUSE_SIZE = 'LARGE'
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 4
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE;

-- 3. Data Science warehouse (large compute for ML workloads)
CREATE WAREHOUSE data_science
    WAREHOUSE_SIZE = 'X-LARGE'
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 2
    AUTO_SUSPEND = 600
    AUTO_RESUME = TRUE;

-- Resource monitor to prevent cost overruns
CREATE RESOURCE MONITOR monthly_budget
    WITH CREDIT_QUOTA = 5000
    TRIGGERS 
        ON 80% DO NOTIFY
        ON 100% DO SUSPEND
        ON 110% DO SUSPEND_IMMEDIATE;

Real-World Scenario: Google

Question: "Explain how Snowflake's architecture enables zero-copy cloning and time travel without duplicating data at the storage layer."

Time Travel & Cloning Architecture

-- Create a production table
CREATE TABLE orders (
    order_id NUMBER AUTOINCREMENT,
    customer_id NUMBER,
    order_date TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    total_amount NUMBER(12,2),
    status VARCHAR(20)
);

-- Insert sample data
INSERT INTO orders (customer_id, order_date, total_amount, status)
SELECT 
    MOD(seq, 1000) + 1,
    DATEADD(day, -seq % 365, CURRENT_DATE()),
    ROUND(RANDOM() * 10000, 2),
    CASE MOD(seq, 4) 
        WHEN 0 THEN 'PENDING'
        WHEN 1 THEN 'SHIPPED'
        WHEN 2 THEN 'DELIVERED'
        WHEN 3 THEN 'CANCELLED'
    END
FROM TABLE(GENERATOR(ROWCOUNT => 1000000)) seq;

-- Zero-copy clone (metadata-only operation)
CREATE TABLE orders_dev CLONE orders;

-- Verify no additional storage used initially
SELECT 
    table_name,
    bytes / (1024*1024*1024) AS storage_gb,
    row_count,
    clone_group_id
FROM information_schema.tables
WHERE table_name IN ('ORDERS', 'ORDERS_DEV');

-- Time travel to a specific timestamp
SELECT * FROM orders 
    AT (TIMESTAMP => '2024-01-15 10:30:00'::TIMESTAMP_NTZ);

-- Time travel to before a specific statement
SELECT * FROM orders 
    BEFORE (STATEMENT => 'abc123-query-id');

-- Set data retention for time travel
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 90;

Edge Cases & Advanced Patterns

Data Skew in Multi-Cluster Warehouses

-- Detect data skew across clusters
SELECT 
    warehouse_name,
    cluster_number,
    AVG(query_execution_time_ms) AS avg_query_time,
    COUNT(*) AS queries_processed,
    STDDEV(query_execution_time_ms) AS time_stddev
FROM snowflake.account_usage.query_history
WHERE warehouse_name = 'elastic_wh'
  AND start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP())
GROUP BY warehouse_name, cluster_number
ORDER BY cluster_number;

-- If skew is detected, consider clustering the base table
ALTER TABLE large_orders 
    CLUSTER BY (order_date, customer_id);

Cross-Cloud Architecture Considerations

-- Replicate database across clouds for DR
CREATE DATABASE analytics_failover
    AS REPLICA OF organization.account.analytics_primary;

-- Check replication status
SELECT 
    database_name,
    created_on,
    replication_status,
    primary_region,
    failover_region
FROM snowflake.account_usage.replication_database_status
WHERE database_name = 'ANALYTICS_FAILOVER';

Best Practices

Key Takeaways

• Storage layer is fully managed, uses micro-partitions for automatic partitioning
• Compute layer is independent per warehouse — scale without affecting storage
• Services layer is the brain — handles metadata, query planning, and transactions
• Multi-cluster warehouses auto-scale based on queue depth and policy
• Zero-copy cloning and time travel are enabled by metadata-driven architecture
• Resource monitors are essential for cost governance