AI agents write SQL fluently, but without business context they guess: which price, which discount, which currency, which join. In this project the business meaning lives in OpenMetadata: a business glossary (RevenueDomain) defines every term with a definition, a formula for derived terms, and the source column plus the exact join for source terms, and the terms are tagged on the physical columns. A custom MCP server gives agents the tool build_query, which loads the glossary and the linked column metadata from OpenMetadata and lets the LLM compose SQL using exclusively that knowledge. Derived terms are expanded recursively until only source columns remain, so “revenue” becomes quantity × daily price × (1 − discount) × exchange rate × (1 + VAT), with all joins exactly as defined. If a question uses a term the glossary does not define, the agent refuses instead of inventing it, and generated SQL only runs through a read-only execute_query tool.
Semantic layer for AI agents: business definitions from OpenMetadata
Why agents need a semantic layer
“Revenue” sounds simple, but in this data model it needs eight tables and four business rules. An agent that only sees table and column names cannot know that.
Agent without a semantic layer
- Guesses from column names: something like quantity × a price column
- Misses the daily price, the customer discount, the USD→EUR rate and VAT
- Picks joins and filters by intuition, different on every run
- Returns numbers that look plausible, but are wrong, in the wrong currency
Agent with OpenMetadata as semantic layer
- Uses the formula the business agreed on, term by term
- Uses the exact join clauses stored with each source term
- Gives the same, explainable answer every time
- Refuses when a term is not defined, instead of inventing it
The RevenueDomain glossary: from business term to column
Four derived terms (grey) are defined by formulas over other terms; five source terms (coloured) point to a physical column and the join to reach it. Click a term to see what is stored in OpenMetadata.
From question to SQL: real examples
These questions were asked to the agent, and the SQL is the unedited output of the MCP tool. Coloured parts come straight from the glossary terms.
- Question in plain languageEnglish or Dutch, as a business user would ask it.
- Load the glossary
build_queryreads all glossary terms from the OpenMetadata API. - Find linked tables and columnsSearch OpenMetadata for columns tagged with the terms, plus tables named in the join clauses.
- Expand derived termsRevenue → FinalAmountEUR → NetAmount → GrossAmount, until only source columns remain.
- Compose SQL from glossary onlyThe LLM may use only these columns and joins: no other schema knowledge in the prompt.
- Review, then execute read-onlyA human reviews;
execute_queryblocks every write statement.
Guardrail: no definition, no SQL
The prompt tells the LLM to refuse when the question uses a term the glossary does not define. The agent then answers with one line, and the fix is to define the term in OpenMetadata, not to let the agent guess.
Agents that build and use the semantic layer
Three Claude Code sub-agents work through the same MCP tools, so the glossary stays the single source of truth from requirement to dashboard.
information-analysis
Captures entities, business rules and KPIs with the business, and writes them as glossary terms in OpenMetadata: definition, formula or source.
technical-design
Turns the functional design into a technical design, links the terms to physical columns and joins, and checks every decision against the platform standards (RAG).
builder
Builds the tables, dbt models, Airflow DAGs and Superset assets, and verifies results with build_query and execute_query.