NL-to-SQL Architecture Evolution

Natural Language Reporting (NL-to-SQL) is a key Generative AI use case where queries are generated with the help of Large Language Models (LLMs) and executed against a structured datastore to generate final insights. This approach enhances Natural Language Processing (NLP)-driven database interactions, making data more accessible to non-technical users.

The NL-to-SQL use case has evolved significantly from an architectural perspective, with various architecture proposed to implement this capability across industries. In this blog, we will explore the architectural evolution of NL-to-SQL, discussing the most promising architecture available today. We will also address the challenges associated with each architecture and how they have evolved to overcome these limitations.

Understanding NL-to-SQL Use Cases

NL-to-SQL use cases can be categorized into two broad personas:

• Internal Facing: Enhancing productivity for database engineers, SQL developers, and operations teams, or enabling internal business users to query structured databases using natural language.
• External Facing: A feature within a product designed for end users/customers, providing an AI-powered database query interface.

A Gen AI-powered feature within web applications or enterprise systems, driven by NL-to-API, is a more robust architectural design pattern compared to directly implementing NL-to-SQL workflows. This is primarily due to security implications, adherence to authorization protocols, and enforcement of business rules and logic. Therefore, NL-to-SQL implementations are far more dependable for internal teams than exposing them to external users.

The following sections talk about different architectures evolved so far.

Architecture 1: Basic Tool-Based Agent Design

Architecture 1: Basic Tool-Based Agent Design

Above architecture depicts very first tool-based agent design for realizing NL-to-SQL based use cases. Here are different steps executed.

1. User asks a question in a natural language #PROMPT
2. SQL Agent gets all the table details (along with description) using “Describe All Tables Tool” and checks with LLM as which all tables relevant for the given user query #TOOL #LLM
3. SQL Agent gets all the schema (column) details & description for the selected tables using “Get Schema & Data Tool” along with few data samples (3–5 rows) from each table and asks LLM to generate a SQL based on the supplied data #TOOL #LLM
4. SQL Agent validates the SQL with the help of “SQL Query Validator Tool” that in turns takes help of LLM to validate the query based on the given dialect. If there are errors, then Agent again asks LLM to regenerate the query #TOOL #LLM
5. SQL Agent executes the query using “Execute Query Tool” to get structured output #TOOL
6. SQL Agent with the help of LLM summarizes the final response for the user #LLM

Pros

• Simple architecture.
• Works well for small databases with a limited number of tables and columns.

Cons

• Does not work well with large databases (many tables and many columns per table).
• Sometimes, generated SQLs are not functionally correct.
• Difficult for LLM to understand business specific rules/jargons to be applied to the SQL.
• Minimum 3–4 LLM calls per query.
• Latency, Tokens, and Cost Per Query is higher.

Refer to LangChain reference implementation for the same. Read more here around agent design with NL-to-SQL implementation details.

Architecture 2: RAG-Based Few-Shot SQL Agent

Architecture 2: RAG-Based Few-Shot SQL Agent

This architecture incorporates Retrieval-Augmented Generation (RAG) within an agentic workflow to address some of the limitations found in Architecture 1.

Building the RAG Index

Prompt-SQL Pair Index

• Stores few-shot learning examples in the prompt to LLM to get better accuracy on SQL generation.
• The quality of SQL query generation depends on the variety of training data covered in this index.

DDL Index

• Contains creation statements for all the tables in scope along with description of table/columns as comments.
• This will provide table & column definitions to LLM

Business Index

• Holds business-related terminologies and documentation to aid the LLM in generating contextually accurate SQL queries.
• This index also contains any documentation in context to the database, tables and columns which will help LLM to generate correct SQLs. For example, join with master_data table to understand any code-decode values.

SQL Agent/Orchestrator

This architecture represents an advanced evolution of the tool-based agent design for NL-to-SQL use cases. The following steps outline how this design functions:

1. Generates embeddings for the user prompt and finds similar records from three given indexes to pass it as part of the prompt to #LLM RAG
   a. 3–5 few-shot examples from prompt-sql pair index.
   b. 7–10 schema definition from #DDLs index
   c. 7–10 business rules or related documentation
2. SQL Agent takes prompt to generate #SQL with the help of #LLM
3. SQL Agent validates the SQL with the help of “SQL Query Validator Tool” that in turns takes help of LLM to validate the query based on the given dialect. If there are errors, then Agent again asks LLM to regenerate the query #TOOL #LLM
4. SQL Agent executes the query using “Execute Query Tool” to get structured output #TOOL
5. SQL Agent with the help of #LLM summarizes the final response for the user #LLM
6. SQL Agent with the help of HUMAN-IN-THE-LOOP will try to continuously improve data in the #RAG index in terms of adding more few-shot examples to improve overall accuracy of the solution #INDEX

Pros

• Improves accuracy with few-shot examples
• Addresses large database challenges to some level
• LLM understands business specific rules/jargons to be applied to the SQL

Cons

• Very large database handling is challenging.
• Retrieval of inaccurate table schemas from DDLs index
• Too many no. of columns of tables could make request run out of context window of LLM.
• Significant initial effort to create quality data in the RAG indexes to cover a variety of scenarios.
• Maintaining the quality of the data in the index.
• 3–4 LLM calls per user query.

Refer to Vanna.ai for reference implementation for the same. There are several other tools such as Wren AI, SQLAI, etc. developed with similar implementation.

Architecture 3: Advanced RAG Based SQL Agent with Human-in-the-Loop

