Using Dosi in your agents

An AI agent that answers data questions has two options. It can read your warehouse schema and write SQL itself — the usual "NL2SQL" setup — or it can call a semantic layer that already knows what a metric means. Dosi ships the second option as a native MCP server, so any MCP client can discover your metrics, compile them to warehouse SQL, and run them.

This page is the agent-agnostic guide: what the agent gets, why it answers better and cheaper, and how to wire it up safely. For a step-by-step walkthrough with real output, pick your client: Claude Code, Codex, or OpenCode.

What the agent gets

Ten tools, in three groups, all speaking the same MetricQuery shape the CLI and REST API use:

Stage	Tools	What the agent learns
Discover	list_metrics, list_dimensions, list_datasets, describe_metric, list_connections, get_capabilities	which metrics exist, what they mean, which fields they can be broken down by
Preview	compile_sql, explain_query	the exact SQL or logical plan, before anything runs
Execute	run_query	rows as JSON, with the applied row cap reported
Author	validate_model	whether a model edit is valid, in the server's engine mode

The vocabulary is small and closed: metrics are named, and group_by fields are dataset.field (plus the reserved metric_time). An agent that stays inside it cannot invent a column, and cannot quietly pick the wrong table.

Why this beats raw-schema NL2SQL

The argument is not that models write bad SQL. It is that the correct SQL for a business question is not derivable from column names. Someone has to encode it once — that is what a semantic model is. Four things follow, all of them visible in the bundled orders example.

It settles definitions instead of guessing them

revenue in the orders model is SUM(orders.amount) over all orders — cancelled ones included. Ask an agent with only table access for "total revenue" and it will usually add WHERE status = 'completed', because that is the reasonable guess. Reasonable, and a different number: 350 instead of 450. Neither is wrong as SQL; only one matches what your company calls revenue. Through the metric, every agent, dashboard, and notebook gets the same answer, and the definition is reviewable in YAML instead of re-invented per question.

It does not double-count across tables

total_margin is defined as SUM(orders.amount) - SUM(products.unit_cost) — an expression over two aggregates on two datasets. Dosi compiles it by aggregating each side before combining them:

WITH m0 AS (SELECT SUM(orders.amount)      AS orders_amount_sum       FROM main.orders   AS orders),
     m1 AS (SELECT SUM(products.unit_cost) AS products_unit_cost_sum  FROM main.products AS products)
SELECT m0.orders_amount_sum - m1.products_unit_cost_sum AS total_margin
FROM m0 CROSS JOIN m1

which answers 360. The natural hand-written version — join orders to products, then SUM(o.amount - p.unit_cost) — answers 270, because the join repeats each product row once per order and inflates the cost side. This is the never-silently-double-counts guarantee doing its job: fan-out is a property of the join graph, not something an agent can see in a schema dump.

It gets grain and de-duplication right

unique_customers is COUNT(DISTINCT orders.customer_id). Broken down by month on the sample data that is 1 / 2 / 1; a plain COUNT(customer_id) gives 2 / 3 / 1. The distinction lives in the metric definition, so the agent does not have to rediscover it — or fail to.

It ports across warehouses for free

The same request compiles to whatever the target dialect actually supports: DuckDB gets DATE_TRUNC('MONTH', …), MySQL — which has no DATE_TRUNC — gets STR_TO_DATE(DATE_FORMAT(…, '%Y-%m-01'), '%Y-%m-%d'). An agent writing SQL by hand has to know each warehouse's date idioms; an agent calling compile_sql just changes dialect.

What it costs

Accuracy is the reason to do this. Cost is more nuanced than the usual pitch, so here is what we actually measure rather than what is convenient to claim.

Two things genuinely get cheaper:

• Compilation is Rust, not tokens. Planning the join graph and rendering dialect SQL takes about a millisecond, outside the model — measured, and deterministic.
• The agent stops probing the data. A raw-SQL agent spends turns on SELECT DISTINCT status, LIMIT 5 peeks and "is this column a code?" checks. With metrics there is nothing to probe, and a rejected name comes back with candidates instead of starting a debugging loop.

