Generating patient cohorts from electronic health records using two-step retrieval-augmented text-to-SQL generation

2025-02-28

AI Summary

Manual SQL authoring for patient cohort definition in electronic health records (EHRs) is labor-intensive, error-prone, and lacks scalability. Method: We propose the first two-step retrieval-augmented text-to-SQL framework for EHR cohort construction. It integrates medical concept standardization—via UMLS/SNOMED CT ontology mapping—with funnel-style patient logic modeling, explicitly encoding temporal constraints and nested Boolean conditions. A dual-level retrieval-augmented generation (RAG) mechanism and a structured SQL synthesizer jointly enhance generation robustness and fidelity. Contribution/Results: Evaluated on real-world EHR data, our framework achieves an F1-score of 0.75—significantly outperforming single-step RAG baselines—and enables automated, high-precision construction of complex epidemiological cohorts.

Technology Category

Cognitive Modeling & Cognitive Systems: Conceptual Inference and ReasoningData Mining & Knowledge Management: Intelligent Query ProcessingKnowledge Representation and Reasoning: Diagnosis and Abductive Reasoning

Application Category

Search and Retrieval-Augmented AI: Retrieval-Augmented Generation (RAG) and multi-modal RAGSemantics and Knowledge: Data modeling to support human-machine intelligence, including LLMs agents, intelligent system behavior, explanations, and user-friendly interactionsUser Modeling, Personalization and Recommendation: Fairness-aware retrieval and ranking

Abstract

Clinical cohort definition is crucial for patient recruitment and observational studies, yet translating inclusion/exclusion criteria into SQL queries remains challenging and manual. We present an automated system utilizing large language models that combines criteria parsing, two-level retrieval augmented generation with specialized knowledge bases, medical concept standardization, and SQL generation to retrieve patient cohorts with patient funnels. The system achieves 0.75 F1-score in cohort identification on EHR data, effectively capturing complex temporal and logical relationships. These results demonstrate the feasibility of automated cohort generation for epidemiological research.

Problem

• Automates patient cohort generation from electronic health records.
• Translates inclusion/exclusion criteria into SQL queries using AI.
• Improves accuracy in capturing temporal and logical patient data relationships.

Innovation

• Two-step retrieval-augmented text-to-SQL generation
• Large language models for criteria parsing
• Medical concept standardization and SQL generation