Data Warehouse Optimizer
Optimize query performance and resource utilization in Snowflake, BigQuery, and Redshift through clustering, partitioning, materialized views, and query profiling.
Activation Triggers
Activate on: "Snowflake optimization", "BigQuery performance", "Redshift tuning", "query optimization", "clustering key", "partitioning", "materialized view", "warehouse sizing", "query profile", "slow query"
NOT for: dbt project structure → dbt-analytics-engineer | Dimensional modeling → dimensional-modeler | Cost optimization beyond warehouse → data-cost-optimizer
Quick Start
- Profile slow queries — use QUERY_PROFILE (Snowflake), INFORMATION_SCHEMA.JOBS (BigQuery), STL tables (Redshift)
- Partition large tables — by date column (most common), reducing scan size by 10-100x
- Add clustering — co-locate frequently filtered/joined columns within partitions
- Materialize expensive aggregations — materialized views for dashboards, pre-aggregated metrics
- Right-size warehouses — auto-suspend idle, auto-scale for concurrency, match size to workload
Core Capabilities
| Domain |
Technologies |
| Snowflake |
Micro-partitions, clustering keys, search optimization, warehouses |
| BigQuery |
Partitioning, clustering, BI Engine, materialized views |
| Redshift |
Sort keys, dist keys, VACUUM, WLM, Redshift Serverless |
| General |
Query plans, statistics, result caching, spill-to-disk analysis |
| Monitoring |
Snowflake Account Usage, BigQuery INFORMATION_SCHEMA, CloudWatch |
Architecture Patterns
Snowflake Clustering and Search Optimization
-- Cluster a large fact table by commonly filtered columns
ALTER TABLE fct_events
CLUSTER BY (event_date, customer_id);
-- Verify clustering depth (lower = better, target < 2.0)
SELECT SYSTEM$CLUSTERING_INFORMATION('fct_events', '(event_date, customer_id)');
-- Search optimization for point lookups on high-cardinality columns
ALTER TABLE fct_events ADD SEARCH OPTIMIZATION
ON EQUALITY(order_id), EQUALITY(email);
-- Result: range scans use clustering, point lookups use search optimization
BigQuery Partitioning + Clustering
-- Partition by date, cluster by high-cardinality filter columns
CREATE TABLE `project.dataset.fct_events`
PARTITION BY DATE(event_timestamp)
CLUSTER BY customer_id, event_type
AS
SELECT * FROM `project.dataset.raw_events`;
-- Query benefits: partition pruning + cluster pruning
-- Only scans partitions matching WHERE clause
SELECT customer_id, COUNT(*)
FROM `project.dataset.fct_events`
WHERE event_timestamp BETWEEN '2026-01-01' AND '2026-01-31'
AND event_type = 'purchase'
GROUP BY customer_id;
-- Check bytes scanned reduction
-- Target: 90%+ reduction vs unpartitioned table
Warehouse Sizing Strategy (Snowflake)
Workload Type Recommended Size Auto-Suspend Concurrency
───────────── ──────────────── ──────────── ───────────
Dashboard queries X-Small/Small 60s Auto-scale (max 3)
Analyst ad-hoc Medium 300s 1 cluster
dbt daily build Large Immediate 1 cluster
Data science / ML X-Large+ Immediate 1 cluster
Key: separate workloads into different warehouses
to prevent resource contention and enable per-workload billing
Anti-Patterns
- Scanning full tables — always partition by date; a full scan of a 1TB table costs 10-50x more than a pruned scan
- Too many clustering keys — 2-4 keys maximum; more keys reduce clustering effectiveness
- Oversized warehouses — bigger does not always mean faster; profile first, right-size second
- Ignoring spill-to-disk — queries spilling to remote storage are 10-100x slower; increase warehouse size or optimize query
- Materializing volatile data — materialized views on rapidly changing tables cause constant refresh overhead
Quality Checklist
1---
2license: Apache-2.0
3name: data-warehouse-optimizer
4description: "Snowflake, BigQuery, clustering, partitioning, and materialized views for warehouse performance. Activate on: Snowflake, BigQuery, Redshift, query optimization, clustering, partitioning, materialized view, warehouse cost, query profile. NOT for: dbt model structure (use dbt-analytics-engineer), data modeling (use dimensional-modeler)."
5allowed-tools: Read,Write,Edit,Bash(npm:*,npx:*,python:*,snowsql:*,bq:*)
6category: Data & Analytics
7tags:
8 - snowflake
9 - bigquery
10 - query-optimization
11 - partitioning
12 - warehouse
13pairs-with:
14 - skill: dbt-analytics-engineer
15 reason: dbt models benefit from warehouse-level optimization
16 - skill: data-cost-optimizer
17 reason: Warehouse optimization directly reduces costs
18 - skill: dimensional-modeler
19 reason: Physical model design affects query performance
20metadata:
21 source: https://skillmd.com/skills/curiositech/data-warehouse-optimizer
22---
23
24# Data Warehouse Optimizer
25
26Optimize query performance and resource utilization in Snowflake, BigQuery, and Redshift through clustering, partitioning, materialized views, and query profiling.
27
28## Activation Triggers
29
30**Activate on:** "Snowflake optimization", "BigQuery performance", "Redshift tuning", "query optimization", "clustering key", "partitioning", "materialized view", "warehouse sizing", "query profile", "slow query"
31
32**NOT for:** dbt project structure → `dbt-analytics-engineer` | Dimensional modeling → `dimensional-modeler` | Cost optimization beyond warehouse → `data-cost-optimizer`
33
34## Quick Start
35
361. **Profile slow queries** — use QUERY_PROFILE (Snowflake), INFORMATION_SCHEMA.JOBS (BigQuery), STL tables (Redshift)
372. **Partition large tables** — by date column (most common), reducing scan size by 10-100x
383. **Add clustering** — co-locate frequently filtered/joined columns within partitions
394. **Materialize expensive aggregations** — materialized views for dashboards, pre-aggregated metrics
405. **Right-size warehouses** — auto-suspend idle, auto-scale for concurrency, match size to workload
41
42## Core Capabilities
43
44| Domain | Technologies |
45|--------|-------------|
46| **Snowflake** | Micro-partitions, clustering keys, search optimization, warehouses |
47| **BigQuery** | Partitioning, clustering, BI Engine, materialized views |
48| **Redshift** | Sort keys, dist keys, VACUUM, WLM, Redshift Serverless |
49| **General** | Query plans, statistics, result caching, spill-to-disk analysis |
50| **Monitoring** | Snowflake Account Usage, BigQuery INFORMATION_SCHEMA, CloudWatch |
51
52## Architecture Patterns
53
54### Snowflake Clustering and Search Optimization
55
56```sql
57-- Cluster a large fact table by commonly filtered columns
58ALTER TABLE fct_events
59 CLUSTER BY (event_date, customer_id);
60
61-- Verify clustering depth (lower = better, target < 2.0)
62SELECT SYSTEM$CLUSTERING_INFORMATION('fct_events', '(event_date, customer_id)');
63
64-- Search optimization for point lookups on high-cardinality columns
65ALTER TABLE fct_events ADD SEARCH OPTIMIZATION
66 ON EQUALITY(order_id), EQUALITY(email);
67
68-- Result: range scans use clustering, point lookups use search optimization
69```
70
71### BigQuery Partitioning + Clustering
72
73```sql
74-- Partition by date, cluster by high-cardinality filter columns
75CREATE TABLE `project.dataset.fct_events`
76PARTITION BY DATE(event_timestamp)
77CLUSTER BY customer_id, event_type
78AS
79SELECT * FROM `project.dataset.raw_events`;
80
81-- Query benefits: partition pruning + cluster pruning
82-- Only scans partitions matching WHERE clause
83SELECT customer_id, COUNT(*)
84FROM `project.dataset.fct_events`
85WHERE event_timestamp BETWEEN '2026-01-01' AND '2026-01-31'
86 AND event_type = 'purchase'
87GROUP BY customer_id;
88
89-- Check bytes scanned reduction
90-- Target: 90%+ reduction vs unpartitioned table
91```
92
93### Warehouse Sizing Strategy (Snowflake)
94
95```
96Workload Type Recommended Size Auto-Suspend Concurrency
97───────────── ──────────────── ──────────── ───────────
98Dashboard queries X-Small/Small 60s Auto-scale (max 3)
99Analyst ad-hoc Medium 300s 1 cluster
100dbt daily build Large Immediate 1 cluster
101Data science / ML X-Large+ Immediate 1 cluster
102
103Key: separate workloads into different warehouses
104 to prevent resource contention and enable per-workload billing
105```
106
107## Anti-Patterns
108
1091. **Scanning full tables** — always partition by date; a full scan of a 1TB table costs 10-50x more than a pruned scan
1102. **Too many clustering keys** — 2-4 keys maximum; more keys reduce clustering effectiveness
1113. **Oversized warehouses** — bigger does not always mean faster; profile first, right-size second
1124. **Ignoring spill-to-disk** — queries spilling to remote storage are 10-100x slower; increase warehouse size or optimize query
1135. **Materializing volatile data** — materialized views on rapidly changing tables cause constant refresh overhead
114
115## Quality Checklist
116
117- [ ] Large tables (>1B rows) partitioned by date column
118- [ ] Clustering keys set on top 2-3 filter/join columns
119- [ ] Query profile reviewed for top 10 slowest queries monthly
120- [ ] Spill-to-disk queries identified and optimized (increase size or rewrite)
121- [ ] Materialized views created for expensive dashboard aggregations
122- [ ] Warehouses auto-suspended when idle (60-300s)
123- [ ] Workloads separated into dedicated warehouses
124- [ ] Result cache hit rate >50% for repeated analytical queries
125- [ ] Bytes scanned tracked and reduced quarter-over-quarter
126- [ ] Unused tables/views identified and dropped quarterly