Ontology-Driven NL2SQL: Semantic Governance Is the Real Prize, Not Accuracy

Reality Pain Points: Why NL2SQL Fails Without Semantics

A retail enterprise with three core tables— dim_customer, fact_order, dim_product —asks: "Last month's average order value for diamond customers in the beauty category in South China, compared to the month before?" A vanilla LLM-to-SQL system makes four typical errors:

Type 1 – Wrong metric formula: Implements "average order value" as AVG(pay_amount) (average per order) while the enterprise definition is total sales / distinct customers. Results can differ by 2–3×.

Type 2 – Wrong dimension values: "South China" in LLM common sense includes Guangdong, Guangxi, Hainan, Fujian, but the company assigns Fujian to East China. This private convention is unknown to the LLM.

Type 3 – Wrong time boundaries: "Last month" could mean calendar month or rolling 30 days; month‑start batch runs may not have complete data; year‑over‑year denominator definition is ambiguous.

Type 4 – Missing status filters: fact_order.status contains paid, cancelled, refunding orders; revenue should only count paid. This business rule lives in no column comment.

None of these are SQL syntax errors; they are semantic gaps. The bottleneck is not SQL generation but semantic understanding—hence ontology-enhanced (Ontology-Enhanced) approaches.

Four Architectural Routes for Intelligent Querying

Route 1 – Pure LLM: Schema stuffed into prompt. Zero modeling cost, covers arbitrary questions. Suffers from context overflow, hallucinations, unstable metric correctness.

Route 2 – Retrieval / Schema Linking (+ fine‑tune): Recalls relevant tables/columns first, shrinks search space. Industry mainstream; boosts SQL accuracy but cannot align business metric definitions or ensure cross‑domain semantic consistency.

Route 3 – Classic OBDA / Semantic Mapping: NL → SPARQL → ontology mapping → SQL. Deterministic, auditable, metrics fixed by ontology. Queries outside ontology coverage fail; ontology construction/maintenance effort is huge.

Route 4 – Ontology‑Enhanced Hybrid: LLM handles intent understanding, schema linking, SQL drafting; ontology supplies retrieval candidates, expands metric definitions, validates results, provides few‑shot examples. Balances generalization and accuracy; traceable query chain. Highest system complexity; requires mature ontology assets.

Ontology-enhanced NL2SQL essentially rewrites querying from "one translation" to "two translations": ontology owns the first translation (semantic parsing: disambiguation, mapping, metric completion), LLM owns the second (SQL generation). Determinism goes to ontology, fuzziness to LLM.

Offline Construction: How to Model the Ontology

Seven Pragmatic Components

Engineering‑ready ontology comprises seven pieces (not academic OWL):

Meta: version, domain, owner, description.

Entity Mapping: Registers physical tables; grain (e.g., one row per order) defines aggregation boundary.

Relationship: Explicit join paths with cardinality (1:N, N:1) to prevent wrong/missing joins.

Metric: Highest‑value component. A metric is not a name but a formula: e.g., "sum of pay_amount for paid orders". Includes synonyms, filters, owner (governance handle).

Dimension: Hierarchy expansion ("South China" → province list) and concept‑to‑physical value mapping ("DIAMOND" string for diamond tier). Open dimensions enumerate known values; new values trigger alerts.

Rule: Executable assertions for validation layer to intercept bad SQL.

Vocabulary: Registers ambiguities (e.g., GMV differs between ops and finance) with explicit "needs clarification" tags.

Derived Metric: Composes complex queries from existing metrics to avoid duplicate definitions.

Construction Pipeline: LLM Extraction + Human Review

Inputs: DB schema + comments, historical SQL repository (verified queries from BI), business documents (metric definitions, meeting minutes), warehouse modeling docs. Output: YAML/JSON/graph‑stored lightweight domain knowledge base (semantic layer + synonym dictionary + dimension hierarchy + metric catalog).

Step 1 – Candidate Metric Extraction: Feed historical SQL to LLM, extract SELECT aggregations, cluster (e.g., 300 SQLs yield 47 SUM(amount)/COUNT(DISTINCT customer_id) → "avg order value" candidate). Historical SQL is the densest semantic sediment.

Step 2 – Vocabulary Merging: Synonym clustering ("avg order value / ATV / per‑capita spend") via embeddings + LLM discrimination, human reviews borderline cases.

