Why Text-to-SQL Fails on SAP: The Semantic Layer We Had to Build First
Alberth Flores
Last updated: August 2026
Text-to-SQL fails on SAP because it assumes that table names and column names carry business meaning. In an ERP they do not. VBAK holds sales orders, quotations and contracts in the same table, and GBSTK = 'C' means fully processed rather than cancelled. The fix is a declared semantic layer over SAP data products, not a better prompt.
The 4 key points:
- The failure is silent. Asked the same stock question against the same warehouse, the agent reported 1,958,814 units available without a gold layer and 21,534 with one: ninety times too large, and the SQL ran cleanly both times (Onibex, 2026)
- The semantic layer is one YAML file per data product, declaring field roles, synonyms, aggregation behaviour and value mappings, so four phrasings resolve to one column by declaration rather than by inference
- Joins are declared as costed edges, not foreign keys.
traversal_costmakes the path deterministic, andaggregation_safety: requires_dedupmarks the six edges in the published examples where aSUMwould fan out - Cost per query fell from around a dollar to around ten cents once path planning stopped being the model's job, measured in our own environment against our previous chunk based approach
None of that meaning is in the schema. It lives in the heads of two or three people per company, and an LLM pointed at the raw tables has no way to recover it.
We hit this while trying to answer one question for a customer: given our sales demand, do we have enough stock to cover it? It is a one line question, and one of the most expensive ones to answer late, because committing a delivery date depends on it. The data was already in the warehouse, complete and current. The problem was never access. It was translation.
So we built a semantic layer over SAP data products, and published the specification. This is what goes in it and why each part earns its place.
Why does text-to-SQL fail on SAP specifically?
Because the failure is silent. A model pointed at raw SAP tables does not usually produce a syntax error. It produces SQL that runs cleanly and returns a number that looks reasonable.
It sums a column that is mostly zeros. It counts quotations as orders. It joins two facts and inflates the total four times over. Nobody catches it, because there is nothing to catch: the query succeeded.
That is the difference between text-to-SQL on a tidy analytics schema and text-to-SQL on an ERP. On a clean star schema the model's guesses are usually right. On SAP the guesses are usually wrong and always confident.
What is an SAP data product, and why is a table not one?
Two things get mixed up constantly, and everything below depends on separating them. A data product is a physical table with business meaning, produced by a pipeline: SILVER_SD_SALES_ORDER, GOLD_SD_OPEN_ORDER_TRACKER. You can SELECT from it.
The semantic layer is what sits on top: one YAML file per data product, declaring what that table is, what each field means, and how it relates to the others. You cannot query it. It is a contract.
The table resolves what the data is. The YAML describes it so an agent never has to resolve it again.
What do the three layers actually resolve?
Medallion is not a naming convention. Each layer removes a different class of ambiguity, and by the time the agent arrives, those ambiguities are gone.
Bronze is the raw node, and the agent skips it. VBAK, VBAP, VBKD: untranslatable names, dates stored as text, amounts as floats, one table serving three document types. Essential as origin and as traceability, and poor context for an agent.
Silver is the Foundational Data Product, and it resolves the business entity. Five bronze tables consolidate into one real artifact, filtered by document type. From there, the question "is this an order, a quotation or a contract?" stops existing. It is already answered in the table.
Gold is the Business Logic Data Product, and it resolves vocabulary and derived fields. Technical names become business terms, text tables get joined by language, and SAP conventions turn into readable values: GBSTK = 'C' becomes order_status = 'CLOSE'.
When a question arrives, the resolver walks the catalog in the reverse order of construction. Gold first, silver as a fallback, bronze skipped by default.
What does the semantic layer declare that a schema does not?
Three field declarations from the published open order tracker, each removing one decision the model would otherwise guess.
How a field aggregates, and every name a user might give it.
- name: "order_qty"
field_role: "measure"
type: "DECIMAL"
description: "Quantity the customer ordered on this line."
aggregation_behavior: "SUM"
synonyms:
- ordered quantity
- demand quantity
- sales order quantity
- order volume
field_role: measure says it aggregates, aggregation_behavior: SUM says how, and synonyms is a first class key that retrieval reads directly. Four phrasings resolve to one column, by declaration rather than by inference.
Names that lie get defused. There is a field called delivery_status, and it is not a status:
- name: "delivery_status"
field_role: "dimension"
type: "INTEGER"
description: "Capped lead-time bucket, NOT a status code despite the name: negative = days
overdue, 0 = due today, 1..7 = days remaining, 8 = eight or more. For fulfilment status
use `order_status`."
Ask an agent about delivery status and it reaches for the field named after it. The description sends it to the right field and explains what this one is for. An inherited name you cannot change stops being a trap once it is documented where the agent reads.
Business rules are written, not inferred. The same rule exists twice, once as the transformation that produces the data and once as the declaration that tells the agent what it means. In the gold pipeline, order_status is a single line of PySpark. In the YAML it reads: derived OPEN or CLOSE from ovrll_sts (VBAK.GBSTK), where 'C' maps to CLOSE and everything else maps to OPEN. The agent filters on a value that already means what it says.
There is a fourth kind worth naming, because it is the VBAK problem reappearing one layer up. Some gold products union several operation types into one fact, each with its own measure column, only one populated per row. The declaration says so: "SPARSE: populated only on rows where operation = 'Sale Order', 0 elsewhere." Omit that sentence and the agent sums a column that is mostly zeros and reports the total with complete confidence.
Writing those descriptions by hand for thousands of fields is not realistic, so AI drafts them under one hard rule: if a rewrite drops a value mapping or the reference to the source field, the rewrite is cancelled. The test behind the prompt is a single line, and it is the one that took us longest to learn. Would removing this change which column, table, join or aggregation the agent picks? If not, cut it.
How does the agent choose a join path?
Relationships between data products are declared as edges, and they are not foreign keys. A foreign key tells you two tables can be joined, and in an ERP that is close to no information, because everything can be joined to everything. What you need to know is not whether a path exists but which of the available paths is right.
So every edge carries a cost and a hazard flag:
- target_entity: "silver_s4h_mm_inv_mov_stock"
relationship_type: "many_to_many"
semantic_label: "demands_stock_from"
traversal_cost: 3.0
aggregation_safety: "requires_dedup"
traversal_cost is what makes choosing possible. On the same entity, customer and sales organization cost 1, plant costs 1.5, material costs 2. With costs on the graph the path is computed with Dijkstra and it is deterministic: same question, same join, no matter how creative the model is feeling that day.
aggregation_safety: requires_dedup is the more interesting one. It says that traversing this edge multiplies rows, so a SUM on top returns an inflated number. Three order lines joined to four stock movements give twelve rows and count every quantity four times. The SQL runs, the result looks reasonable, and it is four times too large. Six edges in the published examples carry that flag, and each one got there because somebody looked at it and decided.
That had a side effect we did not expect. Once path planning stopped being the model's job, cost per query went from around a dollar to around ten cents against our previous chunk based approach. That is our own measurement in our own environment rather than a cloud bill, but the cause is structural: everything already resolved in the definition is context you stop sending.
What happens when the gold layer is missing?
We ran the same question twice against the same warehouse, once before the domain's gold data products were published and once after. The first time, the resolver found no gold to match, fell back to silver, and answered with what was there.
It reported 1,958,814 units available against demand of 93,387. Yes, commit the delivery. With gold published, the answer inverts: 21,534 available against demand of 96,495. Not enough.
The demand figure drifted a little between the two runs, because the warehouse kept moving. The availability figure did not drift. It was replaced.
The difference is that at silver the only stock source was an inventory movement table, and summing movements does not give you a position. It gives you the sum of all history, ninety times larger and meaningless as availability. Gold carries the position itself.
There is a second difference, less visible and more important. At silver, the path from demand to stock runs through demands_stock_from, the many to many edge flagged requires_dedup. At gold, the equivalent relationship is covered_by_current_stock: many to one, and marked safe, because both sides are already at position grain. Publishing gold did not just rename columns. It removed an entire class of error from the path.
Neither run was a failure of the agent. Both did the right thing with what was in front of them. What changed was the substrate, which is why data modeling is not a prerequisite to the AI project. It is the project.
Nobody wrote that SQL, and nobody had to remember what 'C' meant.
How does ASK compare to the semantic layers you already know?
The useful distinction is specification versus runtime.
| Tool | What it is | What ASK adds |
|---|---|---|
| Cube | A runtime with its own APIs | ASK is a YAML contract, not a server. They are complementary: you can serve an ASK catalog from Cube |
| AtScale SML | A declarative, YAML first semantic model | Explicit medallion layering plus a resolution priority built for agents |
| Snowflake semantic model | Grounding for SQL generation on one platform | Layered composition, relationship costing and aggregation safety, without tying you to one platform |
| dbt | Transformation logic that builds the tables | ASK describes the output, not the build. If your gold product comes from a 400 line dbt model, ASK cares about the columns, grain, measures and joins that come out of it |
One clarification, because it comes up: visualizations are an output, not the product. ASK answers with charts and reports and we keep investing there. What we built is the layer that makes the number in the chart the right one.
What does Onibex actually do here?
Three things, and they are separable.
Onibex One Connect, a real time SAP data streaming platform, gets the data out. Each SAP table travels as its own Kafka topic, driven by SAP business events at the business object level, and connectors land it in Postgres, Snowflake, Databricks or wherever the customer already works. That is bronze, and it arrives on its own. Its Data Market ships a catalog of 150+ pre-packaged SAP data products across sales, finance, production and inventory, so the modeling does not start at zero either.
ASK, Agentic Semantic Knowledge, is the semantic layer over the data products built on top of it, plus the platform that authors, versions and publishes them and answers questions against them in natural language. It is the same direction as real time agentic AI on streaming data, applied to the analytical question rather than the event.
The specification is open. It is a runtime agnostic and source system agnostic YAML contract, published as v1 with a normative spec for each layer, worked examples and a license. We opened it because a semantic contract is worth more adopted than protected. Any customer, vendor or team can implement it without lock in, and we would rather compete on the runtime.
Its published example set is deliberately smaller than the Data Market catalog and serves a different purpose: fifteen Bronze, four Silver and four Gold from a real SAP S/4HANA installation, every one of them a file the product itself reads. Worked examples, not a shipped catalog. Either way, what you get is a head start and not an exemption from modeling your own installation, which always has its Z tables and its decisions someone made in 2011 and never documented.
Frequently asked questions
Does a semantic layer replace text-to-SQL?
No. It changes what the model is asked to do. The model still writes the SQL, but it writes it from resolved fields, declared joins and stated aggregation rules instead of guessing them from column names.
Do I need Onibex One Connect to use the ASK specification?
No. The specification describes the structure and meaning of a data product, not how it was built or how the data arrived. You can adopt it over data products you already have.
Does ASK work outside SAP?
The specification was forged on SAP ECC and S/4HANA and nothing in it is SAP specific. What is missing is worked examples for other source systems, which is where a contribution is worth more than a star on the repo.
Which databases does it run against?
The runtime targets SAP HANA and PostgreSQL today, with adapters for other engines in progress.
How is this different from just writing good column comments?
A comment is prose that nobody reads. A declaration is a typed key that the retriever, the planner and the SQL generator each consume. aggregation_safety: requires_dedup changes the shape of the generated query. A comment saying "careful, this fans out" does not.
Alberth Flores architects the Onibex ASK platform and authors the open ASK semantic-layer specification. He built the medallion pipeline these data products come out of. Every run and every number in this article is from his own environment.
Related reading: SAP Data Warehouse Modernization in 15 Hours, SAP Data Doesn't Care About Your Medallion (And I Built One Anyway), and Shrinking the SAP Integration Backlog by Jose Torrealba.
If you are modeling SAP data products for an agent and want the contract we use, the specification, the normative spec for each layer and the worked examples are open at github.com/Onibex/agentic-semantic-knowledge-ask.