Text-to-SQL Systems: Tutorial & Best Practices

Text-to-SQL systems automate two main steps:

1. Query Generation: Creates the SQL statement based on what the user inquires. This step can act as a "SQL copilot" for data engineers.
2. Execution of Queries: The autonomous system executes the generated queries to get the desired data and sends them back directly to the inquirer.

In some systems, only the first step is automatic; a person does the last query check before it is run. When both steps are automated, the system works more like an autonomous agent, writing and running the query without any help from a person. Enterprise-ready text-to-SQL systems should automate these two main steps and improve accuracy based on user feedback.

Text-to-SQL technology has experienced notable advancements. It evolved from rule-based systems with the emergence of large language models (LLMs), which have markedly enhanced the precision and adaptability of SQL query generation from natural language inputs. This article thoroughly explores some of the most important concepts related to this technology, including illustrative examples.

Summary of core text-to-SQL concepts

Challenges of building text-to-SQL systems

Developing production-ready text for SQL systems requires tackling numerous problems. Real-world databases typically have multiple tables with sophisticated relationships. It takes data analysts time to understand the available data and relationships, and writing SQL queries is often an iterative process. Analysts execute trial SQL queries to understand the data model, discover example data, and then refine these queries to answer the original questions about the data. It is difficult to generate accurate SQL queries, especially the first time you encounter a new data warehouse.

Let’s address some of the challenges in building text-to-SQL systems.

Need for the semantic layer

The semantic layer is very important for making it easier to query large databases because it takes away the complexity of basic data and turns it into business terms that provide context. This layer acts as a structured link between the user's natural language queries and the data structures below.

A semantic layer's primary advantage is its capacity to organize data into business definitions. The semantic layer translates business-friendly definitions into queries the database can understand rather than requiring users to know the exact database schema or technical terminology.

For instance, a business user could query, “What are the sales in North America for Q2?” The semantic layer automatically maps the natural language query elements “sales,” “North America,” and “Q2” to the corresponding columns and tables in the database, thereby transforming the query into a precise SQL query.

Ambiguities

Schema understanding and column selection

Early LLM-based systems provided the table schema in the prompt. One of the main issues with this approach is selecting the correct table schemas to include in the prompt, which requires knowledge from the analyst. Even with longer LLM context windows, including the complete database schema with every prompt becomes inefficient and introduces further challenges. This approach relies on the LLM fully understanding the database schema of the user's natural language query. The LLM would need to disambiguate the meaning of terms used within your organization to select the correct tables/columns and deduplicate where multiple tables/columns exist. Typically, these descriptions are intended to provide a brief overview of the contents of tables and columns. However, they do not offer the LLM any actionable instructions regarding using specific tables to generate precise SQL. To make the prompt more practical, structured guidelines that guide the decision-making process for the LLM need to be added.

Query ambiguity

In the text-to-SQL task, ambiguity means that one user query could have multiple semantic meanings based on one table. Given the inherent ambiguity of natural language, resolving ambiguity is one of the most formidable challenges in text-to-SQL systems. A question such as “Show me the top-rated movie” could be related to various columns inside the database, requiring that models detect and resolve these ambiguities to prevent erroneous queries.

Ambiguities are generally categorized into two types: column ambiguity and value ambiguity.

Column ambiguity occurs when a token in the query can be associated with multiple columns. The term “rating” may refer to “IMDB Rating,” “Rotten Tomatoes Rating,” or “Content Rating” in this example. For instance, based on the table below, a possible query is:

SELECT [Movie] ORDER BY [IMDB Rating] DESC LIMIT 1

Need for explainable queries

Another big challenge in building strong text-to-SQL systems is ensuring that responses are deterministic. People expect to get the same answer every time they ask the same question, especially when it's important for business. However, LLMs are not deterministic by nature. This means the same query asked by different users may produce slightly different SQL outputs.

Determinism is very important in business settings where keeping facts consistent is very important. To maintain trust in the system, an expert who asks, "What are the total sales for the last quarter?" must use the same SQL code every time. Text-to-SQL systems need methods like reinforcement learning and feedback loops to make sure that the model's responses stay the same over time to be deterministic.