Step 3 – Dimension Hierarchy Building: Auto‑induce hierarchies from dimension table data (GROUP BY region/province/city, verify tree structure). Anomalies (province appearing in two regions) go to human adjudication—exposing data governance debt.

Step 4 – Metric Ratification: LLM drafts metric definitions (name, formula, filters, applicable dimensions); data team reviews and signs. Owner field mandatory; ownerless metrics are "orphan assets".

Step 5 – Continuous Operations: Weekly "drift detection": new SQL patterns not covered by current ontology trigger alerts and augmentation.

60–70% of project effort is this governance engineering, accelerated by AI.

Storage Formats: YAML vs OWL vs Property Graph

Engineering YAML/JSON (most common): Human‑readable, editable. Example shows metric "avg_order_value" with formula, filters, synonyms, owner.

Academic OWL/Turtle: Formal axioms & reasoning; suitable for consistency checks but rarely used in production query path—only as documentation spec.

Property Graph (Neo4j): Unifies instances ("Zhang San = diamond customer") and concepts ("diamond definition") in one graph; enables graph traversal when ontology must carry both.

Pragmatic rule: YAML/JSON suffices for most query systems; replace OWL reasoning with programmatic validation functions. Property graph only when instance‑concept density demands graph queries. Over‑engineering OWL is "technical self‑indulgence".

Six Modeling Pitfalls

Versioning & Governance: Ontology is a "semantic contract"; must be versioned (semver: breaking metric change → minor, new concept → patch). Every change via PR + review; regression test suite mandatory to prevent silent rot.

Service Exposure: Move from prompt injection to tool calls (MCP etc.): list_metrics(), get_metric_definition("avg_order_value"), expand_dimension("South China"), validate_sql(sql). Turns ontology into enterprise data infrastructure.

Avoid Over‑Modeling: Build Minimum Viable Ontology—only high‑frequency, formalizable, owned concepts; long tail left to LLM fallback.

Don't Treat LLM Extraction as Fully Automatic: Extraction automates candidates; "who owns the definition" remains human. Auto‑generate 100 candidates, business owner must ratify.

Prevent Schema Decoupling: New tables/columns without ontology updates cause systemic, silent errors. Drift detection and quarterly audits required.

Clear LLM/Ontology Division of Labor: Ontology maximizes value via "retrieve + validate" sandwich: upstream retrieval (hit concept → context), downstream validation (intercept errors). LLM handles language understanding; ontology anchors semantics.

Online Execution: How the Ontology Is Applied at Query Time

1. Semantic Parsing

Core: Schema Linking (mention → concept). Input: user query. Output: structured semantic representation (hit concepts, intent, slots). Three sub‑tasks:

Step 1 – Candidate Recall (recall‑first): NER + embedding bidirectional recall maps query mentions to ontology concepts, hitting alias layer. Over‑recall acceptable; disambiguation next.

Step 2 – Ontology‑Constrained Disambiguation (precision‑first): When multiple candidates match, use ontology relevance/exclusion constraints + query context to rerank. Question decomposition splits complex queries before linking.

Step 3 – Concept → Physical Column Mapping: OBDA mapping tables ground selected concepts to concrete tables/columns/join conditions. Deterministic mapping enables auditability.

Intermediate product example: "avg_order_value" linked with 0.97 confidence; "YoY" recognized as compare_period_over_period intent; time slots parsed to 2026‑07 vs 2026‑06.

2. Context Assembly

Decides what fits in the limited context window. Strategies:

Schema Pruning: Only tables/columns involved in hit concepts (e.g., three tables in example). Pruning itself disambiguates—LLM cannot join irrelevant tables.

Join Path Injection: Shortest join path from ontology entity subgraph written directly into prompt ("dim_customer JOIN fact_order ON customer_id, fact_order JOIN dim_product ON product_id").

Metric Definition Injection: Full metric formula, filters, applicable dimensions, and explicitly inapplicable dimensions (e.g., ratio metrics not valid at daily grain).

Dimension Value Expansion: "South China" expanded to province list per ontology hierarchy, eliminating LLM common‑sense guesses.

Few‑Shot Retrieval: 2–3 verified query‑SQL pairs from curated library (itself part of ontology assets).

3. SQL Generation

Hybrid approach: high‑frequency patterns (top metrics × top dimension combos) use IR compilation for determinism; long‑tail queries fall back to end‑to‑end LLM.

4. Validation & Correction (Three Tiers)

