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.
Schema complexity
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
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:
The relational schema: which tables exist, their columns, data types, join relationships, and any database extensions (e.g. JSON fields, specific functionality).
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
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
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.
Last updated