AI Engineering Projects β€Ί P0 β€Ί Build it with the Claude API

🟣 P0 · Building the Agent with the Claude API

The same self-correcting loop β€” now with native tool use Β· your CCA rehearsal in code

WHY THIS VERSION The exam is about this loop

The free local build uses a local model and parses SQL out of a text reply. That teaches the shape of an agent. This page rebuilds the exact same agent on the Claude Messages API with native tool use β€” and that is what the Claude Certified Architect exam actually tests: the stop_reason loop (D1), the tool_use / tool_result contract (D4), prompt caching (D5), and adaptive thinking (D3). Build this once and the D1/D4 questions become muscle memory.

πŸ’‘ Same problem, same loop, two front doors. Local = free & string-parsed. Claude API = paid & structured β€” the model returns a typed tool_use block instead of a string you have to scrape.

LOCAL vs CLAUDE API What changes

ConcernπŸ’» Local (Ollama)🟣 Claude API (native tool use)
How the model asks to run SQLReturns SQL as text; you regex/parse itReturns a typed tool_use block with validated input
How you feed the result backStuff it into the next prompt stringA tool_result block (user turn) with matching tool_use_id
How you know it's doneHeuristic ("looks like an answer")stop_reason == "end_turn" β€” explicit
ErrorsAppend error texttool_result with is_error: true β€” a first-class signal
Cost / auth$0, offlinePaid API key (ANTHROPIC_API_KEY or ant auth login)

DIAGRAM The Messages API tool loop

sequenceDiagram actor U as User participant AG as Your loop participant M as Claude (Messages API) participant DB as DuckDB U->>AG: "How many EU orders last quarter?" loop until stop_reason == end_turn (or max turns) AG->>M: messages + tools + system(schema, cached) alt stop_reason == "tool_use" M-->>AG: assistant turn: [thinking?] + tool_use{run_sql, query} AG->>AG: append the WHOLE assistant turn AG->>DB: run_sql(query) alt query errors DB-->>AG: exception AG-->>M: user turn: tool_result{is_error:true, error} else rows DB-->>AG: rows AG-->>M: user turn: tool_result{tool_use_id, rows} end else stop_reason == "end_turn" M-->>AG: final text answer AG-->>U: answer else stop_reason == "refusal" / "max_tokens" M-->>AG: handle explicitly end end

CODE The whole agent, on the Claude API

One tool (run_sql), the schema cached in the system prompt, a manual loop that routes on stop_reason. Every line is annotated with the CCA concept it embodies.

# pip install anthropic duckdb   Β·   export ANTHROPIC_API_KEY=sk-ant-...  (or: ant auth login)
import anthropic, duckdb

client = anthropic.Anthropic()                 # reads ANTHROPIC_API_KEY or an `ant auth login` profile
con = duckdb.connect("demo.duckdb", read_only=True)  # GUARDRAIL: read-only engine β€” DROP is impossible

def get_schema():
    return con.sql("""SELECT table_name, column_name, data_type
                      FROM information_schema.columns ORDER BY table_name""").df().to_string()

def run_sql(query):                        # the tool Claude calls
    return con.sql(f"SELECT * FROM ({query}) LIMIT 100").df().to_string()  # GUARDRAIL: row cap

# --- D4: the tool definition. The DESCRIPTION is the #1 lever β€” say when to use it. ---
TOOLS = [{
    "name": "run_sql",
    "description": "Run a read-only SQL SELECT against the DuckDB database and return the rows. "
                   "Use this to answer the user's question. Only SELECT is allowed.",
    "input_schema": {"type": "object",
        "properties": {"query": {"type": "string", "description": "One DuckDB SELECT statement."}},
        "required": ["query"]},
}]

# --- D3 + D5: schema lives in the system prompt, marked for prompt caching (stable prefix). ---
SYSTEM = [{
    "type": "text",
    "text": "You are a careful DuckDB analyst. Write ONE SELECT to answer the question and call "
            "run_sql. If it errors, read the error and fix the query. Then answer in one sentence.\n\n"
            f"Schema:\n{get_schema()}",
    "cache_control": {"type": "ephemeral"},   # D5: cache the stable schema prefix (~90% cheaper reads)
}]

