I Gave My AI Agent a Semantic Layer Instead of Raw SQL
Delivering superior structured data retrieval for AI Agents with Semantic Layers
1. The Problem: LLMs Need Structured Data
Every organisation building AI agents eventually hits the same question: how should an agent retrieve data from your structured relational database? "Top 5 customers by revenue." "Average booking value over time." "Performance of VIP customers over the last 4 months."
The answers live in PostgreSQL, your data warehouse, or parquet files in Amazon S3, but models don't natively connect to these stores. RAG is primarily designed for unstructured documents, not tabular data. Pre-built APIs are often too rigid for the open-ended questions agents need to answer.
I found that semantic layers are often a better interface than raw SQL when AI agents need reliable, governed and resilient access to structured business data. Let's compare the semantic-layer approach with text-to-SQL, then walk through a proof-of-concept (built with Cube Core, Aurora PostgreSQL, AgentCore Gateway, and Claude Code).
2. Text-to-SQL: The Default Approach
Text-to-SQL is the obvious first idea. Hand the LLM a database schema, ask it to generate a
SELECT statement, execute it, return the results. It works, and it can work well when the data layer is prepared for it. For example, the AWS team worked with BGL to demonstrate an effective approach , where pre-aggregated, denormalized datasets were used.But in many real-world environments, the data layer isn't that clean. And that's where the problems compound quickly:
- Schema complexity: Databases have dozens of tables, cryptic column names, and implicit relationships. An LLM seeing
txn_amt_net_adjdoesn't know that means "net revenue after refunds." - Business logic leaks into prompts: "Revenue" might mean
SUM(amount) WHERE transaction_type IN ('booking_fee', 'subscription') AND status = 'completed'— not justSUM(amount). That logic ends up buried in prompt engineering, duplicated across agents, and impossible to audit. - Security surface area: Giving an LLM the ability to write arbitrary SQL against your production database is dangerous. Even with read-only users, a poorly constructed query can lock tables, scan billions of rows, or leak data across tenants.
- Inconsistency: Ask the same question twice, get different SQL. Different agents in the same organisation compute "churn rate" differently because each one has its own prompt-embedded definition.
- Fragile to schema changes: Rename a column, and every text-to-SQL prompt that references it breaks silently. The query runs, the results are wrong, and nobody notices.
3. Semantic Layers: An Alternative Abstraction
Semantic layers aren't new. They originated in the BI world as a way to give business users a consistent, governed vocabulary over raw data. The idea is simple: define your business logic once, for example what "revenue" means or how "churn" is calculated, and let every consumer use those same definitions.
What's changed is the consumer. When the consumer was a human writing a BI query, the semantic layer was a convenience. When the consumer is an AI agent that needs to reliably retrieve data on every call, the semantic layer becomes much more valuable. It gives the agent a constrained, typed interface, which is the kind of bounded tool that models are best at using.
A semantic layer sits between the agent and the database, providing a curated, business-friendly API over your data. Instead of writing SQL, the agent constructs a structured query using pre-defined measures, dimensions, and segments.
Take the first question from the opening, "Top 5 customers by revenue," and compare how each approach handles it.
Text-to-SQL — the LLM generates:
1
2
3
4
5
6
7
8
9
SELECT c.first_name || ' ' || c.last_name AS name,
SUM(b.total_cost) AS revenue
FROM bookings b
JOIN customers c ON b.customer_id = c.customer_id
WHERE b.is_completed = true
AND b.cancellation_reason IS NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5
Semantic layer — the agent generates:
1
2
3
4
5
6
7
{
"measures": ["bookings.total_revenue"],
"dimensions": ["customers.display_name"],
"segments": ["bookings.completed_bookings"],
"order": [["bookings.total_revenue", "desc"]],
"limit": 5
}
Both can produce the same result. But the semantic layer version is:
- Deterministic.
bookings.total_revenuealways means the same thing, defined once in YAML. - Safe. The agent is constrained to a governed query surface. It selects from a fixed vocabulary of measures and dimensions rather than writing arbitrary SQL (though access control and tenant isolation still need to be designed explicitly).
- Auditable. Every business definition lives in version-controlled YAML, discoverable via a metadata endpoint. The agent can ask "what measures exist?" before querying.
- Resilient to schema changes. Rename a database column, update the Cube model, and every consumer keeps working.
The agent's job becomes "select the right measures and dimensions" rather than "write correct SQL." That's a much simpler task, and in practice a more reliable fit for how models use tools.
4. Architecture in Practice
To test this pattern, I built a proof-of-concept over a fictional unicorn-rental dataset using:
- Cube Core as the semantic layer, an open-source tool that defines measures, dimensions, segments, and joins in YAML. Cube exposes a REST API that accepts structured JSON queries and handles all SQL generation, join resolution, and query optimisation.
- Aurora PostgreSQL as the data store.
- Bedrock AgentCore Gateway for auth and tool registration. Requests are authenticated using existing AWS credentials (SigV4), with no separate API keys or OAuth flows. AgentCore Gateway can register any backend service as an MCP tool via an OpenAPI spec, so I pointed it at Cube's REST API and got a clean tool surface with no custom integration code. (See this post from my colleague about this pattern )
- Claude Code as the AI agent, translating natural language questions into Cube queries.
System Architecture Diagram: Cube Core for Agentic Analytics
The Semantic Layer in YAML
All business logic lives in Cube model files. Here's an excerpt from the bookings cube, showing the
total_revenue measure and completed_bookings segment that appeared in the comparison above:1
2
3
4
5
6
7
8
9
10
11
12
13
14
cubes:
- name: bookings
sql_table: bookings
measures:
- name: total_revenue
sql: total_cost
type: sum
description: Total booking revenue (base_hourly_rate × duration_hours)
segments:
- name: completed_bookings
sql: "{CUBE}.is_completed = true"
description: Bookings where the unicorn has been returned and inspected
total_revenue means SUM(total_cost), defined once, used everywhere. The completed_bookings segment filters to returned-and-inspected bookings. These descriptions are surfaced to the agent via the metadata API, so the agent understands what each concept means before constructing a query. It never sees the underlying SQL. Just named measures, dimensions, and segments with plain-English descriptions.Three Tools, One Interface
At the gateway level, the interface is exactly three tools:
| Tool | Purpose |
|---|---|
| cube_meta | Discover available cubes, measures, dimensions, segments |
| cube_query | Execute a structured query and return results |
| cube_sql | Inspect the generated SQL (for user validation) |
Compare that with text-to-SQL, where the surface area is "anything expressible in SQL."
Connecting Claude Code to the Gateway
AgentCore Gateway uses AWS IAM (SigV4) for authentication. Unlike OAuth or static API keys, SigV4 includes the request body in the signature computation, so auth can't be established once at connection time. Each tool call carries a different JSON payload and needs its own signature. Most MCP clients don't support this yet.
The workaround: a shell script that uses
curl --aws-sigv4 to sign each request individually, sending standard JSON-RPC (the MCP wire format) to the gateway:1
2
3
4
5
6
7
8
9
10
11
12
curl --aws-sigv4 "aws:amz:us-east-1:bedrock-agentcore" \
--user "${ACCESS_KEY}:${SECRET_KEY}" \
-H "Content-Type: application/json" \
-d '{
"jsonrpc": "2.0", "id": 1,
"method": "tools/call",
"params": {
"name": "CubeSemanticLayer___cube_query",
"arguments": { "query": {"measures": ["bookings.total_revenue"], "limit": 5} }
}
}' \
"$GATEWAY_URL"
A Claude Code Skill wraps this script so the agent invokes it naturally. Same three tools, same JSON query format, just with SigV4 signing handled per-request in the shell layer.
5. End-to-End Walkthrough
Let's walk through the "top 5 customers by revenue" question end to end.
Step 1 — Discover the Schema
The agent starts by calling
cube_meta to learn what's available. The metadata response includes every cube, measure, dimension, and segment, along with their descriptions:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
{
"cubes": [
{
"name": "bookings",
"measures": [
{ "name": "bookings.count", "description": "Total number of bookings" },
{ "name": "bookings.total_revenue", "description": "Total booking revenue..." }
],
"dimensions": [
{ "name": "bookings.start_datetime", "type": "time", "description": "Booking start date and time" }
],
"segments": [
{ "name": "bookings.completed_bookings", "description": "Bookings where the unicorn has been returned and inspected" }
]
},
{
"name": "customers",
"dimensions": [
{ "name": "customers.display_name", "description": "Display name — full name for individuals, org name for organizations" }
],
"segments": [
{ "name": "customers.vip_customers", "description": "VIP tier: lifetime revenue >= $5,000" }
]
},
{ "...": "additional cubes omitted for brevity" }
]
}
This is the agent's "menu." From a single call, it knows what's available and what vocabulary to use, without seeing the underlying tables and joins.
Step 2 — Construct and Execute the Query
The agent constructs the same query from the comparison above — selecting a measure, dimension, and segment from the metadata:
1
2
3
4
5
6
7
{
"measures": ["bookings.total_revenue"],
"dimensions": ["customers.display_name"],
"segments": ["bookings.completed_bookings"],
"order": [["bookings.total_revenue", "desc"]],
"limit": 5
}
Cube returns the results, and the agent presents them:
| Rank | Customer | Revenue |
|---|---|---|
| 1 | Example Fantasy Ecosystems | $8,353,915 |
| 2 | Example Enchanted Logistics | $5,503,774 |
| 3 | Example Mythical Logistics | $5,434,981 |
| 4 | Example Mythical Transport Solutions | $3,348,033 |
| 5 | Rvthelia Fxstorm | $3,165,561 |
The agent didn't need to know which table holds customer names, how individuals and organizations are displayed differently, or how to join bookings to customers. It selected three business concepts (a measure, a dimension, and a segment) and Cube handled the rest. Our agent also calls cube_sql to show the user how the SQL query looks like:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
CASE WHEN customers.customer_type = 'individual'
THEN CONCAT(customers.first_name, ' ', customers.last_name)
ELSE customers.organization_name
END AS display_name,
SUM(bookings.total_cost) AS total_revenue
FROM bookings
LEFT JOIN customers
ON bookings.customer_id = customers.customer_id
WHERE bookings.is_completed = true
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5
6. Limitations and Beyond
This was a proof-of-concept: single EC2 instance, no caching, no row-level security, fictional data. The goal was to validate the pattern, while production semantic layers go much further: caching and pre-aggregation to reduce query latency, row-level and column-level access control for multi-tenant isolation, governance and lineage with single-source metric definitions in version-controlled YAML, and multi-engine support to abstract across PostgreSQL, Snowflake, and BigQuery with a single API. You may still want to consider Text-to-SQL approaches for simple schemas with obvious relationships.
Cube Core is just one implementation of the semantic layer. The principle is the same: give your AI agents a curated, governed interface to your data instead of a blank SQL prompt. If you're building AI agents that need to retrieve structured data, semantic layers are worth considering.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article