Tier 1 – Ontology Rule Validation (compile‑time): Pattern‑match SQL metric expressions against ontology formulas. LLM writing AVG(pay_amount) for avg order value mismatches ontology → reject with correct formula. Missing status='PAID' filter violates "refunds excluded" rule.

Tier 2 – Execution Validation (runtime): Syntax check, EXPLAIN plan (full scan? join order?), empty result detection (likely wrong filter → auto‑retry).

Tier 3 – Result Plausibility Validation (semantic): LLM judges magnitude against business common sense ("avg order value 3.8M CNY" → likely grain error). Compare against historical value range for same metric; flag outliers.

Example outcome: metric expression matches ontology; status='PAID' present; dimension values match expansion; execution returns two non‑null values within historical range (300–500 CNY) → pass. Final answer: "July avg order value 412 CNY, June 389 CNY, up 5.9% MoM."

Correction follows "minimum intervention": patch expression if possible; else return error to generator for retry (max 2 retries); then degrade to user clarification ("Do you mean A or B?"). Admitting uncertainty is a reliability prerequisite.

Future Trends: Critical Technical Review

Bottleneck is missing private semantic knowledge; ontology is the engineering form to carry it explicitly. Few‑shot and fine‑tuning compress knowledge into parameters (implicit, unauditable, non‑reusable). Ontology externalizes as assets (explicit, versioned, cross‑model reusable). When swapping foundation models, ontology assets remain intact—decoupling assets from models, long‑term more valuable than any single accuracy bump.

Four Structural Defects

Construction Cost Paradox: Quality ceiling set by ontology quality, but high‑quality ontology needs domain experts + data engineers long‑term. Knowledge acquisition bottleneck persists; LLM assists (cost down 10×) but doesn't vanish. Small teams often build only "metric + synonym" skeleton, reduced effectiveness.

Coverage Ceiling: Ontology only covers high‑frequency, explicit, formalized semantics. Ad‑hoc analysis ("check last week's campaign repeat purchase") and fuzzy exploration ("this data feels off") fall outside, still relying on LLM guesses. Ontology lifts score from 60 to 85; last 15 points are hardest to formalize, diminishing returns.

Ontology Rot: Business change → schema change → ontology must sync, else it gives wrong answers silently. Worse: ontology‑backed errors carry "authority", users lose skepticism. A stale ontology is more dangerous than none—it upgrades random errors to systemic misdirection. Mitigation = heavy ops (drift detection, annual reviews, error feedback), looping back to cost problem.

Tacit Knowledge Boundary: Much business semantics is organizational tacit knowledge: "high‑value customer" defined differently by marketing vs risk, both sides think theirs is obvious. Forcing unification surfaces politics, not technology. Practice often ends with two metrics each with own owner—conflict moved from language layer to governance layer, not eliminated.

Paradigm Shift: From Prompt Injection to Tool Interface

Current architecture injects ontology into prompt—transitional. Prompt is crowded, token‑limited, ephemeral. Emerging shift: ontology becomes the tool interface itself. Semantic layer (ontology's engineering incarnation) exposed via standard protocol (MCP etc.) as tools: list_metrics(), get_metric_definition("avg_order_value"), expand_dimension("South China"), execute_metric_query(metric, dims, filters). Agent plans on tool interface (discover metrics → fetch definition → execute query → validate) instead of translating inside prompt.

Pushing this to its logical conclusion yields a counter‑intuitive outcome: the proposition "Ontology‑Enhanced Intelligent Querying" itself dissolves. When semantic layer becomes standard enterprise infrastructure and standard tool in agent ecosystems, "querying" is no longer a separate system needing special enhancement—it's the natural result of any agent calling data tools. Ontology‑enhanced querying is a necessary transition phase; its greatest legacy is not query accuracy but the batch of enterprise semantics forced to become explicit, assetized, and governed.

Narrative Bubbles to Pierce

"Ontology reasoning brings capability leap": Production mainline rarely runs OWL description‑logic reasoning; LLM in‑context ability replaces it. "Reasoning engine" mostly lives in architecture diagrams.

"Build once, benefit forever": Ignores ongoing maintenance cost; real cost curve ~40% build, 60% continuous ops.

"Ontology makes system understand business": System doesn't "understand"; it retrieves structured definitions. Calling retrieval "understanding" is marketing covering engineering fact.

"Fully automated ontology construction": LLM extraction automates candidates; but ratification (who decides, how conflicts resolved) is inherently human, unautomatable.