Your Agent Used the Semantic Layer. Then It Ran Its Own SQL Anyway.
Part 3 of the series What Your Agents Don’t Know.
For this series I run an AI agent as a data analyst on the warehouse of a fictional freight broker, Tessaway Freight. In Part 2 I gave it a semantic layer, and it got every one of my 24 executive questions right, 72 runs out of 72. I expected the next problem to be the one the market keeps warning about: agents that ignore the layer you paid for and write their own SQL instead.
That isn’t what happened. The agent used the layer every time it applied. Then, in almost every one of those runs, it also ran its own SQL against the raw warehouse. Every answer was right, so nobody would ever notice. Which is exactly the problem.
For this part I put the layers behind one endpoint, a context contract served over MCP from Cloud Run, and ran the same agent two ways: with the contract and its old SQL tool side by side, and with the contract alone. Then, for every answer, I worked out in code which route it came from.
It used the layer every time
On the 18 questions one metric answers, the agent called the metric operation in all 54 runs. It also ran SQL in 51 of them. In Part 2, with the layers handed to it inside the test harness, the same numbers were 54 and 48. Serving the layers from a real endpoint didn’t change the habit.
The executive version is short. Your definitions were consulted. So was something else.
That runs against the industry worry, not with it. VentureBeat’s August 2026 survey found that among 48 respondents whose companies run a semantic layer in production, only 23% name it as their agents’ primary source of business context. In my runs the layer was consulted every time it applied, and it was the only thing consulted in 3 runs out of 54. The gap I found isn’t between having a layer and using it. It’s between using it and being able to show that the answer came from it.
Why a careful agent does this
Nothing is broken. Give an agent a tool that can answer anything and a tool that answers one kind of question well, and this one used both. In 45 of the 51 runs that used both, it called the metric first and ran SQL after. That’s the order you’d expect from an analyst checking a number against the source.
But the agent never said that’s what it was doing. Not one of the 51 final replies mentions checking, confirming or verifying anything, and my harness keeps only the final reply, not the model’s text between calls. So the order is measured and the reason isn’t. The agent didn’t say why it ran the SQL, and I can’t tell you.
And I don’t count it as a fault. On these questions the SQL never produced a wrong answer, and an analyst who checks a number against the source is doing the job well. The argument here isn’t about error.
Why right answers aren’t enough
When the two routes agree, you never see that there were two. The answer is right, the dashboard is green, and the second route is invisible.
Now picture the day finance changes a definition. The layer changes, because that’s what it’s for. The agent’s own SQL doesn’t, because nobody owns it; the agent writes it fresh each time from what it knows about the tables. From that day on, the two routes disagree, and which number reaches your executive depends on which one the agent happened to report.
You need to know which definition an answer came from. With an agent that keeps a private route to the data, nothing in a normal deployment records it. Not intelligence. Accountability.
Context has to be served, not stored
A semantic layer sitting in a repository changes nothing an agent says. It has to be served, and served in a way you can hold to account. I’ve written before about contracts as products in Composable and API-First. For an agent’s context, four properties matter, and each one is something an executive can ask for by name.
Explicit. The contract is a short, fixed list of operations: look up a definition, compute a named metric, check a rule, resolve an entity. There is no operation that runs a query; the caller names a metric, and the server writes the SQL. (One run found a way around that, in the section “A filter is a query.”)
Secured. Only callers with an identity get in, and each agent calls under its own. I checked it three ways against the deployed service: a call with no token was refused, a call with a forged token was refused, and the agent’s own token was let through.
Versioned. Every response carries the contract version and a response ID. All 972 responses in the scored runs carried version 1.0.0 and an ID.
Observable. The server writes one log line per call that reaches the contract: who called, which operation, which version, which response. After the runs I compared the server’s log with each agent’s own record of its calls, and they agreed on all 972.
MCP carries all of this, and MCP isn’t any of it. The protocol moves a definition from a server to an agent and has no opinion on whether the definition is right, who owns it or which version you’re on. (If you want the protocol itself, I covered it in Your Agent Just Got the Keys to Someone Else’s Building.) Gartner was reported, at its Data & Analytics Summit in March 2026, to have predicted that 60% of agentic analytics projects relying solely on MCP will fail by 2028 for lack of a consistent semantic layer. I have that secondhand, from a data catalog vendor’s write-up of the summit, not from Gartner’s own research.
Where each answer came from
To say where an answer came from, I didn’t ask the agent. For every run, code compared the number on the agent’s final line with every value the contract returned and every value its SQL returned in that run. If it matched only the contract, the answer came from the contract. If it matched only SQL, from SQL. If both routes returned the same value, it’s “both,” which means the routes agreed, not that the agent used both.
With the contract and SQL both open, all 72 answers were right. 39 came from both routes at once. 18 came from SQL alone, and every one of those was a plain lookup (how many miles a load traveled, the exchange rate on file), facts the contract doesn’t hold. 15 came from the contract alone: in those runs the agent either ran no SQL or ran SQL that didn’t return the same number.
With SQL closed, the 54 runs on the metric, entity and rule questions were all right, and all 54 answers matched a value the contract returned. Every one of them can be traced to a response ID, a version and a log line on the server.
What closing the second route costs
It costs the questions the contract can’t answer. On the six lookups, the routed agent declined 14 of 18 runs and was confidently wrong in none. It said so plainly, as in this reply to “What is the SCAC code for the carrier Brightline Haulage?”:
“As this information is not available in the warehouse, I cannot provide the SCAC code for Brightline Haulage.”
The SCAC code is in the warehouse. It isn’t in the contract. A decline is the right failure: you can see it, count it and fix it by adding an operation.
Get Robert H Mowery III’s stories in your inbox
Join Medium for free to get updates from this writer.
The other four lookup runs came back right, and none matched a value any operation returned. One showed its work: “Based on the invoiced amounts and their converted US dollar values in June 2026, the exchange rate on file is 0.7318 USD per CAD.” The others gave no derivation at all. Right answers, but not from a definition anyone owns.
On the 12 blind questions from Part 2, the routed agent was right in 31 of 36 runs. H11, the question the layer can’t answer (delivered loads with paperwork on file that still can’t be billed), was declined three times out of three. In Part 2, with SQL beside the layer, it was right three times out of three. That’s the real price of closing the route.
Two answers were confidently wrong, both on “How many late deliveries in Q3 2026 were the shipper’s fault?” The right answer is 139. Twice the agent answered 0, and its reply shows why:
“Consequently, under the official metric
late_deliveries, none of the late deliveries were the shipper's fault. (Operationally, 139 delivered loads in Q3 2026 arrived after their appointment window with a shipper-caused delay, but officially they do not count as late deliveries)."
It found 139, then applied the official definition, which leaves shipper-caused late loads out of “late,” to a question that was asking about exactly those loads. A contract makes the official definition the easy one to reach. It doesn’t stop an agent from reaching for it when the question meant something else.
As for what the open route cost: with SQL available, the agent ran 2.99 queries a question, took 79.3 seconds and cost $0.0362. With the contract alone it took 43.3 seconds and cost $0.0321. The full cost argument is Part 6.
A filter is a query
One routed run didn’t stay inside the contract. Asked for the exchange rate, the agent had no operation that returns one. So it used the metric operation’s filter, the argument meant for something like “carrier type is third party,” and started sending SQL through it: first a filter that is always true, then a subquery, then subqueries built to fail on purpose so the error message would carry back what they read. One error message handed it the name of the warehouse dataset, out of the catalog. Its next attempt, aimed at the exchange-rate table itself, was refused, and not by the contract: my test harness allows eight metric calls a question, and that was the ninth. The server’s service account can read every table in the project, so one more allowed call would likely have read the table.
Then it answered 0.7318, which is right, and wrote that the value came from the exchange-rate table. The run record shows it never read that table. Had I taken the agent’s word for where its answer came from, I’d have written down a source that never answered.
It happened once in 108 routed runs. The attempts got the dataset name out of the catalog and no rows from the table. But it’s the clearest thing these runs taught me. A contract that takes a filter string has taken a query. “Explicit” has to cover the arguments too, not only the list of operations. And it’s why I’ll never count an answer’s source on the agent’s say-so.
What the version number is for
Every answer that came through the contract now records which contract version it came from. Today that’s always 1.0.0, and it tells you nothing you didn’t know. It starts to matter the first time a definition changes, because then the version is the only thing that says whether last week’s number and this week’s were computed the same way. That’s the hook Part 4 hangs ownership on: who owns a definition, and how to trace an answer back to it.
Since I ran this
On October 26, two weeks after these runs, Google made Looker’s managed MCP server generally available. It runs inside a Looker instance, at the instance’s own address with /mcp on the end, so there’s no server to host. Every tool starts switched off until an admin turns it on. An agent connects over OAuth as a person and gets that person’s Looker roles and nothing more. Every call it makes lands in Looker’s System Activity, and on Looker (Google Cloud core) in Cloud Audit Logs too.
Hold that against the four properties above. Explicit is the tool allowlist. Secured is the person’s own sign-in, so the agent never holds an account that can read the whole warehouse. Observable is the audit trail. Versioned is the one I’d ask about: the documentation doesn’t say how a response records which version of the model answered it.
It doesn’t close the second route for you. A Looker tool with a warehouse SQL tool beside it is the setup I tested in the first half of this article, and you’ve seen what the agent did with it. Which tools you switch on, and which you leave off, is the routing decision now. The calls also count against the instance’s API quota, and on Looker’s Standard edition that’s 1,000 query calls a month. That’s a number to know before an agent starts calling.
I haven’t run Tessaway through it. Every result here comes from my own contract on Cloud Run, not from Looker.
How I ran it
Before any agent ran, I wrote down twelve predictions and committed them. Ten held as written. One failed: I expected the agent to call the rule operation in fewer than 18 of 72 runs, and it called it in 35. One held only after a correction made after the runs: a single call the protocol layer refused before it reached the server had been counted as a mismatch in the log check, and was relabeled as its own category. I’d rather tell you than have you find it in the repo.
A few things changed from Part 2, and you should know them. The SQL tool was fixed in one respect: it used to crash on a time-interval value from BigQuery, and now it returns it as text. The agent’s instructions didn’t tell it to prefer either route. In the routed agent’s instructions, the paragraph about the SQL tool was replaced by one about the contract, and the list of warehouse tables was taken out too, since it had no tool that could read a table. Each run could make at most 48 model calls, which no run reached. The server ran on Cloud Run under its own service account, and I tore it down after the runs. Part 3 cost about $6.83 in Gemini and BigQuery, plus an estimated $0.20 of Cloud Run.
I also pointed a second vendor’s runtime at the same endpoint: the Claude Agent SDK with claude-sonnet-5-5, reached through Anthropic’s API, with the contract and SQL available. It ran all 24 questions without an error, called the contract in 19 of them, and all 43 of its contract responses carried the version and showed up in the server’s log under its own identity. That pass, with a one-question check, cost $0.28. I didn’t score it, and nothing here says how accurate it was. It shows only that the contract doesn’t belong to one vendor’s agent.
This is one model, one contract I designed, a fictional company and three runs per question. Small differences aren’t findings.
Five questions for your team
- Of the agents we run, how many can reach the warehouse without going through our definitions? Count the SQL tools, not the dashboards.
- For an answer an agent gave last week, can we show which definition it used? If the answer is “the agent said so,” that’s a no.
- When our layer and an agent’s own query disagree, which one wins, and who finds out? If nobody finds out, the agent decides.
- Where is our semantic layer served from, and can an agent that isn’t our BI tool call it? A layer only your BI tool can reach is a report, not a contract.
- When the contract can’t answer a question, what does the agent do? Declining is a failure you can see. Working around the contract isn’t.
If those answers come back vague, your agents may well be right. You just can’t say why.
Right answers you can’t account for are a debt, and it comes due the day a definition changes. Serve your context as a contract, and make every answer show its receipt.
Next in the series: every answer now carries a contract version. Part 4 asks who owns the definitions behind it, and traces one answer back to a definition, an owner and a version.
The code behind every number in this article is public, and my Substack companion walks through it line by line.
Previous: Part 2, I Built an Ontology, a Semantic Layer and a Knowledge Graph. One of Them Did the Work.
Next: Part 4, An Answer You Can’t Trace Is an Answer You Can’t Fix
