Investigate a production warehouse query whose runtime or cost regressed. Work from evidence rather than rewriting SQL by intuition.
Warehouse and version/edition: [Snowflake / BigQuery / Redshift / Databricks SQL]
Query and query ID:
[Paste SQL]
Before/after runtime, bytes scanned, and cost: [metrics]
Before/after EXPLAIN or query profile:
[Paste plans]
Recent changes: [schema, statistics, clustering, data growth, code, warehouse size]
Table sizes, partitions, clustering/sort keys: [details]
Concurrency and workload context: [details]
Provide:
1. Plan-diff analysis identifying the first meaningful divergence
2. Ranked root-cause hypotheses with evidence and falsification checks
3. Analysis of pruning, cardinality estimates, join order, spills, skew, and repeated scans
4. Minimal SQL or model changes, with dialect-correct examples
5. Statistics, clustering, partitioning, or materialization changes only when justified
6. A reproducible benchmark protocol using representative parameters
7. Guardrails to catch future regressions in CI or production
8. Expected impact, trade-offs, and rollback plan for each recommendation
Preserve query semantics. Flag any optimization that can change null, duplicate, decimal, or timestamp behavior.