> For the complete documentation index, see [llms.txt](https://docs.buildings.ability.abb/collection/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.buildings.ability.abb/collection/academy/building-intelligence-ai-and-machine-learning-in-buildingpro-suites/agents/nl2sql/why-nl2sql-is-hard.md).

# Why NL2SQL is hard

Despite significant research progress, NL2SQL remains a challenging problem in practice. The difficulty increases substantially when moving from academic benchmarks (which typically use clean, small schemas with unambiguous questions) to real-world production databases.

What users see in the User Interface of an application as opposed to how the application looks like internally in the database are often two pair of shoes.\
The more these two pairs of shoes differ the harder it is to consolidate a request that is based on the assumptions of one structure with the other.<br>

### **Schema complexity**&#x20;

Real-world databases have dozens of tables with non-obvious naming conventions, implicit relationships, and domain-specific semantics. Column names may be abbreviated, overloaded, or only meaningful in context. Foreign key relationships may be implicit rather than declared. Domain-specific extensions (e.g. TimescaleDB hyperfunctions for time-series data) add syntax that general-purpose models rarely encounter in training.

### Ambiguity

User questions are inherently ambiguous. "Show me energy usage" could refer to current readings, historical aggregations, or a comparison across locations. It does not specify which assets measure energy, what unit to use, or what time range to consider. Resolving this ambiguity requires domain knowledge that goes beyond the database schema.

### **Correctness vs. plausibility**

A generated query can be syntactically valid SQL and still return wrong data. It might join on the wrong key, apply an incorrect aggregation, query the wrong table, or miss a necessary filter. The most dangerous failure mode is a query that executes successfully and returns plausible-looking but incorrect results. Detecting this requires understanding what the correct result should look like which is exactly what the user does not know (otherwise they would not be asking).

### **Evaluation difficulty**&#x20;

Measuring NL2SQL quality is non-trivial. A generated query that differs syntactically from a reference query may still be semantically correct (e.g. different join order, equivalent subquery vs. CTE). Conversely, two queries that produce the same result on a small test dataset may diverge on production data.

## The Challenge of Flexible Data Models

Standard NL2SQL benchmarks (i.e. Spider, BIRD) assume a fixed, known schema.\
The model learns to generate SQL for a specific set of tables and columns.

Many real-world platforms, particularly in IoT and smart building domains, do not have a single fixed schema in this sense. They have a **relational schema** (the database tables themselves) plus a **project-specific data model** defined by the content of those tables. Assets, their types, their attributes, their hierarchical relationships, and their telemetry structures are all configured per tenant and their portfolio.

This means a NL2SQL system must understand two layers:

1. **The relational schema**: which tables exist, their columns, data types, join relationships, and any database extensions (e.g. JSON fields, specific functionality).
2. **The instance-level data model**: which asset types are configured, what attributes they define, how assets are organized hierarchically, and what telemetry data is actually available. All of which varies between projects.

A model that has learned the relational schema perfectly can still fail if it does not know that "energy usage" corresponds to an attribute called "energy" on assets of type "EMeter" with subtype "input" - information that lives in the data, not in the schema definition.

### Two Types of Ambiguity

This dual-layer structure introduces two distinct types of ambiguity:

#### **Query ambiguity**&#x20;

The user's question itself is underspecified:

* "Show me data for the building" - which building? What data?
* "Are there anomalies?" - what counts as an anomaly? Which assets? What time range?

#### **Data ambiguity**&#x20;

The database contains multiple valid interpretations:

* Multiple buildings with the same name in different locations
* Multiple asset types that can measure the same physical quantity (e.g. both a dedicated temperature sensor and a room controller may record temperature)
* Hierarchical relationships where the "right" scope depends on context (e.g. energy meters three levels below the building the user asked about)

Resolving query ambiguity requires understanding the user's intent. Resolving data ambiguity requires understanding the project's data model. A pure NL2SQL model, no matter how well trained, can only address the first if it has been given the second as context.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.buildings.ability.abb/collection/academy/building-intelligence-ai-and-machine-learning-in-buildingpro-suites/agents/nl2sql/why-nl2sql-is-hard.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.