MSD, a leading global pharmaceutical company, partnered with AWS Generative Innovation Center (GenAIIC) to develop and deploy a production-ready text-to-SQL generative AI solution. The system aims to streamline data extraction from complex healthcare databases, enabling analysts and data scientists to query databases using natural language instead of writing complex SQL queries manually. ## Business Context - MSD employs numerous analysts and data scientists who regularly analyze healthcare databases - Manual SQL query writing was time-consuming and reduced productivity - Need to democratize data access for non-technical staff - Goal to accelerate data-driven decision-making ## Technical Architecture ### Core Components - Amazon Bedrock as the primary LLM service platform - Anthropic's Claude 3.5 Sonnet model for query generation - SQLite database system (adaptable to other RDBMS) - Custom lookup tools for code translation - Comprehensive prompt engineering system ### LLM Integration - Uses Amazon Bedrock Converse API - Implements tool calling capabilities for code lookups - Zero temperature setting for deterministic code generation - Managed through boto3 client integration ### Database Handling - Support for complex healthcare database schemas - Handles coded columns through lookup tools - Works with DE-SynPUF dataset structure - Adaptable to different database engines ## Key Technical Features ### Lookup Tools Implementation - Custom tool calling framework for code translation - Tools for gender, race, and state location code lookups - JSON-based tool specifications - Handles code conversion transparently ### Prompt Engineering System - Comprehensive system prompt template - Includes database schema information - Sample data integration - Column and table descriptions - Few-shot examples for complex queries ### Query Processing Pipeline - Input question preprocessing - Code list placeholder handling - Tool calling for code lookups - SQL generation with explanation - Query validation ## Production Considerations ### Scalability - Token count management for large schemas - Efficient prompt template design - Modular tool system for extensibility ### Reliability - Error handling for tool calls - Validation of generated SQL - Fallback mechanisms for ambiguous queries ### Performance Optimization - Sample data selection for context - Few-shot example curation - Token optimization strategies ## Data Handling Features ### Schema Management - Dynamic schema information inclusion - Sample data integration - Column and table description formatting - XML-based metadata organization ### Query Enhancement - Support for complex medical code lists - Handling of coded columns - Management of non-intuitive column names - Resolution of ambiguous queries ## Best Practices Implemented ### Prompt Design - Clear instruction formatting - Comprehensive context provision - Efficient token usage - Strategic few-shot example selection ### Tool Integration - Well-defined tool specifications - Efficient tool call handling - Clear input/output protocols - Error handling mechanisms ### Query Generation - Strict schema adherence - Column name validation - Table relationship management - Query explanation generation ## Results and Impact - Reduced query time from hours to minutes - Improved accessibility for non-technical staff - Enhanced organizational productivity - Accelerated decision-making process - Democratized data access across the organization ## Future Enhancements - Integration with Amazon Bedrock Knowledge Bases - Data visualization capabilities - Voice assistant integration - Multi-language support - Enhanced few-shot learning through RAG ## LLMOps Considerations ### Monitoring - Tool usage tracking - Query success rates - Performance metrics - Error pattern analysis