MCP in the data warehouse: agents need a semantic layer, not SQL

The Model Context Protocol describes how a model discovers and calls tools and data sources. For a data warehouse it is temptingly easy to implement: one tool that accepts SQL, one connection, done. In practice this creates three problems that share a single root — the model is given too much freedom in the wrong place.

Problem one: a schema is not semantics

A warehouse schema describes how data is laid out, not what it means. In a typical environment several tables plausibly look like revenue: one from source-system replication, one with cancellations removed, one with group currency conversion applied. All three have a column called amount.

A model that only knows the schema picks whatever fits best — by name similarity. It has no way of knowing that the controlling figure comes from the third table, that cancellations must be deducted, and that foreign currency is converted at the month-end rate. The answer is syntactically valid SQL that returns a number. Just not the number in the report.

The mistake is not that the model guesses badly. The mistake is letting it guess.

Problem two: permissions belong to the request, not the connection

An MCP server runs as a process and typically holds one connection to the warehouse. If that connection is opened under a service account, every agent sees everything that account may see — regardless of whom it is asking on behalf of. Row filters and column masking, carefully set up for humans, are bypassed.

The correct approach is the reverse: the requester's identity is carried through into the database session, and the permission check happens where it happens for humans too. Technically that means token passthrough over OIDC and one session per request instead of a shared connection.

# Wrong: one connection, one service account
connection:
  user: svc_mcp
  role: analytics_all

# Right: identity per request, role follows the requester
auth:
  mode: oidc_token_passthrough
  session: per_request
  fallback_role: none        # no silent fallback to broader rights

The fallback_role: none is the important part. A fallback to a default role looks operationally convenient and, in the failure case, removes exactly the boundary you meant to draw.

Problem three: free-form SQL is not auditable

An audit log full of arbitrary SQL text is an archive, not evidence. Asked by an auditor whether an agent accessed personnel data, you have to parse thousands of queries. With a semantic layer the log entry reads: metric, dimension, filter, role, timestamp — and the question is answered with one query.

What the interface should look like instead

An MCP server for a warehouse should not offer one tool but several, graded from coarse to fine:

1. 1list_metrics — which reviewed metrics exist, with descriptions and permitted dimensions. This is the catalog the model chooses from.
2. 2describe_metric — how a metric is defined, which filters belong to it, who approved it. This lets the model justify its choice.
3. 3query_metric — metric plus dimensions plus filters. The warehouse generates the SQL, not the model.
4. 4run_sql — as a last tier, with its own role, a tight timeout and explicit approval. For cases the semantic layer does not cover.

The difference is not cosmetic. In the first design the model answers the question “which table does the user mean”. In the second the semantic layer answers that, and the model only answers “which metric is being asked for”. That is the job it is good at.

What this means for text-to-SQL

Text-to-SQL is usually discussed as a capability of the model. The limiting factor, however, is rarely the model but whether a unique correct answer can exist at all. Where three revenue tables exist and none is marked authoritative, the question is underdetermined — no model can solve it reliably.

Anyone wanting to improve text-to-SQL therefore usually works on metric definitions rather than prompts. That is less glamorous work and it has more effect.

In brief

An agent that says “that metric is not available to me” is more annoying than one that always answers. It is also the only one whose answers you can put in a report.