data-warehouse-optimizer

license	Apache-2.0
name	data-warehouse-optimizer
description	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).
allowed-tools	Read,Write,Edit,Bash(npm:*,npx:*,python:*,snowsql:*,bq:*)
category	Data & Analytics
tags	["snowflake","bigquery","query-optimization","partitioning","warehouse"]
pairs-with	[{"skill":"dbt-analytics-engineer","reason":"dbt models benefit from warehouse-level optimization"},{"skill":"data-cost-optimizer","reason":"Warehouse optimization directly reduces costs"},{"skill":"dimensional-modeler","reason":"Physical model design affects query performance"}]

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

1. Profile slow queries — use QUERY_PROFILE (Snowflake), INFORMATION_SCHEMA.JOBS (BigQuery), STL tables (Redshift)
2. Partition large tables — by date column (most common), reducing scan size by 10-100x
3. Add clustering — co-locate frequently filtered/joined columns within partitions
4. Materialize expensive aggregations — materialized views for dashboards, pre-aggregated metrics
5. 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   OPTIMIZATION
   EQUALITY(order_id), EQUALITY(email);