/optimizing-databricks-sql

name: optimizing-databricks-sql
description: Analyze DBSQL queries, including SQL embedded in notebooks (`spark.sql(...)`, `%sql` cells), for anti-patterns, lint issues, and performance problems, using Databricks-specific dialect and platform knowledge (Delta, Photon, Unity Catalog) layered on top of altimate-code's generic SQL engine. Use when a user asks to optimize, review, or lint DBSQL queries on Databricks, whether standalone or embedded in a notebook.

Databricks Optimize (DBSQL)

A Databricks/Delta-platform **overlay**, not a standalone optimizer. The generic rewrite/analyze/verify pipeline (Steps 1, 5, 6) is the same pipeline altimate-code's native `query-optimize` skill runs — same tool calls, same composition. This skill's only job is the part `query-optimize` structurally can't do: detecting Databricks/Delta platform-specific issues (Z-ordering, table statistics, Photon UDF blocking) that need table metadata and Databricks docs knowledge, not just query text, and folding those findings in alongside the generic ones.

If `query-optimize` is installed in the current environment, prefer invoking it for Steps 1/5/6 and layer Steps 2–4's Databricks-specific findings on top of its output, the same way `sql-review` defers to sibling skills instead of duplicating them. If it isn't available (e.g. this skill is running standalone, outside altimate-code), fall back to calling the same tools directly as described below — the tool calls are identical either way, so behavior doesn't change based on which path ran.

No rewrite is reported as "optimized" until it clears the validation gate in Step 7 — that gate is a hard requirement, not a suggestion.

When to use this skill

**Use when:**

• The query is DBSQL (standalone or embedded in a notebook cell) and the

question involves Databricks/Delta-specific behavior: Z-ordering, liquid clustering, table statistics, Photon, Unity Catalog, Predictive Optimization.

**Do NOT use for:**

• Generic, dialect-agnostic SQL optimization with no Databricks-specific

angle → use `query-optimize` directly.

• SQL quality/safety linting unrelated to performance (injection, PII

exposure, style) → use `sql-review`.

• PySpark DataFrame-level code (`.collect()`, `.join()`, UDFs used as

Python objects) → deferred to a later phase, out of scope for now.

Before anything else — confirm the file path resolved correctly

**Confirm the resolved file path matches what was requested, before doing anything else.** If the user gave an explicit path (a `/Workspace/...` path, a repo path, any specific path), the tool used to read or write it must resolve to *that exact path* — not a same-named file found some other way. If the proper access tool fails, or the only way to find something is a generic filename search, **stop and disclose it explicitly** before analyzing or writing anything: state what path was requested, what (if anything) was found instead and where, and ask how to proceed. Never silently substitute a different file and continue as if it's the same one.

**If no specific path was given at all** (a generic request naming no file), a broader search — local files, a workspace listing — is a reasonable way to find a match. But the report must still state which path was actually used, so the reader can confirm it's the right one rather than discovering it was wrong after the fact.

Workflow

| Step | Action | Tool(s) | Detail | |---|---|---|---| | 0. Locate warehouse | Confirm a Databricks connection exists; get its name for later calls | `warehouse_list` | — | | 1. Baseline | Run with `dialect: databricks`, plus the composite validate/lint/safety/PII pass | `sql_analyze`, `altimate_core_check` | via `query-optimize` if available; see below if parsing fails | | 2. Platform overlay | Cross-check against Databricks/Delta patterns the generic engine structurally can't detect from query text alone | — | [references/dbsql-anti-patterns.md](references/dbsql-anti-patterns.md) | | 3. Ground truth | If a warehouse connection exists, confirm claims against real table metadata before stating them as fact | `schema_inspect`, `sql_execute` (`DESCRIBE DETAIL`) | [references/delta-table-health.md](references/delta-table-health.md) | | 4. Classify | Tag each finding predicate-level vs. strategy-level | `sql_explain` (`EXPLAIN FORMATTED`) | see below | | 5. Rewrite | Generate and verify a fix | `altimate_core_rewrite` (`verify_equivalence: true`), `altimate_core_equivalence` for hand-authored fallbacks | see below, via `query-optimize` if available | | 6. Grade | Present grade + findings, tagged by source | `altimate_core_grade` | via `query-optimize` if available | | 7. Validate | Correctness + performance gate | — | [references/validation.md](references/validation.md) | | 8. Apply | Write the rewrite back, only on explicit confirmation | file edit | see below |

Step 1 — when the statement type can't be parsed

Confirmed twice in live testing: `sql_analyze`/`altimate_core_check` and related tools (`altimate_core_validate`, `altimate_core_migration`) cannot reliably parse DDL (`CREATE TABLE ...`) or `MERGE` statements — either failing outright or returning nothing, sometimes inconsistently across tools on the identical text. This is a real tool-coverage gap, not a signal that the statement has no findings. When it happens, fall back to live execution for grounding instead of treating the parse failure as "nothing to report": run the statement (or `EXPLAIN`/ `DESCRIBE DETAIL` against it) directly via `sql_execute`, and reason from what actually happens — a real parse/constraint error from the engine itself (e.g. `NON_LAST_MATCHED_CLAUSE_OMIT_CONDITION`, `[MANAGED_TABLE_FORMAT]`) is stronger, more specific evidence than any static tool's silence would have been anyway.

Step 3 — ground truth

When Step 0 found a live Databricks warehouse connection, don't call a query expensive on text alone — confirm it. `schema_inspect` the referenced tables