Semantic Layer Generator: An Agentic Pipeline That Designs Wide Tables
A semantic layer generator: a five-phase agentic pipeline turns a normalized schema and business questions into Snowflake wide-table views, with MCP access.
This article is published in English.
Sources: The Semantic Layer Generator: When Agentic AI Meets Data Architecture
Between a normalised warehouse schema and the flat tables analysts actually query sits a gap, and most data teams fill it by hand. A semantic layer generator is an agentic system that tries to close that gap on its own. It takes a raw schema plus a list of business questions and returns optimised wide-table views in seconds, ready for production.
The gap nobody budgets for
Engineers build warehouses and analysts build dashboards. The semantic layer in the middle is usually written by hand, thinly documented and out of date almost immediately.
Building one properly takes real effort. A data engineer has to understand the normalised schema in depth: each foreign key, each chain of relationships, each edge case where a left join quietly turns into an inner join. Then they must interview stakeholders to learn which analytical questions matter. Only after that can they design wide tables, which are denormalised, pre-joined views that flatten a relational model into something a BI tool can query without a six-table join. For a schema of middling complexity the job takes days or weeks, and the result is a static artifact that starts to decay once the schema changes or a new question appears.
The root of the problem is a tension. Normalised schemas protect data integrity, while analytical queries want speed and simplicity. The semantic layer translates between the two, and it has been manual work for decades.
Missing or sloppy layers have visible costs. Analysts write ad-hoc queries full of redundant joins. Dashboards crawl. Text2SQL tools hallucinate because they can't find their way around a complicated schema. Every new business question turns into another engineering ticket.
Why it matters to the business
This is a strategic bottleneck as much as a technical annoyance. An organization that can't turn raw data into governed, analyst-friendly tables can't move faster than its own backlog allows. A good semantic layer does three things at once: analysts become self-sufficient, queries get much faster because pre-joined views remove runtime join costs, and Text2SQL becomes dependable because a flat table whose columns have clear names is the kind of input a language model needs to produce correct SQL.
Nobody doubted that a semantic layer was needed. The open question was whether building one could be automated without giving up quality.
Three things had to mature first
Models that read schemas for meaning. Today's LLMs do more than parse JSON. Shown a DEPARTMENT_ID foreign key, one can tell what it stands for and how it links to EMPLOYEES. Asked about pay spread by role band, it knows the join has to go from SALARIES through EMPLOYEES to ROLES. The whole pipeline depends on that reasoning.
Agentic orchestration. One LLM call can't do the job. You need several phases where each stage feeds the next, covering analysis, optimisation, design, generation and reporting, with deterministic optimisation steps based on pure set logic mixed in with the model-driven reasoning. This is agentic AI at its most useful: not a chatbot, but an autonomous system that completes an engineering workflow from start to finish.
MCP as the connector. With the Model Context Protocol the generator becomes a tool callable from any AI assistant, be it Claude, an in-house agent or a data platform's copilot. Mid-conversation, an engineer can pass in a schema and receive production SQL and a design report, with no change of context. Good agentic tooling should be invisible infrastructure and not yet another dashboard.
What forward-deployed means here
The phrase describes an engineering philosophy. Instead of shipping a generic platform and hoping users bend to it, you put engineering capability at the point of need. That doesn't mean customizing for a single client. The tool meets the problem where it lives: in your schema, shaped by your questions, deployed to your warehouse. Three traits follow from it.
- It is schema-native. There's no DSL to learn, no YAML manifest to configure and no new platform to adopt. You supply the schema you already have, with tables, columns, types and relationships, as plain JSON.
- It is driven by questions. The entire optimisation pipeline is anchored to the analytical questions you give it, so each wide table can be traced back to a specific business question. A table that helps answer nothing is pruned.
- Its output can go live. The tool writes DDL as
CREATE OR REPLACE VIEWstatements for Snowflake, a typed output schema that downstream Text2SQL tools can consume, and a standalone HTML report documenting every column. The SQL can be deployed the same day.
Five phases, no human in between
What makes the tool agentic isn't the presence of an LLM. It's that several reasoning and optimisation stages run autonomously, with no human step between phases. Analysis, optimisation, design, generation and reporting follow one another, and the design deliberately alternates probabilistic and deterministic work. Phases 1, 3, 4 and 5 use LLM reasoning. Phase 2 is plain Python set logic. The pipeline therefore gets the creativity and semantic understanding of language models where those help, and keeps optimisation rigorous and auditable where precision is required.
Who benefits
Data engineers receive a draft that would otherwise cost days of effort. They review the wide tables it produced, change join types, add computed columns and deploy, putting their skill into refinement and not construction. The HTML report becomes living documentation that previously didn't exist at all.
Analytics teams get flat, pre-joined tables with clear column definitions, so queries are simpler, results come back faster and nobody has to decode a 15-table ER diagram. A question-coverage matrix maps each question to the table that answers it.
Text2SQL systems may gain the most, and it's the benefit people notice least. Models such as GPT, Claude or open-source ones do far better against flat schemas than normalised ones. Fewer tables, fewer joins, descriptive names and column definitions all cut hallucination and raise SQL accuracy. The output schema JSON is formatted to plug straight into downstream Text2SQL tools, giving a smooth route from raw database to natural-language querying.
The organization gets time back. Each hour an engineer doesn't spend designing wide tables goes to data products, pipelines or genuinely new problems, and a recurring cost turns into a single invocation.
A worked example
Picture an HR database with five normalised tables (EMPLOYEES, DEPARTMENTS, ROLES, SALARIES, LEAVE_REQUESTS) joined by foreign keys in the usual star-schema style. The tool is given five questions: pay by department and role band, who has taken the most leave, hires per quarter by location, how pay is spread across bands, and how headcount grows in each department.
The run reports 5 source tables and creates 4 wide tables. No views are eliminated, since the schema was already close to optimal, and no questions are left unanswered, which means full coverage. The four tables are:
FACT_EMPLOYEES_FULLfor hiring trends and headcountFACT_SALARIESfor compensation analysisFACT_LEAVE_REQUESTSfor leave patternsDIM_DEPARTMENTSas the department dimension
That makes four views, nothing unanswered, every column traced to its source table and every view deployable to Snowflake as it stands. The whole process took under thirty seconds.
Beyond this one tool
The generator is one instance of a wider pattern: agentic systems that automate a well-defined engineering workflow by pairing LLM reasoning with deterministic optimisation. The same chain of analyse, optimise, design, generate and report fits ETL pipeline design, API schema generation, test scaffolding and many other translation-layer problems in software.
Language models, orchestration frameworks and open protocols like MCP have all matured enough that these pipelines can be trusted in production, as infrastructure and not as demos. The semantic layer was always a sound idea. It simply needed a builder that never forgets a foreign key and can redesign the whole layer as soon as a new question arrives.