sql-query-optimizer

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": "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"]
}

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

• Snowflake Query Performance
• BigQuery Best Practices
• Redshift Performance Tuning
• Databricks SQL Best Practices

Version History

• 1.0.0 - Initial release with multi-platform support