Architecture 3: Advance RAG with Human-in-the-loop with Few-shot SQL Agent

This architecture improves further on previous architecture to have better RAG, breaking large database into logical schemas, and optimizing the number of columns to address some of the limitations of Architecture 2.

Get Gen AI @ Cybage Software Private Limited’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Here is how this architecture works:

Build RAG Index

Prompt — SQL Pair Index

• These would act as a few-shot examples in the prompt to LLM to get better accuracy on SQL generation
• The quality of the LLM generated queries would depend on the variety of data covered in this index
• Add category/intent (more explained below) as a metadata for filtering

Business Index

• This index will contain any business-related jargons to help LLM generate SQLs correctly
• This index could also contain any documentation in context to database, tables and columns which will help LLM to generate correct SQLs. For example, join with master_data table to understand any code-decode values
• Add category/intent (more explained below) as a metadata for filtering

No need to build DDLs/Schema-based index since schema will be pulled based on actual tables acknowledged by the user.

SQL Agent/Orchestrator

The following are the few key aspects of architecture.

Intent Identification & Logical Schemas

• The strategy works well for larger databases where the number of tables is too many.
• The idea is to logically divide the schema (group of tables queried together) and tag it to a category or an intent. The division could happen functionally or technically.
• Based on the user prompt and instructions, LLM helps to pick the right category/intent which could be used for further processing with limited set of tables.

Tables Identification Through Human-In-The-Loop

• Based on the category/intent, user query, few-shot examples from Prompt-SQL pair index, few-shot business documentation/rules and table descriptions, check with LLM to pick right set of tables required to generate SQL for the given user query.
  (Above step could be skipped if the number of tables are manageable for LLM context window to handle)
• Check with user to acknowledge on the table set. Allow user to add/delete the tables from the list to ensure agent has right set of tables to query.

Column’s Optimization

• If there are tables which have too many columns then column optimization could be done with the help of LLM to avoid issues with LLM’s context window limitations.

This architecture enhances NL-to-SQL capabilities by leveraging a Retrieval Augmented Generation (RAG) approach with structured indexing and HUMAN-IN-THE-LOOP validation. The following steps outline how this design functions:

1. Identify intent/category based on user query/prompt #LLM.
2. Identify all the tables associated with the intent based on the intent-table pair index.
3. Generate embedding for the user prompt and find similar records from the given two indexes as few-shot examples. Ensure to filter the data on intent/category metadata #RAG.
   a. 3–5 few-shot examples from prompt-sql pair index.
   b. 7–10 business rules or related documentation
4. SQL Agent, with the help of LLM, identifies various tables to be used for the user query #LLM
5. Get user acknowledgment for all the tables. The user should be able to add or remove tables from the list #HUMAN-IN-THE-LOOP
6. Optimize number of columns for tables with many columns with the help of LLM, if required
7. SQL Agent with help of LLM generates SQL query with the given set of tables, columns, few-shot prompt-sql pair examples, few-shot business documentation/rules, user query and instructions #LLM
8. SQL Agent executes the query using “Execute Query Tool” to get structured output #TOOL
9. SQL Agent with the help of LLM summarizes the final response for the user #LLM
10. SQL Agent with the help of HUMAN-IN-THE-LOOP will try to continuously improve data in the RAG index in terms of adding more few-shot examples to improve overall accuracy of the solution #INDEX

Pros

• Improved accuracy with few-shot examples.
• Tackles large database challenges by organizing data into logical groupings.
• LLM understands business-specific rules/jargon to be applied to the SQL.
• Human-in-the-loop to validate & acknowledge the tables used for generations to improve overall accuracy.
• Table column optimization where there are many numbers of columns to handle.

Cons

• Significant initial effort to create quality data in the RAG indexes to cover a variety of scenarios.
• Maintaining the quality of the data in the index over time.
• Complex Architecture.
• 3–4 LLM calls per user query.

Final Thoughts & Recommendations

Architecture 3 represents the most promising approach currently available. The architecture is inspired from a leading enterprise, Uber, as highlighted in their QueryGPT system.

Key Takeaways for Adoption

• Evaluate OOTB GenAI-powered tools like AI Smart Assistance (Dbeaver), Microsoft PowerBI AI features, Gemini in BigQuery before proceeding with custom development.
• Prefer NL-to-SQL solutions for internal teams over customer facing applications due to security risks associated with it.
• For customer facing solution, minimize the permissions/accesses to the data by either considering minimum number of tables or by creating separate views as per the use cases.
• Prefer NL-to-API based approach over NL-to-SQL in a GenAI powered application, wherever possible
• In agent designs, a pre-defined SQL or StoredProcedure invocation could be used as a tool with proper design & considerations.
• Avoid running AI generated SQLs against transactional data store since they may not be performance efficient.
• Proper prompting, HUMAN-IN-THE-LOOP and engineering best practices to be adopted to avoid performance inefficient queries.

Conclusion

We explored different NL-to-SQL architectural patterns, each with its advantages and challenges. When implementing these architectures, it is crucial to carefully evaluate factors such as the user base, database size, table permissions, and HUMAN-IN-THE-LOOP validation.

A robust and preferred approach is to adopt NL-to-API -based solutions, as they offer greater security, scalability, and compliance with business rules. However, with well-defined user access controls, data boundaries, and adherence to best engineering practices and security protocols, a custom NL-to-SQL solution can also be effectively deployed.

We are here to guide and support your journey toward AI-powered database interactions. For more details, contact us.