Text-to-SQL System for Complex Healthcare Database Queries

msd · Healthcare · 2024 · source Agentic: yes · promotional bias: high · source-backed · extracted with prompt 0.5.6 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: Healthcare analytics and natural-language database access

MSD needed analysts and data scientists to query complex healthcare databases without spending hours manually writing SQL, while handling coded healthcare columns such as state, race, and gender values.

Constraints:

Healthcare databases contain coded columns that require lookup before SQL generation.
Analysts needed natural-language access without hand-writing complex SQL.
The system needed secure managed model access through AWS.
Generated SQL needed structured parsing and explanation.
Reported benefits are from an AWS co-authored case study.

Architecture

MSD and AWS built a Claude 3.5 Sonnet text-to-SQL system on Amazon Bedrock, with a Streamlit UI, detailed column descriptions, few-shot examples, optional RAG for examples, XML-style output tags, and tool calling for coded-column lookups such as state, sex/gender, and race/ethnicity codes.

Diagram confidence: reconstructed (reconstructed from prose).

flowchart LR
  streamlit_ui["Streamlit UI"] --> claude_3_5_sonnet_on_bedrock["Claude 3.5 Sonnet on Bedrock"]
  column_metadata_prompt["Column metadata prompt"] --> claude_3_5_sonnet_on_bedrock
  claude_3_5_sonnet_on_bedrock --> lookup_tools["Lookup tools"]
  lookup_tools --> claude_3_5_sonnet_on_bedrock
  claude_3_5_sonnet_on_bedrock --> healthcare_database["Healthcare database"]

Drag to pan · scroll or use +/− to zoom · reconstructed from the source's described component topology

Components

Component	Role	Interacts with
`Streamlit UI`	User interface for entering natural-language questions and selecting Claude models.	`Claude 3.5 Sonnet on Bedrock`
`Claude 3.5 Sonnet on Bedrock`	Generates SQL and summaries with tool-calling support.	`Streamlit UI`, `Column metadata prompt`, `Lookup tools`, `Healthcare database`
`Column metadata prompt`	Detailed table and column descriptions plus few-shot examples.	`Claude 3.5 Sonnet on Bedrock`
`Lookup tools`	Translate human terms such as state, gender, or race into coded database values.	`Claude 3.5 Sonnet on Bedrock`
`Healthcare database`	Complex dataset such as DE-SynPUF queried by generated SQL.	`Claude 3.5 Sonnet on Bedrock`

Data flow

A user asks a natural-language healthcare query. The system assembles schema descriptions, SQL dialect instructions, few-shot examples, and coded-column tools; Claude invokes lookup tools when needed, receives tool results in the conversation history, then emits SQL and summary tags for parsing.

4 supporting quotes

“implement a text-to-SQL solution that translates natural language questions into SQL queries for complex healthcare databases.”summary, stated

“The solution uses Anthropic's Claude 3.5 Sonnet on Amazon Bedrock, featuring custom lookup tools for coded columns, detailed column descriptions, and few-shot learning.”summary, stated

“The prompt instructs the LLM to output the generated SQL in an XML-style tag and the query description in a

tag”summary, stated

“This creates an agentic loop where the LLM can gather necessary information before finalizing the SQL query.”summary, stated

Why this architecture

Tool calling separates reference-data management from prompts, while Bedrock and Claude provide managed secure model access for complex reasoning and SQL generation.

“This approach separates reference data management from the core LLM prompt, improving maintainability and scalability.”summary, stated

“The choice of Claude 3.5 Sonnet reflects the need for a model with strong code generation capabilities and support for complex reasoning tasks.”summary, stated

Models used: Claude 3.5 Sonnet · Amazon Bedrock

Scale

The system reportedly reduced query time from hours to minutes.
“reportedly reducing query time from hours to minutes and democratizing data access across technical and non-technical staff.”summary, stated

Alternatives considered (roads not taken)

Manual SQL writing by analysts

Rejected because: It consumed considerable time and delayed data-driven decisions.

“numerous analysts and data scientists were spending considerable time manually querying databases, which slowed productivity and delayed data-driven decision-making.”summary, stated

Put all coded values directly into the prompt

Rejected because: Lookup tools make reference data management more maintainable and scalable.

“separates reference data management from the core LLM prompt, improving maintainability and scalability.”summary, stated

Patterns

`tool-use-orchestration` (agent tools)

Claude invokes lookup tools for coded columns before final SQL generation.

“the LLM recognizes it needs the state code, invokes the get_state_code tool with input "WI", receives the code "52"”summary, stated

`rag-retrieval` (examples)

Few-shot examples can be retrieved from a vector store based on similarity to the incoming query.

“few-shot examples can be made more relevant using a RAG approach, where sample queries are embedded in a vector store and retrieved based on similarity”summary, stated

Tradeoffs

Use a managed Bedrock model stack.

Chose Claude 3.5 Sonnet on Amazon Bedrock over Self-hosted SQL-generation model.

Rationale: The case emphasizes managed model access, security, and strong reasoning/code generation.

“Amazon Bedrock is a fully managed service offering access to foundation models from multiple providers through a single API, with built-in security and privacy features.”summary, stated

Gotchas (what bit them)

Healthcare-coded columns are not self-explanatory to an LLM.

Root cause: Human terms like Wisconsin or female must map to database codes.

Resolution: Expose lookup tools for state, sex/gender, and race/ethnicity codes.

“Three lookup tools are defined for translating sex/gender, race/ethnicity, and US state names into their corresponding database codes.”summary, stated

Evaluation

The case reports qualitative productivity gains and query-time reduction, but does not provide controlled benchmarks.

Metrics: Query time reduced from hours to minutes. · No controlled benchmark disclosed.

“The solution reportedly reduces query time from hours to minutes, though specific metrics or controlled benchmarks are not provided. The democratization of data access is highlighted as a key benefit, enabling non-technical staff to extract insights without SQL expertise.”summary, stated

Outcomes

Analysts can ask natural-language questions and receive SQL.
Query time reportedly dropped from hours to minutes.
Data access expanded to non-technical staff.

The summary cites numerous analysts and data scientists but does not provide usage volume.

“This approach enables analysts to extract insights from databases like DE-SynPUF without writing complex SQL manually, reportedly reducing query time from hours to minutes and democratizing data access across technical and non-technical staff.”summary, stated