# Warehouse Optimization

> Query optimization, partitioning, clustering, and warehouse tuning.

- Skill: `majiayu000/warehouse-optimization` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add majiayu000/warehouse-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/majiayu000/warehouse-optimization/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: majiayu000 (https://skillmd.com/u/majiayu000)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/majiayu000/warehouse-optimization

---


# Warehouse Optimization

## Partitioning

```sql
-- 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

```sql
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

```sql
-- ❌ 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

```sql
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