What Text-to-SQL Misses Without a Semantic Layer

Executive Summary: I asked how many units we sold in Q2 2008 against an AdventureWorks tutorial dataset and got 25,427 units. Correct. A second calendar in the same schema, a retail 4-4-5, returned 24,639. Also correct. Claude with direct database access answered the question five times and never mentioned the second calendar existed. Through a semantic layer, it stopped and asked which quarter I meant before computing anything. Key Takeaways: • The word “semantic” has two meanings. In vector databases, meaning is derived from content. In a semantic layer, it’s declared by the business.
• Your fiscal calendar isn’t in your schema. Which set of days counts as “Q2” is a business decision, not something that reading the tables will reveal.
• Correct SQL and a defensible answer are different things. An ambiguous question can result in many conflicting “right” answers.
• Whether an AI asks a clarifying question is a property of the path, not the model. Same model straight to the database: zero of five runs asked. Through a semantic layer: it asked before it computed anything.
• More context doesn’t take the decision away from the model. A data dictionary, a catalog, or a skill file gives it more to read. It’s still the thing choosing the table, the join, and the grain.

What I didn’t understand about semantic layers

I just joined AtScale. This is the first post in a hundred days of learning it in public.

The first time I put a question to a semantic layer, it wouldn’t answer me.

At least not right away… I’d asked something I thought was trivial: how many units we sold in Q2 2008. What came back was a question: did I mean the calendar quarter or the retail one?

I’d been building with vector databases for years, so the word semantic felt familiar. I figured I had a head start. Turns out I was coming from a different understanding of what “semantic” meant.

So this article is me working that out. First, what “semantic” means here, and why it isn’t what I expected. Then one question with two correct answers, asked first straight at the database and then through a semantic layer. And finally why more context, the fix everyone reaches for first, doesn’t close the gap.

What does “semantic” mean?

In vector databases, semantics is meaning pulled out of unstructured data. You embed a document, you embed a question, and similarity does the rest: two sentences that mean roughly the same thing end up near each other. The meaning is derived. It comes out of the content.

A semantic layer runs the other direction. Here the meaning is declared. The business decides what revenue means, and that a quarter is thirteen weeks starting on a Sunday, and those decisions sit between your data and every tool that queries it.

Both of them hand an LLM context. Only one of them computes.

A semantic layer holds the logic that calculates revenue. Ask the same question from an agent, a notebook, or Excel, and the same definition runs every time.

The quarter that wasn’t

Most of my learning so far has been tutorials, starter models, and sample datasets, plus a lot of questions to check whether I understood each concept.

The dataset is AdventureWorks internet sales. Bikes and accessories, about as ordinary as retail data gets. I just wanted to confirm I could read a schema.

So I started with a simple question: how many units did we sell in Q2 2008?

The table factinternetsales has an orderquantity column and an orderdate column with full timestamps, so the query practically writes itself. Filter the range. Sum the column.

25,427 units. April 1 through June 30. Nothing tricky about it. Right?

Then I found datecustom.

It’s a calendar table, and it carries two different quarter columns. quarter_name is the Gregorian calendar, the one I’d already assumed. rpt_quarter_name is a retail 4-4-5 calendar: thirteen-week quarters that don’t start on April 1. Here’s the same question asked against both.

SQL SELECT ‘gregorian’ AS calendar, SUM(f.orderquantity) AS units
FROM as_adventure.factinternetsales f
JOIN as_adventure.datecustom d ON f.orderdatekey = d.datekey
WHERE d.quarter_name = ‘Quarter 2, 2008’

UNION ALL

SELECT ‘retail 4-4-5’, SUM(f.orderquantity)
FROM as_adventure.factinternetsales f
JOIN as_adventure.datecustom d ON f.orderdatekey = d.datekey
WHERE d.rpt_quarter_name = ‘Reporting Quarter 2, 2008’;

None calendar | units
————–+——-
gregorian | 25427
retail 4-4-5 | 24639

788 units apart.

That’s about a 3% difference. Small enough that you could glance past it, big enough to matter if you’re reporting it.

They disagree because they cover different days:

SQL SELECT DISTINCT quarter_name, rpt_quarter_name
FROM as_adventure.datecustom
WHERE quarter_name LIKE ‘%2008%’
ORDER BY 1, 2;

Reporting Quarter 2, 2008 runs from April 6 through July 5. Gregorian Q2 runs from April 1 through June 30. And which set of days counts as “Q2” isn’t something the fact table can tell you. It’s a business decision.

Then I ran the original question through Claude Code with live psql access, five times, fresh session each time, giving it the connection string, the schema name, and the question. All five returned 25,427. Zero of five asked which calendar. Zero of five opened a date table. Every run had already listed all nine date dimension tables in its very first command, but there was no obvious reason to open one because factinternetsales had a date right there.

This is text-to-SQL working exactly as advertised. Plain English in, correct SQL out.

That’s the failure. Clean query, careful model, and a second calendar sitting one join away.

(There’s a lot more in that experiment, including what happened when I pushed back and it told me the retail calendar didn’t exist. That’s for another article.)

“How many units did we sell in Q2 2008?”

Same question, same data, Claude both times. The difference was this time it was talking to AtScale’s semantic layer over MCP instead of straight to Postgres.

Rather than answer right away, it told me what it planned to do, and then stopped.

Claude clarifying a query using AtScale semantic layer

Three things in there I hadn’t fully thought through.

Claude picked the measure and said why, rejecting Average Sold Product per Order and the other per-order ratios because they aren’t measures of unit volume. I’d been treating “units” as obvious.

Then it found two calendars, Order Quarter on the Gregorian hierarchy and Order Reporting Quarter on the retail 4-4-5, and surfaced the conflict before running anything.

The last one I never would have asked about. There’s a ship-date version of every one of those, so “sold in Q2” could mean ordered in Q2 or shipped in Q2. It didn’t leave the decision hanging either. It offered a default: “I’d default to order date.”

I picked Gregorian and order date. 25,427 units, and it named the measure, the hierarchy, and the date basis that produced it. The same number I’d gotten straight from Postgres. This time I knew what “units,” “Q2,” and “sold” each meant.

Why not just give the model more context?

My immediate thought was: isn’t this just a documentation problem? Write a good data dictionary. Point the model at a catalog. Put the fiscal calendar in a skill file and tell the agent to read it first.

I thought that was the answer for about a week. It isn’t. All of those give the model more context. They don’t take the decision away from it.

A description sits there and hopes the model reads it, understands it, and applies it. Most times it will. But even in the best case, where the agent reads your dictionary and correctly learns that your fiscal year starts in February, it’s still the thing assembling the query. It still picks the table, the join, the filter, the grain. Every one of those is a decision you didn’t see it make.

A description tells the model the rule. The semantic layer runs it.

One answer, from every tool. Without it, two people ask the same question, both get a right answer, and the answers don’t match.

The definition can’t be routed around. An agent doesn’t get to substitute its own version of revenue, because it isn’t the one computing it.

Nothing gets copied. The model is metadata over your tables. No extract, sync, or copy.

Before the number comes the meaning

The SQL was fine. But without the business context, the LLM gave a defensible answer to a question with two right answers, missing the second calendar in the schema. The semantic layer already mapped those business definitions to the data, so the ambiguity surfaced before the number was computed. Same answer, but this time the choice was mine.

The model didn’t need to get smarter. It needed a path where the business had already decided what the question meant, and a layer that computed the answer from that decision.

Connect with me on LinkedIn!

Reviewed by: Sarah Mulcahy