6 A scientific approach for validating LLM-based solutions
This chapter introduces a practical text-to-SQL service as the example system for applying LLMs in a controlled, maintainable way. The design emphasizes hiding the provider behind a clean service interface so the application is not locked to a single API vendor, while still allowing the model to use useful context such as table schemas and recent queries. The service is built with Spring Boot, exposed through a REST controller, and tested at the API layer by mocking the LLM-facing service to keep the web layer isolated.
The chapter then shifts to a scientific validation mindset, arguing that ordinary integration tests are not enough to judge model quality because they cover only a small slice of inputs and do not measure accuracy broadly. To address this, it builds a benchmark using the BIRD dataset and compares generated SQL against expected SQL with an overlap-based similarity metric. This framework makes it possible to evaluate performance systematically across different databases and difficulty levels, revealing that seemingly successful outputs can still be semantically wrong even when they share some surface tokens with the target query.
With measurement in place, the chapter explores how expanding prompt context affects both accuracy and cost. Adding recent successful queries generally improves results, especially for simpler and moderate tasks, but the gains diminish as more examples are added, and the extra tokens increase API expense. The chapter also demonstrates how to switch from OpenAI to Gemini without rewriting the core application logic, then deploys the service to Google Cloud Run with secure secret handling, showing that careful abstraction, benchmarking, and cost analysis together lead to more reliable LLM-based systems.
Comparison of the p50 vs the number of last queries submitted.
Comparison of the p90 vs the number of last queries submitted.
Comparison of the p99 vs the number of last queries submitted.
Comparison of the p50 vs the cost of running the model.
Comparison of the p50 vs the cost of running the model per day.
Example Gemini API Usage for the Free Tier User.
Text-to-SQL in Cloud Run Logs.
Summary
- Introducing proper encapsulation of the LLM provider enables us to mitigate the issue of tight coupling with the given provider, allowing easier migration to another provider if needed.
- Researching white papers on text-to-SQL solutions allowed us to refine the system by focusing on key aspects and providing additional context to the LLM provider.
- The scientific method for validating the production system involved creating an accuracy calculator function, which entails a trade-off between process understandability and complexity.
- We developed an end-to-end scientific framework for validating our system across various difficulty levels: simple, moderate, and challenging.
- The accuracy measurement tool enabled us to verify and quantify trade-offs among 3 main factors when using LLMs: context size, accuracy, and cost.
- We made an informed decision on how to set the ideal context size without unnecessarily increasing the costs of OpenAI API usage.
- We alleviate the mistake of AI provider vendor lock-in by integrating with Gemini AI and OpenAI with the help of Vibe Engineering Techniques
- We learned how to productionize the application by deploying it to the Google Cloud Run.
FAQ
Why is it important to encapsulate the LLM provider behind a clean service API?
Encapsulation avoids tight coupling to a specific vendor, which makes the application easier to maintain and switch later if you change providers. It also keeps the REST layer independent from provider-specific details and simplifies testing by allowing the service to be mocked.Why are simple integration tests not enough to validate an LLM-based text-to-SQL solution?
Integration tests can confirm that the end-to-end flow works for a small set of inputs, but they do not measure model accuracy across many cases. To understand quality scientifically, you need a benchmark framework and a metric that compares generated SQL against expected SQL at scale.What problem does the chapter’s accuracy verification framework solve?
It provides a way to measure how well generated SQL matches ground truth across many test cases, so prompt changes or model changes can be evaluated objectively. This helps detect silent quality regressions that might otherwise go unnoticed.Why does the chapter use the BIRD benchmark dataset?
BIRD supplies a ready-made text-to-SQL dataset with questions, expected SQL, and database schemas, which makes it suitable for repeatable benchmarking. It removes the need to manually create a large validation set from scratch.What is overlap-based SQL similarity, and how does it work?
It is a simple text-based metric based on Jaccard similarity. The method extracts meaningful tokens from two SQL queries, removes common SQL keywords and punctuation, and then computes similarity as intersection size divided by union size.What are the limitations of text-based SQL comparison metrics?
They are fast and easy to implement, but they are semantically blind. Two queries can look similar lexically while being logically wrong, or they can be logically equivalent while having low text similarity.Why does the prompt include table definitions and recent queries?
Table definitions give the model schema context, including available columns and types, which reduces guessing. Recent queries act as examples that can improve the model’s output by showing patterns it should follow.What trade-off exists between context size and model accuracy?
Adding more context, such as more example queries, generally improves accuracy up to a point. After that, returns diminish, and extra context may not meaningfully improve results, especially for harder queries.Why does increasing context size also increase cost?
LLM APIs are billed by tokens, so a larger prompt consumes more input tokens and costs more to process. The chapter shows that better accuracy often comes with higher running cost, so you need to balance both factors carefully.How does the chapter reduce vendor lock-in when switching between OpenAI and Gemini?
It introduces a commonLanguageModelClient abstraction and provides separate implementations for each provider. The service selects the active provider through configuration, so switching models becomes a configuration change rather than a code rewrite.
Vibe Engineering ebook for free
