AI Engineering Projects β€Ί P0 β€Ί Databricks Unity Catalog (production)

🧱 NLβ†’SQL on Databricks Unity Catalog

From the P0 toy to a real copilot Β· every answer mapped to a Claude feature & CCA domain

THE JUMP Toy β†’ real: the problem changes shape

The P0 Claude API agent had one DuckDB with a handful of tables β€” you could paste the whole schema into the prompt. On Databricks Unity Catalog you have many catalogs β†’ schemas β†’ tables/views/materialized views, possibly thousands of columns and rows of metadata. You cannot send it all. The whole game becomes: get the right context to the model, keep it small, and prove the SQL is correct before you trust it. Every technique below is a Claude/CCA lesson.

Databricks ships its own NL→SQL (AI/BI Genie). We build our own on the Claude API so each piece maps to an exam concept — that's the point of this page.

PART A Grounding β€” hitting the right catalogΒ·schemaΒ·table

Q: How do we ensure it forms SQL against the RIGHT catalog / schema / table?

You never let the model guess from thousands of tables. You give it a retrieval + grounding layer and make it look things up with tools instead of inventing names. Three moves:

  1. Retrieve candidate tables β€” at ingestion, pull every table's fully-qualified name + description + column list from UC (system.information_schema, DESCRIBE TABLE EXTENDED), embed it, and index it (Databricks Vector Search or pgvector). At query time, retrieve the top-K tables relevant to the question. RAG D5
  2. Ground with tools, not the prompt β€” expose search_tables(query) and describe_table(full_name). The model must call describe_table to see real columns before writing SQL. It queries reality β†’ it can't hallucinate a column. tool use D4
  3. Enforce the 3-level namespace + an allow-set β€” require catalog.schema.table in generated SQL; reject any table not in the retrieved candidate set. Set a default catalog/schema in the cached system prompt to cut ambiguity. guardrail D1/D4
Governance is the real backstop. Run the agent under a service principal with least-privilege SELECT grants. UC enforces access at the engine β€” the model literally cannot read a table the principal can't, and row/column masks apply automatically. Same rule as P0: security lives at the grant layer, never in the prompt.

Reference architecture

