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.
PART A Grounding β hitting 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:
- 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 - Ground with tools, not the prompt β expose
search_tables(query)anddescribe_table(full_name). The model must calldescribe_tableto see real columns before writing SQL. It queries reality β it can't hallucinate a column. tool use D4 - Enforce the 3-level namespace + an allow-set β require
catalog.schema.tablein 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
Reference architecture
+ 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;
For the retrieved candidate tables only, send a compact, high-signal packet:
| Include β | Why it helps the model |
|---|---|
Fully-qualified name catalog.schema.table | Correct 3-level references; disambiguates same-named tables |
| Column names + data types | Avoids 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 columns | Maps "EU" β the real code 'EMEA'; disambiguates enums & statuses |
| Partition columns, rough row count | Nudges 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 table | Blows the context window & cost; drowns signal |
| Raw PII in sample values | Mask it β sample categorical columns, not names/emails |
| Full data / large text blobs | Send shape, not payload |
SELECT DISTINCT col LIMIT 20 for
low-cardinality columns, mask PII, and cache the result. Don't sample at query time (cost + latency).- Retrieval (top-K tables) is the primary lever β send 3β8 candidate tables, not 3,000. RAG
- Progressive disclosure β send names + one-line comments first; the model calls
describe_tableonly for the tables it actually wants. Keeps the fixed prompt tiny. tool use - A curated semantic layer beats raw introspection β a human-maintained data dictionary / glossary (metrics, joins, business terms) is fewer tokens and higher quality. This is exactly what a Skill or a cached instruction doc is for (Part C). Skills D2
- Prompt caching β cache the stable prefix (SQL dialect rules + safety rules + semantic glossary); put the per-question retrieved schema after the last cache breakpoint (it's volatile). prompt caching D5
- Tool search /
defer_loadingif you expose many per-domain tools. tool search D4 - Context editing / compaction for long multi-question sessions β clear stale tool results so the window stays lean. context editing D5
PART B Correctness β syntactic, right-tables, and logically right
Three different problems, three different guards. Never trust the model's SQL directly.
| Kind of wrong | Guard | Claude / 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/columns | EXPLAIN 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)
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
| Metric | What it measures |
|---|---|
| Execution accuracy | Does the generated SQL run without error? |
| Result-set match | Does it return the gold answer (order-insensitive)? β the real "correct" |
| Table-selection precision/recall | Did it pick the right tables from the metastore? |
| Self-correction count | How many EXPLAIN retries to get valid SQL (efficiency signal) |
| Latency Β· tokens Β· cost Β· bytes scanned | Cost/perf per query (Claude usage + warehouse compute) |
| Refusal / clarify rate | How often it declines or asks β calibrate ambiguity handling |
- Golden set = (question, gold SQL, gold result) pairs, incl. ambiguous and adversarial questions.
- For fuzzy answers, use an LLM-as-judge with a different model + a rubric. D5
- Run against a staging metastore snapshot; re-run on every prompt/model/schema change (regression gate).
- Track drift β execution accuracy quietly dropping as the schema evolves is your early warning.
- Ground, don't guess β the model must
describe_tablereal columns before writing SQL. This alone kills most column hallucination. - EXPLAIN rejects any hallucinated table/column (they don't resolve).
- Constrain to the retrieved allow-set β no querying random tables.
- Give it an out β an
ask_user()tool so it clarifies instead of inventing an answer to an ambiguous question. D3 - Rich comments + sample values so business terms map to real columns/codes.
- Cite which tables/columns it used, and show the SQL β the user can sanity-check.
PART C The agent β pattern, tools, CLAUDE.md & Skills, error handling
β 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_tablecalls in code β only the final result returns to context (token saver, D4)
| Tool | Does | Guardrails / notes |
|---|---|---|
search_tables(query) | Retrieval β candidate FQNs + comments | Returns only tables the SP can read |
describe_table(full_name) | Columns, types, comments, PK/FK, sample distinct values | The grounding tool β PII masked |
validate_sql(query) | Runs EXPLAIN, returns ok / analysis error | The deterministic validator β cheap, no data read |
run_sql(query) | Executes read-only | SELECT-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)
Two honest interpretations β know both for the exam:
- CLAUDE.md β a Claude Code (D2) feature used while you build the agent: it lives in the repo and guides Claude Code with conventions, how to run the eval harness, the UC connection setup, "never generate non-SELECT." Its runtime analog in the Messages API is your cached system prompt: Spark/Databricks SQL dialect, the 3-level FQN rule, SELECT-only + always-LIMIT, prefer PK/FK joins, and the semantic glossary. Same idea β a stable, cached instruction doc. D2 prompt caching
- Agent Skills (SKILL.md) package reusable expertise, model-invoked via progressive disclosure (loads
only when relevant β saves context). Build a
databricks-uc-sqlskill: dialect rules, common patterns (windowing, date filters, region-code mapping), a checklist, plus bundled scripts (an EXPLAIN validator, a formatter). On the Messages API, skills load via thecontainer+ code-execution + skills betas. Skills D2
- Tool errors β
tool_resultwithis_error:true(EXPLAIN failure, run error, permission denied, timeout) β the model reads it and fixes. - API errors β SDK auto-retries 429/5xx with backoff; handle all six
stop_reasons (tool_use / end_turn / max_tokens / refusal / stop_sequence / pause_turn) β see the Claude API build; never fall through. - Guardrail denials (non-SELECT, disallowed table) β return a clear message, don't execute.
- Loop termination β cap self-correction turns; on exhaustion, return the best SQL for a human to run (graceful degradation).
PART D Production β cost, reliability, scale, observability
- Prompt-cache the stable system prompt + SQL guidelines + glossary (~90% cheaper cached reads; put volatile retrieved-schema after the last breakpoint).
- Model tiering β Haiku 4.5 for retrieval/routing & simple SQL; Opus/Sonnet for hard multi-join reasoning. Route by question complexity.
- Retrieval keeps input tokens tiny (don't send the metastore).
- Cache introspected metadata + sample values β refresh only on DDL change, not per request.
- EXPLAIN before run β avoid paying warehouse compute for a wrong query (Databricks cost, and it's the bigger bill).
- Batch API (~50% off) for scheduled/offline report generation; effort low for simple SQL.
- Bound self-correction retries (each is a full request).
- Deterministic validation (EXPLAIN) + bounded self-correction β the core reliability move.
- Retries w/ backoff on 429/5xx (SDK); fallbacks β model fallback on overload, and a degraded mode that returns the SQL for a human if execution keeps failing.
- Warehouse timeouts + statement cancellation; a circuit breaker if the warehouse is down.
- Idempotency is free β read-only queries are naturally safe to retry.
- Least-privilege SP + LIMIT + row cap bound the blast radius.
- Stateless service β scale horizontally (many workers); prompt cache is shared.
- Vector index scales to many tables; refresh incrementally on DDL/lineage changes.
- Watch ITPM/OTPM rate limits β queue, and spread warehouse concurrency (serverless SQL, multiple warehouses).
- Async / Batch for heavy or scheduled loads; per-tenant isolation via the SP's grants (multi-catalog/workspace).
- Trace every request: question β retrieved tables β generated SQL β EXPLAIN result β #retries β final SQL β
rows β latency β tokens (
input/cache_read/cache_creation/output) β cost β warehouse bytes scanned β success/fail. Use MLflow Tracing / Langfuse / Phoenix. - Usage fields β cost per query + cache hit-rate; capture
request-id/_request_idfor support. - SLOs: valid-SQL rate, execution accuracy (from evals), p50/p95 latency, cost/query, cache hit-rate, retry rate, refusal rate.
- Databricks audit β query history / system tables record the executed SQL; lineage shows what was touched.
- Alert on error-rate/cost spikes and drift (execution accuracy falling as the schema changes).
CERT MAP What this one project teaches for the exam
| Concern here | Claude feature | CCA domain |
|---|---|---|
| Search/describe/validate/run tools; is_error results; tool_choice; strict schemas; PTC; tool search | Native tool use | D4 |
| Self-correcting loop, stop_reason routing, loop termination, single-vs-multi-agent, sub-agent verifier | Agentic loop | D1 |
| System-prompt/glossary design, clarify-vs-guess, grounding, few-shot patterns | Prompt engineering | D3 |
| Retrieval to bound context, prompt caching, context editing, model tiering, batch, cost, evals, observability | Context & reliability | D5 |
| CLAUDE.md (build-time), Agent Skills / SKILL.md (semantic layer as a skill) | Claude Code & Skills | D2 |