warehouse-optimization
Query optimization, partitioning, clustering, and warehouse tuning.
When & Why to Use This Skill
This Claude skill provides expert-level guidance on SQL query optimization and data warehouse performance tuning. It focuses on improving execution efficiency and reducing compute costs across major platforms like Snowflake, BigQuery, and Redshift through advanced techniques such as partitioning, clustering, and join optimization.
Use Cases
- Performance Tuning: Identifying and fixing slow-running queries by analyzing execution plans (EXPLAIN ANALYZE) and resolving bottlenecks like full table scans.
- Cost Management: Reducing data warehouse expenses by implementing efficient partitioning, clustering strategies, and monitoring byte usage.
- Schema Design: Designing high-performance table structures using distribution keys, sort keys, and materialized views for large-scale analytical workloads.
- Query Refactoring: Optimizing complex joins and data distribution to prevent resource exhaustion and improve data processing speed.
| name | warehouse-optimization |
|---|---|
| description | Query optimization, partitioning, clustering, and warehouse tuning. |
Warehouse Optimization
Partitioning
-- Snowflake: Automatic clustering
ALTER TABLE fct_sales
CLUSTER BY (date_key, store_key);
-- BigQuery: Partition by date
CREATE TABLE fct_sales
PARTITION BY DATE(ordered_at)
CLUSTER BY store_id, product_id;
-- Redshift: Distribution and sort keys
CREATE TABLE fct_sales (
...
)
DISTKEY(store_id)
SORTKEY(ordered_at, store_id);
Query Optimization
Use EXPLAIN
EXPLAIN ANALYZE
SELECT ...
FROM fct_sales
WHERE date_key BETWEEN 20240101 AND 20240131;
Common Issues
| Problem | Solution |
|---|---|
| Full table scan | Add partition filter |
| Skewed joins | Redistribute data |
| Large sorts | Pre-aggregate |
| Too many columns | Select only needed |
Efficient Joins
-- ❌ Bad: Large table on left
SELECT * FROM fct_sales s
JOIN dim_date d ON s.date_key = d.date_key;
-- ✅ Good: Small table on left (broadcast)
SELECT * FROM dim_date d
JOIN fct_sales s ON d.date_key = s.date_key;
Materialized Views
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
date_key,
store_key,
SUM(amount) as total_sales,
COUNT(*) as transaction_count
FROM fct_sales
GROUP BY 1, 2;
Cost Management
- Monitor credit/byte usage
- Set query timeouts
- Use warehouse scheduling
- Archive old partitions
- Implement query tagging