Dialogue with data: translating natural language into queries using AI

Text-to-SQL (Structured Query Language) is a technology that enables users to query relational databases using natural language instead of traditional SQL syntax. It leverages Natural Language Processing (NLP) and Large Language Models (LLMs) to translate human-readable questions into structured SQL queries. 

This approach is particularly valuable in large organisations, where empowering non-technical users to access data directly can reduce bottlenecks, improve agility, and support data-driven decision making. 

Over the past year, Lloyds Banking Group has made significant strides towards this vision. This article highlights the key technical achievements and milestones shaping the future of data interaction at the Group. 

Why Text-to-SQL 

The concept isn’t new; systems like LUNAR¹ and CHAT-80² explored natural language interfaces in the 70s and 80s, validating the need for intuitive data access. However, rule-based methods and compute constraints limited its scalability. Today, advances in LLMs, schema-aware encoding, and prompt engineering bring us closer than ever to accurately generating SQL across diverse domains. 

Almost two years ago we had a vision of improving our engineering efficiency and speeding up delivery cycles. We also wanted to democratise access to data and insights, through an intuitive tool that eliminates technical barriers and unlocks greater value from our data assets. 

The evolution of Dialogue with Data  

Dialogue with Data (DwD) is Lloyds Banking Group’s experiment to explore text-to-SQL using GenAI technology, introducing Agentic concepts. It was developed and tested in our “Innovation Sandbox”  using Google Cloud Platform (GCP) environment isolated from production. This provided safe conditions, access to the latest models and scalable compute for rapid, iterative learnings.  

Through our experimentation framework, we selected a low risk, yet high‑value Business Intelligence (BI) use case, using a synthetically generated Human Resource (HR) dataset that mirrors holiday and training records. This enabled us to validate end‑to‑end Text‑to‑SQL workflows (governance, guardrails, and UX) without exposing sensitive data.  

The experiment was divided into three phases: 

• Phase one – Framework exploration and zero-shot LLM benchmarking 
• Phase two – Experimentation sprints: functional demo and SQL generation 
• Phase three – Introducing semantic layer into target state experiment architecture  

Phase one – framework and zero shot LLM benchmarks 

Phase one benchmarked LLMs on their ability to generate accurate SQL from natural language prompts. The experiment surfaced limitations in off-the-shelf evaluation tools like Spider and Bird, prompting the creation of a bespoke Group-specific automated evaluation pipeline. The resulting framework supports nuanced assessment, including human-in-the-loop review, and laid the foundations for future phases.

Phase one labelled ‘zero shot’ (see Figure 1). We provided the full schema the above instruction prompt (see Figure 2) and iterated through a set of 44 questions curated from seven business users ranging in difficulty from easy to extra hard.   

To ensure deterministic behaviour and reproducibility across model evaluations through all phases; all sampling related parameters including temperature³, top-p⁴ and top-k⁵ were fixed at zero or their lowest permissible values. This attempted to suppress the inherent stochasticity of LLMs, enabling as consistent output generation as possible across repeated runs and helped in facilitating fair comparative analysis.  

The automated evaluation pipeline included metrics such as execution accuracy, syntax and semantic correctness, and time-based scoring (R-Ves⁶). Additional metrics like Soft-F1⁷ were used to account for minor variations in output structure, further strengthening the robustness of the analysis. 

Whilst the automated evaluation pipeline performed well, it became clear that human evaluators played a crucial role. Programmatic metrics such as soft F1, Exact Match (EM), and accuracy do not always fully capture correctness. In practice, evaluation often required resolving ambiguity in cases where multiple permutations of valid SQL statements could be considered correct. Human evaluators were also essential in judging partial correctness, ensuring that results aligned with the intended meaning of the input query. 

The evaluation revealed that while state of the art (SoA) models such as Claude 3.5 Sonnet, GPT 4.0 and Gemini demonstrated strong performance on objective queries, particularly in the "easy" and "medium" categories, they struggled with more subjective or domain-specific questions. This insight was reinforced through targeted human evaluation, which underscored the importance of schema context and the injection of domain knowledge.

Notably, specialised text-to-SQL open-source models such as Defog.ai and PipableSQL exhibited significantly lower accuracy across all difficulty levels, highlighting a clear performance gap between SoA and open-source alternatives.  

Another divergence between experimental setups and real-world deployment is the impact of differing Data Definition Language (DDL) environments on performance metrics. Initially, we aligned with open-source frameworks used by Spider and Bird, which at the time relied on SQLite. This consistency supported the development of our own automated evaluation pipeline. However, deploying SQLite in a production grade enterprise environment proved more complex, prompting a transition to PostgreSQL as an interim step. 

Following the PostgreSQL migration, model performance degraded. We attribute this, in part, to dialect bias driven by SQLite’s simpler SQL and its prevalence in benchmark datasets, which nudges models toward SQLite syntax. To enable migration from the Sandbox to other environments and improve scalability, we made the decision to transition to Google Gemini paired with BigQuery. 

Phase 2 – experimentation sprints: functional demo and SQL generation

To bring the experiment to life and enable user experience testing, we developed a working MVP (Minimum Viable Product) that served as both a functional prototype and a demonstration tool. This allowed us not only to showcase the potential of conversational analytics in a live environment, but also to validate integration pathways, assess usability and gather early feedback to inform future iterations. 

We manage conversations using a simple memory buffer, as we currently have no prior user history. At this stage, we’ve adopted a straightforward approach by leveraging Gemini Flash 1.5 to handle user intent (see conversation management figure): 

• New Query: Run SQL generation process 
• Follow-up: Create an augmented question using the previous question from memory buffer 
• Follow up - non query: If the user required different aliases that does not require SQL generation 
• Non-Database question: Re-prompt user to clarify intent with DwD rules  
• Example: “What is Joe Blogs job title”, we can see the previous context is included in the augmented question. (See example)

While the conversation management handles structured exchanges effectively and is simple to implement, it doesn’t yet account for the probabilistic and context-sensitive nature of real human conversation. Future iterations will need to incorporate more sophisticated intent modelling and contextual memory to manage conversational permutations.