Building a Reliable Natural-Language-to-SQL Agent
Generating SQL is easy. Generating the SQL that matches what the business actually means is the difficult part.TL;DRA reliable natural-language-to-SQL system should not send a user’s question directly to an LLM and hope for the best.It should first retrieve the relevant schema, resolve business…
Generating SQL is easy. Generating the SQL that matches what the business actually means is the difficult part.TL;DRA reliable natural-language-to-SQL system should not send a user’s question directly to an LLM and hope for the best.It should first retrieve the relevant schema, resolve business terminology against authoritative metadata, derive joins from documented relationships, account for cardinality and effective-dated data, generate a read-only query, and validate the result before returning it.A few important principles are:Retrieve only the schema relevant to the question.Map business terms to documented fields and definitions rather than model assumptions.Prefer authoritative metadata over naming similarity.Derive joins from documented relationships, not columns that merely look related.Understand relationship cardinality and temporal rules before joining tables.Treat existing application SQL as supporting evidence, not unquestioned truth.Separate syntactic validity, safety, schema validity, and semantic correctness.Surface ambiguity instead of silently inventing a business definition.Keep database execution separate from SQL generation unless execution is explicitly authorized.The hard part of Text-to-SQL is not SQL syntax. It is grounding business language in the database’s actual meaning.A Plausible Query Can Still Be WrongSuppose someone asks an AI system:Show me all active Finance employees who are eligible for remote work.At first glance, this sounds like a simple SQL-generation task. An LLM might immediately produce a query joining an EMPLOYEES table to a DEPARTMENTS table. The SQL might compile. It might return rows. It might even look completely reasonable to an experienced developer. But that still does not mean the query is correct.The real question is not:Can the model write SQL?It is:Can the system translate the user’s business language into the database’s actual meaning?That distinction is the foundation of a reliable natural-language-to-SQL agent.The Problem With “Just Ask the LLM to Write SQL”Imagine a simplified Oracle database containing employee information.A user asks:Find active employees in Finance who are eligible for remote work.The model sees several familiar concepts:employeedepartmentactiveFinanceremote workIt could generate something like:SELECT e.employee_id, e.employee_nameFROM employees eJOIN departments d ON d.department_id = e.department_idWHERE d.department_name = 'Finance'AND e.status = 'ACTIVE'AND e.office_location IS NULL;The SQL looks plausible.The problem is the last condition:e.office_location IS NULLWhy should a missing office location mean that an employee is eligible for remote work?The model invented that relationship.Perhaps the real system contains a separate table:EMPLOYEE_WORK_POLICY--------------------EMPLOYEE_IDREMOTE_ELIGIBLEHYBRID_ELIGIBLEPOLICY_EFFECTIVE_DATEand its metadata says:REMOTE_ELIGIBLEIndicates whether the employee is currently approved for remote work.In that case, REMOTE_ELIGIBLE = 'Y' is the authoritative business definition. The first query may be syntactically correct while answering the wrong question. That is the central problem in natural-language-to-SQL systems.A Better ArchitectureInstead of sending the user’s question directly to an LLM, treat SQL generation as a small reasoning pipeline:User question ↓Identify entities and business terms ↓Retrieve relevant schema ↓Map business terms to authoritative fields ↓Determine documented relationships ↓Generate one read-only SQL query ↓Validate query structure ↓Return SQL with schema evidence and assumptionsEach stage prevents a different type of mistake. The language model still plays an important role, but it works from retrieved facts instead of trying to reconstruct the database from general knowledge.1. Start With a Read-Only BoundaryA natural-language-to-SQL assistant should normally generate queries, not modify data.A simple safety policy can allow:SELECT ...and:WITH ...SELECT ...while rejecting operations such as:INSERTUPDATEDELETEMERGECREATEALTERDROPTRUNCATEThe validator should also consider less obvious cases such as:SELECT ... FOR UPDATEmultiple SQL statements, stored-procedure execution, or constructs that create or modify database objects. This is important, but it solves only the safety problem. A read-only query can still be completely wrong.2. Retrieve Only the Schema You NeedLarge enterprise databases can contain thousands of tables, views, columns, relationships, extensions, and historical objects.Putting the complete database dictionary into the model’s context creates several problems:unnecessary token usage;more similarly named fields to confuse;higher chances of choosing obsolete objects;more opportunities to invent incorrect joins.Instead, retrieve schema incrementally.For our employee question, the first retrieval might search for concepts such as:employeedepartmentactive employeeremote eligibleremote workThat search could return:EMPLOYEES- EMPLOYEE_ID- EMPLOYEE_NAME- DEPARTMENT_ID- EMPLOYMENT_STATUSDEPARTMENTS- DEPARTMENT_ID- DEPARTMENT_NAMEEMPLOYEE_WORK_POLICY- EMPLOYEE_ID- REMOTE_ELIGIBLE- POLICY_EFFECTIVE_DATEThis is far more useful than giving the model hundreds of unrelated HR, payroll, finance, security, and audit tables.The principle is simple:Retrieve the smallest amount of schema that can answer the question confidently.3. Search Business Descriptions, Not Just Column NamesA schema search should not stop at names. Business concepts often appear in metadata descriptions rather than table or column names.Consider the phrase:active employeeThe database might not have a column called:ACTIVE_EMPLOYEEInstead, it might have:EMPLOYMENT_STATUSDescription:Current employment status of the worker.Possible values include ACTIVE, TERMINATED, and LEAVE.Now the agent has evidence for:e.employment_status = 'ACTIVE'The same applies to remote eligibility.If metadata says:REMOTE_ELIGIBLEDescription:Indicates whether the employee is currently approvedto work remotely.then this:wp.remote_eligible = 'Y'is stronger than guessing from:office location;job title;department;manager;address;work schedule.Business descriptions are often where the real semantics live.4. Organization-Specific Tables MatterEnterprise systems rarely consist only of standard vendor tables, especially when organizations extend off-the-shelf applications.Companies add:extension tables;custom attributes;reporting views;integration tables;application-specific metadata;historical compatibility structures.Imagine that employee information comes from standard HR tables, while remote-work approval is stored in a company-specific table:CORP_EMPLOYEE_WORK_POLICYIf the agent searches only the standard employee schema, it may never discover the field that actually answers the question.A practical retrieval sequence can therefore look like:standard database schema;application-specific schema;organization-specific extensions;metadata descriptions;existing application SQL;live metadata inspection, when explicitly authorized.Custom schema is not automatically less trustworthy. For some business questions, it may contain the most authoritative definition.5. Never Guess a Join Because the Columns Look SimilarJoining tables is one of the easiest places for an LLM to produce convincing but incorrect SQL.Suppose the schema documents:EMPLOYEES.DEPARTMENT_IDReferences DEPARTMENTS.DEPARTMENT_IDThat supports:e.department_id = d.department_idLikewise, if:EMPLOYEE_WORK_POLICY.EMPLOYEE_IDReferences EMPLOYEES.EMPLOYEE_IDthen the second join is clear:wp.employee_id = e.employee_idNow imagine two tables both contain:NAMEThat is not enough evidence to write:a.name = b.nameGeneric column names such as NAME, TYPE, CODE, STATUS, or ID are especially dangerous.A reliable agent derives joins from:foreign-key metadata;documented object references;data-dictionary relationships;explicit field descriptions;trusted application queries;verified schema documentation.A join should exist because the schema says the objects are related, not because the column names happen to resemble each other.6. Existing Application SQL Can Be Valuable EvidenceFormal schemas do not always document every implementation detail. Existing code can help.For example, suppose several production reports contain:JOIN employee_work_policy wp ON wp.employee_id = e.employee_idThat does not automatically prove every future query should use the same join. But it is useful supporting evidence.Repositories can reveal important database behavior through:reporting queries;batch jobs;stored queries;integration code;migration scripts;test utilities.The key is to use existing SQL as evidence, not as unquestioned truth. Old code may be obsolete, application-specific, or built around assumptions that no longer apply.7. Separate SQL Validity From SQL CorrectnessA useful mental model is to evaluate SQL at four levels.Level 1: Syntactic validityWill Oracle accept the SQL statement?Level 2: SafetyIs it a read-only query?Level 3: Schema validityDo the referenced tables and columns actually exist?Level 4: Semantic correctnessDoes the query answer what the user meant?The fourth level is the hardest.Consider:e.office_location IS NULLversus:wp.remote_eligible = 'Y'Both may be valid Oracle SQL. Both may reference real columns. Both may return results. But only one may represent the company’s official meaning of “eligible for remote work.” A SQL parser cannot discover that distinction. Schema grounding can.8. The Employee Example, End to EndNow consider the original request:Show all active Finance employees who are eligible for remote work.Step 1: Identify the conceptsThe agent extracts:Entity: EmployeeEntity: DepartmentBusiness terms:- active employee- Finance- eligible for remote workStep 2: Retrieve employee schemaThe system discovers:EMPLOYEESEMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_IDEMPLOYMENT_STATUSMetadata defines EMPLOYMENT_STATUS = 'ACTIVE' as the required employment state.Step 3: Resolve FinanceSchema retrieval finds:DEPARTMENTSDEPARTMENT_IDDEPARTMENT_NAMEwith a documented relationship:EMPLOYEES.DEPARTMENT_ID →DEPARTMENTS.DEPARTMENT_IDStep 4: Resolve remote eligibilityA metadata-description search finds:EMPLOYEE_WORK_POLICY.REMOTE_ELIGIBLEIndicates whether the employee is currentlyapproved for remote work.The schema also documents:EMPLOYEE_WORK_POLICY.EMPLOYEE_ID →EMPLOYEES.EMPLOYEE_IDStep 5: Generate SQLThe grounded query becomes:SELECT e.employee_id, e.employee_name, d.department_nameFROM employees eJOIN departments d ON d.department_id = e.department_idJOIN employee_work_policy wp ON wp.employee_id = e.employee_idWHERE e.employment_status = 'ACTIVE'AND d.department_name = :department_nameAND wp.remote_eligible = 'Y';with::department_name = FinanceThe important achievement is not the SQL formatting.It is how every important part of the query was justified:EMPLOYMENT_STATUS defines “active.”DEPARTMENT_NAME identifies Finance.REMOTE_ELIGIBLE defines remote-work eligibility.both joins come from documented relationships.the external department value uses a bind variable.That is what makes the query reviewable.Prototype: A Schema-Grounded SQL SkillThe prototype turns a natural-language question into a single validated, read-only SQL query.Instead of asking the model to generate SQL directly, the skill first retrieves only the relevant schema definitions, searches field descriptions for business meaning, derives joins from documented relationships, and validates the generated query against safety rules.The skill should also explain the tables, columns, joins, and assumptions behind the SQL so the result can be reviewed before execution.Database execution remains a separate step and should happen only after explicit approval.Prototype File Structurenatural-language-sql/├── SKILL.md├── examples.md├── joins-and-business.md└── scripts/ ├── extract_schema.py └── assert_select_only.pyWhat Each File DoesSKILL.md — Defines when the skill should be triggered, the safety rules, schema lookup order, and the overall natural-language-to-SQL workflow.examples.md — Contains worked natural-language-to-SQL examples, expected outputs, and common failure cases.joins-and-business.md — Documents how to resolve business terms, derive joins from evidence, and handle more complex relationships such as nested structures.scripts/extract_schema.py — Retrieves only the relevant tables, fields, relationships, and business descriptions needed for the current question.scripts/assert_select_only.py — Validates the generated SQL, rejects unsafe or multi-statement queries, and allows only read-only statements.The goal of this prototype is intentionally narrow: generate grounded SQL, explain why it is correct, and keep execution separate. This makes the skill easier to test, review, and improve before connecting it to a live database.9. What the Agent Should ReturnA production-quality SQL agent should return more than a code block. A useful response can contain:SQL — The single read-only query.Business-term mapping"active"→ EMPLOYEES.EMPLOYMENT_STATUS = 'ACTIVE'"Finance"→ DEPARTMENTS.DEPARTMENT_NAME"eligible for remote work"→ EMPLOYEE_WORK_POLICY.REMOTE_ELIGIBLE = 'Y'Join evidenceEMPLOYEES.DEPARTMENT_ID→ DEPARTMENTS.DEPARTMENT_IDEMPLOYEE_WORK_POLICY.EMPLOYEE_ID→ EMPLOYEES.EMPLOYEE_IDAssumptionsAny business meaning or schema relationship that could not be fully confirmed.Bind variablesValues supplied by the user rather than inserted directly into the SQL string.This makes mistakes visible instead of hiding uncertainty behind polished SQL.A Compact ChecklistBefore returning generated SQL, ask:Is the statement a single read-only query?Does every referenced table come from retrieved schema?Does every referenced column actually exist?Were important business terms searched in metadata descriptions?Were application-specific or custom tables considered?Are joins backed by documented relationships?Are stable identifiers used instead of fragile text matching where possible?Are user-provided values represented with bind variables?Are unresolved assumptions clearly identified?Is execution separated from generation?If the agent cannot answer one of these confidently, it should say so rather than inventing the missing information.Key TakeawayNatural-language-to-SQL is not mainly a SQL-writing problem. Modern language models are already good at arranging known tables, columns, filters, and joins into SQL. The difficult part is supplying the right facts.A reliable system therefore spends much of its effort on schema retrieval, business-definition mapping, relationship evidence, safety validation, and assumption tracking.The best SQL agent is not the one that writes a query fastest. It is the one that can explain why every important table, join, and filter belongs there.This story is published under the Generative AI publication. Connect with us on LinkedIn and follow Zeniteq to stay in the loop with the latest AI stories. Let’s shape the future of AI together!Building a Reliable Natural-Language-to-SQL Agent was originally published in Generative AI on Medium, where people are continuing the conversation by highlighting and responding to this story.Source: Generative AI Pub — Published — Category: Image AI