Need for inbuilt security

Data security, privacy, and governance preservation become increasingly important as text-to-SQL systems are integrated into enterprise environments. These systems must be designed to prevent unauthorized access, data leakage, and breaches as they interact with sensitive data. Role-based access controls (RBAC) ensure that users can only access data they are permitted to view, while query validation mechanisms sanitize inputs to prevent attacks such as SQL injection. Furthermore, encryption and data obfuscation safeguard proprietary data and personally identifiable information (PII). From a governance perspective, audit logs monitor user queries and data access, thereby guaranteeing adherence to data regulations. By incorporating these features into the Semantic and Context layers, this layered approach guarantees that security and privacy are ingrained throughout the data pipeline.

Why semantic layers are not enough for text-to-SQL?

There are still problems with accuracy even after giving the system the cleanest semantic layer. It's still possible for LLMs to make mistakes when writing the right SQL query because normal language is often complicated, and ambiguity is still a problem. If the model doesn't know the difference between "revenue" from different sources (like gross revenue vs. net revenue), a question like "Give me the revenue for last month" might still give you mixed results. Also, the system might have trouble with semantic variations, which happen when different areas use words that mean the same thing.

For many business questions, it's not enough for the system to understand natural language; the model also needs to know the business logic and formulas beneath the words.

For instance, to find a business measure like ARR (Annual Recurring Revenue), you don't just use a SQL query. Instead, you use a formula that combines different database fields, often incorporating more complex logic. This is one way to figure out ARR:

SELECT SUM(contract_value) AS ARR 
FROM contracts 
WHERE contract_type = 'recurring' AND start_date >= '2023-01-01';

However, even this is a simple yet business-context-sensitive formula. More realistic formulas can be much more complex, such as:

SUM(contract_value) for recurring contracts + SUM(contract_value)/3 for perpetual contracts - SUM(contract_value) for churn

Due to this, the LLM needs to know that ARR is a business figure and not just a sum of numbers. To add these business semantics to the text-to-SQL system, you need to make rules and semantic layers that can understand complicated business formulas and instantly figure out things like ARR, CLV, or gross margin.

Manual semantic layer modeling is impractical even for simple datasets. Automating the creation of the semantic layer is a good approach, but its accuracy will depend on the availability of historical data. This article introduces the concept of a context layer to address these challenges with the semantic layer and extend its capabilities. By ingesting all the existing enterprise knowledge into a knowledge graph. For example, data, metadata, queries, schemas, and business jargon can be captured in a knowledge graph that can be continuously updated. Context is added to a user’s query by selecting the right information from the knowledge graph rather than following rules provided by a semantic layer.

{{banner-large-1="/banners"}}

Leveraging LLMs for Enterprise Text-to-SQL

Using large language models such as GPT-4 and Gemini in text-to-SQL systems has revolutionized the conversion of natural language into SQL queries. LLMs excel at comprehending and producing human-like text, allowing them to manage progressively more intricate SQL queries derived from natural language inputs. They surpass conventional techniques by employing sophisticated neural network architectures that learn from extensive datasets, enhancing precision and adaptability in creating SQL queries. This leads to text-to-SQL systems capable of managing ambiguous queries, discerning user intent, and adapting their responses using prompt chaining. Nevertheless, LLMs' accuracy in producing SQL from text isn't 100% yet. To make the system productive and enterprise-ready, additional techniques are needed. Even if LLMs offer greater language interpretation and code generation capabilities. Because of this versatility, organizations can select the strategy that best suits their requirements, be it more accurate, domain-specific SQL creation, or flexibility over a wide variety of queries. possibly not as skilfully.

Fine-tuning vs. in-context learning

Fine-tuning involves training an LLM with additional datasets to adapt to a specific task. Text-to-SQL systems would be fine-tuned using pairs of natural language and corresponding SQL queries and updating the model weights. This approach was more relevant for early language models. The current generation of LLMs show good performance without extensive fine-tuning. This is important because fine-tuning LLMs is costly and time-consuming, and over-training the model may make it difficult to adapt to new information. There are also security implications of updating the model itself with enterprise data.