flowchart LR subgraph ING["Ingestion (offline, on DDL change)"] UC[("UC information_schema
+ DESCRIBE + comments + PK/FK")] --> META["Metadata + sample values"] META --> EMB["Embed"] --> VDB[("Vector index")] end Q["User question"] --> AG["Claude agent (tools)"] AG -->|"search_tables"| VDB AG -->|"describe_table"| UC AG -->|"validate_sql (EXPLAIN)"| WH["Databricks SQL warehouse"] AG -->|"run_sql (read-only SP)"| WH WH -->|"rows / error"| AG --> ANS["Answer + the SQL"] classDef c fill:#eef6ff,stroke:#3b7bd0,color:#1f5fa8; class AG c;
Q: To make it "informed" for the AI β€” what should the schema context contain?

For the retrieved candidate tables only, send a compact, high-signal packet:

Include βœ…Why it helps the model
Fully-qualified name catalog.schema.tableCorrect 3-level references; disambiguates same-named tables
Column names + data typesAvoids type errors, informs casts/date handling
Table & column COMMENTs (descriptions)Maps business language β†’ columns (the single biggest quality lever)
PK / FK constraints (UC informational constraints)Correct joins β€” the #1 source of logically-wrong SQL
Sample / distinct values for low-cardinality columnsMaps "EU" β†’ the real code 'EMEA'; disambiguates enums & statuses
Partition columns, rough row countNudges toward partition filters; avoids full scans
Object type (table / view / materialized view)MVs/views may already pre-aggregate β€” prefer them; know what's queryable
Skip / be careful ⚠️Reason
The whole metastore, every column of every tableBlows the context window & cost; drowns signal
Raw PII in sample valuesMask it β€” sample categorical columns, not names/emails
Full data / large text blobsSend shape, not payload
Sample values β€” yes, but bounded. At ingestion run SELECT DISTINCT col LIMIT 20 for low-cardinality columns, mask PII, and cache the result. Don't sample at query time (cost + latency).
Q: How do we limit context β€” we can't send it all?

PART B Correctness β€” syntactic, right-tables, and logically right

Q: How do we ensure the SQL is syntactically valid, against the right tables, and logically correct?

Three different problems, three different guards. Never trust the model's SQL directly.

Kind of wrongGuardClaude / CCA feature
Syntactic (won't parse)EXPLAIN the query first β€” Databricks parses & analyzes (resolving every table/column) without running it. On error, feed it back β†’ self-correct.tool_result is_error D1 loop
Wrong tables/columnsEXPLAIN also fails on unresolved names; plus reject FQNs outside the retrieved allow-set.guardrail D4
Logically wrong (runs, wrong answer)The hard one. Layer: (1) model self-checks the query against the question; (2) evals vs golden queries (result-set match); (3) clarify ambiguous questions instead of guessing; (4) human approval for high-stakes; (5) show the SQL + a plain-English restatement.evals HITL D5/D1

The runtime pipeline (deterministic spine + a bounded self-correction loop)

flowchart TB Q["Question"] --> R["Retrieve candidate tables (top-K)"] R --> A{"Ambiguous?"} A -->|"yes"| CLR["ask_user() β€” clarify"] --> R A -->|"no"| G["Claude: describe_table β†’ write SQL"] G --> V["validate_sql via EXPLAIN"] V -->|"error"| FIX["feed error back β†’ fix"]:::loop FIX --> G V -->|"ok"| X["run_sql (read-only, LIMIT, timeout)"] X -->|"error"| FIX X -->|"rows"| SC["self-check + summarize"] SC --> OUT["Answer + SQL (+ approval if high-stakes)"] classDef loop fill:#fff4e0,stroke:#b5691a,color:#7a4a0a;
Notice: most of this is a deterministic workflow (retrieve β†’ generate β†’ validate β†’ execute β†’ summarize). The only genuinely agentic part is the fix-on-error loop. That's the D1 lesson β€” don't agentify the parts you can hardcode.
Q: What test harness do we build?

A golden-query eval suite across catalogs β€” the thing that tells you it works and catches regressions when the schema, prompt, or model changes. evals D5

MetricWhat it measures
Execution accuracyDoes the generated SQL run without error?
Result-set matchDoes it return the gold answer (order-insensitive)? β€” the real "correct"
Table-selection precision/recallDid it pick the right tables from the metastore?
Self-correction countHow many EXPLAIN retries to get valid SQL (efficiency signal)
Latency Β· tokens Β· cost Β· bytes scannedCost/perf per query (Claude usage + warehouse compute)
Refusal / clarify rateHow often it declines or asks β€” calibrate ambiguity handling
Q: How do we bring hallucination down?

PART C The agent β€” pattern, tools, CLAUDE.md & Skills, error handling

Q: What agentic pattern fits here?

βœ… Default: single agent + tools (hub-and-spoke)

  • One agent, tools: search_tables Β· describe_table Β· validate_sql Β· run_sql Β· ask_user
  • The self-correcting loop over EXPLAIN/execute errors
  • Wraps a deterministic retrieveβ†’validateβ†’execute pipeline
  • Right for ~90% of NLβ†’SQL (D1: single-agent-plus-tools beats multi-agent here)

βš™οΈ Reach further only when warranted

  • Fresh-context verifier sub-agent to double-check the SQL for high-stakes queries (D1 sub-agent isolation)
  • Planner + parallel sub-agents only for genuinely multi-part analytical questions (decompose β†’ sub-queries β†’ merge)
  • Programmatic tool calling to chain many describe_table calls in code β€” only the final result returns to context (token saver, D4)
Q: What tools do we expose? D4 Β· Tool Design
ToolDoesGuardrails / notes
search_tables(query)Retrieval β†’ candidate FQNs + commentsReturns only tables the SP can read
describe_table(full_name)Columns, types, comments, PK/FK, sample distinct valuesThe grounding tool β€” PII masked
validate_sql(query)Runs EXPLAIN, returns ok / analysis errorThe deterministic validator β€” cheap, no data read
run_sql(query)Executes read-onlySELECT-only SP Β· LIMIT Β· statement timeout Β· result-row cap
ask_user(question)Clarify an ambiguous request (HITL)Blocks & hands control to a human

Tool-design rules that are exam gold: the description is the #1 lever (say when to use it), constrain inputs with input_schema (+ strict), return failures as tool_result with is_error:true so Claude recovers, and namespace tools if you have per-domain sets.

# The two load-bearing tools (abridged) β€” validate BEFORE you execute
def validate_sql(query):                 # deterministic guard: EXPLAIN parses+analyzes, no data read
    try:
        warehouse.execute(f"EXPLAIN {query}")   # errors on bad syntax OR unresolved table/column
        return {"ok": True}
    except Exception as e:
        return {"ok": False, "error": str(e)}   # fed back as is_error β†’ self-correction (D1)

def run_sql(query):                      # read-only SP + LIMIT + timeout enforced at the engine
    return warehouse.execute(f"SELECT * FROM ({query}) LIMIT 200", timeout_s=30)
Q: How do CLAUDE.md and Skills fit? (exam D2)

Two honest interpretations β€” know both for the exam:

Skills vs tools vs RAG (a classic exam distinction): Skills = reusable how-to knowledge (SQL patterns); tools = actions (describe/validate/run); retrieval = the specific schema data for this question. Three different jobs.
Q: Error handling? D4/D5

PART D Production β€” cost, reliability, scale, observability

Q: How do we bring the cost down? D5
Q: How do we make it reliable? D5/D1
Q: How do we make it scalable?
Q: What observability do we build? D5

CERT MAP What this one project teaches for the exam

Concern hereClaude featureCCA domain
Search/describe/validate/run tools; is_error results; tool_choice; strict schemas; PTC; tool searchNative tool useD4
Self-correcting loop, stop_reason routing, loop termination, single-vs-multi-agent, sub-agent verifierAgentic loopD1
System-prompt/glossary design, clarify-vs-guess, grounding, few-shot patternsPrompt engineeringD3
Retrieval to bound context, prompt caching, context editing, model tiering, batch, cost, evals, observabilityContext & reliabilityD5
CLAUDE.md (build-time), Agent Skills / SKILL.md (semantic layer as a skill)Claude Code & SkillsD2
One project, all five domains. Build the P0 loop first (it's the beating heart), then layer on retrieval + EXPLAIN-validation + evals + caching β€” and you've rehearsed the whole exam in code.