Snowflake Architecture: Multi-Cluster Shared Data & Virtual Warehouses
Architecture Diagram 1: Complete Snowflake Architecture
┌─────────────────────────────────────────────────────────────────────────────┐
│ SNOWFLAKE CLOUD DATA PLATFORM │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ CONSUMPTION LAYER │ │
│ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────────────┐ │ │
│ │ │BI Tools │ │ SQL │ │ ODBC/ │ │ Snowflake │ │ │
│ │ │Tableau │ │ Client │ │ JDBC │ │ Connector │ │ │
│ │ │Power BI │ │ worksheet│ │ Driver │ │ (Python/Java) │ │ │
│ │ └──────────┘ └──────────┘ └──────────┘ └──────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ CLOUD SERVICES LAYER │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ ┌──────────┐ │ │
│ │ │ Authentication│ │ Infrastructure│ │ Metadata │ │ Security │ │ │
│ │ │ & Access │ │ Management │ │ Management│ │ Controls │ │ │
│ │ │ Management │ │ │ │ │ │ │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ └──────────┘ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌────────────────────────────┐ │ │
│ │ │ Query │ │ Optimizer │ │ Service Engineering │ │ │
│ │ │ Processing │ │ & Planning │ │ (Global Service) │ │ │
│ │ └─────────────┘ └─────────────┘ └────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ QUERY PROCESSING LAYER │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ VIRTUAL WAREHOUSE CLUSTER │ │ │
│ │ │ │ │ │
│ │ │ ┌───────────┐ ┌───────────┐ ┌───────────┐ ┌──────────┐ │ │ │
│ │ │ │ Node 1 │ │ Node 2 │ │ Node 3 │ │ Node N │ │ │ │
│ │ │ │ ┌───────┐ │ │ ┌───────┐ │ │ ┌───────┐ │ │ ┌──────┐ │ │ │ │
│ │ │ │ │Cache │ │ │ │Cache │ │ │ │Cache │ │ │ │Cache │ │ │ │ │
│ │ │ │ │Engine │ │ │ │Engine │ │ │ │Engine │ │ │ │Engine│ │ │ │ │
│ │ │ │ └───────┘ │ │ └───────┘ │ │ └───────┘ │ │ └──────┘ │ │ │ │
│ │ │ └───────────┘ └───────────┘ └───────────┘ └──────────┘ │ │ │
│ │ │ │ │ │
│ │ │ ┌──────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ RESULT CACHE / SERVICE RESULT CACHE │ │ │ │
│ │ │ └──────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌─────────────────┐ ┌─────────────────┐ ┌────────────────┐ │ │
│ │ │ Warehouse 1 │ │ Warehouse 2 │ │ Warehouse N │ │ │
│ │ │ (XS-Small) │ │ (Large) │ │ (Multi-Cluster)│ │ │
│ │ └─────────────────┘ └─────────────────┘ └────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ STORAGE LAYER │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ MICRO-PARTITION STORAGE │ │ │
│ │ │ │ │ │
│ │ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌──────┐ │ │ │
│ │ │ │Micro- │ │Micro- │ │Micro- │ │Micro- │ │Micro-│ │ │ │
│ │ │ │Partition│ │Partition│ │Partition│ │Partition│ │ .. │ │ │ │
│ │ │ │ (001) │ │ (002) │ │ (003) │ │ (004) │ │ │ │ │ │
│ │ │ │ 50-500MB│ │ 50-500MB│ │ 50-500MB│ │ 50-500MB│ │ │ │ │ │
│ │ │ └─────────┘ └─────────┘ └─────────┘ └─────────┘ └──────┘ │ │ │
│ │ │ │ │ │
│ │ │ ┌──────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ Columnar Storage Format (Parquet-like) │ │ │ │
│ │ │ │ • Column groups • Run-length encoding │ │ │ │
│ │ │ │ • Dictionary encoding • Delta encoding │ │ │ │
│ │ │ │ • ZSTD compression • Metadata statistics │ │ │ │
│ │ │ └──────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ CLOUD BLOB STORAGE (S3/Azure/GCS) │ │ │
│ │ │ ┌─────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ • Automatic compression (typically 4-6x) │ │ │ │
│ │ │ │ • Encryption at rest (AES-256) │ │ │ │
│ │ │ │ • Pointers maintained in metadata │ │ │ │
│ │ │ └─────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Architecture Diagram 2: Micro-Partition & Pruning Strategy
┌─────────────────────────────────────────────────────────────────────────────┐
│ MICRO-PARTITION PRUNING STRATEGY │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ SQL Query: SELECT * FROM sales WHERE region = 'US' AND amount > 1000 │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ METADATA LAYER │ │
│ │ │ │
│ │ Micro-Partition Statistics: │ │
│ │ ┌──────────────┬────────┬─────────┬─────────┬─────────────┐ │ │
│ │ │ Partition ID │ Region │ Amount │ Date │ Rows │ │ │
│ │ ├──────────────┼────────┼─────────┼─────────┼─────────────┤ │ │
│ │ │ MP_001 │ US/EU │ 100-5K │ 2024-Q1 │ 45,000 │ │ │
│ │ │ MP_002 │ US/ASIA│ 500-10K │ 2024-Q2 │ 52,000 │ │ │
│ │ │ MP_003 │ EU/APAC│ 200-8K │ 2024-Q3 │ 38,000 │ │ │
│ │ │ MP_004 │ US/EU │ 1K-15K │ 2024-Q4 │ 61,000 │ │ │
│ │ │ MP_005 │ ASIA │ 50-2K │ 2024-Q1 │ 29,000 │ │ │
│ │ └──────────────┴────────┴─────────┴─────────┴─────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ PRUNING PHASE 1: Partition Pruning │ │
│ │ │ │
│ │ Filter: region = 'US' │ │
│ │ │ │
│ │ MP_001 [US/EU] ──────────┐ │ │
│ │ MP_002 [US/ASIA] ────────┼──── PRUNED ──▶ Candidate Partitions │ │
│ │ MP_003 [EU/APAC] ────X │ (3 of 5) │ │
│ │ MP_004 [US/EU] ──────────┤ │ │
│ │ MP_005 [ASIA] ───────X │ │ │
│ │ │ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ PRUNING PHASE 2: Column Pruning │ │
│ │ │ │
│ │ ┌─────────────────────────────────────────────────────────────┐ │ │
│ │ │ SELECT * FROM sales │ │ │
│ │ │ ───────────────────── │ │ │
│ │ │ Column Groups in Micro-Partition: │ │ │
│ │ │ │ │ │
│ │ │ CG1: [region, country, city] ──────▶ READ (region needed) │ │ │
│ │ │ CG2: [amount, currency] ──────▶ READ (amount needed) │ │ │
│ │ │ CG3: [date, timestamp] ──────▶ SKIPPED (not needed)│ │ │
│ │ │ CG4: [product_id, sku] ──────▶ SKIPPED (not needed)│ │ │
│ │ │ CG5: [customer_id, email] ──────▶ SKIPPED (not needed)│ │ │
│ │ └─────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ PRUNING PHASE 3: Row-Level Filtering │ │
│ │ │ │
│ │ ┌─────────────────────────────────────────────────────────────┐ │ │
│ │ │ Amount Column Data in MP_001: │ │ │
│ │ │ │ │ │
│ │ │ Run-Length Encoded: │ │ │
│ │ │ [100, 150, 200, 250, 300, 350, 400, 450, 500, 550, ...] │ │ │
│ │ │ ▲ │ │ │
│ │ │ │ │ │ │
│ │ │ Amount > 1000: Skip values 100-999 │ │ │
│ │ │ Scan from index ~20 only (1000/50 increment) │ │ │
│ │ └─────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ RESULT: 3 micro-partitions scanned → 2 columns read → rows filtered │
│ EFFICIENCY: ~85% data skipped through metadata-based pruning │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Architecture Diagram 3: Virtual Warehouse Scaling & Multi-Cluster
┌─────────────────────────────────────────────────────────────────────────────┐
│ VIRTUAL WAREHOUSE MULTI-CLUSTER SCALING │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ MULTI-CLUSTER WAREHOUSE: Analytics_WH (Min: 1, Max: 5) │
│ │
│ CLUSTER 1 (Primary) CLUSTER 2 (Auto-Started) │
│ ┌─────────────────────────┐ ┌─────────────────────────┐ │
│ │ Warehouse: WH_1 │ │ Warehouse: WH_2 │ │
│ │ Size: X-Large │ │ Size: X-Large │ │
│ │ Status: ACTIVE │ │ Status: ACTIVE │ │
│ │ │ │ │ │
│ │ ┌────────────────────┐ │ │ ┌────────────────────┐ │ │
│ │ │ Node Pool: 8 nodes│ │ │ │ Node Pool: 8 nodes│ │ │
│ │ │ ┌──┐┌──┐┌──┐┌──┐ │ │ │ │ ┌──┐┌──┐┌──┐┌──┐ │ │ │
│ │ │ │N1││N2││N3││N4│ │ │ │ │ │N5││N6││N7││N8│ │ │ │
│ │ │ └──┘└──┘└──┘└──┘ │ │ │ │ └──┘└──┘└──┘└──┘ │ │ │
│ │ │ ┌──┐┌──┐┌──┐┌──┐ │ │ │ │ ┌──┐┌──┐┌──┐┌──┐ │ │ │
│ │ │ │N9││10││11││12│ │ │ │ │ │13││14││15││16│ │ │ │
│ │ │ └──┘└──┘└──┘└──┘ │ │ │ │ └──┘└──┘└──┘└──┘ │ │ │
│ │ └────────────────────┘ │ │ └────────────────────┘ │ │
│ │ │ │ │ │
│ │ Queue: 15 queries │ │ Queue: 8 queries │ │
│ │ Running: 8 queries │ │ Running: 8 queries │ │
│ │ Avg Wait: 2.3s │ │ Avg Wait: 1.1s │ │
│ └─────────────────────────┘ └─────────────────────────┘ │
│ │
│ ┌─────────────────────────┐ ┌─────────────────────────┐ │
│ │ Warehouse: WH_3 │ │ Warehouse: WH_4 │ (Standby) │
│ │ Status: STANDBY │ │ Status: STANDBY │ │
│ │ │ │ │ │
│ │ Not yet started │ │ Not yet started │ │
│ │ (Will start if queue │ │ (Will start if queue │ │
│ │ > 10 for > 3 min) │ │ > 10 for > 3 min) │ │
│ └─────────────────────────┘ └─────────────────────────┘ │
│ │
│ SCALING POLICY: │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ Scale UP (Larger nodes): Scale OUT (More clusters): │ │
│ │ ─────────────────────── ──────────────────────────── │ │
│ │ • Query uses 1 cluster • Queue depth > 10 │ │
│ │ • More CPU/Memory per node • Sustained for > 3 minutes │ │
│ │ • Better for single queries • Better for concurrent queries │ │
│ │ • XS → S → M → L → XL • Auto-scale to Max clusters │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ AUTO-SUSPEND BEHAVIOR: │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ Time │ Cluster 1 │ Cluster 2 │ Cluster 3 │ Cluster 4 │ │
│ │ ──────────────┼───────────┼───────────┼───────────┼────────── │ │
│ │ 0:00 (start) │ ACTIVE │ --- │ --- │ --- │ │
│ │ 0:05 (heavy) │ ACTIVE │ ACTIVE │ --- │ --- │ │
│ │ 0:10 (peak) │ ACTIVE │ ACTIVE │ ACTIVE │ --- │ │
│ │ 0:15 (drop) │ ACTIVE │ ACTIVE │ --- │ --- │ │
│ │ 0:20 (idle) │ ACTIVE │ --- │ --- │ --- │ │
│ │ 0:25 (idle) │ SUSPENDED │ --- │ --- │ --- │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
A micro-partition is a compressed, columnar data chunk (50–500 MB uncompressed) that serves as Snowflake's fundamental storage unit. Each micro-partition stores metadata including min/max values per column, distinct counts, and null counts, enabling partition pruning — skipping irrelevant micro-partitions during query execution.
A virtual warehouse is an independent compute cluster of 1–128 nodes, each containing CPU cores, RAM, and local SSD cache. Warehouses execute SQL queries and DML operations, scaling independently of storage. Multiple warehouses can access the same data simultaneously without contention.
Snowflake's compute-storage separation means warehouses are ephemeral — they can be started, stopped, or resized independently of persistent cloud storage (S3/Azure/GCS). This enables true multi-tenancy where different workloads use isolated compute resources.
- Three-layer design: Cloud Services (metadata/optimization), Query Processing (warehouses), Storage (micro-partitions)
- Micro-partition pruning eliminates 80–95% of data before scanning via metadata
- Multi-cluster warehouses auto-scale from 1 to N clusters based on queue depth
- Result cache accelerates repeated identical queries with zero compute
- Compression ratio of 4–6× across all data types reduces storage costs
Detailed Explanation
Snowflake's architecture represents a paradigm shift in cloud data warehousing, fundamentally rethinking how compute and storage interact in a distributed system. At its core, Snowflake implements a multi-cluster shared data architecture that completely separates compute resources from persistent cloud storage, enabling independent scaling of each layer without the traditional trade-offs that plagued earlier data warehouse systems.
The Three-Layer Architecture
Snowflake's architecture consists of three distinct but interconnected layers, each serving a specific purpose in the overall system design. The cloud services layer handles all metadata operations, query parsing, optimization, and security enforcement. This layer is fully managed by Snowflake and operates across all availability zones within a region, ensuring high availability and fault tolerance. The services layer maintains a centralized metadata store that tracks all objects, access control policies, and transaction states without requiring users to manage any infrastructure.
The query processing layer contains virtual warehouses that execute SQL queries and DML operations. Each virtual warehouse is an independent compute cluster consisting of multiple nodes, each with its own CPU, memory, and local SSD cache. Virtual warehouses can be scaled up (increasing node size) or scaled out (adding more clusters) independently of each other and the storage layer. This separation enables true multi-tenancy where different departments or workloads can use dedicated warehouses without resource contention.
The storage layer utilizes a revolutionary micro-partitioning scheme that automatically organizes data into compressed, columnar micro-partitions ranging from 50MB to 500MB in size. Unlike traditional partitioning, micro-partitions are automatically managed and do not require explicit definition by users. Each micro-partition maintains rich metadata including column-level min/max values, distinct counts, and null counts, enabling sophisticated partition pruning during query execution.
Micro-Partitioning and Automatic Clustering
Snowflake's micro-partitioning system operates transparently, automatically organizing data as it's ingested. When data is loaded, Snowflake divides it into micro-partitions and applies columnar compression using a combination of run-length encoding, dictionary encoding, delta encoding, and ZSTD compression. This typically achieves 4-6x compression ratios compared to raw data sizes.
The automatic clustering feature continuously optimizes data placement based on query patterns. When users define clustering keys, Snowflake automatically reorganizes micro-partitions to maximize data locality for those key columns. This background process runs asynchronously without impacting query performance and continuously adapts as data volumes grow and query patterns evolve.
Virtual Warehouse Architecture
Virtual warehouses in Snowflake are not traditional fixed-size compute clusters. Instead, they are elastic compute resources that can dynamically scale based on workload demands. Each warehouse consists of a pool of nodes, where each node contains CPU cores, RAM, and local SSD cache. The cache stores frequently accessed data and intermediate query results, reducing the need to fetch data from remote storage.
Multi-cluster warehouses extend this concept by automatically managing multiple independent clusters under a single logical warehouse. When the primary cluster becomes overloaded (typically measured by queue depth exceeding a threshold), Snowflake automatically starts additional clusters to handle the load. When the load decreases, clusters are automatically suspended to reduce costs. This automatic scaling happens transparently without any user intervention.
Data Flow and Query Execution
When a query arrives, the cloud services layer parses the SQL, generates an optimized query plan, and determines which virtual warehouse should execute it. The optimizer uses micro-partition metadata to perform sophisticated partition pruning, eliminating entire micro-partitions that cannot contain qualifying rows. This pruning can often eliminate 80-95% of data before any actual data scanning occurs.
During execution, the virtual warehouse fetches only the required micro-partitions from cloud storage, caches them locally, and processes them in parallel across all available nodes. Results are cached at multiple levels (node-level cache, warehouse-level cache, and global result cache) to accelerate subsequent identical or similar queries.
Key Concepts Table
| Component | Description | Scaling Behavior | Cost Model |
|---|---|---|---|
| Cloud Services | Metadata, security, query optimization | Automatic, serverless | Included in compute cost |
| Virtual Warehouse | Query execution, DML processing | Manual or auto-scale | Per-second billing (60s minimum) |
| Micro-Partition | 50-500MB compressed columnar chunks | Automatic creation | Storage cost only |
| Result Cache | Query result caching | Automatic invalidation | No additional cost |
| Storage | Persistent cloud blob storage | Automatic expansion | Per TB per month |
| Warehouse Size | vCPU | Memory | Max Concurrent Queries |
|---|---|---|---|
| X-Small | 1 | 2 GB | 1 |
| Small | 2 | 4 GB | 2 |
| Medium | 4 | 8 GB | 4 |
| Large | 8 | 16 GB | 8 |
| X-Large | 16 | 32 GB | 16 |
| 2X-Large | 32 | 64 GB | 32 |
| 3X-Large | 64 | 128 GB | 64 |
| 4X-Large | 128 | 256 GB | 128 |
| Metric | Value | Description |
|---|---|---|
| Compression Ratio | 4-6x | Average compression across all data types |
| Micro-Partition Size | 50-500 MB | Target size after compression |
| Partition Pruning | 80-95% | Average data skipped during queries |
| Result Cache Hit Rate | 30-70% | Depends on query repetition patterns |
| Fail-Safe Retention | 7 days | Additional protection beyond Time Travel |
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
| Metric | Target | Warning | Critical | Description |
|---|---|---|---|---|
| Query Queue Depth | < 5 | 5-10 | > 10 | Number of queries waiting to execute |
| Cluster Scaling Time | < 30s | 30-60s | > 60s | Time to start additional cluster |
| Warehouse Utilization | 60-80% | 80-95% | > 95% | CPU/Memory utilization |
| Partition Pruning | > 80% | 60-80% | < 60% | Percentage of partitions eliminated |
| Cache Hit Rate | > 50% | 30-50% | < 30% | Result cache effectiveness |
| Auto-Suspend Delay | 60-300s | 300-600s | > 600s | Time before warehouse suspends |
Best Practices
-
Right-size warehouses: Start with Medium for ad-hoc queries, scale up for batch ETL. Monitor queue depth to identify under-provisioned warehouses.
-
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.
-
Implement warehouse isolation: Create separate warehouses for different workloads (ETL, reporting, ad-hoc) to prevent resource contention and enable independent scaling.
-
Configure appropriate timeouts: Set STATEMENT_QUEUED_TIMEOUT for queries that shouldn't run indefinitely and STATEMENT_TIMEOUT to kill long-running queries.
-
Leverage result cache: Ensure identical queries can hit the cache by avoiding non-deterministic functions. Use QUERY_TAG to group similar queries.
-
Monitor partition pruning: Use QUERY_HISTORY to identify queries with poor pruning (< 60% data elimination). Consider adding clustering keys for frequently filtered columns.
-
Implement resource monitors: Set up alerts for credit consumption to prevent unexpected costs. Use MAX_CREDIT_QUOTA per warehouse for budget control.
-
Use automatic clustering: Define clustering keys on large tables (100GB+) that are frequently filtered. Start with low cardinality columns used in WHERE/JOIN clauses.
-
Optimize data placement: Distribute data across micro-partitions based on query patterns. Regularly review CLUSTERING_INFORMATION() for clustering depth and overlap metrics.
-
Cache management: Understand result cache invalidation rules (data modifications, DDL changes). Use PERSISTENT_RESULT_CACHE_VARCHAR for long-lived result sets.
See Also
- PySpark Iceberg - Iceberg table format integration
- Delta Lake on Databricks - Delta Lake table format comparison
- Data Warehouse Concepts - Data warehouse design principles