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.
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 SQL | Returns SQL as text; you regex/parse it | Returns a typed tool_use block with validated input |
| How you feed the result back | Stuff it into the next prompt string | A tool_result block (user turn) with matching tool_use_id |
| How you know it's done | Heuristic ("looks like an answer") | stop_reason == "end_turn" β explicit |
| Errors | Append error text | tool_result with is_error: true β a first-class signal |
| Cost / auth | $0, offline | Paid API key (ANTHROPIC_API_KEY or ant auth login) |
DIAGRAM The Messages API tool loop
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_reason | Means | This agent does |
|---|---|---|
tool_use | Claude wants to call run_sql | Append the whole turn, run the tool, return tool_result(s), continue |
end_turn | Finished naturally | Return the final answer text |
max_tokens | Output was cut off β incomplete | Stop and report β raise max_tokens or stream, then retry (do not treat as done) |
refusal | Safety classifier declined (HTTP 200, empty/partial) | Return a graceful "declined" message |
stop_sequence | Hit a custom stop string | N/A here (we set none) β treat as done if it ever fires |
pause_turn | A long server-side tool loop paused | N/A here (client tool) β but resend to resume if you add server tools |
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:
end_turn and missing refusal or max_tokens.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).{"type":"adaptive"}, not budget_tokens β D3/D1. Trap: budget_tokens returns a 400 on Opus 4.8 / 4.7 / Sonnet 5.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.messages list every turn β D1 / D5. Trap: assuming the API "remembers" prior calls.max_turns) β D1 Β· Autonomy & Guardrails. Trap: no cap β runaway/cost.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
stop_reason β tool_use β tool_result cycle is automatic. For production, reach for the Tool Runner.SWAP-INS The same code, bigger
- Warehouse instead of DuckDB β point
run_sqlat Databricks SQL / BigQuery / Athena. The loop is identical. - Structured final answer β add
output_config.format(a JSON schema) so the answer is machine-parseable (D3). - Cheaper β
model="claude-haiku-4-5"for the SQL step; keep Opus for hard reconciliation (D5 model tiering). - Long outputs β stream with
client.messages.stream(...)andget_final_message()(D5). - A verification step β after success, ask Claude to check the SQL matches the question β the seed of every bigger agent's parity harness.