Unlocking the Power of LLMs for Context-Aware SQL and Reporting/Visualization Generation

Introduction

In the ever-evolving landscape of financial technology, the ability to seamlessly translate natural language queries into SQL—and then visualize the results—is not just a convenience; it's a necessity. As businesses strive to extract actionable insights from vast datasets, our focus has shifted toward generating contextually relevant SQL queries that cater to complex and dynamic user interactions. This blog will delve into our innovative approach to text-to-SQL generation within the payments and payouts domain, illustrating how we harness the power of Large Language Models (LLMs) not only for SQL generation but also for visualizing the generated data.

The Challenge of Contextual Understanding

Generating SQL queries from natural language goes beyond mere question-answering or simple translation. Users often frame questions that reference previous interactions or require data from multiple schemas. Consider these sample queries from fintech merchants:

1. What was my overall payment success rate yesterday for all transactions?
2. How many transactions failed due to hard failures?
3. What is my net revenue from last month?

The second and third questions build on the context established by the first. Recognizing this interconnectedness is crucial for generating accurate SQL queries.

Our Approach: Enhancing SQL Generation with Interaction History

Our strategy is rooted in the observation that adjacent natural language questions are frequently linguistically dependent. To leverage this dependency, we employ a novel editing mechanism that allows us to modify previously generated SQL queries rather than starting from scratch. By treating SQL as a sequence of tokens, we can effectively reuse parts of earlier queries, leading to increased efficiency and accuracy.

Editing Mechanism: Token-Level Reuse

Our editing mechanism is designed to be flexible and robust. It allows for individual token adjustments, minimizing the risk of error propagation that can occur with fully sequential generation approaches. This capability is particularly beneficial when dealing with similar queries that require slight modifications based on user input.

Integrating Context with a Table-Aware Architecture

To navigate the complexities of different table structures across domains, we utilize an utterance-table encoder paired with a table-aware decoder. This architecture integrates the context of the user’s question with the underlying table schema, enabling the model to generate more contextually aware SQL queries. The decoder employs an attention-enhanced LSTM, ensuring that the generated SQL reflects the intricacies of both the user utterance and the database schema.

Technical Architecture

To better illustrate our approach, let’s break down the technical architecture involved in transforming user queries into visual reports:

Common Data Model (CDM)

CDM is a standard & extensible schema (entities, attributes, relationships) that represents concepts that are frequently used in enterprises. CDM exists in order to facilitate data interoperability. Merchants can name the columns differently as long as they match with CDM and as long as the database is CDM compliant, we can easily fine-tune the pre-trained Text-to-SQL model

User Input to LLM

Users can enter their natural language questions directly into the system. Our Large Language Model (LLM) processes this input to generate context-aware SQL queries, informed by the interaction history and the intent behind the user’s inquiry.

To enhance the model's understanding, we utilize contextualized word representations from pre-trained language models such as BERT, RoBERTa, and XLNET. This approach allows us to incorporate not only the natural language query but also the relevant column names from the database. By leveraging attention mechanisms, the model can focus on the most pertinent words or subwords in the query during training.

We also implement execution-guided decoding, which ensures the generated SQL adheres to type constraints. For instance, certain operators are incompatible with specific data types—like attempting to sum a string column or using comparison operators with non-numeric types. This decoding method identifies and excludes faulty SQL statements, enhancing overall accuracy. Additionally, we can incorporate type encoding as part of the input to provide further context.

The final layer of our model consists of classification modules that handle six key components of the SQL query. Each module employs its own attention weights to align the model's hidden representations with the appropriate labels:

• SELECT: Specifies the columns to retrieve.
• AGGREGATION: Options include None, COUNT, SUM, AVG, MIN, MAX.
• Number of WHERE Conditions: Ranges from 0 to 3.
• WHERE Column: Identifies the column for filtering.
• WHERE Operators: Includes =, <, >.
• WHERE Value: Represents a text span derived from the natural language question, including the start and end positions based on probability. This value depends on both the selected column and the operator used in the condition.

LLM to Lake House/Database

