On a small database, with clear names and few relationships, an AI model can turn a question into a plausible SQL query. But when the schema contains hundreds of tables, historical views, internal abbreviations and different join paths, writing SQL is only the final part of the process.
First, the system needs to understand which parts of the database are relevant, what they mean to the business and how they can be connected without distorting the result. Then it needs to verify that the query is not only executable, but also consistent with the question and the access rules.
This is the difference between a demo that generates code and a text-to-SQL system that can be evaluated for real-world use.
In brief: on complex databases, asking a model to generate SQL is not enough. At a minimum, you need context selection, schema linking, an understanding of relationships, business definitions, validation and controlled execution.
Why 500 tables change the problem
“500 tables” is not a universal technical threshold. It is a concrete way to describe what happens when a schema can no longer be treated as a list to paste into a prompt.
A business database may contain:
- operational tables and historical copies;
- views created for specific reports;
- abbreviations understood only by people familiar with the business software;
- columns with similar names but different meanings;
- relationships declared in the database and joins used only in application code;
- duplicate master data tables or ones specific to individual business areas;
- business rules that do not appear in the schema;
- customisations that vary by customer or software version.
If all this context is fed indiscriminately to the model, it adds noise and makes it harder to identify the right objects. But if the context is narrowed too far, the very table, relationship or rule needed to answer the question may be missing.
The central challenge therefore becomes retrieving the right context for that question, not showing the model everything that exists.
Five ways AI can produce valid SQL and a wrong answer
1. It selects a plausible table, not the authoritative one
Imagine a business application containing ORD_T, FAT_T, MOV_MAG, V_RICAVI and a historical view called RICAVI_OLD. When asked “Which customers generated the most revenue?”, several objects may seem relevant.
The right choice depends on the business meaning of “revenue”, the period, document statuses and the source considered official. The most intuitive name is not necessarily the canonical source.
This is a schema linking problem: connecting the concepts in the question to the correct database objects before generating the query.
2. It interprets an abbreviation without knowing its local meaning
In real databases, names such as CD_CL, TIP_DOC, FL_AN, DT_EV or QTA_UM2 do not explain what they represent on their own. A model can make a reasonable guess, but a guess is not a definition.
Values can be opaque too. A field containing STATO = 'C' might mean closed, confirmed, posted or cancelled. SQL syntax does not resolve the ambiguity.
Descriptions, controlled examples, documentation and domain knowledge are needed. If the meaning remains uncertain, the most reliable behaviour is not to guess: it is to ask for clarification or flag the limitation.
3. It chooses a possible join, but not the correct one
Two tables can be connected in more than one way. A query can pass a syntax check, execute and return rows without using the appropriate join path.
The risk increases with one-to-many and many-to-many relationships. Joining headers, line items, transactions and master data without considering granularity can duplicate amounts and inflate a total. In other cases, a filter applied after the join removes records that should have remained in the analysis.
To avoid these errors, the system needs to understand not just the keys, but also:
- relationship cardinality;
- allowed join paths;
- the granularity of each table;
- preferred views or models;
- exceptions that can cause duplication.
4. It translates the sentence well, but not the business rule
Terms such as revenue, booked orders, active customer, margin or product sold do not always have a single definition.
The schema tells you where amounts, dates and statuses are stored. It does not necessarily tell you which combination the management accounting team uses, which documents should be excluded or how to handle returns and credit notes.
This layer of meaning can be made available through descriptions, glossaries, verified queries, rules and a semantic layer. The point is not to add generic text to the prompt, but to connect definitions to the data and keep them up to date.
5. It checks that the query runs, not that the answer is correct
An executable query is not automatically a correct query. It can use existing tables and columns, return a plausible result and answer a different question from the one the user asked.
This is why text-to-SQL systems should be evaluated on their results and the operational reasoning that produced them. Research in this area and benchmarks such as BIRD and LiveSQLBench emphasise execution and matching results, not just similarity between SQL strings.
In production, evaluation should include real questions, expected results, ambiguous cases and regression tests. The useful question is not “did it generate a query?”, but “did it use the sources, relationships and rules we would have approved?”.
What you need beyond the prompt
A reliable system for complex databases is more than a single model. It is a pipeline in which each step reduces a different class of error.
1. Schema onboarding and cataloguing
The first step is to explore the database and build a usable representation of its tables, columns, types, keys and relationships. Where possible, this foundation should be enriched with descriptions, synonyms, controlled sample values and guidance on canonical sources.
Onboarding is not something to forget after the initial setup. If the schema, views or business application customisations change, the context must be updated and versioned.
2. Selective context retrieval
When a user asks a question, the system needs to narrow the search space: identify candidate tables, retrieve relevant relationships and include only the information useful for the query.
This step reduces noise and makes a large schema more manageable. However, it must also be able to broaden the search when the initial set of tables is insufficient, rather than forcing an answer from incomplete context.
3. Schema linking and join planning
Before writing SQL, the metrics, entities, filters and time periods in the question need to be linked to actual database objects. In complex cases, it is useful to plan the join path and check the granularity of the sources.
If several plausible paths exist, the system should compare them, apply known rules or ask for confirmation. Hiding ambiguity behind a well-formatted query makes the result riskier, not more reliable.
4. Semantic context and business rules
Business definitions need to be available when the question is interpreted. They may include:
- approved formulas for KPIs and metrics;
- synonyms used by users;
- statuses and codes to include or exclude;
- geographical or organisational hierarchies;
- mandatory filters;
- previously validated reference queries.
This context does not replace the schema: it makes the schema understandable in terms of how the organisation uses its data.
5. Generation, validation and controlled execution
Only at this point is the query generated. Before execution, checks can be applied to:
- SQL syntax and dialect;
- the existence of tables and columns;
- permitted operations;
- the user’s identity and permissions;
- row, time and resource limits;
- the presence of mandatory filters;
- consistency with approved joins and metrics.
Execution can also provide useful signals: errors, empty results or unusual distributions can trigger a correction, a clarification request or a human review. Not everything has to be resolved automatically.
6. Observability, feedback and regression testing
A system that works today can change its behaviour after a schema, rule or model update. Logs, context versioning, a record of corrections and a set of reference questions to rerun over time are essential.
The most useful feedback is not a generic thumbs-up. It is knowing which table, join, filter or definition caused the error and turning the correction into reusable knowledge.
An example: “net revenue by customer over the last 12 months”
Consider an illustrative example, not a DataTalk test or a customer case study.
A business application database contains headers and line items for orders, invoices and credit notes, along with customer master data, exchange rates, inventory transactions and historical views. The user asks:
“Show me net revenue by customer over the last 12 months, compared with the previous 12 months.”
A SQL generator needs to make decisions that the sentence does not spell out:
- should it use orders, issued documents or shipped goods?
- which statuses make a document valid?
- should credit notes be deducted by issue date or linked to the original invoice?
- are amounts already net of discounts and taxes?
- how should different currencies be handled?
- does “customer” mean the account holder, the group or the ship-to customer?
- does the comparison use rolling months or calendar years?
The final query may be long, but its length is not the main challenge. The challenge is choosing the right definitions and sources before writing it.
A credible workflow should therefore:
- identify the metric, entities and time periods;
- retrieve candidate sources and net revenue rules;
- check relationships and granularity;
- ask for clarification on unresolved ambiguities;
- generate the query;
- validate it and execute it with limited permissions;
- present the result, filters and provenance in a verifiable way.
How to test a text-to-SQL system on a real database
A demo on three well-named tables mainly measures the model’s ability to write SQL. Assessing its suitability for a business database requires a more representative pilot.
Pilot checklist
- Choose a database with complexity similar to production.
- Define 20–30 real questions, not ones created solely for the demo.
- Include simple, multi-table and ambiguous requests, as well as questions that cannot be answered without clarification.
- Prepare reference results or queries approved by the data team.
- Check the correctness of sources, joins, filters and metrics.
- Test two roles with different access scopes.
- Include cases involving legacy tables, similar names and coded values.
- Measure how many corrections require changes to the context, not just the prompt.
- Repeat the tests after a schema or configuration change.
- Define when the system should answer, ask for clarification or abstain.
This approach makes it possible to compare in-house development and integration against observable criteria. If you are evaluating both options, you can also explore what integrating AI into business software involves compared with building it in-house.
Where DataTalk API fits in
DataTalk API is designed for software vendors, SaaS providers, system integrators and technical teams that want to add natural language querying to products, internal tools and AI workflows.
The approach goes beyond SQL generation. During onboarding, DataTalk explores the database structure and builds context the agent can use. When a question arrives, the system searches for relevant information rather than treating the entire schema as a single block.
Quality still needs to be verified against the real use case. Specific definitions, permissions, validation criteria and execution methods must be agreed and tested on the customer’s database and workflow.
The best starting point is not a generic question. It is a request that currently takes an analyst time to answer, together with the queries or results considered correct.
Want to see how AI handles a database with real tables, abbreviations and relationships? Explore DataTalk API and prepare a representative set of questions for the pilot.