THE PROJECT One line, and why it's the teaching agent
Ask a question in English β the agent writes SQL, runs it, and if it errors, reads the database error and fixes the query, looping until it works β then answers. It's the smallest thing that is genuinely agentic: you cannot know the SQL is correct without running it, so the decide β act β observe β adjust loop is unavoidable (not a workflow you could hardcode). Read-only, ~100 lines, one dataset β so you learn the loop and the tool contract with nothing else in the way.
1 Β· CONCEPTUAL DESIGN The logic, no infrastructure
Conceptual design answers "what are the moving parts and how do they interact?" β independent of where it runs. There are only four logical roles plus a loop:
| Logical role | Responsibility |
|---|---|
| Agent / Orchestrator | Owns the loop: prompt the model, execute its tool calls, feed results back, decide when to stop |
| LLM (reasoner) | Turns question + schema (+ prior errors) into a SQL query; later, turns rows into a plain answer |
| Tools | get_schema() and run_sql(query) β the agent's only ways to touch the world |
| Data (SQL engine) | Executes the query, returns rows or an error message (the crucial feedback signal) |
The loop, as a sequence
β What makes this an agent
- The next action depends on the observed result (the error), not a fixed script
- The model, not your code, decides the fix
- There's a real feedback loop and a terminal condition
β οΈ Design decisions to make now
- Loop termination: cap at N tries (D1) β never loop forever
- Tool contract: how errors are returned to the model (D4)
- Guardrails: read-only, row limit, timeout (built in, not prompted)
2 Β· PHYSICAL DESIGN Same agent, five homes
Physical design maps each logical role onto concrete technology. The beautiful part: the agent logic never changes β only the boxes it plugs into. Here's the reference shape, then the same four roles realized on each platform:
(container / function)"] APP -->|"inference"| LLM["LLM endpoint"] APP -->|"get_schema / run_sql"| SQL["SQL engine + data"] APP -.logs.-> LOG["Logging"] APP -.traces/metrics.-> OBS["Observability"] APP -.reads.-> SEC["Secrets"] classDef core fill:#f3f0ff,stroke:#764ba2,color:#4a3b7a; class APP core;
The "flavour of each cloud" map
| Role | π» Local (Docker) | π§± Databricks | π§ AWS | π· Azure | π΄ GCP |
|---|---|---|---|---|---|
| Where the agent runs | Python in a container (Docker Compose) | Job / notebook / Databricks App | Lambda or ECS/Fargate | Functions or Container Apps | Cloud Run |
| LLM (inference) | Ollama (local open model) | Mosaic AI Model Serving / Foundation Model APIs | Amazon Bedrock | Azure AI Foundry / Azure OpenAI | Vertex AI (Gemini + Model Garden) |
| SQL engine + data | DuckDB (embedded) | Databricks SQL / Spark on Unity Catalog | Athena (S3) / Redshift | Synapse / Fabric / Azure SQL | BigQuery |
| Container registry/runtime | Docker Desktop | Managed (or Container Services) | ECR + ECS/EKS | ACR + Container Apps/AKS | Artifact Registry + Cloud Run/GKE |
| Secrets | .env + dotenv | Databricks Secret Scopes | Secrets Manager / SSM | Key Vault | Secret Manager |
| Logging | stdout / JSON file | Driver logs + system tables | CloudWatch Logs | Azure Monitor / Log Analytics | Cloud Logging |
| Observability / tracing | Phoenix / Langfuse (self-host) | MLflow Tracing + AI Gateway | CloudWatch + X-Ray; Bedrock invocation logs | Application Insights; Foundry tracing | Cloud Monitoring + Cloud Trace |
| Identity / access | local user | Unity Catalog + workspace IAM | IAM roles | Entra ID + managed identity | IAM + service accounts |
| Orchestration (if scheduled) | cron / plain script | Databricks Workflows | Step Functions / EventBridge | Logic Apps / Durable Functions | Workflows / Cloud Scheduler |
3 Β· CROSS-CUTTING CONCERNS The stuff that decides if it's real
| Concern | What it means for this agent | Levers / practices |
|---|---|---|
| π° Cost | The schema is re-sent on every retry; each attempt = input tokens + output SQL. Retries multiply it. | Cache the schema prompt Β· cap max-tries (2β4) Β· use a small SQL-tuned model Β· trim schema to relevant tables |
| π Observability | You need to see the agent think: each iteration's SQL, the error, the fix, tokens, latency, #retries, success/fail. | Trace every loop step (Phoenix/Langfuse/MLflow) Β· a run-id per request Β· dashboards on success-rate & avg-retries |
| π Security | User text is untrusted (prompt injection) and SQL can be destructive. "Please DROP TABLE" must be impossible, not just discouraged. | Enforce at the DB layer: read-only role, no DDL/DML, allow-listed schemas Β· statement timeout Β· LIMIT Β· redact PII in logs Β· never trust the prompt for safety |
| π Logging | Reproduce and debug any run; prove what happened; don't leak data. | Structured JSON logs (prompt, SQL, result-shape, tokens) Β· correlation id Β· scrub secrets/PII Β· log levels |
| π‘οΈ Reliability | Model errors, DB timeouts, no-valid-SQL-found. | Loop cap + graceful "I couldn't answer" Β· retry model calls with backoff Β· timeouts on every tool |
| β Evals | "Did it work?" is measurable here β the query runs and returns the right answer, or not. | Execution accuracy (does it run?) + result match vs gold queries + avg #self-corrections Β· run on every prompt/model change |
4 Β· OPEN-SOURCE TOOLBOX A free option for every responsibility
| Responsibility | Open-source options |
|---|---|
| Local LLM serving | Ollama, llama.cpp, vLLM, LM Studio |
| SQL engine | DuckDB, PostgreSQL, Trino/Presto, Apache Spark |
| Agent orchestration | Plain Python (recommended for learning), LangGraph, LangChain, LlamaIndex |
| Tracing / observability | Arize Phoenix, Langfuse, OpenLLMetry, OpenTelemetry |
| Metrics & dashboards | Prometheus + Grafana; Loki (logs), Tempo (traces) |
| Evals | promptfoo, Phoenix Evals, DeepEval, Ragas (for RAG) |
| Guardrails | Guardrails AI, NeMo Guardrails, LLM Guard |
| Secrets | dotenv (local), SOPS, HashiCorp Vault |
| Containerization | Docker + Docker Compose, Podman |
5 Β· BUILD IT FREE & LOCAL $0, offline, on your machine
What we'll actually run β every piece free and local:
qwen2.5-coder, llama3.1, or sqlcoder). Free, offline. ποΈ Data β DuckDB (embedded, zero-setup, reads CSV/Parquet).
π Agent β plain Python (no framework β you see every step).
π Tracing β Arize Phoenix (
pip install, runs locally). π¦ Containers β optional Docker Compose to practice the deployment shape.
Since this repo is Claude-focused: in a real build you'd point the same code at Claude via the
Anthropic API (or Bedrock/Vertex/Foundry). The client is swappable β only the endpoint changes. Free-tier hosted alternatives to
Ollama: Google AI Studio, Groq, or OpenRouter :free models.
stop_reason loop, the tool_use/tool_result contract,
prompt caching, and adaptive thinking β each line mapped to a CCA domain. That's the version the exam tests.The whole agent (minimal, real)
# pip install duckdb requests Β· ollama pull qwen2.5-coder:7b import duckdb, requests, json # --- Data: open READ-ONLY so DROP/DELETE are impossible at the engine layer --- con = duckdb.connect("demo.duckdb", read_only=True) 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(q): # the tool the model calls return con.sql(f"SELECT * FROM ({q}) LIMIT 100").df() # guardrail: cap rows def ask(prompt): # call the local model (Ollama) r = requests.post("http://localhost:11434/api/generate", json={"model":"qwen2.5-coder:7b", "prompt":prompt, "stream":False}) return r.json()["response"].strip() def agent(question, max_tries=4): # THE LOOP β this is the whole agent schema, err = get_schema(), "" for attempt in range(max_tries): sql = ask(f"DuckDB schema:\n{schema}\nQuestion: {question}\n" f"{err}Return ONLY a SELECT query, no prose.") try: rows = run_sql(sql) # act return ask(f"Q: {question}\nRows:\n{rows}\nAnswer in one sentence.") # success β stop except Exception as e: err = f"Your last query failed with: {e}\nFix it. " # observe β feed back β loop return "I couldn't answer that reliably." # graceful termination print(agent("How many orders were placed in 2024?"))
for loop with the try/except feedback is the agent. Everything else in
this page is what turns these 25 lines into something you'd trust in production.Optional: the container shape (Docker Compose)
# docker-compose.yml β practice the deployment shape locally services: ollama: # the model endpoint image: ollama/ollama ports: ["11434:11434"] phoenix: # observability UI image: arizephoenix/phoenix ports: ["6006:6006"] agent: # our Python app build: . depends_on: [ollama, phoenix]
6 Β· PRODUCTION FAILURE POINTS
LIMIT, and (on real warehouses) a cost/bytes-scanned cap.7 Β· CONCEPTS β CCA MAP
| You practised⦠| CCA domain |
|---|---|
| The agentic loop, loop termination, single-agent-+-tools | D1 Β· Agentic Architecture |
| Tool definition, the tool-result contract, returning errors as feedback | D4 Β· Tool Design |
| Read-only grants, guardrails, permission at the tool layer | D1 (guardrails) Β· D4 |
| Prompting the model to emit only SQL, schema in context | D3 Β· Prompt Engineering |
| Schema caching, trimming context, cost of retries, evals, observability | D5 Β· Context & Reliability |
8 Β· LEVEL IT UP
- Add a verification step β self-check the SQL against the question before trusting it (the seed of the DQ / migration agents' parity harness).
- RAG over the schema β retrieve only relevant tables for big warehouses.
- Swap the engine β point
run_sqlat Databricks SQL / BigQuery / Athena β same loop, new home. - Swap the model β point
askat Claude via the Anthropic API or a cloud gateway. - Add tracing β wire Phoenix/Langfuse and watch every loop iteration.