Introduction
In our previous blog — NL2SQL Agent: An MCP-Powered Natural Language Insights for Enterprise Data — we demonstrated how Oracle’s GenAI stack can answer natural language questions directly against enterprise databases using a powerful agentic, MCP-based architecture.
That solution was built on a critical assumption: that the AI agent understands the database it is querying. In large companies, databases can have hundreds of tables. Table names may be hard to understand, and documentation may be missing or old. This makes work difficult for new data engineers and for AI systems — especially when they need to convert natural language questions into SQL queries.
If an AI agent does not understand the database schema well, it may guess wrong table names and create incorrect SQL queries. The Schema Discovery Agent solves this problem. It is an intelligent 11-step pipeline that can automatically document, map, and organize enterprise Oracle databases — creating the schema intelligence layer that makes reliable NL2SQL possible.
This blog explains how the Schema Discovery Agent works, how it combines graph algorithms with AI, and how it connects directly to the NL2SQL architecture covered in our previous post.
Why Schema Intelligence Is an Agentic Foundation
Reliable NL2SQL is not only a query-generation problem. It is a data-foundation problem. Enterprise agents need to understand what data exists, what it means, how it relates to other data, who is allowed to access it, and which business domain a user question belongs to.
The Schema Discovery Agent creates this foundation by converting raw Oracle database metadata into a governed semantic layer: business domains, object descriptions, column-level meaning, relationship graphs, and searchable metadata. This becomes the cornerstone for NL2SQL agents in OCI AI, Oracle Database 26ai, and AIDP-based agent architectures.
In an Oracle enterprise architecture, this pattern can be used across multiple execution models:
In AIDP, the discovered domains, table profiles, column descriptions, and relationships can become part of the enterprise data catalog and governance layer. This supports discoverability, lineage, business glossary creation, and controlled access for downstream AI and analytics use cases.
In Oracle Database 26ai and OCI AI, the same metadata can power vector-based metadata search, semantic search, and hybrid search over schemas, tables, columns, and business domains. This allows an NL2SQL or analytics agent to retrieve the right schema context before generating SQL.
Across both models, the discovered metadata can help enforce security and access controls by mapping users, roles, domains, tables, and sensitive columns before the agent is allowed to reason over or query enterprise data.
From Schema Discovery to Enterprise Ontology
The output of the Schema Discovery Agent is more than database documentation. It lays the groundwork for a semantic data layer — a structured, machine-readable understanding of business concepts built from the schema up.
Tables, columns, foreign keys, views, sample values, generated descriptions, and domain classifications together create a map of meaningful groupings. “Customer Management,” “Order Processing,” “Inventory & Logistics,” and “Financial Transactions” are not just cluster names. They become meaningful groupings that agents can use to scope and interpret queries.
Business users do not ask questions using table names. They ask using business terms — customer, revenue, exposure, project margin, shipment, cost code, or claims. The agent needs to map these terms to the right domains, tables, joins, and measures before generating SQL. The semantic layer created by schema discovery helps make this mapping possible.
As enterprise agents mature, this kind of semantic metadata becomes increasingly important. It can help agents decide which data domain is relevant, which tables should be joined, which columns represent business measures, which terms are synonyms, and which users are allowed to access which data. The schema intelligence layer created here builds the semantic metadata foundation on which a full enterprise ontology can be developed.
Why This Is a Game-Changer
Before walking through the pipeline, it is worth understanding why this approach is fundamentally different from simply passing a schema to a language model.
| 🎯 Context Isolation for AI | If an NL2SQL agent receives a schema with 500 tables, it quickly loses focus and hits token limits. The Schema Discovery Agent groups the database into Business Domains. The AI can then choose the right domain first, and only process a smaller, more relevant slice of the schema — improving SQL accuracy and reducing response time. |
| 🏛️ Automated Data Governance | Data dictionaries and schemas often become outdated. The Schema Discovery Agent creates a self-documenting database by continuously writing AI-generated descriptions back into the database as native Oracle comments — keeping documentation always current. |
| 🔀 Hybrid Clustering | The system combines Louvain graph clustering (which follows actual database structure) with K-Means vector clustering (which finds semantic meaning). Together they create a strong, complete map of the data landscape — catching relationships that either method alone would miss. |
What the Agent Discovers: A Real-World Example
The following results were produced by running the Schema Discovery Agent against a production Oracle schema. All figures are representative of a real enterprise data platform.
| Metric | Result |
| Schema Analysed | TECPDATP01 |
| Total Database Objects (Tables + Views + MVs) | 380 |
| Business Domains Classified | 12 |
| Average Tables per Business Domain | 30 |
| Columns Documented with AI Descriptions | 4,200+ |
| Oracle DB Comments Applied Automatically | 4,200+ |
| Total Pipeline Runtime | ~142 seconds |
Architecture Overview
The 11-Step Schema Discovery Pipeline
The Schema Discovery Agent orchestrates an 11-step pipeline. Each step builds on the last, moving from raw Oracle metadata all the way to named business domains written back into the database.
Figure 1: Schema Discovery Agent — full 11-step pipeline flow
Step 1: Schema Scanner
The pipeline begins by querying Oracle system views — ALL_TABLES, ALL_VIEWS, ALL_MVIEWS, ALL_TAB_COLUMNS, ALL_CONSTRAINTS, and ALL_DEPENDENCIES — to build a complete structural map of the schema. Every table, view, materialized view, column, data type, primary key, foreign key, and view dependency is captured into a unified JSON metadata file.
| Output | schema_metadata.json — a complete structural inventory of all database objects |
Step 2: Data Profiling
For each column across all objects, the agent samples up to 5 actual values from the database. This is a critical step because column names alone can be misleading. A column named STATUS could mean anything. Seeing actual values like [ACTIVE, PENDING, CLOSED] or [INPATIENT, OUTPATIENT, EMERGENCY] immediately reveals the business meaning. These sample values are later used to anchor LLM-generated descriptions to reality, not guesswork.
| Output | table_samples.json — real sample values per column, used to enrich LLM prompts in Step 5 |
Steps 3 & 4: Graph Building and Louvain Community Detection
The schema is modelled as a graph. Tables and views become nodes. Foreign key relationships and view dependencies become edges. This graph is then passed to the Louvain community detection algorithm.
Understanding the Louvain Algorithm
The Louvain algorithm is different from normal clustering methods. It is designed for finding structure inside complex networks. Instead of grouping points by distance, it finds communities — groups of nodes that are more strongly connected to each other than to the rest of the network. It uses a score called modularity to find hidden patterns in large graphs.
Modularity measures how well a network is divided into meaningful groups. A high modularity score means that nodes within a group are densely connected to each other, and sparsely connected to nodes outside their group.
To understand this with an analogy: imagine a social network. You may have friend groups such as school friends, family, and work colleagues. People inside each group talk to each other far more often than they talk to people outside their group. Modularity checks whether this type of natural grouping exists in a network.
• Nodes are strongly connected inside their own group
• Nodes are weakly connected to other groups
• The algorithm finds this structure automatically — no need to specify the number of clusters upfront
In database terms: ORDER_HEADER, ORDER_LINE, and SHIPMENT_DETAIL cluster together because they are all linked by foreign keys. PATIENT_RECORD, DIAGNOSIS, and TREATMENT_PLAN form their own community because they reference each other.
| Output | graph_domains.json — schema objects partitioned into structural communities based on FK and view dependency relationships |
Step 5: LLM-Powered Object Profile Generation
For every database object, the agent constructs a rich prompt including the object name, type, column list with data types, foreign key relationships, and the sample values collected in Step 2. This prompt is sent to Oracle OCI GenAI to generate a plain-English description of the table and each of its columns.
The data profiles from Step 2 are essential here: table column names can sometimes give the wrong meaning. Looking at a few sample values helps confirm what the columns actually represent. A column called TYPE with values [CREDIT, DEBIT, TRANSFER] is a payment transaction type — something no amount of schema inference could tell you without looking at the data.
| Output | table_profiles.json — structured plain-English profiles for all objects, used for embedding in Step 6 and domain naming in Step 9 |
Steps 6 & 7: Embedding Generation and Semantic Clustering (K-Means)
K-Means Clustering
K-Means clustering is a widely used method in data analysis. It is popular because it is simple and effective. It groups data by placing each item into the cluster whose centre is closest to it, iteratively refining cluster assignments until they stabilise. This helps divide complex data into clear, meaningful groups.
How It Is Applied Here
Each object profile from Step 5 is embedded using Oracle OCI’s Cohere embed-english-v3.0 model. The resulting vectors capture the semantic meaning of each table — its business context, not just its column names. K-Means clustering then groups these vectors, with the optimal number of clusters k determined automatically by comparing elbow analysis and silhouette scores.
These two methods — Louvain and K-Means — often give similar but slightly different results. For example, a table may be structurally connected to a main USERS table via a foreign key, but its metadata and content may be semantically closer to a LOGS or AUDIT domain. Neither method alone would get this right. The next step combines both.
| Output | semantic_clusters.json — objects grouped by semantic (business meaning) similarity |
Step 8: Cluster Merge Engine
To create the final and reliable business domains, we merge the results from both algorithms. The engine compares the structural graph domains from Step 4 with the semantic clusters from Step 7.
The merge works by calculating semantic cluster similarity: for each pair of structural and semantic clusters, we compute the average cosine similarity between all objects in one group and all objects in the other, using the vector embeddings already created in Step 6. If the average cosine similarity score between a structural group and a semantic group is above a set threshold — 0.75 by default — we combine both groups.
This creates the final unified clusters. It ensures that tables which are strongly related, either logically or physically, are never separated by the limitations of a single algorithm.
| Output | final_domains.json — unified domain clusters combining structural and semantic relationships |
Step 9: Naming the Domains — Generative AI at Work
At this stage, we have accurate clusters of database objects, but they still need meaning. They are only lists of table names. This is where Large Language Models do their most visible work.
To name each domain, we create a prompt that includes the grouped database objects and their short data profiles. The LLM proposes a 2–3 word business domain name that a business user would immediately recognise. Data profiles are important here because, again, table column names can sometimes give the wrong meaning — looking at actual data values helps confirm what each cluster truly represents.
• Customer Management
• Order Processing
• Inventory & Logistics
• Financial Transactions
• Healthcare Records
| Output | business_domains.json — each domain cluster named with a meaningful business label |
Steps 10 & 11: Closing the Loop — Applying Comments Back to the Database
Finally, the agent applies comments to the database as the last two steps. Step 10 inserts the domain-to-object mappings into two Oracle tables (DATA_AREAS and AREA_TABLES), making the classification queryable by any application. Step 11 goes through all the AI-generated descriptions and writes them back as native Oracle database comments — COMMENT ON TABLE and COMMENT ON COLUMN statements.
By writing this metadata back into the source database, we improve the database layer itself. Future developers, metadata scanners, BI tools, and the NL2SQL agent itself can immediately benefit from this automated discovery work — without any manual documentation effort.
| Output | Oracle DB updated with COMMENT ON TABLE and COMMENT ON COLUMN for all 380 objects and 4,200+ columns |
Running the Agent: Pipeline Dashboard
The Schema Discovery Agent ships with a FastAPI-powered dashboard for running and monitoring the pipeline step-by-step. Each step shows its live status, runtime, and log output as it executes via Server-Sent Events (SSE) streaming.
Figure 2: Schema Discovery Agent pipeline dashboard — completed 11-step run against TECPDATP01
The dashboard provides real-time step status (idle → running → done), per-step timing, a live colour-coded output log, and the ability to resume from any step. This last feature is particularly useful when iterating on large schemas where early steps (data profiling, embedding generation) take significant time and do not need to be re-run on every iteration.
The Output: Business Domain Classification
After the full pipeline completes, the schema is classified into named business domains. Below is the breakdown for TECPDATP01 — 380 objects across 12 business domains, each with a distinct business identity:
Figure 3: Business domain classification — 380 tables classified into 12 business domains
Industry-Specific Ontology Examples
This pattern becomes especially powerful when enriched with industry-specific context.
OFSAA / Financial Services: The agent can map business terms such as customer, account, product, transaction, risk exposure, and compliance metric to the right source tables and derived measures — the same first-class concepts that anchor deep business semantics like credit risk, liquidity status, and capital adequacy in Oracle’s financial services data models.
C&E (Construction and Engineering): The agent can map business terms such as project, asset, work package, cost code, schedule, change order, risk, and commitment into domain-specific clusters -— the same first-class concepts that anchor deep business semantics for projects, assets, and delivery performance in construction-aware data models.
The business domain layer adapts to whatever industry vocabulary the enterprise uses.
.
Knowledge Graph as the Next Evolution :- (NL2SQL accuracy improves when the agent understands the customer’s industry language, not just the physical schema.)
Looking ahead, the relationship graph built in Steps 3 and 4 — tables, views, foreign keys, and dependencies as a connected network — is a natural fit for Oracle Graph and Property Graph capabilities. Representing the schema as a knowledge graph opens a future path where agents can traverse business domain relationships, infer data lineage, and reason over connected entities rather than flat table lists.
Connecting Back to the NL2SQL Agent
With the schema fully classified, the NL2SQL agent from our previous blog gains a critical capability: domain-scoped query resolution. When a user asks a question, the agent no longer needs to search all 380 tables. It first identifies the relevant domain, then queries only the 28–42 tables within that domain.
For example, when an executive asks: “Which customer accounts increased revenue but decreased profitability in APAC this quarter?” — the NL2SQL agent identifies this as a Financial Transactions + Customer Management query and scopes its schema context to those two domains only. The result:
• Faster response — smaller schema context means faster LLM reasoning
• Higher SQL accuracy — fewer irrelevant tables means fewer wrong joins and hallucinated columns
• No token overflow — even 380-table schemas fit comfortably within LLM context limits
• Self-documenting database — Oracle comments mean the schema explains itself to every tool that queries it
Conclusion
Building a scalable NL2SQL engine needs more than sending user questions and a large DDLs to an LLM. By combining graph theory (Louvain community detection), embedding-based semantic analysis (K-Means on OCI Cohere vectors), well-defined merging rules, generative domain naming, and direct Oracle database integration, the Schema Discovery Agent can automatically understand the business context of any enterprise Oracle database.
The result is a foundation that is not static. Every time the pipeline runs, the database documents itself. Business domains stay current. Column descriptions reflect real data. Domain mappings are queryable.
In Oracle context, this schema intelligence layer supports multiple enterprise AI architectures. Schema Discovery should therefore be positioned beyond NL2SQL accuracy. It creates the metadata, ontology-ready semantic foundation, lineage, semantic search, and governed-access foundation for enterprise agents In AIDP, the discovered domains, table profiles, column descriptions, glossary candidates, and sensitive-data classifications can become part of the catalog and governance foundation. In Oracle Database 26ai and OCI AI, the same metadata can be embedded and indexed for metadata search, semantic search, and hybrid search — allowing an NL2SQL agent to retrieve the right schema context before generating SQL. Across all these patterns, the Schema Discovery Agent becomes part of the Oracle data foundation for enterprise agents: not only improving NL2SQL accuracy, but helping create the governed semantic layer that agents need before they can safely reason, retrieve, answer, or act.
This creates a foundation where AI is not just querying tables
It is querying connected business domains