--- name: sql-query-optimizer-a723b4b27b72 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 ```json { "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 ```json { "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 ```json { "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 ```json { "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 ```json { "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 - [Snowflake Query Performance](https://docs.snowflake.com/en/user-guide/performance-query) - [BigQuery Best Practices](https://cloud.google.com/bigquery/docs/best-practices-performance-overview) - [Redshift Performance Tuning](https://docs.aws.amazon.com/redshift/latest/dg/c_designing-queries-best-practices.html) - [Databricks SQL Best Practices](https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select.html) ## Version History - **1.0.0** - Initial release with multi-platform support --- Source attribution: a5c-ai Original project: https://github.com/a5c-ai/babysitter License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Mega AI Hub. ## Upstream license notice MIT License Copyright (c) 2026 Gabriel Moreira Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Mega AI Hub resource metadata Creator / publisher: a5c-ai Original project: https://github.com/a5c-ai/babysitter Attribution basis: Upstream repository explicitly recorded in package mirror path Fields of work: Data & Analytics, Development & Engineering, Marketing, Sales Roles: Business Development, Data Analyst, Data Scientist, Marketing Analyst, ML / AI Engineer Specialties: API, Database Tools: MongoDB, MySQL, Sql, PostgreSQL Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: f9c2296c9cea03e975615fd59f912c13a4e8655ea375890c515ae65e76d22295