One thing does not, and it is worth knowing before you plan a budget: on a small schema the MCP path uses more model context, not less. The tool definitions — ten tools, including the full metric-query shape — are a fixed cost paid every call, and on our three-table example fixture that outweighs the entire schema dump the baseline gets for free. In our own runs the semantic-layer arm took more turns and more context than the raw-SQL arm on that fixture.

The trade flips as the warehouse grows: the tool surface stays the same size while a schema dump grows with every column, and probe turns grow with every ambiguous code column. Treat "cheaper" as a property of your schema size, and measure it — the harness we use takes your own questions.

Measured accuracy and cost for both arms, with the baseline prompt committed so you can audit whether the comparison is fair, are in Benchmarks.

What it does not fix

Being straight about the limits, because they decide whether this fits you:

• Word-to-metric mapping is still the agent's job. list_metrics exposes each metric's name and description. It does not yet project the OSI model's ai_context synonyms, so a question phrased in vocabulary that appears nowhere in a name or description can still miss.
• Questions no metric covers cannot be answered through these tools. That is deliberate — a refusal beats a confident wrong number — but it means the model's coverage is your coverage. Keep raw SQL available for exploration.
• A wrong definition is now wrong everywhere. Centralizing the definition centralizes the blast radius; treat model.yaml as reviewed code, and run dosi validate in CI.

Wire it up

Choose a transport

	Streamable HTTP (POST /mcp)	stdio (--mcp-stdio)
Who starts it	you, as a long-lived process	the client, as a child process
Good for	shared servers, several clients, CI, containers	one local client, zero setup
Auth	--auth-token, checked per request	process boundary
Logs	stdout/stderr as usual	forced to stderr; stdout is protocol only

Both serve the identical tool set, and both require --model. Startup is fail-fast: an invalid model exits before the socket binds or stdio speaks.

Point it at data

$ duckdb orders.duckdb < $DOSI_EXAMPLES/orders/seed.sql
$ dosi-server --model $DOSI_EXAMPLES/orders/model.yaml --db orders.duckdb

--db is not optional in practice

Without it the server's local DuckDB is in-memory and unseeded: compile_sql works, run_query fails with a missing-table error. This is the most common first-run surprise.

For a real warehouse, pass --connections (a datasources: YAML or a Datus agent.yml) and let the agent pick a profile from list_connections; see Connect a warehouse.

Register the server

Most clients accept either a command to spawn or a URL to call. Committing a project-scoped config file is usually best — teammates clone the repo and get the same tools:

.mcp.json

{
  "mcpServers": {
    "dosi": {
      "command": "dosi-server",
      "args": ["--model", "examples/orders/model.yaml",
               "--db", "orders.duckdb", "--mcp-stdio"]
    }
  }
}

The HTTP form of the same thing:

{
  "mcpServers": {
    "dosi": {
      "type": "http",
      "url": "http://127.0.0.1:8081/mcp",
      "headers": { "Authorization": "Bearer ${DOSI_SERVER_TOKEN}" }
    }
  }
}

Relative paths resolve from the project root, so copy the example model into your repo (or point --model at an absolute path), and seed orders.duckdb first. If dosi-server is not on the client's PATH, give command an absolute path — and never point it at cargo run, which an MCP client cannot distinguish from a hang.

Decide what the agent may do

Two independent dials, and they compose:

• Server side — --disable-execute. run_query becomes a tool-level forbidden error; the agent can still discover and compile. This is the right shape for a shared, read-only deployment: nothing the agent does can touch the warehouse.
• Client side — tool allowlists. Grant compile_sql and explain_query but not run_query for a review workflow; grant all of them for analysis. Most clients name MCP tools mcp__<server>__<tool>, so a Dosi-only agent is an allowlist of mcp__dosi__* plus a denial of shell access.

