What Nobody Tells You About Schema Complexity When You're Building NLQ
When we first got natural language querying working end to end, the demo was clean. Ten tables, obvious relationships, column names that matched what a business user would actually say out loud. "Show me revenue by region last quarter" turned into a correct SQL query almost every time.
Then we pointed it at a real customer schema. Two hundred and forty tables. Column names like acct_stat_cd and txn_ln_amt_2. Three different tables that all had something called customer_id, none of which meant the same thing. That's when the actual problem revealed itself — and it's not really about generating SQL. It's about figuring out which twelve tables out of two hundred and forty are even relevant to the question in the first place.
I want to walk through the three layers of this problem in the order we ran into them, because each one exposes a different failure mode, and each one needed a different kind of fix.
Layer one: table and column selection
The first wall we hit was simple in hindsight — you can't just stuff a full schema into a prompt and hope the model figures out what matters. Even with generous context windows, dumping 240 tables' worth of DDL in front of an LLM does two bad things. It buries the relevant tables in noise, and it gives the model plausible-looking but wrong tables to hallucinate joins against. A model that's seen orders, order_items, order_archive, and orders_v2 in the same prompt will happily pick the wrong one with total confidence.
The fix here is retrieval, not reasoning. Instead of asking the model to read the whole schema and decide what's relevant, we narrow the candidate set first — surfacing a small, high-confidence subset of tables and columns based on the query — and only then hand that subset to the model for SQL generation. Smaller surface area, fewer plausible wrong answers.
This layer is the most "solved" of the three. Retrieval over schema metadata is a well-understood pattern at this point. It's not perfect, but it's tractable.
Layer two: join path resolution
Getting the right tables doesn't get you the right query. The second wall is figuring out how those tables actually connect — and this is where things get genuinely messy in enterprise schemas.
Foreign keys are often missing, inconsistently enforced, or simply wrong relative to how the data is actually used. A customer_id column might exist in five tables, but only three of those relationships are meaningful for a given business question, and the "correct" join path frequently isn't the shortest one — it's the one that matches how the business actually thinks about the relationship. Two tables might be joinable three different ways, each producing a technically valid but semantically different result. Ask for "customer revenue" and get three different answers depending on which join path you picked, all of them defensible, only one of them right.
This is the layer where naive text-to-SQL systems quietly produce wrong-but-plausible results — the query runs, returns numbers, and nobody notices anything is off until someone cross-checks against a report they trust. That failure mode is worse than an error message, because an error at least gets investigated.
Layer three: naming and semantic drift
The third wall is the one that's hardest to explain to anyone who hasn't lived in enterprise data — the gap between what a business user says and what the schema calls it. "Revenue" might mean net_amt in one table and gross_txn_val in another, and neither name tells you which one the finance team actually means when they say "revenue" in a meeting. Column names drift from the vocabulary people actually use, sometimes because of legacy system naming conventions, sometimes because different teams built different parts of the schema at different points with different conventions.
This is a mapping problem, not a language problem, and no amount of LLM sophistication resolves it on its own — the model can't infer a business definition that was never written down anywhere.
What's actually worked: a semantic layer
The approach that's moved the needle most for us is building an explicit semantic layer — a maintained mapping between business terminology and the underlying schema, sitting between the natural language query and the retrieval/generation steps. Instead of asking the model to infer that "revenue" means net_amt in this context, that mapping is defined once, by someone who actually knows the data, and the pipeline consults it rather than guessing.
Practically, this means the NLQ pipeline isn't just "user question → retrieval → SQL generation." It's closer to: user question → semantic layer lookup for business terms → schema retrieval scoped to what the mapping resolves to → SQL generation informed by both. The semantic layer doesn't replace retrieval or generation, but it constrains both — it narrows what "revenue" could plausibly mean before the model ever has to guess.
The honest caveat here is that this only works as well as the semantic layer is maintained. It's not a model problem you solve once; it's an ongoing curation problem, and it doesn't fully cover the long tail. Ambiguous terms with genuinely multiple valid interpretations still need a clarification step rather than a confident guess — and we're still figuring out where that clarification should happen in the flow. Should the system ask the user? Pick the most common interpretation and flag it? Show multiple interpretations at once? We don't have a settled answer yet.
Where this leaves us
None of these three layers are fully solved, and I'd be skeptical of anyone claiming they are for a schema of real enterprise size. Retrieval handles table selection reasonably well. Join resolution is improving but still produces confidently wrong answers often enough to matter. Naming and semantic drift is the layer that most depends on human-maintained context rather than model capability — and that's probably not going away, because business meaning isn't something you can fully derive from a schema, no matter how good the model gets at reading one.
About the Author
Written by Manikandan
Software Engineer at DataBrain, where we build embedded analytics for B2B SaaS products. I write about the engineering behind enterprise analytics—tenant isolation, SQL generation, AI guardrails, security, and the product architecture required to make customer-facing analytics trustworthy.
If you found this article useful, consider following me here on Medium.
If your team is evaluating embedded analytics vendors or deciding whether to build analytics in-house, check out what a real proof of concept with DataBrain looks like.
Top comments (0)