Letting a model query the warehouse without handing it the keys
We build governed query layers that sit between a language model and your data, so the business can ask questions in plain English. The interesting engineering is almost entirely in what the layer refuses to do.
Overview
We built this for a US-based D2C company so their senior leadership could ask business questions directly in Claude and get answers from their data lake. A language model can write the SQL. What stops these systems reaching production is everything around the SQL.
The model needs credentials it must never hold. It will run a SELECT * that scans a terabyte. It cannot tell a table it may read from one it may not. And it will answer confidently and wrongly when two columns both look like they mean 'revenue' - the failure that actually costs you, because a query that errors is free and a plausible wrong number ends up in a board deck.
So we treat this as a governance problem wearing an AI hat. A server sits between the model and the data, exposes a small fixed set of tools, holds every credential itself, and refuses anything outside a written definition of what the business means.
The question that used to be a dashboard
Before this, the Chief Marketing Officer opened a dashboard, applied filters, exported to a spreadsheet, and worked out the answer from there. Now the question goes to Claude.
The exchange below is the shape that takes, across a portfolio of twelve house brands, each with its own storefront. The questions are the ones the leadership team asks; the answers are what the system returns.
Across August to date, storefront sessions convert at 3.5%. Three brands draw heavy traffic while converting below that line:
Volt Peripherals is the clearest case. It takes 26% of all storefront traffic and converts at 2.0%, against 6.3% for Kestrel Outfitters on a fifth of the sessions. On net revenue per thousand sessions that is $5,610 against $21,610.
Net revenue is order totals excluding cancelled and refunded orders, less refunds requested this month. Figures cover 1 to 18 August, so they are month-to-date and not comparable to a full prior month.
Returns do not look like the cause. Volt had 28 returns this month and 21% were defect-related, in line with the 21% group average. The leading reasons are size too small (18%), changed mind (18%) and size too large (14%), which point at sizing guidance and listing accuracy rather than faulty goods.
The conversion gap is upstream of the order. I can break Volt's sessions down by channel and device, or compare its checkout drop-off against the other brand storefronts, whichever is more useful.
Two things in that exchange are worth drawing out, because both are the semantic layer doing its job rather than the model being clever.
The answer states its definition of net revenue without being asked, and flags that August is month-to-date. Neither is model initiative. Both are written into the tool descriptions, so every answer carries them and a reader cannot mistake a partial month for a full one.
The question set covers brand-wise commercial summaries, on-time delivery rates, products selling above 50 units with review averages below 3.5, and which brands hold the most customers who have not ordered in 30 days. Each was previously a dashboard, an export, or a request to an analyst.
The answer that has to be blank
- One storefront, Juniper Stationery, took 32 orders against zero recorded sessions. Its conversion rate is not 0%. It is undefined, and the system returns null.
- A tool reporting '0%' would put a working storefront at the bottom of that chart and send someone to fix a problem that does not exist. The rule that counts return 0 and rates return null is one line in the semantic layer, and this is the question where it earns its place.
The question set covers brand-wise commercial summaries, on-time delivery rates, products selling above 50 units with review averages below 3.5, and which brands hold the most customers who have not ordered in 30 days. Each was previously a dashboard, an export, or a request to an analyst.
Approach: One protocol, three layers that never touch
The system is built on the Model Context Protocol, so any MCP-capable client - Claude Desktop, a custom app, another agent - connects to a server exposing a fixed set of tools. That buys the property the whole design rests on: the model reaches the data only through tools we wrote. No shell, no connection string, no escape hatch.
The model holds a short-lived access token scoped to the server; the server holds the credentials for every source behind it.
We reject code that reaches across the boundaries even when it would work - a connector that reads an auth token, a tool handler that builds SQL of its own. Both are tempting, and both cost you reviewability: if only the core layer can authorise a query, then auditing authorisation means reading one layer instead of every connector.
The connector layer is not tied to one engine
The implementation runs on Athena over a catalog, because that is where the data sat. Nothing above the connector layer knows that. Each source implements the same four operations, while everything that makes the system safe - token verification, scopes, row and time limits, statement validation, the audit line - lives in the core layer above them and applies to all of them equally.
| Operation | What a connector must provide |
|---|---|
| describe_schema | Tables, columns, types - however the source expresses them. |
| sample_rows | A cheap preview, for grounding the model before it writes anything. |
| validate | Reject writes in the source's own dialect, before execution. |
| run_query | Bounded execution - row cap and timeout, no exceptions. |
A warehouse connector swaps the driver and the dialect rules; a REST connector maps endpoints to tables; a file connector reads Parquet or CSV directly. The work stays inside one adapter - tools, auth path and guardrails untouched. Which is also what makes several sources safe to expose at once, since a caller's scopes decide which they can reach. Adding a source widens what the system can answer without widening what any given caller can see.
What we learned
We start with definitions, not code - a single document specifying exactly what each metric means, which table answers it, and what the system should do when a question can't be answered. That document is served to the model as context, implemented by hand-written reference SQL, and used to score the evaluation. Three rules generalise to any warehouse:
DENOMINATORS
Pick one and write it down
Conversion rate can be sessions, unique visitors, or carts created. All three are defensible; mixing them across two questions gives numbers that can never be reconciled.
EMPTY VS. ZERO
Return null, never 0
A channel that ran no campaigns last month, reported at "0% conversion", reads as catastrophic performance. It isn't performance at all. Counts return 0; rates return null.
GRAIN
Name the table explicitly
Where a plausible-looking table can't actually answer the question, the tool description has to say so - or the model will join it anyway.
THE GRAIN TRAP
Ask how many orders were returned, and the model finds a returns table. It looks right and it answers the wrong question - returns is one row per returned item, so a three-item order sent back whole counts as three. The order-level flag lives on orders. A model handed both reaches for the table whose name matches the question and returns a confident, wrong number.
The fix is not better prompting - it's stating the grain in the tool description, where the model reads it on every call.
The same document produces the system's most counterintuitive behaviour: questions it is designed to refuse. A lifetime-orders counter on the customer record carries no dates, so "how many repeat customers did we gain this month" cannot be answered from it at all. Refusing with an explanation is correct; silently computing over all history and labelling it "this month" is the failure mode worth engineering against.
If a question can't be answered by hand-written SQL, the model can't answer it either. That's a data gap to close now, not a model failure to debug later.
API keys are the tempting shortcut here, and full OAuth looks like too much to take on. That trade is worth re-checking rather than recalling from memory: MCP's specification now deprecates dynamic client registration and treats a pre-registered client as a first-class path. What looks like a rebuild is a supported configuration.
The server is an OAuth resource server. It never sees a password, an authorization code, or a refresh token - it verifies signatures against the identity provider's public keys. It therefore needs zero permissions on the identity provider: a compromised query server cannot mint or alter an identity.
THE AUDIENCE TRAP
Cognito populates the aud claim only when the client sends the RFC 8707 resource parameter, and only for user grants - never for machine-to-machine credentials. Three separate strings must match exactly: the resource server identifier, the server's public URL, and the client's resource parameter.
A mismatch fails late. Discovery works. Browser login works. Then every call returns 401. Worse, a token with no aud at all isn't a benign default - it is replayable against any service sharing the identity pool. The server refuses such tokens by default, and a standalone verification script exists purely to catch the mismatch before a client is ever wired up.
The denial paths carry the design, so they carry the tests - a suite weighted toward refusal rather than the happy path: forged signatures, alg=none, tampered payloads, expired tokens, wrong pool, wrong audience, missing scopes, and ID tokens presented as access tokens. That last one matters more than it looks: an ID token verifies cryptographically against the same keys but carries no scopes, so accepting one bypasses scope checking entirely.
Missing scope returns 403, not 401. A 401 makes the client discard a good token and re-run the entire browser flow; a 403 with insufficient_scope prompts step-up authorisation instead.
Unreachable keys return 503, not 401. Our outage should not look to the client like their credential problem.
Metadata is served unauthenticated. Requiring a token to read the document that explains how to get a token is a deadlock.
The model writes SQL and the server executes it. Everything between those two facts is the safety surface. Read-only enforcement can't be a keyword blocklist applied to a raw string - that's defeated by a comment, a stacked statement, or a write wrapped in a CTE.
So the validator tokenises first: it strips line and block comments, skips over quoted strings and quoted identifiers with correct escape handling, then inspects the resulting keyword stream. A statement must start with SELECT, WITH, or EXPLAIN; it must contain no forbidden keyword anywhere; and a semicolon may appear only as the final token.
Containment doesn't stop at syntax. Because a serverless query engine bills per byte scanned, an unbounded SELECT * is a financial incident as much as a performance one. The controls are layered so that defeating one still hits the next:
| CONTROL | WHERE IT LIVES | WHAT IT STOPS |
|---|---|---|
| Statement validation | Server, pre-submission | Writes, DDL, multi-statement, obfuscated writes |
| Row cap | Server config, hard ceiling | Result sets large enough to blow the model's context |
| Scan cutoff | Warehouse workgroup | Runaway cost - enforced by the engine, not by us |
| Workgroup pinning | IAM conditional deny | Escaping the workgroup, and therefore the scan cutoff |
| DDL denial | IAM explicit deny | Schema changes, even if a future policy grants them |
| Write-path denial | IAM explicit deny on data prefix | Writes to source data; results prefix is the only writable path |
The IAM layer matters because it survives our own mistakes. Registration tooling needs permission to create tables; the server never does, so the server's role carries an explicit deny across all catalog mutations. An explicit deny wins evaluation regardless of what any future policy allows - the guardrail outlives the person who wrote it.
The exposed surface is deliberately small - four tools. Every tool added is a new capability granted to every model that connects, permanently, so the bar for adding one is high.
| TOOL | CONTRACT |
|---|---|
| list_tables | Enumerate what exists. Must be called first; the model is told not to guess names. |
| get_table_metadata | Columns, types, descriptions, partition keys - read live from the catalog, so it can't drift from reality. |
| execute_athena_query | Validate, submit, return an execution id. Never blocks. |
| get_athena_query_result | Poll status, then page results with an explicit continuation token. |
Splitting submission from retrieval is the load-bearing decision. Analytical queries take seconds to minutes; a blocking call either times out at the transport or holds a connection open for the duration. Handing back an execution id lets the model poll, report progress, and page through results at its own pace - and it makes truncation explicit rather than silent, because pagination state is right there in the response alongside the row count and the bytes scanned.
WHY SILENT TRUNCATION IS THE WORST BUG
A model that receives 100 of 4,000 rows without being told will sum them and state the total with complete confidence. Every response therefore carries its own row count and a continuation token, and every description tells the model what those mean. Truncation the model knows about is a manageable limitation; truncation it doesn't is a fabrication engine.
The same principle governs errors. A raw driver exception is noise the model will either surface to the user verbatim or, worse, try to interpret. Every AWS failure mode is translated into structured, actionable text - missing credentials, unset region, endpoint unreachable, access denied, entity not found - each naming the operation that failed and what would fix it. The model can act on table "ordrs" not found. It cannot act on EntityNotFoundException.
Scored against hand-written SQL, not plausibility
Evaluation is the part most demos skip, because eyeballing output is fast and grading is slow - and "looks right" is precisely the failure this system exists to prevent.
So a representative question set gets answered by hand first, in SQL, against the registered tables. Those results are the answer key. Behind it sits a purpose-built dataset, generated deterministically from a fixed seed so any run is reproducible, with realism engineered rather than assumed:
Edge cases are planted deliberately
A channel that spent nothing last month, order totals sitting exactly on a free-shipping threshold a question filters against, customers with no sessions attached, a product returned every time it sold, a region with no orders at all.
The validator asserts every planted case survives regeneration
On one run the customer meant to demonstrate a refund-after-chargeback case drew no orders, so there was no case to find. Marking a row as special does not make the scenario exist.
Volume is tuned for statistical meaning
A first pass gave barely a dozen orders per channel per month - a margin of error wide enough that any conversion rate from it was noise. Volume goes where the questions actually look.
Two infrastructure decisions here are counterintuitive enough to state outright.
PARTITIONING
Monthly beat daily
Daily partitioning produced ~410 partitions per table averaging under 3 KB each. Per-prefix listing overhead exceeded the pruning benefit. Monthly partitions, with the full date kept as a regular column, won on both counts.
REGISTRATION
No schema crawler
A crawler re-infers types from the files and discards curated corrections and column descriptions - decimals come back as strings, booleans as text. Explicit DDL keeps the semantics that make the catalog worth reading.
The catalog is not incidental. Because get_table_metadata reads live from it, every column description written during registration becomes context the model sees at query time. Catalog investment pays out as answer quality - a far better place to spend effort than the prompt.
A query layer your security team can read
Semantic layer
Your metric definitions, written down and version-controlled - the artifact that decides whether answers are right.
Deployable build
Containerised, non-root runtime, private registry, health endpoint. Runs where the rest of your estate runs.
Zero secrets
No credential in client config. Browser login supplies the token; the server holds everything else.
Answer key
Reference SQL for your question set, so accuracy is measured rather than assumed.
Integration for the people using it is a URL. No key, no secret, no endpoint list - the client runs the discovery flow and the browser login itself:
{
"mcpServers": {
"analytics": {
"type": "http",
"url": "https://analytics.your-company.com/mcp"
}
}
}How we build these
The architecture is not domain-specific - the same shape works over a warehouse, a lakehouse, or operational databases. Six principles, worth applying whether or not we are the ones building it:
Write the semantic layer before the server. Cheapest artifact to produce, most expensive to retrofit - wrong definitions produce confidently wrong answers that no error handling detects.
Put cost control in infrastructure, not application code. A scan cutoff on the engine holds even when the application has a bug.
Bind tokens to the resource. Any system with more than one service behind one identity provider is a missing claim away from cross-service replay.
Budget for the catalog. Column descriptions aren't documentation overhead - here they are model context, and the highest-leverage quality investment available.
Keep the tool surface small. Every tool is a permanent capability grant to every model that connects.
Measure accuracy against hand-written SQL. If a question can't be answered by hand, it can't be answered by a model - and "looks plausible" is not a test result.
The model is never the bottleneck. Deciding what "revenue" means, and proving the system can't do anything else, is the whole job.
If your data is already in a warehouse and the queue for answers runs through one or two people who know the schema, this is the layer that shortens it - without widening what anyone, or anything, can reach.
Conclusion
The true value of an AI query layer isn't just in generating the SQL - it's in the boundaries it strictly enforces. By abstracting the complex semantic rules, limiting credentials to the server-side, and refusing to guess on ambiguous metrics, we transform a potential liability into a safe, production-ready tool.
When business leaders can interrogate their data warehouse directly with natural language and get reliable, secure answers, the bottleneck of manual dashboarding and analyst queues is effectively eliminated. The result is faster, more confident decision-making built on trust.
Empower Your Business With Cutting-edge IT Solutions
Unlock Innovation and Growth with Our Expert Solutions