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 \u2014 especially when they need to convert natural language questions into SQL queries.<\/p>\n
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 \u2014 creating the schema intelligence layer that makes reliable NL2SQL possible.<\/p>\n
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.<\/p>\n
Why Schema Intelligence Is an Agentic Foundation<\/p>\n
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.<\/p>\n
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.<\/p>\n
In an Oracle enterprise architecture, this pattern can be used across multiple execution models:<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
From Schema Discovery to Enterprise Ontology<\/p>\n
The output of the Schema Discovery Agent is more than database documentation. It lays the groundwork for a semantic data layer \u2014 a structured, machine-readable understanding of business concepts built from the schema up.<\/p>\n
Tables, columns, foreign keys, views, sample values, generated descriptions, and domain classifications together create a map of meaningful groupings. \u201cCustomer Management,\u201d \u201cOrder Processing,\u201d \u201cInventory & Logistics,\u201d and \u201cFinancial Transactions\u201d are not just cluster names. They become meaningful groupings that agents can use to scope and interpret queries.<\/p>\n
Business users do not ask questions using table names. They ask using business terms \u2014 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.<\/p>\n
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.<\/p>\n
Why This Is a Game-Changer<\/p>\n
Before walking through the pipeline, it is worth understanding why this approach is fundamentally different from simply passing a schema to a language model.<\/p>\n
\ud83c\udfaf\u00a0 Context Isolation for AIIf 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 \u2014 improving SQL accuracy and reducing response time.\ud83c\udfdb\ufe0f\u00a0 Automated Data GovernanceData 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 \u2014 keeping documentation always current.\ud83d\udd00\u00a0 Hybrid ClusteringThe 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 \u2014 catching relationships that either method alone would miss.<\/p>\n
What the Agent Discovers: A Real-World Example<\/p>\n
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.<\/p>\n
MetricResultSchema AnalysedTECPDATP01Total Database Objects (Tables + Views + MVs)380Business Domains Classified12Average Tables per Business Domain30Columns Documented with AI Descriptions4,200+Oracle DB Comments Applied Automatically4,200+Total Pipeline Runtime~142 seconds<\/p>\n
\u00a0Architecture Overview <\/p>\n
![\"](\"https:\/\/www.europesays.com\/ai\/wp-content\/uploads\/2026\/07\/image-137-1024x554.png\")
<\/p>\n
The 11-Step Schema Discovery Pipeline<\/p>\n
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.<\/p>\n
![\"Schema](\"https:\/\/www.europesays.com\/ai\/wp-content\/uploads\/2026\/07\/image-138-1024x481.png\")
<\/p>\n
Figure 1: Schema Discovery Agent \u2014 full 11-step pipeline flow<\/p>\n
Step 1: Schema Scanner<\/p>\n
The pipeline begins by querying Oracle system views \u2014 ALL_TABLES, ALL_VIEWS, ALL_MVIEWS, ALL_TAB_COLUMNS, ALL_CONSTRAINTS, and ALL_DEPENDENCIES \u2014 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.<\/p>\n
Outputschema_metadata.json \u2014 a complete structural inventory of all database objects<\/p>\n
Step 2: Data Profiling<\/p>\n
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.<\/p>\n
Outputtable_samples.json \u2014 real sample values per column, used to enrich LLM prompts in Step 5<\/p>\n
Steps 3 & 4: Graph Building and Louvain Community Detection<\/p>\n
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.<\/p>\n
Understanding the Louvain Algorithm<\/p>\n
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 \u2014 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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
\u2022\u00a0 Nodes are strongly connected inside their own group<\/p>\n
\u2022\u00a0 Nodes are weakly connected to other groups<\/p>\n
\u2022\u00a0 The algorithm finds this structure automatically \u2014 no need to specify the number of clusters upfront<\/p>\n
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.<\/p>\n
Outputgraph_domains.json \u2014 schema objects partitioned into structural communities based on FK and view dependency relationships<\/p>\n
Step 5: LLM-Powered Object Profile Generation<\/p>\n
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.<\/p>\n
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 \u2014 something no amount of schema inference could tell you without looking at the data.<\/p>\n
Outputtable_profiles.json \u2014 structured plain-English profiles for all objects, used for embedding in Step 6 and domain naming in Step 9<\/p>\n
Steps 6 & 7: Embedding Generation and Semantic Clustering (K-Means)<\/p>\n
K-Means Clustering<\/p>\n
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.<\/p>\n
How It Is Applied Here<\/p>\n
Each object profile from Step 5 is embedded using Oracle OCI\u2019s Cohere embed-english-v3.0 model. The resulting vectors capture the semantic meaning of each table \u2014 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.<\/p>\n
These two methods \u2014 Louvain and K-Means \u2014 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.<\/p>\n
Outputsemantic_clusters.json \u2014 objects grouped by semantic (business meaning) similarity<\/p>\n
Step 8: Cluster Merge Engine<\/p>\n
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.<\/p>\n
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 \u2014 0.75 by default \u2014 we combine both groups.<\/p>\n
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.<\/p>\n
Outputfinal_domains.json \u2014 unified domain clusters combining structural and semantic relationships<\/p>\n
Step 9: Naming the Domains \u2014 Generative AI at Work<\/p>\n
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.<\/p>\n
To name each domain, we create a prompt that includes the grouped database objects and their short data profiles. The LLM proposes a 2\u20133 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 \u2014 looking at actual data values helps confirm what each cluster truly represents.<\/p>\n
\u2022\u00a0 Customer Management<\/p>\n
\u2022\u00a0 Order Processing<\/p>\n
\u2022\u00a0 Inventory & Logistics<\/p>\n
\u2022\u00a0 Financial Transactions<\/p>\n
\u2022\u00a0 Healthcare Records<\/p>\n
Outputbusiness_domains.json \u2014 each domain cluster named with a meaningful business label<\/p>\n
Steps 10 & 11: Closing the Loop \u2014 Applying Comments Back to the Database<\/p>\n
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 \u2014 COMMENT ON TABLE and COMMENT ON COLUMN statements.<\/p>\n
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 \u2014 without any manual documentation effort.<\/p>\n
OutputOracle DB updated with COMMENT ON TABLE and COMMENT ON COLUMN for all 380 objects and 4,200+ columns<\/p>\n
Running the Agent: Pipeline Dashboard<\/p>\n
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.<\/p>\n
![\"Schema](\"https:\/\/www.europesays.com\/ai\/wp-content\/uploads\/2026\/07\/Picture4-4.png\")
<\/p>\n
Figure 2: Schema Discovery Agent pipeline dashboard \u2014 completed 11-step run against TECPDATP01<\/p>\n
The dashboard provides real-time step status (idle \u2192 running \u2192 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.<\/p>\n
The Output: Business Domain Classification<\/p>\n
After the full pipeline completes, the schema is classified into named business domains. Below is the breakdown for TECPDATP01 \u2014 380 objects across 12 business domains, each with a distinct business identity:<\/p>\n
![\"Schema](\"https:\/\/www.europesays.com\/ai\/wp-content\/uploads\/2026\/07\/Picture5-2.png\")
<\/p>\n
Figure 3: Business domain classification \u2014 380 tables classified into 12 business domains<\/p>\n
Industry-Specific Ontology Examples<\/p>\n
This pattern becomes especially powerful when enriched with industry-specific context.<\/p>\n
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 \u2014 the same first-class concepts that anchor deep business semantics like credit risk, liquidity status, and capital adequacy in Oracle\u2019s financial services data models.<\/p>\n
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 -\u2014 the same first-class concepts that anchor deep business semantics for projects, assets, and delivery performance in construction-aware data models.<\/p>\n
The business domain layer adapts to whatever industry vocabulary the enterprise uses.<\/p>\n
.<\/p>\n
Knowledge Graph as the Next Evolution :- (NL2SQL accuracy improves when the agent understands the customer\u2019s industry language, not just the physical schema.)<\/p>\n
Looking ahead, the relationship graph built in Steps 3 and 4 \u2014 tables, views, foreign keys, and dependencies as a connected network \u2014 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.<\/p>\n
Connecting Back to the NL2SQL Agent<\/p>\n
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\u201342 tables within that domain.<\/p>\n
For example, when an executive asks: \u201cWhich customer accounts increased revenue but decreased profitability in APAC this quarter?\u201d \u2014 the NL2SQL agent identifies this as a Financial Transactions + Customer Management query and scopes its schema context to those two domains only. The result:<\/p>\n
\u2022\u00a0 Faster response \u2014 smaller schema context means faster LLM reasoning<\/p>\n
\u2022\u00a0 Higher SQL accuracy \u2014 fewer irrelevant tables means fewer wrong joins and hallucinated columns<\/p>\n
\u2022\u00a0 No token overflow \u2014 even 380-table schemas fit comfortably within LLM context limits<\/p>\n
\u2022\u00a0 Self-documenting database \u2014 Oracle comments mean the schema explains itself to every tool that queries it<\/p>\n
Conclusion<\/p>\n
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.<\/p>\n
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.<\/p>\n
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 \u2014 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.<\/p>\n
This creates a foundation where AI is not just querying tables
It is querying connected business domains<\/p>\n","protected":false},"excerpt":{"rendered":"Introduction In our previous blog \u2014 NL2SQL Agent: An MCP-Powered Natural Language Insights for Enterprise Data \u2014 we…\n","protected":false},"author":2,"featured_media":96775,"comment_status":"","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[405,7537,7342,7343],"class_list":["post-96774","post","type-post","status-publish","format-standard","has-post-thumbnail","category-agentic-ai","tag-ai-agents","tag-artificial-intelligence-agents","tag-oracle-ai","tag-oracle-cloud-infrastructure-oci"],"_links":{"self":[{"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/posts\/96774","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/comments?post=96774"}],"version-history":[{"count":0,"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/posts\/96774\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/media\/96775"}],"wp:attachment":[{"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/media?parent=96774"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/categories?post=96774"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.europesays.com\/ai\/wp-json\/wp\/v2\/tags?post=96774"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}