The generated SQL queries are executed against a Lake House architecture—a unified data platform that combines elements of data lakes and data warehouses. This structure allows us to efficiently store, manage, and access both structured and unstructured data across various domains, ensuring that the SQL queries can pull from multiple sources seamlessly.

SQL Execution

The SQL queries are run against the data stored in the Lake House. This process involves retrieving relevant data based on the queries generated from user inputs. The execution results yield structured datasets reflecting user queries.

Results to Visualization Engine

The results obtained from the SQL execution are then passed to a visualization engine. This engine processes the structured datasets and prepares them for graphical representation.

Visual Report Generation

Finally, the visualization engine generates dynamic visual reports, including:

• Dashboards: Interactive dashboards displaying key metrics derived from the SQL results.
• Graphs and Charts: Visual representations such as line graphs, bar charts, and pie charts to convey trends and distributions effectively.
• Dynamic Filtering Options: Allowing users to interact with visualizations by applying filters based on date ranges, transaction types, or user-defined parameters.

Visualizing SQL Query Results

Once the SQL query is generated, the next critical step is to present the results in a meaningful way. Visualization enhances data comprehension, enabling users to derive insights quickly and effectively.

Example Visualization

To illustrate this process, consider the following SQL query generated from the user’s natural language question:

Generated SQL Query:

SELECT?

????DATE(transaction_date) AS date,?

????COUNT(*) AS success_count?

FROM?

????transactions?

WHERE?

????status = 'success'?

????AND transaction_date >= CURDATE() - INTERVAL 1 DAY?

GROUP BY?

????DATE(transaction_date);

Visualization Output: A line graph plotting success_count over the last seven days, allowing users to observe trends in success rates, pinpointing peak performance days or identifying drops.

Evaluating Our Approach

Our framework has been evaluated using a dedicated payments dataset. Initial results indicate a marked improvement in SQL generation quality compared to state-of-the-art baselines that generate queries from scratch. By incorporating interaction history and leveraging our editing mechanism, we can produce SQL that is not only more accurate but also more efficient.

Below evolving framework could lead to more intelligent systems that provide users with not just answers, but meaningful insights based on a deep understanding of data relationships and context.

Improved Query Understanding: By leveraging contextual embeddings, models can better understand user queries' nuances, leading to more accurate interpretations and retrieval of relevant information from knowledge graphs.

Dynamic Schema Integration: As knowledge graphs evolve, execution-guided decoding could adapt in real-time, updating query structures based on the current schema and ensuring that queries remain valid and efficient.

Semantic Reasoning: The integration of execution-guided techniques with reasoning capabilities could enable more complex queries that require inferencing, such as finding relationships between entities that are not explicitly stated.

Personalized Search Experiences: By understanding user intent and context better, this framework could facilitate personalized semantic search results, tailoring responses based on user history and preferences.

Feedback Loops: Incorporating user feedback into the execution-guided framework can refine the model’s understanding and improve future query handling, creating a more robust system over time.

Interoperability: As different knowledge graphs and databases are integrated, a unified framework for executing guided queries could streamline cross-domain searches, allowing for richer, multi-faceted results.

Proof of Concept (PoC) and Comparative Analysis

To validate our approach further, we will conduct multiple Proofs of Concept (PoC). These experiments will focus on comparing the results of our LLM-based editing mechanism against traditional SQL generation methods, as well as the efficacy of our visualizations. By analyzing the performance across various use cases, we aim to identify the optimal strategy for integrating natural language processing with our data platform.

Conclusion

As the demand for effective data insights continues to grow, the ability to generate context-aware SQL queries from natural language inputs—and visualize the results—becomes increasingly vital. Our approach—leveraging the power of LLMs, interaction history, and robust visualization techniques—sets a new standard in the cross-domain text-to-SQL generation landscape. By continuing to refine our methods and conduct comprehensive evaluations, we aim to empower merchants with the tools they need to navigate their data effectively and efficiently.

Stay tuned for future updates as we progress through our PoC evaluations and explore the full potential of LLMs in transforming natural language interactions into actionable SQL queries and compelling visual insights.