Analyzes and optimizes SQL queries across different data warehouse platforms (Snowflake, BigQuery, Redshift, Databricks) with platform-specific recommendations.
Analyzes and optimizes SQL queries across different data warehouse platforms (Snowflake, BigQuery, Redshift, Databricks) with platform-specific recommendations.
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
{"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":"Table statistics including row counts, column cardinality","properties":{"tables":{"type":"array","items":{"name":"string","rowCount":"number","sizeGB":"number","columns":"array"}}}},"executionPlan":{"type":"object","description":"Query execution plan (EXPLAIN output)"},"queryHistory":{"type":"object","description":"Historical query performance metrics"},"optimizationGoals":{"type":"array","items":{"type":"string","enum":["latency","cost","throughput","scan_reduction"]},"default":["latency","cost"]}}
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":"number","originalCode":"string","optimizedCode":"string"}},"indexRecommendations":{"type":"array","items":{"table":"string","type":"clustering|sort|partition|index","columns":"array","rationale":"string","ddl":"string"}},"estimatedImprovement":{"scanReduction":{"type":"number","description":"Percentage reduction in data scanned"},"timeReduction":{"type":"number","description":"Percentage reduction in execution time"},"costReduction":{"type":"number","description":"Percentage reduction in query cost"}},"antiPatterns":{"type":"array","items":{"pattern":"string","severity":"high|medium|low","location":"string","suggestion":"string"}},"platformSpecificNotes":{"type":"array","items":"string"}}
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"]}
