Why AI-Generated SQL Isn't Enough: Semantic Views Bridge the Business Logic Gap

The Semantic Gap in AI-Generated SQL

As Text-to-SQL, ChatBI, and Data Agents mature, users increasingly query enterprise data via natural language. However, generating syntactically valid SQL does not guarantee business-correct answers. The same term — "sales" — may mean order price, discounted amount, actual payment, or recognized revenue. Multiple plausible join paths can exist between tables. Critical context — which rows to exclude, the grain of aggregation, which dimension state matches a fact's timestamp — is rarely captured in database schemas and cannot be reliably inferred from table and column names alone.

In traditional BI this manifests as inconsistent metric definitions across reports, SQL, and pipelines, preventing a single source of truth. In the AI era the problem amplifies: faced with the semantic gap between physical schema and business language, models may produce executable SQL that violates actual business logic.

Semantic View as an Information Contract

When AI agents become a primary query entry point, the database must not only execute SQL correctly but also supply the context needed for AI to generate business-correct SQL. Mirror Boat (镜舟) therefore designs Semantic View as a first-class database object — an Information Contract that lives on the query execution path. At definition time it captures business terminology, table relationships, primary keys, metric definitions, and filter rules; at query time the system deterministically expands it into SQL.

Unlike external documentation or prompt injections, semantics placed on the execution path become constraints that every query must obey. The same definitions can be reused by AI agents, BI tools, and hand-written SQL, and the expanded SQL remains inspectable.

Four Design Principles

The Semantic View rests on four principles (illustrated in the diagram below). Omitting any weakens the guarantees it provides.

These principles map to distinct responsibilities:

Humans define business meaning and make judgments (e.g., which column represents "actual payment amount").

AI interprets user questions and generates queries.

The database system handles relationship resolution, semantic expansion, and deterministic execution.

Semantic View does not require AI to infer all business rules; instead it shifts as much certainty as possible from query-generation time to definition time.

Concrete Example: Fishing Gear Spend Analysis

The example uses one fact table ( t_item) and two dimension tables ( t_item_type, t_item_brand) to model personal fishing gear purchases.

Defining a Semantic View

CREATE OR REPLACE SEMANTIC VIEW sv_fishing_item_spend
  TABLES (
    items      AS t_item        PRIMARY KEY (id)
      WITH SYNONYMS = ('fishing gear spend', 'purchase record')
      COMMENT = 'one row = one purchase',
    item_types AS t_item_type   PRIMARY KEY (id),
    brands     AS t_item_brand  PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    item_to_type  AS items (item_type) REFERENCES item_types (id),
    item_to_brand AS items (brand)     REFERENCES brands (id)
  )
  DIMENSIONS (
    items.buy_year AS YEAR(items.buy_date)
      WITH SYNONYMS = ('purchase year', 'year'),
    item_types.item_type_name AS item_types.item_type
      WITH SYNONYMS = ('gear category', 'category')
      SAMPLE_VALUES = ['rod','line','hook','accessory','bait','float'],
    brands.item_brand_name AS brands.item_brand
      WITH SYNONYMS = ('gear brand', 'brand', 'make')
      SAMPLE_VALUES = ['Woding','Handing','Liuzhiqiang','Other','Lianqiu','Chuanze']
  )
  METRICS (
    items.purchase_count   AS COUNT(items.id)           WITH SYNONYMS = ('purchase count'),
    items.total_real_spend AS SUM(items.real_amount)    WITH SYNONYMS = ('total amount paid'),
    brands.brand_count     AS COUNT(DISTINCT brands.id),
    items.avg_brand_cost   AS items.total_real_spend / brands.brand_count  -- derived metric
  )
  AI_SQL_GENERATION 'For spend use total_real_spend; for counts use purchase_count.'
  AI_QUESTION_CATEGORIZATION 'Route questions about gear spending, amounts and brand breakdowns to this view.';

Three notable aspects:

Once RELATIONSHIPS declares table links, subsequent queries never need manual joins. SYNONYMS and SAMPLE_VALUES serve the language model, not the database engine. They map user phrases like "purchase year" or "year" to buy_year; "gear brand", "brand", "make" to the brand dimension; and values like "float" or "Handing" to concrete data values. avg_brand_cost is a derived metric defined from other metrics.

Two Query Methods

For the question "In 2024, how much did I actually spend per brand and how many purchases per brand?" a semantic query avoids joins, aggregation functions, and physical column names:

SELECT * FROM SEMANTIC_VIEW(
  sv_fishing_item_spend
  DIMENSIONS brands.item_brand_name
  METRICS    items.total_real_spend, items.purchase_count
  WHERE      items.buy_year = 2024
  ORDER BY   items.total_real_spend DESC
) AS sv;

This form mirrors analytical intent and is easier for language models to produce.

Semantic View can also be read as a flattened wide table, allowing BI tools that don't understand the semantic syntax to reuse the same dimension and metric definitions:

SELECT items__buy_month, items__total_real_spend
FROM sv_fishing_item_spend
WHERE items__buy_year = 2024;

Transparent SQL Expansion

Running EXPLAIN INLINE on the semantic query returns the exact SQL that will execute:

EXPLAIN INLINE
SELECT * FROM SEMANTIC_VIEW(
  sv_fishing_item_spend
  DIMENSIONS brands.item_brand_name
  METRICS    items.total_real_spend
  WHERE      items.buy_year = 2024
) AS sv;

Output:

SELECT `brands`.`item_brand` AS `item_brand_name`,
       SUM(`items`.`real_amount`) AS `total_real_spend`
FROM `test`.`t_item` AS `items`
LEFT JOIN `test`.`t_item_type` AS `item_types` ON `items`.`item_type` = `item_types`.`id`
LEFT JOIN `test`.`t_item_brand` AS `brands` ON `items`.`brand` = `brands`.`id`
WHERE YEAR(`items`.`buy_date`) = 2024
GROUP BY 1;

Auditors can inspect every line of the actual computation. This embodies the "SQL in, SQL out" principle: trust comes from transparent, verifiable execution, not unverifiable promises.

How Semantic Information Guides Model Query Generation

For the question "How much did I spend on Handing brand floats in 2024?" the model resolves each phrase using metadata from the Semantic View:

Without Semantic View, every step is a potential error point:

A column named brand may store brand IDs, not names.

Data values may differ from user terminology. amount and real_amount look similar but represent list price vs. actual payment.

Stronger models can craft plausible-looking SQL from limited clues, but without required business semantics that plausibility remains inference, not certainty.

Series Preview

This first article establishes why AI query generation lacks essential context and why that context must move from query time to definition time. Upcoming parts will cover:

Part 2: What Is Semantic View — full breakdown of the Information Contract: what information is defined, by whom, and why placing semantics on the database execution path turns external reference into mandatory constraint. Also covers Semantic View's role in agent architectures and dual service to AI agents and BI tools.

Part 3: Where Accuracy Comes From, and What Semantic View Does Not Do — explains why query accuracy is not a single model capability but a dependency chain built on business rules, relationships, primary keys, and granularity. Clarifies responsibility boundaries between Semantic View, business context, data modeling, query optimization, and physical acceleration.