Add --auth-token (env DOSI_SERVER_TOKEN) for any HTTP deployment. It is checked on every request — there is no session to authenticate once — which is exactly what a stateless, load-balanced deployment wants.

Teach the agent to prefer metrics

Tools alone do not stop an agent from writing SQL by hand when it also has shell access. These rules do. Drop them into your CLAUDE.md, AGENTS.md, or your client's system-prompt equivalent:

## Data questions

Answer data questions through the `dosi` MCP tools, never by writing SQL
against raw tables.

1. Discover: `list_metrics`, then `list_dimensions` for the breakdown fields
   (`dataset.field`, plus the reserved `metric_time`). Use `describe_metric`
   when a metric's meaning matters.
2. Preview: `compile_sql` (or `explain_query`) before executing, and show me
   the SQL you are about to run.
3. Execute: `run_query`. Omit `connection` for the server's local database, or
   pick one from `list_connections`.

Rules:
- Never invent a metric or dimension name. If a name is rejected, read the
  error's `candidates` / `suggested_retry` and retry with a valid one.
- Never re-derive a metric by hand — no ad-hoc `SUM`/`COUNT DISTINCT`, no
  hand-written joins or filters. The metric definition is the governed answer,
  and a hand-rolled equivalent will drift.
- If no metric covers the question, say so instead of approximating.
- Report the number the tool returned, plus the metric name and any filter
  applied, so the answer is auditable.
- `run_query` caps rows (`row_limit_applied`). Never present a capped page as
  a complete result — aggregate in the query instead.

This text is the same file the benchmark harness uses as its system prompt (tests/nl2sql/prompts/arm_b_mcp.md), so the documented behavior and the measured behavior cannot drift apart.

Clients

Client	Status	Notes
Claude Code	Supported, walkthrough available	claude mcp add, .mcp.json, headless claude -p
Codex	Supported, walkthrough available	codex mcp add, user-level ~/.codex/config.toml, headless codex exec. Needs default_tools_approval_mode = "approve" and an AGENTS.md rule before tool calls work at all
OpenCode	Supported, walkthrough available	committed opencode.json, {env:VAR} token substitution, headless opencode run
MCP Inspector	Debugging	npx @modelcontextprotocol/inspector <binary> … --mcp-stdio
datus-agent	Supported	also has a native Python adapter over the same engine
Other MCP clients	Expected to work	anything speaking MCP tools

Dosi exposes tools only — no resources, no prompts — so a client that supports MCP tool calling needs nothing else.

Troubleshooting

Symptom	Cause	Fix
run_query → table does not exist	in-memory, unseeded DuckDB	seed a file and pass --db
client shows the server as failed	wrong path (/v1/mcp), server not running, or cargo run still compiling	MCP is POST /mcp at the top level; use a prebuilt binary
400 with an empty body	missing Host header (DNS-rebinding protection)	do not strip Host in proxies or hand-built requests
401 on every call	--auth-token set, client sends no bearer header	add the header; auth is per request, not per session
config error: "Conflicting lock is held"	DuckDB allows one writer per file and another process already has it	give each server its own database file, or stop the other process
forbidden from run_query	--disable-execute	use compile_sql, or run a server without the flag
busy / timeout	execute semaphore saturated / execute timeout hit	raise --max-concurrent-executions / --execute-timeout-secs, or make the query cheaper
validate_model fails on a large model	/mcp bodies are capped at 1 MiB	validate large models with dosi validate
results look truncated	default LIMIT 500, row_limit clamps at 5000	aggregate in the query; read row_limit_applied
agent invents a metric name	no prompt rules in place	add the snippet above

Where to next

• Claude Code

  ---

  The full walkthrough: register, ask, read the trace, verify the numbers by hand.

• MCP reference

  ---

  Every tool, its input shape, the protocol, and the error contract.

• Connect a warehouse

  ---

  Move from the sample DuckDB file to Postgres, Snowflake, StarRocks, and more.