optimizing-databricks-sql
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.
AltimateAI · Static checks on file
Installation
Read the source and dependencies before running a command. Copying does not install or execute anything.
npx skills add AltimateAI/data-engineering-skills --skill optimizing-databricks-sqlAbout this skill
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-optimizedirectly. - 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 |
| 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 |
| 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 |
| 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