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

The adtribute team designed an MCP server that exposes a company's marketing attribution data to LLM clients like Claude through a governed semantic layer rather than raw SQL. Query tools accept metric and dimension keys that the server validates against each organization's catalog, then compiles into parameterized queries, with calculated fields such as ROAS expanded after aggregation as ratios of sums. The server also runs an ambiguity check on ad-hoc formulas and requires a plan-confirmation step before returning insights, so the model cannot silently choose an attribution model or invent a calculation.

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 https://www.adtribute.io/mcp , 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. 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. 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 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 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. 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. was truncated , total row count and a note say so. 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. Disclosure: I work at adtribute, the company that builds the MCP server described in this post.