| name | sql-query-optimizer |
| description | Analyzes and optimizes SQL queries across different data warehouse platforms (Snowflake, BigQuery, Redshift, Databricks) with platform-specific recommendations. |
| version | 1.0.0 |
| category | SQL Optimization |
| skill-id | SK-DEA-004 |
| allowed-tools | Read, Grep, Glob, Bash, WebFetch |
| graph | {"domains":["domain:data-engineering"],"specializations":["specialization:data-engineering-analytics"],"skillAreas":["skill-area:data-warehouse-modeling","skill-area:big-data-tools"],"roles":["role:data-engineer","role:analytics-engineer"],"workflows":["workflow:data-pipeline-deployment"]} |
SQL Query Optimizer
Analyzes and optimizes SQL queries across different data warehouse platforms with platform-specific recommendations.
Overview
This skill examines SQL queries to identify performance bottlenecks, suggest optimizations, and provide platform-specific recommendations for Snowflake, BigQuery, Redshift, and Databricks. It analyzes query execution plans, recommends indexes/clustering keys, and identifies anti-patterns.
Capabilities
- Query execution plan analysis - Parse and analyze EXPLAIN outputs
- Index recommendations - Suggest clustering keys, sort keys, partition keys
- Join optimization - Identify inefficient join patterns and suggest improvements
- Subquery elimination - Convert correlated subqueries to CTEs or joins
- CTE optimization - Materialize vs reference optimization
- Window function optimization - Frame and partition optimization
- Predicate pushdown validation - Verify filter pushdown effectiveness
- Clustering key recommendations - Platform-specific clustering strategies
- Materialized view suggestions - Identify candidates for materialized views
- Platform-specific optimizations - Snowflake, BigQuery, Redshift, Databricks
Input Schema
{
"query": {
"type": "string",
"description": "The SQL query to analyze",
"required": true
},
"platform": {
"type": "string",
"enum": ["snowflake", "bigquery", "redshift", "databricks", "postgres"],
"required": true,
"description": "Target data warehouse platform"
},
"tableStatistics": {
"type": "object",
"description":
Output Schema
{
"optimizedQuery": {
"type": "string",
"description": "The optimized SQL query"
},
"improvements": {
"type": "array",
"items": {
"type": {
"type": "string",
"enum": ["join", "predicate", "aggregation", "cte", "window", "scan", "index"]
},
"description": "string",
"impact": "high|medium|low",
"lineNumber"
Usage Examples
Basic Query Optimization
{
"query": "SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.created_at > '2024-01-01'",
"platform": "snowflake"
}
With Execution Plan Analysis
{
"query": "SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id",
"platform": "bigquery",
"executionPlan": {
"stages": [...],
"totalBytesProcessed": 1073741824
},
"optimizationGoals": ["cost", "scan_reduction"]
}
With Table Statistics
{
"query": "SELECT ... complex query ...",
"platform": "redshift",
"tableStatistics": {
"tables": [
{
"name": "orders",
"rowCount": 10000000,
"sizeGB": 50,
"columns": [
{"name": "order_id", "cardinality": 10000000},
{"name": "customer_id", "cardinality": 500000}
]
}
]
}
}
Platform-Specific Optimizations
Snowflake
| Optimization | Description |
|---|
| Clustering keys | Recommend micro-partition clustering |
| Result cache | Identify queries benefiting from caching |
| Query acceleration | Suggest QUERY_ACCELERATION_MAX_SCALE_FACTOR |
| Warehouse sizing | Right-size warehouse recommendations |
BigQuery
| Optimization | Description |
|---|
| Partitioning | DATE/TIMESTAMP partitioning recommendations |
| Clustering | Up to 4 clustering columns |
| BI Engine | Identify BI Engine-eligible queries |
| Slots | Estimate slot usage optimization |
Redshift
| Optimization | Description |
|---|
| Sort keys | COMPOUND vs INTERLEAVED recommendations |
| Distribution | KEY, EVEN, ALL distribution strategies |
| Compression | Column encoding recommendations |
| Vacuum | VACUUM and ANALYZE recommendations |
Databricks
| Optimization | Description |
|---|
| Z-ordering | Multi-column Z-order recommendations |
| Delta cache | Caching strategy recommendations |
| Photon | Photon-eligible query patterns |
| Adaptive execution | AQE configuration suggestions |
Common Anti-Patterns Detected
Query Structure
| Anti-Pattern | Impact | Fix |
|---|
| SELECT * | High | Specify columns explicitly |
| Correlated subqueries | High | Convert to JOIN or CTE |
| DISTINCT on large datasets | Medium | Use GROUP BY or window functions |
| Non-SARGable predicates | High | Rewrite for index usage |
Join Issues
| Anti-Pattern | Impact | Fix |
|---|
| Cartesian products | Critical | Add join conditions |
| Implicit joins | Medium | Use explicit JOIN syntax |
| Wrong join order | High | Reorder by selectivity |
| Missing indexes on join keys | High | Add clustering/sort keys |
Aggregation Issues
| Anti-Pattern | Impact | Fix |
|---|
| GROUP BY ordinal | Low | Use column names |
| Aggregating before filter | High | Filter first, then aggregate |
| Over-grouping | Medium | Reduce GROUP BY columns |
Integration Points
MCP Server Integration
- Snowflake MCP - Real-time execution plan analysis
- BigQuery MCP - Cost estimation and slot analysis
- Redshift MCP - Query execution and statistics
Related Skills
- Data Quality Profiler (SK-DEA-005) - Table statistics gathering
- dbt Project Analyzer (SK-DEA-003) - Model query optimization
Applicable Processes
- Query Optimization (
query-optimization.js)
- Data Warehouse Setup (
data-warehouse-setup.js)
- BI Dashboard Development (
bi-dashboard.js)
- OBT Creation (
obt-creation.js)
References
Version History
- 1.0.0 - Initial release with multi-platform support
