Retrieval augmented text-to- SQL generation for epidemiological question answering using electronic health records (2024)

Abstract: Electronic health records (EHR) and claims data are rich sources of real-world data that reflect patient health status and healthcare utilization. Querying these databases to answer epidemiological questions is challenging due to the intricacy of medical terminology and the need for complex SQL queries. Here, we introduce an end-to-end methodology that combines text-to-SQL generation with retrieval augmented generation (RAG) to answer epidemiological questions using EHR and claims data. We show that our approach, which integrates a medical coding step into the text-to-SQL process, significantly improves the performance over simple prompting. Our findings indicate that although current language models are not yet sufficiently accurate for unsupervised use, RAG offers a promising direction for improving their capabilities, as shown in a realistic industry setting.

## introduction real-world data (rwd) are data routinely gathered from various sources that capture aspects of patient health status and the provision of health care. this encompasses electronic health records (ehr), medical claims data, disease registries, and emerging sources like digital health technologies. by investigating epidemiological quantities like patients' counts and demographics, disease incidence and prevalence, natural history of diseases, and treatment patterns in real-world clinical practice, researchers and healthcare organizations can identify for example target patient populations with unmet needs, discover unknown benefits of available drugs, evaluate potential for market entry, and estimate the potential enrolment of clinical trials. problem statement. addressing epidemiological questions using rwd databases is complex, as it requires not only an understanding of the data's characteristics, including biases, confounders, and limitations, but also involves interpreting medical terminology across various ontologies, formulating precise sql queries, executing these queries, and accurately synthesizing the results. contributions. with this paper, we present a straightforward and effective end-to-end approach to answer epidemiological questions based on data queried from ehr/claims databases. the presented approach is currently deployed at bayer in experimental mode. epidemiologists and data analysts are using the system to explore and evaluate its capabilities, ensuring that its use is carefully monitored and supervised. ## the dataset quantity value # of question/sql pairs (all) 306 # of different tables used (all) 13 # of different columns used (all) 44 # logical conditions/query 6.4 (6.7) # nesting levels/query 1.5 (1.1) # tables/query 2.7 (0.9) # columns/query 6.3 (4.7) # medical entities/query 2.0 (4.1) question length [char]/query 91.7 (81.2) sql query length [char]/query 796.4 (448.5) statistics on the dataset are shown in applicability across rwd databases. to address the challenge of data retrieval variability across databases with differing data models, we leverage the omop-cdm. this model, underpinned by standardized vocabularies @xcite , harmonizes observational healthcare data and it is widely recognized as the standard for rwd analysis, with data from over 2.1 billion patient records across 34 countries @xcite . ## methods our methodology, outlined in fig. 1 , employs llm prompting to translate natural language questions into sql queries. it advances ehr text-to-sql methods beyond the constraints of exact or string-based matching to fully encompass the semantic complexities of clinical terminology @xcite . to achieve this, we introduce a step where an llm generates sql with placeholders for medical entities (e.g., [condition@disphagia] in fig. 1d ), which are then mapped to precise clinical ontology terms (sec. 4.1, fig. 1d-e ). this yields executable queries that accurately retrieve database information. building on the success of rag in enhancing llms for complex nlp tasks @xcite , we use our dataset (sec. 2) as an external knowledge base. relevant question-sql pairs are extracted and incorporated into the prompt, refining sql generation. the completed sql queries, embedded with medical codes, are run on an omop cdm-compliant database (fig. 1f ) to facilitate data retrieval. if needed, an answer can be articulated from the retrieved data through further llm prompting (fig. 1g ). ## conclusion in this work, we presented the task of answering epidemiological questions using rwd. we demonstrated that rag is effective in improving performance on all tested scenarios. our study extends the demonstrated efficacy of rag from general text-to-sql benchmarks @xcite to include to small, domain-specific biomedical datasets, underlining its utility in data-scarce industry settings. the primary limitation is the dataset's limited size and specialized focus on epidemiological questions, suggesting further research should broaden its scope and scale.