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