def agent(question, max_turns=6):          # D1: loop termination β€” never loop forever
    messages = [{"role": "user", "content": question}]  # D1: API is stateless β€” you resend history
    for _ in range(max_turns):
        resp = client.messages.create(
            model="claude-opus-4-8",            # D5: model tier by task (Haiku 4.5 is cheaper for SQL)
            max_tokens=16000,
            system=SYSTEM, tools=TOOLS,
            thinking={"type": "adaptive"},     # D3/D1: adaptive thinking (NOT budget_tokens on 4.8)
            messages=messages,
        )
        sr = resp.stop_reason                    # D1: ROUTE on stop_reason β€” handle every value, never fall through
        if sr == "tool_use":                    # Claude wants the tool β†’ run it, feed results back, continue
            messages.append({"role": "assistant", "content": resp.content})  # D1: append the WHOLE turn (thinking + tool_use)
            results = []
            for block in resp.content:
                if block.type == "tool_use":
                    try:
                        out = run_sql(block.input["query"])
                        results.append({"type": "tool_result", "tool_use_id": block.id, "content": out})
                    except Exception as e:            # D4: return the error, don't throw β€” Claude recovers
                        results.append({"type": "tool_result", "tool_use_id": block.id,
                                        "content": f"SQL error: {e}", "is_error": True})
            messages.append({"role": "user", "content": results})  # D4: ALL results, in ONE user turn
            continue
        if sr == "end_turn":                    # normal finish β†’ return the answer text
            return next((b.text for b in resp.content if b.type == "text"), "")
        if sr == "max_tokens":                  # response was CUT OFF β€” NOT complete; don't treat as done
            return "Answer was truncated (max_tokens). Raise max_tokens or stream, then retry."
        if sr == "refusal":                    # safety classifier declined (HTTP 200; content empty or partial)
            return "Request was declined for safety reasons."
        if sr == "stop_sequence":               # only fires if you set stop_sequences (we don't) β†’ treat as done
            return next((b.text for b in resp.content if b.type == "text"), "")
        if sr == "pause_turn":                  # only with SERVER tools (web search/code exec); resend to resume
            messages.append({"role": "assistant", "content": resp.content}); continue
        return f"Unexpected stop_reason: {sr}"   # future-proof: never silently loop on an unknown reason
    return "Could not answer within the turn budget."  # D1: loop cap hit (loop termination)

print(agent("How many orders were placed in 2024?"))

STOP REASONS Handle all six β€” never fall through

Every response carries a stop_reason. Routing on it is the heart of the agentic loop (D1) β€” and a favourite exam trap. If you handle only end_turn and tool_use, an unhandled value (like max_tokens) silently re-sends the same request and burns your turn budget. Here's the full set and how this agent handles each:

stop_reasonMeansThis agent does
tool_useClaude wants to call run_sqlAppend the whole turn, run the tool, return tool_result(s), continue
end_turnFinished naturallyReturn the final answer text
max_tokensOutput was cut off β€” incompleteStop and report β€” raise max_tokens or stream, then retry (do not treat as done)
refusalSafety classifier declined (HTTP 200, empty/partial)Return a graceful "declined" message
stop_sequenceHit a custom stop stringN/A here (we set none) β€” treat as done if it ever fires
pause_turnA long server-side tool loop pausedN/A here (client tool) β€” but resend to resume if you add server tools
Two the exam loves: max_tokens (truncated β‰  finished) and refusal (a successful HTTP 200 you must still check). Always add a catch-all else so an unknown future value can't put you in a silent loop.

CERT MAP Every API line ↔ an exam concept

This is why it's the perfect rehearsal β€” each mechanic above is a place the exam probes:

stop_reason routing (end_turn / tool_use / refusal / max_tokens) β†’ D1 Β· Stop Reasons. Trap: handling only end_turn and missing refusal or max_tokens.
Append the whole assistant turn β€” including thinking + tool_use blocks β€” before returning results β†’ D1 Β· Foundations. Trap: appending only the text.
tool_result in a USER turn, matching tool_use_id, all results in one message, is_error for failures β†’ D4 Β· Tool Results. Getting any of these wrong is a 400 (the classic contract failure).
Tool definition (name Β· description Β· input_schema; description is the lever) β†’ D4 Β· Designing Tools.
Adaptive thinking {"type":"adaptive"}, not budget_tokens β†’ D3/D1. Trap: budget_tokens returns a 400 on Opus 4.8 / 4.7 / Sonnet 5.
Prompt caching the schema prefix (cache_control, order tools→system→messages, byte-stable) → D5 · Prompt Caching. Trap: the min cacheable prefix on Opus 4.8 is ~4096 tokens — a tiny schema silently won't cache.
Statelessness β€” you resend the full messages list every turn β†’ D1 / D5. Trap: assuming the API "remembers" prior calls.
Loop termination (max_turns) β†’ D1 Β· Autonomy & Guardrails. Trap: no cap β†’ runaway/cost.
Security at the tool layer (read-only DB grant + LIMIT) not the prompt β†’ D1/D4. The prompt can be jailbroken; the grant cannot.

TRADE-OFFS Manual loop vs the Tool Runner

βœ… Manual loop (shown above)

  • You see every mechanic β€” this is what the exam tests
  • Full control over gating, logging, retries
  • No beta dependency

βš™οΈ Tool Runner (production shortcut)

  • client.beta.messages.tool_runner(...) drives the loop for you β€” you write only the tool functions
  • Less code; per-turn hooks for approval/interception
  • Great for prod β€” but learn the manual loop first
For the exam, hand-write the loop until the stop_reason β†’ tool_use β†’ tool_result cycle is automatic. For production, reach for the Tool Runner.

SWAP-INS The same code, bigger

🧱 Scaling this to a real warehouse? See NLβ†’SQL on Databricks Unity Catalog β€” grounding against thousands of tables, what schema context to send, EXPLAIN validation, evals, cost/reliability/observability, and how CLAUDE.md & Skills fit, all mapped to the five CCA domains.