The emergence of more capable LLMs has led to in-context learning becoming more viable. Instead of fine-tuning, in-context learning is a technique where we use the prompt to adapt the LLM to new tasks. For example, prompt chaining, a method wherein the model deconstructs intricate requests into a sequence of simpler consecutive prompts. This systematic method clarifies ambiguous queries and enhances the level of accuracy of the generated SQL queries. When a user poses an unclear query, the LLM may require more explanation or context to ensure the accurate execution of the query.

Consider a user who inquires about "Show me the revenue from last month and explain the trends." This request has two components: retrieving data and providing an analysis. The LLM determines the primary request and generates a prompt: "Retrieve the revenue for last month."

SELECT SUM(revenue) AS last_month_revenue 
FROM sales 
WHERE sale_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' 
AND sale_date < DATE_TRUNC('month', CURRENT_DATE);

Upon receiving the revenue data, the LLM acknowledges the necessity of addressing the second component of the user's inquiry, which pertains to trends. It may enquire, "What specific trends are you interested in?" For instance, are you seeking comparisons with previous months, growth percentages, or customer segments?

When the user provides more information, such as "I want to compare it to last quarter's revenue," the LLM creates a more complicated SQL query for that analysis.

SELECT  
    SUM(CASE WHEN sale_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '1 quarter' THEN revenue END) AS last_quarter_revenue,
    SUM(CASE WHEN sale_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' THEN revenue END) AS last_month_revenue
FROM sales;

The LLM improves the accuracy and relevance of the query by using prompt chaining, which leads to a more meaningful exchange. This way not only helps unambiguously define what the user wants, but it also improves the flow of information so that the user gets all the information they need.

Retrieving the right context

Named Entity Recognition (NER)

Models frequently dynamically deconstruct queries into related contexts to address ambiguity. For example, the system can identify the entities involved in a query, such as “people” and “locations,” and then generate a query based on the recognized context. This dynamic decomposition ensures that the appropriate SQL query is generated and prevents confusion between similar entities.

Ambiguity resolution

LLMs can scrutinize extensive datasets of natural language inquiries alongside their associated SQL queries to discern trends, resulting in enhanced management of diverse query types. By comprehending syntax, context, and semantics, LLMs facilitate more efficient translation from natural language to SQL.

The LLM could resolve ambiguities in the phrase by checking if the user is referring to an IMDB rating, Rotten Tomatoes score, or content rating based on the context provided by the database (see figure below).

Value ambiguity means a token may correspond to multiple values within a table. A query such as “Show me the data for Jack” could lead to ambiguity if “Jack” is present as both a name in one column and as a product code in another.

A practical solution is achieved by incorporating ambiguity resolution mechanisms, such as sequence labeling or using models like Detecting-Then-Explaining (DTE), as discussed in this paper. These techniques enable text-to-SQL systems to identify and classify ambiguous elements in the user's query, request clarification, or offer potential explanations for the ambiguity. This system is able to more effectively manage and resolve ambiguous spans of text by designating them.

Verification and feedback loops

Though LLMs' capabilities constantly evolve, it's essential to know that the generated SQL for a given prompt may vary with each model iteration. This could cause challenges when implementing text-to-SQL systems across several versions of LLMs, as previously functional queries could show unexpected behavior. A continuous assessment mechanism is essential for the whole system and individual queries. This framework will monitor the model's performance, maintain consistency between versions, and identify any differences that occur during system updates.

Another critical component is that LLMs are engineered to respond to every inquiry, yet not all inquiries can be addressed with SQL. SQL has inherent restrictions, including managing recursive queries, intricate multi-table joins, and hierarchical data structures. Moreover, LLMs are predominantly trained on publicly accessible SQL datasets, frequently consisting of less complex queries. Complex SQL concepts, including fan-trap and chasm-trap scenarios, continue challenging even the most advanced LLMs in generating accurate SQL queries. These pitfalls arise in database schema design and query formulation, resulting in erroneous outcomes when many join, or object relationships are misconstrued.

Leveraging the semantic layer

The semantic layer makes it easier to query large databases because it takes away the complexity of basic data and turns it into business terms that provide context. This layer acts as a structured link between the user's natural language queries and the data structures below.

A semantic layer's primary advantage is its capacity to organize data into business definitions. The semantic layer translates business-friendly definitions into queries the database can understand rather than requiring users to know the exact database schema or technical terminology.

For instance, a business user could query, “What are the sales in North America for Q2?” The semantic layer automatically maps the natural language query elements “sales,” “North America,” and “Q2” to the corresponding columns and tables in the database, thereby transforming the query into a precise SQL query.

Here are some demo tables showing how a semantic layer would translate a business query into an SQL query.

Business query: “What are the sales in North America for Q2?”

Semantic layer mapping:

• “Sales” → SUM(sale_amount) from the Sales table
• “North America” → region_id corresponding to North America in the Region table
• “Q2” → sale_date from the Sales table, cross-referenced with Q2 in the Time table

SQL query generated by semantic layer:

SELECT SUM(s.sale_amount) AS total_sales
FROM Sales s
JOIN Region r ON s.region_id = r.region_id
JOIN Time t ON QUARTER(s.sale_date) = t.quarter AND YEAR(s.sale_date) = t.year
WHERE r.region_name = 'North America'
  AND t.quarter = 'Q2'
  AND t.year = 2023;

The semantic layer's ability to organize raw data into intuitive, business-friendly definitions is demonstrated in this example, enabling users to extract valuable insights without needing technical SQL expertise.

The semantic layer plays a critical role in this scenario by converting business terms such as “North America” and “Q2” into their respective time-based filters and IDs. These terms are automatically mapped to the appropriate database columns, producing a precise and optimized SQL query without needing the user to comprehend the underlying schema.

Optimizing join relationships

Join links are also optimized by dynamic semantic layers, which figure out the best order in which to run them. When you query across multiple tables with complex links, the system looks at the query to figure out which tables should be joined first. This saves time and keeps you from having to scan tables that aren't needed. This optimization makes queries run faster, especially on big datasets where joins that don't work well can slow down response times.

Let’s examine an example of how a dynamic semantic layer can optimize the join operations for a query. This requires the system to efficiently retrieve information by joining tables containing sales data, product information, and geographic regions.

User Query: “What are the total sales of electronic products in the last quarter across North America?”

Step 1: Identify key tables

• Sales table (for sales data and dates)
• Product table (to filter for electronic products)
• Region table (to identify North America)

Step 2: Prioritize the joins based on existing relationships

1. First, join the Sales and Product table on product_id to narrow the results to electronic products.
2. Next, join the Sales table and Region table on region_id to filter for sales in North America.

Step 3: Apply Necessary Filters

Once the relationships are established, the system applies two filters:

1. region_name = North America
2. The sale_date falls within the last quarter of the year (Q4).

SELECT SUM(s.sale_amount) AS total_sales
FROM Sales s
JOIN Product p ON s.product_id = p.product_id
JOIN Region r ON s.region_id = r.region_id
WHERE p.category = 'Electronics'
  AND r.region_name = 'North America'
  AND QUARTER(s.sale_date) = 4
  AND YEAR(s.sale_date) = 2023;

In this case, the dynamic semantic layer not only makes the process easier but also accurate and deterministic, allowing complicated queries with many relationships and filters to be run quickly and correctly.

{{banner-small-1="/banners"}}

Introducing the Context Layer (by WisdomAI)

The Context Layer is a new idea, that is introduced by WisdomAI, that goes beyond the semantic layer. This layer encapsulates the advantages of the semantic layer and also includes the business context by describing tables, columns, or business measures and setting rules for when and how to use them. The semantic layer stores business-level definitions, and the Context Layer tells you how to use them based on certain circumstances. WisdomAI saw a 50% improvement in query execution accuracy using a Context Layer.

The Context Layer is created by automatically ingesting existing knowledge into a large knowledge graph that contains:

• Mined enterprise language context enables the text-to-SQL system to understand business jargon and synonyms. Furthermore, implicit assumptions made within your business can be understood. For example, a sales team may know that “pipeline always means current quarter.” So when querying for “What sales are in the pipeline?”, the Context Layer can translate this into a time-based SQL query for the current quarter.
• Mined SQL patterns provide the text-to-SQL system with measures like table and column popularity, column properties, frequently used filters, and expressions. This provides user data to the text-to-SQL system to increase the likelihood of generating useful SQL queries. By capturing user feedback, the system can learn which questions and queries are most useful.
• Business awareness so that the text-to-SQL system understands your enterprise's fiscal quarters for the financial year, enabling queries like “What were the sales figures from Q1?” to be executed.
• User awareness enables the text-to-SQL system to answer queries like “Show me my deals?” The appropriate SQL query for my depends on the user’s role within the enterprise.

For example, the Context Layer deals with unclear questions or situations where multiple business terms (like ARR, Revenue, or Churn) could be useful at different times. The Context Layer outlines the appropriate scenarios for utilizing ARR versus Revenue, considering factors such as query intent, time frames, or user access privileges. It might outline guidelines such as “Utilize ARR for SaaS products” or “When preparing quarterly reports, focus on Revenue.” With contextual adaptability, WisdomAI ensures that users receive more pertinent results and that the system reacts more intelligently to intricate queries. This layered approach allows WisdomAI to provide a more robust, scalable, and adaptive solution for businesses, filling in the gaps left by traditional semantic models.

Text-to-SQL system security

Text-to-SQL systems face a critical challenge in ensuring the security of their underlying data sources, especially when they are used to manage sensitive data and complex databases. If not configured with the appropriate security measures, text-to-SQL systems may be vulnerable to SQL injection attacks, data leakage, or unauthorized access to confidential information.

This section delves into fundamental security measures, including the function of semantic layers in preserving data security, query disinfection, and validation methods.

Query disinfection and validation methods

SQL injection, which involves the introduction of malevolent code into queries, is a significant risk in any system that converts natural language into SQL queries. Query disinfection techniques must be implemented to alleviate this issue. These techniques involve analyzing and sanitizing user input before its conversion into SQL queries. This guarantees that any potentially harmful inputs are neutralized, preventing assailants from executing unintended commands.

Standard methods for query disinfection include the following:

• Parameterized queries: User inputs are regarded as parameters rather than executable code through parameterized queries. This serves to prevent the execution of malicious code.
• Input escape: Special characters in the user input that could be interpreted as SQL commands are either escaped or stripped out.
• Query structure validation: After a query has been generated, it can be compared to predetermined rules to guarantee that it is consistent with anticipated structures. For instance, the system can verify that only SELECT queries are executed in read-only environments, preventing any attempts to insert, alter, or delete data.
  • Query structure validation checks that written SQL queries match a set of rules before they are run. This makes sure that queries are consistent and safe. The following query cannot be executed in a read-only environment:

DELETE FROM orders WHERE order_id = 123;

This query tries to get rid of a record from the orders table. The environment is set up for read-only activities, so the system uses validation rules to determine that DELETE statements are not allowed. After that, the query would be stopped, and the system might show a warning message like

Invalid query: DELETE statements are not permitted in this environment. Your query should only use SELECT methods.

Dealing with sensitive data in text-to-SQL systems

Text-to-SQL systems frequently handle sensitive data, including personally identifiable information (PII) and proprietary business data. Consequently, security considerations extend beyond the mere validation of queries. Implementing data masking and access control mechanisms is imperative to prevent the unintentional disclosure of sensitive data.

The following are the primary methods for managing sensitive data:

• Data masking is the process of obscuring or masking sensitive data (e.g., credit card numbers or social security numbers) prior to its presentation to users. This enables users to query the data while guaranteeing that sensitive information is concealed.
• Role-based access control (RBAC) is essential in enterprise systems to guarantee that only authorized users can access specific data fields or tables. RBAC can enforce strict data access rules based on user roles, ensuring that sensitive data is only accessible to those with the appropriate clearance.some text
  • Assume that Alice and Bob, two sales team members, are in different parts of the company, and both ask, "What were their sales numbers for Q4?" RBAC ensures that users only see data from their region, even if they are on the same sales team.
    The RBAC technique uses an identity provider to determine Alice's role and data access when she queries the system. The system recognizes her as the East Region sales representative and gets only her Q4 sales statistics.

SELECT SUM(sale_amount) AS total_sales 
FROM sales 
WHERE region = 'East' AND quarter = 'Q4';

In the same way, when Bob, who is in charge of the West Region, asks the same question, the system again talks to the identity provider to make changes to the query. Only regional sales data will be sent to Bob.

SELECT SUM(sale_amount) AS total_sales 
FROM sales 
WHERE region = ‘West’ AND quarter = 'Q4';

This example shows how RBAC improves the relevancy of the data presented while also ensuring data security by restricting access according to user responsibilities.

If the sales team manager, Lucy, asked the same question, the text-to-SQL system must account for Lucy’s enhanced privileges by executing an SQL query that retrieves ALL of the Q4 sales figures.

• Administrators should implement comprehensive audit logs to monitor who accessed which data and when. This can assist in detecting suspicious activity, unauthorized access, or unexpected queries.

WisdomAI data security & audit

Technologies like WisdomAI provide security safeguards that protect sensitive data while facilitating uninterrupted query execution. WisdomAI employs end-to-end encryption, role-based access control, and query validation methods to protect data in corporate settings.

In WisdomAI's approach, queries are sanitized before execution, and role-based access controls guarantee that users can only access data they are permitted to view. WisdomAI's security framework facilitates ongoing monitoring and audit recording, enabling administrators to oversee data access and maintain adherence to security protocols.

Text-to-SQL systems can protect sensitive data by including security measures like query validation, data masking, and role-based access control. At the same time, ensuring users have unobstructed access to necessary information. WisdomAI and analogous technologies, in conjunction with semantic layers, guarantee that security is integrated into all facets of data querying and retrieval, preserving efficiency and robust protection.

In addition to its robust security features, WisdomAI involves auditing capabilities that allow administrators to verify that security controls are being adequately enforced. Monitoring the data obtained during each encounter and recording user instructions are essential to this feature. Administrators can verify that users have only viewed the material they can view. This audit trail is crucial for ensuring compliance with data governance regulations. It guarantees that security specifications are fulfilled, providing users with the assurance that private data is safeguarded even while they interact with the system in real time.

Last thoughts on text-to-SQL

This article covered the essential elements that facilitate the success of a resilient and scalable text-to-SQL system. Text-to-SQL systems are revolutionizing user interaction with databases by enabling inquiries in natural language to yield structured query responses. This transition promises to reduce the need for SQL expertise, making data more accessible across organizations. Instead of manually crafting complex SQL queries, users can simply make requests in natural language. Subsequently, the system constructs the appropriate SQL query, retrieves the data, and displays the results.

We highlighted the significance of semantic layers in streamlining business inquiries through the abstraction of technical intricacies. Manual semantic layer building is not practical, even for simple datasets, and automating the creation of the semantic layer cannot capture the nuances of enterprise data and language. The Context Layer was introduced to solve this and can adjust to real enterprise situations, enhancing query efficiency and relevance.

Security is essential to any text-to-SQL system, particularly when handling sensitive information. We examined how query disinfection, data masking, and role-based access restrictions safeguard data and inhibit unauthorized access. Finally, we emphasized the significance of feedback loops and testing in enhancing these systems, guaranteeing ongoing progress informed by user interactions.

WisdomAI represents a simplified method for generating insightful analysis from data. Like conversational AI programs like ChatGPT, you can just enter a query to get pertinent knowledge, instantly enabling effective, data-driven decision-making. WisdomAI dynamically optimizes queries based on context and user intent by building a dynamic semantic layer. This flexibility enables managing big table collections and intricate join relationships effectively, enhancing queries' accuracy and speed. Furthermore, WisdomAI offers a strong foundation for safely managing sensitive data and an underlying AI knowledge graph that learns more with more data and users.