Chapter 6: A scientific approach for validating LLM-based solutions
The chapter introduces a text-to-SQL service and explains why provider independence and measured accuracy matter when building an LLM-based solution.
After this chapter you will be able to
- Provider Independent Service Boundary. Explain why the text-to-SQL service hides its LLM provider behind a clean API, and describe the trade-off of using a managed AI gateway instead.
- Measured SQL Generation Accuracy. Explain why a few integration tests are insufficient to judge text-to-SQL accuracy and why the chapter calls for a validation metric and suitable input data.
- Spring Boot service foundation. Explain that the text-to-SQL service uses Spring Boot so it can run as an embedded HTTP service and expose REST APIs.
- Spring service skeleton components. Describe the Java main-function class as the Spring application’s entry point and application.yaml as the file for main Spring settings, such as ports.
- Query request inputs. Describe the inputs to a SQL-generation request: a user’s plain-language prompt and a list of relevant tables. State that the request can also include recent queries from that user, without explaining how they are used.
- Table schema context. Explain what the Table object can supply and why that information helps SQL generation.
- Service contract and provider isolation. Explain how the REST controller uses the SqlGeneratorService contract and why the LLM provider’s implementation is kept out of the REST layer.
- SQL prompt construction. Trace how the service turns user input and database context into a prompt that asks for executable SQL.
- Output and input token limits. Distinguish the output limit set by maxTokens(500) from the separate protections needed for incoming prompts.
- Temperature choice and exception. Explain why the example uses a low temperature for SQL generation and why that setting must be checked against model-specific guidance.
- Nondeterministic SQL test assertions. Explain why the integration test checks broad properties of generated SQL rather than asserting an exact query.
- BIRD Dataset for Verification. Explain why the section chooses the BIRD benchmark as the starting point for verifying a text-to-SQL service.
- Benchmark cases and reference SQL. Explain how dev.json provides benchmark cases: each question is a prompt for the model, and its SQL field is the expected output. Describe why the evidence field is withheld from this benchmark.
- Performance by database and difficulty. Describe how db_id and difficulty organize the benchmark results, and what reporting precision for each pair is intended to reveal.
- SQL comparison methods and trade-offs. Distinguish text-based, structural, and semantic ways to compare generated SQL with ground-truth SQL, including what each compares and its main trade-off.
- Overlap-based SQL similarity. Explain why the framework uses a text-based overlap measure and apply its Jaccard calculation to the meaningful tokens in two queries.
- Disjoint evaluation and example pools. Explain why the benchmark loads evaluation queries from dev.json and few-shot examples from a separate, frozen examples.json.
- Benchmark submission and reporting flow. Trace how the benchmark prepares inputs, submits evaluation queries to the SQL-generator service, and records results.
- Semantic limits of overlap scores. Explain why the example's 0.29 Overlap Similarity score does not mean the generated SQL is 29% correct.
- Contamination-free example selection. Explain why evaluation examples must not come from the dataset being tested, and describe how selection from a separate frozen, database-specific pool plus exclusion of the current gold SQL prevents answer leakage.
- Query history accuracy trade-off. Describe how adding last queries to the context changes SQL similarity across difficulty levels, and explain why more examples are not always better.
- Context accuracy cost trade-off. Explain how providing more prior queries as context affects token cost and measured accuracy, and why the author favors ten prior queries over twenty in this benchmark.
- Benchmark cost traffic projection. Describe how the author scales the cost of a 316-query benchmark to an estimated daily cost at 100 requests per second, and what the six-versus-seven-cent comparison shows.
- Shared language model interface. Explain how the refactoring supports both OpenAI and Gemini without duplicating the text-to-SQL service logic.
- Provider selection versus fallback. Distinguish the configured OpenAI default from a runtime fallback, and explain why the distinction matters for benchmarking.
- Deployment generation guardrails. Explain why the deployment prompt explicitly requires Google Artifact Registry and Secret Manager while keeping deployment changes separate from production code.
- Container build and deployment flow. Trace how the generated Dockerfile and commands take the service from source code to a running Cloud Run application.
- Secure runtime configuration. Describe how the deployment supplies API credentials, selects an AI provider, and obtains its HTTP port.
- Cloud service verification. Describe how the deployed service is checked by sending a REST request with a user_prompt and two tables to its returned URL, then inspecting the generated SQL and Google console logs.
- Overload error routing trade-off. Explain why a Gemini 503 overload error reaches the user in this test, rather than triggering a fallback to OpenAI, and contrast that choice with the desired behavior in a highly available production system.
- AI provider switching. Describe how the deployed application switches from Gemini to OpenAI and how to verify the change. Explain why this tests for vendor lock-in.
Section by section
Introduction
The chapter introduces a text-to-SQL service and explains why provider independence and measured accuracy matter when building an LLM-based solution.
- Provider Independent Service Boundary: Explain why the text-to-SQL service hides its LLM provider behind a clean API, and describe the trade-off of using a managed AI gateway instead.
Why it matters: A stable service boundary makes a future provider change less disruptive to users of the text-to-SQL service. - Measured SQL Generation Accuracy: Explain why a few integration tests are insufficient to judge text-to-SQL accuracy and why the chapter calls for a validation metric and suitable input data.
Why it matters: Without an accuracy measure, a prompt change can appear promising without evidence that it improves results across cases. - Input Context Cost Tradeoff: State why input-context size is a variable to consider when experimenting with the service, including its stated effect on running cost.
Why it matters: A measured accuracy gain from adding context must be considered alongside the higher cost of using it.
Key terms: text-to-SQL, LLM API provider, clean API, managed AI gateways, vendor lock-in, integration tests, model accuracy, validation framework, metric, bird-bench
6.1 Creating a text-to-SQL service
Introduces the Spring Boot text-to-SQL service and explains why it is used to expose the service over HTTP.
- Spring Boot service foundation: Explain that the text-to-SQL service uses Spring Boot so it can run as an embedded HTTP service and expose REST APIs.
Why it matters: This identifies how the text-to-SQL functionality will be made available to callers.
Key terms: Spring Boot, embedded HTTP service, REST APIs, text-to-SQL
6.1.1 Creating the service skeleton
Identifies the Spring application entry point and configuration files that form the text-to-SQL service skeleton.
- Spring service skeleton components: Describe the Java main-function class as the Spring application’s entry point and application.yaml as the file for main Spring settings, such as ports.
Why it matters: These are the two pieces the section identifies before moving on to the service API. - Production and test configuration files: Explain why this application has separate application.yaml and application-test.yaml files, and distinguish that simplified setup from the per-environment files used in real-life deployments.
Why it matters: It clarifies which settings belong to the running application and which belong to integration tests.
Key terms: Java main function, application.yaml, Spring settings, application-test.yaml, integration tests
6.1.2 Defining the API of the service
This section defines the text-to-SQL service’s REST request and response, explains how table metadata guides SQL generation, and separates the controller from the LLM provider.
- Query request inputs: Describe the inputs to a SQL-generation request: a user’s plain-language prompt and a list of relevant tables. State that the request can also include recent queries from that user, without explaining how they are used.
Why it matters: These inputs define what the service has to work with when generating SQL. - Table schema context: Explain what the Table object can supply and why that information helps SQL generation.
Why it matters: Providing field information helps the model generate SQL against the actual tables. - Service contract and provider isolation: Explain how the REST controller uses the SqlGeneratorService contract and why the LLM provider’s implementation is kept out of the REST layer.
Why it matters: A stable service contract lets the API stay the same if its model provider changes. - Isolated REST layer testing: Describe how the controller can be tested without the real SQL-generation service and what that test checks.
Why it matters: A mocked service lets the REST contract be checked independently of model integration.
Key terms: SqlGeneratorQueryRequest, user_prompt, list of tables, Table, tableName, SQL, fields, types, SqlGeneratorController, SqlGeneratorService
6.2 Integration with the OpenAI API
The section explains how a text-to-SQL service configures an LLM provider, builds a SQL-generation prompt, controls requests, and turns the model response into executable SQL.
- Provider configuration and cost: Explain why the example uses the OpenAI API and how model selection affects running cost and potential accuracy.
Why it matters: Provider and model choices affect the cost of running the same service. - SQL prompt construction: Trace how the service turns user input and database context into a prompt that asks for executable SQL.
Why it matters: The prompt supplies the schema and examples the model needs while directing it to produce usable output. - Output and input token limits: Distinguish the output limit set by maxTokens(500) from the separate protections needed for incoming prompts.
Why it matters: An output cap alone does not protect the service from oversized, costly input. - Temperature choice and exception: Explain why the example uses a low temperature for SQL generation and why that setting must be checked against model-specific guidance.
Why it matters: Temperature affects consistency, but blindly applying one value across models can hurt results. - Completion extraction and cleanup: Trace how the service obtains SQL text from the API response and removes formatting that would prevent direct execution.
Why it matters: Extracting and cleaning the response turns model output into the SQL string the service needs.
Key terms: OpenAI API, model, application.yaml, generateSql(), buildPrompt(), input tables, recent queries, SQL queries, maxTokens(500), input-token costs
6.2.1 Testing the integration
This subsection shows how to configure an OpenAI integration test and why it checks basic SQL properties instead of an exact generated query.
- Integration test configuration: Describe how the integration test obtains its service and what configuration is needed to run it.
Why it matters: Without the test-profile setup and API key, the integration test cannot exercise the service. - Nondeterministic SQL test assertions: Explain why the integration test checks broad properties of generated SQL rather than asserting an exact query.
Why it matters: These assertions let the test check for a basic usable query without failing over harmless variation in LLM output.
Key terms: SqlGeneratorService, test profile, application-test.yaml, OpenAI API key, generated SQL, SELECT statement, not 100% deterministic
6.3 Developing the accuracy verification framework
The section motivates using the BIRD benchmark to verify text-to-SQL accuracy and identifies two input and prompting choices that may improve generated SQL.
- BIRD Dataset for Verification: Explain why the section chooses the BIRD benchmark as the starting point for verifying a text-to-SQL service.
Why it matters: A usable benchmark lets the team begin checking accuracy without first constructing a large dataset. - Table Schema as Input: State the section's claim about table-schema information when generating SQL.
Why it matters: The model's input needs to identify the database structure it is meant to query. - Step-by-Step Prompting Hint: Explain the qualified value of asking a model to think step by step for SQL generation.
Why it matters: It frames the prompt as an option to test, not a substitute for measuring SQL accuracy.
Key terms: BIRD benchmark, BIRD dataset, text-to-SQL, schema of the tables, description of each column, Let’s think step by step, reasoning models, chain of thoughts
6.3.1 Delving into the bird-bench dataset
The section explains how BIRD’s development dataset supplies reference questions, SQL, and schemas for evaluating a text-to-SQL service.
- Benchmark cases and reference SQL: Explain how dev.json provides benchmark cases: each question is a prompt for the model, and its SQL field is the expected output. Describe why the evidence field is withheld from this benchmark.
Why it matters: Reference answers make evaluation more informative than a few gut-feeling tests. - Performance by database and difficulty: Describe how db_id and difficulty organize the benchmark results, and what reporting precision for each pair is intended to reveal.
Why it matters: A single overall result could hide differences between databases or levels of query difficulty. - Database schema files: Explain what the SQL files associated with the benchmark databases contain and why the text-to-SQL service does not need to create their tables.
Why it matters: The schema definitions provide database context without requiring the service to host a database.
Key terms: BIRD benchmark, dev.json, question, evidence, SQL, gold standard, db_id, difficulty, simple, moderate
6.3.2 Methods for comparing expected vs the actual generated SQL and their trade-offs
This section compares ways to evaluate generated SQL against ground-truth SQL, then explains a simple word-overlap measure chosen for the verification framework.
- SQL comparison methods and trade-offs: Distinguish text-based, structural, and semantic ways to compare generated SQL with ground-truth SQL, including what each compares and its main trade-off.
Why it matters: The comparison method determines what an accuracy measurement can detect and what it costs to run. - Overlap-based SQL similarity: Explain why the framework uses a text-based overlap measure and apply its Jaccard calculation to the meaningful tokens in two queries.
Why it matters: This makes the framework's similarity score interpretable without mistaking it for proof that queries behave alike.
Key terms: Text-Based Methods, Structural (AST-Based) Methods, Semantic Methods, Embedding-Based Semantic, Execution-Based Semantic, Overlap-Based SQL Query Similarity, Jaccard similarity, intersection, union, non-keyword tokens
6.3.3 Submitting the queries to the SQL-generator-service
The section explains how the SQL-generator benchmark keeps evaluation data separate from prompt examples, submits queries, reports results, and interprets an overlap score.
- Disjoint evaluation and example pools: Explain why the benchmark loads evaluation queries from dev.json and few-shot examples from a separate, frozen examples.json.
Why it matters: A benchmark is less trustworthy if its answers can leak into the prompts it evaluates. - Benchmark submission and reporting flow: Trace how the benchmark prepares inputs, submits evaluation queries to the SQL-generator service, and records results.
Why it matters: This flow turns generated SQL into results that can be inspected by query and summarized by database and difficulty. - Semantic limits of overlap scores: Explain why the example's 0.29 Overlap Similarity score does not mean the generated SQL is 29% correct.
Why it matters: A numeric overlap score must not be mistaken for a measure of whether the SQL answers the user's question.
Key terms: dev.json, examples.json, data contamination, few-shot prompt context, ValidateSqlGeneratorPrecision, CREATE TABLE, schema, Overlap Similarity, db_id/difficulty, gold-standard answer
6.4 Trade-offs with input-context size
This section explains how to add example SQL queries to a text-to-SQL prompt without contaminating the evaluation used to judge their effect.
- Example queries in prompt context: Explain that callers can provide queries through nLastQueries, which passes them alongside the user prompt as examples of what generated SQL should look like.
Why it matters: This is the input-context change whose effect the evaluation is meant to measure. - Contamination-free example selection: Explain why evaluation examples must not come from the dataset being tested, and describe how selection from a separate frozen, database-specific pool plus exclusion of the current gold SQL prevents answer leakage.
Why it matters: An accuracy result is not meaningful if the model was shown the answer in its prompt.
Key terms: SqlGeneratorQueryRequest, nLastQueries, context, few-shot examples, data contamination, dev.json, examples.json, dbId, gold SQL
6.4.1 Accuracy vs context-size
The section interprets SQL-similarity benchmark results and shows that adding recent queries to the LLM’s context improves accuracy, but with diminishing returns.
- SQL similarity percentile results: Explain what the p50, p90, and p99 values in the summary results say about generated SQL, and why the section uses a summary rather than inspecting hundreds of queries individually.
Why it matters: These summaries make it possible to judge whether the generated SQL is useful without reviewing hundreds of individual results. - Query history accuracy trade-off: Describe how adding last queries to the context changes SQL similarity across difficulty levels, and explain why more examples are not always better.
Why it matters: Testing several context sizes helps you choose useful query history instead of assuming that a larger prompt will keep improving results.
Key terms: p50, p90, p99, similarity, summary file, zero last queries, query history, context size, diminishing returns, sweet spot
6.4.2 Context-size vs cost of running the model
The section combines measured accuracy, context size, and token-based cost to choose a text-to-SQL configuration and estimate its cost at production traffic.
- Context accuracy cost trade-off: Explain how providing more prior queries as context affects token cost and measured accuracy, and why the author favors ten prior queries over twenty in this benchmark.
Why it matters: It helps you choose context based on the accuracy gained for the money spent, rather than maximizing context by default. - Benchmark cost traffic projection: Describe how the author scales the cost of a 316-query benchmark to an estimated daily cost at 100 requests per second, and what the six-versus-seven-cent comparison shows.
Why it matters: A small per-benchmark price difference can become a substantial daily operating cost at production traffic.
Key terms: context size, tokens, p50 accuracy, accuracy-to-cost ratio, 316 queries, 100 requests per second, cost per day
6.4.3 Migration to another AI provider - Google Gemini
The section adds Google Gemini as a selectable text-to-SQL provider while preserving OpenAI support, then checks that Gemini is actually used.
- Shared language model interface: Explain how the refactoring supports both OpenAI and Gemini without duplicating the text-to-SQL service logic.
Why it matters: The shared interface makes adding a provider possible without copying the service’s prompt and cleanup logic. - Provider selection versus fallback: Distinguish the configured OpenAI default from a runtime fallback, and explain why the distinction matters for benchmarking.
Why it matters: A silent provider switch would make accuracy measurements misleading. - Gemini SDK integration review: Explain why the prompt specified the Google Genai Java SDK and what generated integration details still need human review.
Why it matters: Being specific about the SDK and reviewing generated choices avoids unnecessary custom integration code and outdated dependency usage. - Gemini integration verification: Describe how the section verifies that the text-to-SQL application actually called Gemini.
Why it matters: Checking both the test output and provider usage helps confirm which API the application contacted.
Key terms: LanguageModelClient, SqlGeneratorServiceImpl, generateText(), ai.provider, configuration default, runtime fallback, precision metrics, Google Genai Java SDK, GeminiLanguageModelClient, Vibe Engineering
6.4.4 Deploying the application to Google Cloud Run
The section shows how to guide an LLM to generate a secure Google Cloud Run deployment, then build, configure, and deploy the text-to-SQL service.
- Deployment generation guardrails: Explain why the deployment prompt explicitly requires Google Artifact Registry and Secret Manager while keeping deployment changes separate from production code.
Why it matters: A deployment prompt can prevent avoidable security and portability problems before code is generated. - Container build and deployment flow: Trace how the generated Dockerfile and commands take the service from source code to a running Cloud Run application.
Why it matters: The build and deploy steps distinguish creating an image from starting the service that uses it. - Secure runtime configuration: Describe how the deployment supplies API credentials, selects an AI provider, and obtains its HTTP port.
Why it matters: This configuration lets the running service use an AI backend without placing raw credentials in deployment arguments.
Key terms: Google Artifact Registry, Container Registry, Google Cloud Secret Manager, guardrails, Dockerfile, Maven, gcloud builds submit, gcloud run deploy, Secret Manager, --set-secrets
6.4.5 Verifying the text-to-SQL running in the cloud
The section verifies the deployed text-to-SQL service through a REST request and Cloud Run logs, then explains why an overloaded-provider error reaches the user.
- Cloud service verification: Describe how the deployed service is checked by sending a REST request with a user_prompt and two tables to its returned URL, then inspecting the generated SQL and Google console logs.
Why it matters: Checking both the response and logs shows what the deployed service actually did. - Overload error routing trade-off: Explain why a Gemini 503 overload error reaches the user in this test, rather than triggering a fallback to OpenAI, and contrast that choice with the desired behavior in a highly available production system.
Why it matters: The visible error confirms the intended test isolation, even though production availability would call for different routing.
Key terms: REST API, user_prompt, Google console logs, gemini-2.5-flash, 503, free tier, runtime fallback, strict configuration default, benchmarking data contamination, isolated routing
6.4.6 Switch the AI provider
Shows how to check that the deployed application can switch from Gemini to OpenAI without being locked into one AI provider.
- AI provider switching: Describe how the deployed application switches from Gemini to OpenAI and how to verify the change. Explain why this tests for vendor lock-in.
Why it matters: A provider switch is a practical check that the application is not tied to a single AI API provider.
Key terms: vendor lock-in, AI_PROVIDER, Secret Manager, AI provider
6.5 Summary
Where this is used later
- The context problem: paying for noise (chapter 9) · Provides an example of paying for additional context and judging whether its benefit warrants the cost.
- Token economics 101 (chapter 9) · Connects token-based pricing to an estimate of operating cost at a given request volume.
Concepts explained in this chapter
- context windowThe finite amount of input information a language model can consider during a single inference or conversation state.
- accuracyCorrectness is the degree to which software behavior satisfies its intended specification and real-world requirements.
- costThe total monetary, computational, operational, and human-review expense incurred in obtaining a system output.
- text-to-SQLText-to-SQL is the task of translating natural-language requests into executable SQL queries against a database.
- vendor lock-inVendor lock-in is dependency on a particular supplier that makes switching to an alternative costly or difficult.
- Cloud RunGoogle Cloud Run is a managed platform for running containerized applications in a serverless execution environment.
- promptA prompt is an input instruction or request supplied to an AI model to elicit a response or action.
- tradeoffA trade-off is a compromise in which improving one objective or property requires accepting a reduction in another.
- runtime fallbackA runtime fallback is an operational pattern that redirects a failed request to an alternative service or provider while the system is running.
- integration testAn integration test evaluates interactions between multiple software components or with external infrastructure rather than isolating a single unit.
- Gemini APIGemini Developer API is Google's developer-facing API backend for accessing Gemini models with an API key.
- Google Genai Java SDKGoogle Genai Java client SDK is a Java client library for communicating with Google's generative AI services.
- language model client interfaceA language model client interface is an abstraction that defines a common contract for invoking different language-model providers.
- OpenAI APIThe OpenAI API is a public programming interface for accessing OpenAI-hosted artificial-intelligence models and services.
- prompt engineeringPrompting is the practice of formulating and supplying instructions or context to an AI model to guide its output.
- data leakageData contamination is the leakage of evaluation answers or related held-out information into model inputs or training-like context, invalidating an evaluation.
- deploymentDeployment is the process of installing and configuring software so that it runs in a target environment.
- diminishing returnsDiminishing returns is a pattern in which each additional increase in an input produces a smaller incremental improvement in outcome.
- benchmarkA benchmark is a standardized evaluation procedure that measures a system against defined inputs and expected results.
- precisionPrecision is the proportion of predicted positive instances that are actually positive.
- controllerA controller is an application component that receives requests, coordinates application behavior, and returns responses at an interface boundary.
- chain-of-thought promptingChain-of-thought prompting is a technique in which a language model generates intermediate reasoning steps before producing its final answer.
- verificationThe systematic evaluation of an implementation or output against specified requirements and expected behavior.
- token limitA token budget is a configured target or limit for the number of language-model tokens a task may consume.
- GPT-4oGPT-4o is a multimodal generative language model from OpenAI.
- MarkdownMarkdown is a lightweight markup language for formatting plain-text documents.
- guardrailA guardrail is a constraint or control that limits an AI system's actions to reduce unsafe, unauthorized, or unreliable outcomes.
- securitySecurity is the protection of software, data, and operations against unauthorized access, misuse, disruption, or alteration.
- vibe engineeringA disciplined approach to AI-assisted software development that emphasizes clear intent, clean abstractions, verification, testing, quality, and maintainable ownership.
- metricA metric is a quantitative measure used to assess a property or outcome of a software system or process.
- tokenA unit of model input text counted when measuring the size and cost of a language-model request.
- percentileA percentile is a distribution threshold below which a specified percentage of observed values falls.
- evaluation datasetA held-out portion of data used to evaluate a trained machine-learning model on previously unseen inputs and targets.
- validationThe process of checking that software behavior or output satisfies intended requirements and real-world constraints.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.
