Don't let the model write SQL: designing an MCP server around a semantic layer

Daniel Busch — Mon, 09 Nov 2026 11:41:18 +0000

Ask an LLM with a warehouse connection for last week's ROAS and you get an answer in seconds. It is confident, nicely formatted, and often wrong.

The model doesn't know which attribution model your company reports with, whether "revenue" means gross or net, what counts as a new customer, or how contribution margin is defined. When it can't answer precisely, it still picks a plausible calculation, writes the SQL, and the query runs. Nothing flags the mismatch.

A better prompt doesn't fix this. Taking the decisions away from the model does. Here is how we designed the adtribute MCP server, which lets clients like Claude query a company's marketing attribution data, built from shop systems like Shopify, ad platforms like Meta, Google and TikTok, CRM and email tools like HubSpot and Klaviyo, and first-party tracking.

The model picks keys, the server writes SQL

Each organization has a semantic layer, exposed through drill-down tools such as get_dimensions and get_attribution_models: metrics, dimensions, attribution models and calculated fields, each with a key, a display name and a description. A small get_context index lists the drill-downs and their entry counts but no keys, so a large catalog doesn't flood the context window.

The query tools take keys, never SQL:

{
  "intent": "ROAS by channel last week",
  "metrics": ["roas"],
  "dimensions": ["channel"],
  "date_range": { "from": "2026-10-26", "to": "2026-11-01" }
}

The server validates every key against the organization's catalog; an unknown key fails with an error listing the valid ones, so the model can correct itself. Then the server compiles the query: identifiers must match a strict pattern and are quoted, filter values and dates are bound parameters, and the dataset comes from the authenticated organization, never from tool input.

Validation also refuses questions the data can't answer honestly. An early version applied metric filters such as spend > 0 to raw rows before summing. Spend and conversions live on different rows, so that filter silently zeroed revenue. Now metric filters are rejected with a hint to sort and limit instead.

Formulas live in the data model, not the prompt

Metrics like ROAS, average order value and contribution margins are calculated fields the organization defines once, the same definitions its reporting uses. The model passes the key, and the server expands the stored formula after aggregation: a ratio of sums, never a sum of ratios. Divisions are guarded with NULLIF, and inputs without rows count as zero. The tool description tells the model not to recompute formulas itself.

When a ratio isn't defined as a calculated field, the model may send an ad_hoc_formula: tokens over known metric keys, operators and constants. That still isn't SQL, and the answer labels the result as ad hoc.

Ad-hoc formulas get a mechanical ambiguity check. If a chosen metric has siblings that share words in their names or descriptions, like gross and net revenue, and the user's intent doesn't contain its name, the server returns the siblings for the user to pick from instead of a plan. Dimensions get no such check; there, the instructions tell the model to surface every plausible candidate.

Plan first, then query

plan_insights takes the query's inputs plus a required intent, the question in the user's own words. It returns no metric values, only a discovery_required payload or a plan: a confirmation block in display names, a provenance sentence, warnings about thinly described entries, and the exact row count. The model is told to paste the confirmation block verbatim and wait for the user to confirm before calling get_insights.

Each HTTP request gets a fresh MCP server instance, so get_insights doesn't trust the plan. It re-runs validation and model selection; the plan's session id only correlates logs, and a call without one is logged.

The server settles ambiguity, in a fixed order

The LLM must never pick an attribution model silently, so the server settles it in the order a person would:

// Simplified
async function settleModel(q: Question): Promise<ModelChoice> {
  if (!q.needsModel || q.modelNamed) return { use: q.modelNamed };
  const agree = await sameUnderEveryModel(q); // conversion-side questions only
  if (agree) return { use: agree.reference, note: "model doesn't change these numbers" };
  const fallback = organizationDefault(); // exactly one marked default, or none
  return fallback ? { use: fallback, note: "organization default" } : { ask: viableModels() };
}

A model counts as named if the user named it in the conversation or a loaded skill (an organization-specific runbook) names it. If the question only groups and filters by the conversion's own fields, such as date, country or product, the server runs it under every model and compares the numbers within a small tolerance. If they agree, the answer says the model doesn't matter. Next comes the default: the one model whose description starts with "Default Model". Two marked models count as none.

Only then does the server ask:

{
  "discovery_required": true,
  "discovery_kind": "attribution_model",
  "viable_models": [
    { "name": "First Touch", "description": "..." },
    { "name": "Linear 30 days", "description": "..." }
  ]
}

The instructions tell the model to list the options neutrally, without an "I'd use X", because the choice changes what the numbers mean. Applying the default in code instead of the prompt was deliberate: a reworded instruction behaves differently from run to run and can't be tested or logged.

Numbers count by conversion date unless the caller asks for click date, and a click-date request for a model without click data is refused, not quietly answered by conversion date. When a user compares against a report and the date basis is unclear, the model is told to ask first.

Make the response hard to misread

Responses are written for a reader that likes to improvise:

result_interpretation is a server-written sentence naming the model, date basis, metrics, date range, timezone and currency. The model is told to lead with it verbatim.
display_labels map every key to its display name. The model is told to keep keys and SQL out of the chat; SQL comes back only on request, as a debug field.
Totals, and a "rest" for rows that didn't fit, come from the same query via window sums over per-group parts, so ratios are recomputed, not added. When rows are cut, was_truncated, total_row_count and a note say so.

Read-only, per person, logged

No tool writes anything. Lint rules ban database writes and raw SQL in the package, warehouse queries are SELECTs the server builds, and each runs under a cost cap and a timeout. Every number is an aggregate, such as a sum or a formula over sums, grouped by report dimensions.

People connect through OAuth on a URL that names their organization, and the bearer token is verified on every request. The user must be a member of that organization with an allowed role (admins, editors and report viewers; limited report viewers are out), and the organization must have switched the MCP on in its settings, which is off by default. Non-members get one uninformative denial, so organization ids can't be probed; members get a reason they can act on.

Every tool call writes a structured log line tied to the organization and the user. Planning and query calls also log the question in the user's words and how the attribution model was settled; query calls add the warehouse job id.

Takeaways for your own MCP server

Expose governed keys and compile the query yourself. Return errors that list the valid options.
Keep definitions in data, not prompts. Formulas and defaults the server applies can be tested and logged.
Split plan from execute and detect ambiguity in code. Return an "ask the user" payload, and re-check on execute: calls are stateless.
Write responses for an LLM reader. Display names, a provenance sentence to quote, server-side totals, explicit truncation.
Authenticate the person, not the workspace. Gate by organization opt-in and role, and log intent next to the query id.

Disclosure: I work at adtribute, the company that builds the MCP server described in this post.

DEV Community: Daniel Busch