Can AI Auto‑Generate the Semantic Layer? MotherDuck Shows the Real Asset

Over the past year the semantic layer has re‑emerged as a hot concept in data and AI, aiming to provide a business‑level language (metrics, entities, relationships, business logic) that bridges raw databases and AI agents.

01 – AI Can Build the Semantic Layer

MotherDuck ran an experiment using Malloy, an open‑source data‑modeling and query language, to test two hypotheses: (1) an agent can, with minimal human input, generate an accurate Malloy semantic layer from a data manual, training Q&A, and schema; (2) the Malloy semantic definitions would reduce prompt tokens and be faster than a baseline of Markdown + SQL while maintaining accuracy.

The first hypothesis was confirmed. The agent read the manual, generated a Malloy model, compiled, executed, and repaired it in a loop. Provenance and hash checks ensured the model was fully generated by the agent, not manually tweaked.

02 – Semantic Layer Does Not Win on Held‑Out Questions

The experiment used 26 training questions and 419 held‑out questions from the DABStep benchmark (450+ real data‑analysis tasks). Results:

Markdown + SQL (Gemini) – 418/419 correct (99.8%); median prompt tokens ≈ 42,370.

Malloy (Gemini) – 382/419 correct (91.2%); median prompt tokens ≈ 106,609 (≈2.5× higher).

Malloy (Sonnet + Opus) – 295/419 correct (70.4%); median prompt tokens ≈ 84,010.

The controlled comparison (same Gemini model, only the knowledge carrier changed) shows the semantic layer adds more context for the agent to read, increasing token usage without improving accuracy.

03 – Pre‑Built Semantics May Restrict Agents

Traditional semantic layers fix business logic (e.g., revenue definition, customer deduplication) in metrics or views, reducing drift for human analysts. However, agents dynamically read documentation, inspect data, write SQL, and iterate, so a pre‑compiled view can lock the agent into an incorrect interpretation. MotherDuck cites an example where a view calculated an average fee while the correct answer required a sum, causing the agent to persist the wrong logic.

Thus, while semantic models provide deterministic, reusable contracts, they may limit the flexibility of agents that could otherwise adapt context per query.

04 – Evaluation (Eval) Becomes More Valuable Than the Model

The experiment demonstrated that the semantic layer can be regenerated, validated, and discarded repeatedly using a stable manual and a set of Q&A. This leads MotherDuck to argue that “your data tests become more valuable than your semantic layer.” The Eval consists of business intent, expected answers, and boundary conditions that can be automatically re‑tested when the model is regenerated.

05 – The Role of the Semantic Layer Shifts

MotherDuck emphasizes three enduring uses of semantic layers: isolating downstream consumers from schema changes, propagating metric definition updates, and providing guardrails for domain experts. However, the layer may no longer be the sole repository of business intent. Future data‑agent architectures might store intent and context separately, let agents generate or adjust implementations, and keep the semantic layer as a governed, executable contract for verified definitions.

In summary, the study challenges the assumption that a semantic layer always improves AI‑agent performance; instead, it highlights the rising importance of verifiable business intent and evaluation as the durable asset.