Snowflake Load & Scale
Overview
Load testing, scaling strategies, and capacity planning for Snowflake workloads using warehouse sizing, multi-cluster configuration, and concurrent query simulation.
Scaling Model
| Dimension | How to Scale | When |
|---|
| Single query speed | Scale UP (bigger warehouse) | Complex queries, large scans |
| Concurrent queries | Scale OUT (multi-cluster) | Many users, dashboard refresh |
| Data volume | Scale UP + clustering | Tables > 1TB |
| Mixed workloads | Separate warehouses | ETL + analytics on same data |
Instructions
Step 1: Benchmark Current Performance
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT * FROM orders WHERE order_id = 12345;
SELECT DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY month ORDER BY month;
SELECT c.name, SUM(o.amount) AS total_spend
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= DATEADD(days, -90, CURRENT_DATE())
GROUP BY c.name
ORDER BY total_spend DESC
LIMIT 100;
SELECT query_id, query_text, warehouse_name, warehouse_size,
total_elapsed_time / 1000 AS seconds,
bytes_scanned / 1e9 AS gb_scanned,
rows_produced, partitions_scanned, partitions_total,
bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION())
ORDER BY start_time DESC
LIMIT 10;
ALTER SESSION SET USE_CACHED_RESULT = TRUE;
Step 2: Test Warehouse Size Impact
ALTER WAREHOUSE BENCHMARK_WH SET WAREHOUSE_SIZE = 'XSMALL';
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT ...;
ALTER WAREHOUSE BENCHMARK_WH SET WAREHOUSE_SIZE = 'SMALL';
SELECT ...;
ALTER WAREHOUSE BENCHMARK_WH SET WAREHOUSE_SIZE = 'MEDIUM';
SELECT ...;
SELECT warehouse_size, query_id,
total_elapsed_time / 1000 AS seconds,
bytes_scanned / 1e9 AS gb_scanned
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION())
WHERE query_text LIKE '%BENCHMARK_%'
ORDER BY start_time DESC;
Step 3: Concurrent Load Testing
import snowflake.connector
import threading
import time
import os
from statistics import mean, median
CONCURRENT_USERS = 20
QUERIES_PER_USER = 10
WAREHOUSE = 'LOAD_TEST_WH'
TEST_QUERIES = [
"SELECT COUNT(*) FROM orders WHERE order_date = CURRENT_DATE() - 1",
"SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id LIMIT 100",
"SELECT * FROM orders WHERE order_id = %s",
]
results = []
errors = []
def run_user_session(user_id: int):
conn = snowflake.connector.connect(
account=os.environ['SNOWFLAKE_ACCOUNT'],
user=os.environ['SNOWFLAKE_USER'],
password=os.environ['SNOWFLAKE_PASSWORD'],
warehouse=WAREHOUSE,
database='PROD_DW',
schema='GOLD',
)
cursor = conn.cursor()
for i in range(QUERIES_PER_USER):
query = TEST_QUERIES[i % len(TEST_QUERIES)]
start = time.time()
try:
if '%s' in query:
cursor.execute(query, (user_id * 1000 + i,))
else:
cursor.execute(query)
cursor.fetchall()
elapsed = time.time() - start
results.append({'user': user_id, 'query': i, 'seconds': elapsed})
except Exception as e:
errors.append({'user': user_id, 'query': i, : (e)})
conn.close()
threads = []
start_time = time.time()
uid (CONCURRENT_USERS):
t = threading.Thread(target=run_user_session, args=(uid,))
threads.append(t)
t.start()
t threads:
t.join()
total_time = time.time() - start_time
times = [r[] r results]
()
()
()
()
()
()
()
()
Step 4: Multi-Cluster Warehouse Configuration
CREATE OR REPLACE WAREHOUSE ANALYTICS_WH
WAREHOUSE_SIZE = 'MEDIUM'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 6
SCALING_POLICY = 'STANDARD'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;
ALTER WAREHOUSE ANALYTICS_WH SET SCALING_POLICY = 'ECONOMY';
CREATE WAREHOUSE DASHBOARD_WH
WAREHOUSE_SIZE = 'SMALL'
MIN_CLUSTER_COUNT = 3
MAX_CLUSTER_COUNT = 3
AUTO_SUSPEND = 120
AUTO_RESUME = TRUE;
SELECT start_time, warehouse_name,
avg_running, avg_queued_load, avg_queued_provisioning
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
DATE_RANGE_START => DATEADD(hours, -4, CURRENT_TIMESTAMP()),
WAREHOUSE_NAME => 'ANALYTICS_WH'
))
WHERE avg_queued_load > 0
start_time ;
Step 5: Capacity Planning
SELECT DATE_TRUNC('week', start_time) AS week,
SUM(credits_used) AS weekly_credits,
COUNT(DISTINCT query_id) AS weekly_queries,
ROUND(SUM(credits_used) / NULLIF(COUNT(DISTINCT query_id), 0), 4) AS credits_per_query
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY w
JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
ON w.warehouse_name = q.warehouse_name
WHERE w.start_time >= DATEADD(months, -3, CURRENT_TIMESTAMP())
GROUP BY week
ORDER BY week;
SELECT usage_date,
ROUND(storage_bytes / 1e12, 3) AS data_tb,
LAG(ROUND(storage_bytes / 1e12, 3)) OVER (ORDER BY usage_date) AS prev_tb,
ROUND((storage_bytes - LAG(storage_bytes) OVER (ORDER BY usage_date)) / 1e9, ) daily_growth_gb
SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
usage_date DATEADD(days, , ())
usage_date;
Benchmark Results Template
## Snowflake Performance Benchmark
Date: YYYY-MM-DD
Environment: [staging/production]
Table size: [X rows, Y GB]
| Warehouse | Query Type | Avg (s) | P95 (s) | GB Scanned | Spill |
|-----------|-----------|---------|---------|-----------|-------|
| XS | Agg | | | | |
| S | Agg | | | | |
| M | Agg | | | | |
Concurrent: [N users, M queries, QPS achieved]
Recommendation: [sizing/clustering/multi-cluster advice]
Error Handling
| Issue | Cause | Solution |
|---|
| Queries queuing | Concurrency > capacity | Add multi-cluster or separate warehouse |
| Linear scaling fails | Query not parallelizable | Optimize SQL (reduce shuffle) |
| Spilling on larger warehouse | Data skew | Check for hot partition/join skew |
| Load test throttled | Login rate limit | Use connection pooling |
Resources
Next Steps
For reliability patterns, see snowflake-reliability-patterns.
