TL;DR
Preprocessing Electronic Medical Record (EMR) data for machine learning traditionally requires laborious, hand-crafted SQL rules to define patient cohorts and map hospital-specific medical codes. EMR-AGENT is an LLM-powered multi-agent framework that dynamically interacts with live databases, using SQL as an exploratory probe to resolve schema ambiguities and automate data extraction. Across benchmarks on MIMIC-III, eICU, and an unseen dataset (SICdb), the system achieves expert-level cohort selection and cuts high-cost manual code-mapping operations by 57%.
What Problem Does This Paper Address?
Before training clinical machine learning models (e.g., predicting ICU mortality or length of stay), researchers must extract the right patient cohorts and align measurements across databases. In practice, this process is brittle and painful:
- Severe schema heterogeneity: Different hospitals store the same concept under entirely different names and structures. "Heart rate" might be stored as
itemid=211in MIMIC-III, as a dedicated columnheartratein eICU, or asHeartRateECGin SICdb. - Ambiguous cohort criteria: Criteria such as "first ICU admission" are easily misinterpreted across different schema setups, forcing engineers to write bespoke scripts for every new dataset.
- Failure of conventional Text-to-SQL: Standard Text-to-SQL models assume a static, known schema and treat SQL generation as a single-turn translation task. They lack the ability to inspect live database values to understand how categories are encoded.
Methods
Rather than generating SQL in a single shot, EMR-AGENT uses LLM agents that iteratively explore, query, and verify the database schema:
User Request + Database Docs
│
▼
Schema Linking & Guideline Generation ──► Identifies schema & missing info
│
├──► Cohort & Feature Selection Agent (CFSA)
│ └── SQL Observation Loop ──► Probes database (e.g., SELECT DISTINCT)
│ └── SQL Generation & Error Feedback (Syntactic vs. Schema Mismatch)
│
└──► Code Mapping Agent (CMA)
└── Feature Locating ────► Checks for direct column matches
└── Candidates Matching ─► Filters distinct row values via semantic similarity
- Schema Guideline Generation: Translates user requests and schema documentation into explicit guidelines that highlight missing information gaps (e.g., "we need gender, but do not yet know the internal code representation for 'Male'").
- Cohort and Feature Selection Agent (CFSA): Iteratively executes exploratory SQL queries (e.g., inspecting distinct categorical values). Once it discovers the correct mappings (such as finding that gender is coded as
735and736via a lookup table), it updates its guidelines, generates the final data extraction SQL, and refines it via syntax and semantic error-feedback loops. - Code Mapping Agent (CMA): Maps user-requested clinical measurements (e.g., arterial hemoglobin) to raw database codes. If a concept is not a column header, CMA generates
SELECT DISTINCTqueries against definition tables and scores matching candidate rows against semantic similarity thresholds.
Datasets
The authors introduce PreCISE-EMR, a standardized evaluation benchmark containing:
- MIMIC-III (v1.4): Single-center U.S. ICU database (26 tables).
- eICU (v2.0): Multi-center U.S. ICU database (31 tables, mixing wide and long table formats).
- SICdb (v1.0.8): European ICU database released in September 2024. Because Claude-3.5-Sonnet's training cutoff is June 2024, SICdb acts as a strictly unseen schema to test zero-shot generalization.
- Ground truth consists of 7 complex cohort selection scenarios and 56 standardized vital/lab features (mapped to 53–126 database-specific codes curated by medical experts via Athena OHDSI).
Key Insights and Ideas
- SQL as an instrument of perception, not just an endpoint: Traditional Text-to-SQL treats the query as the final deliverable. EMR-AGENT uses lightweight SQL probes (
SELECT DISTINCT, sample retrievals) as exploratory sensory actions to inspect database states and clear up ambiguities. - Self-compensating knowledge retrieval: When external database manuals and memos were withheld during ablation tests, the agent's observation SQL queries increased significantly. The agent automatically issued more exploratory queries to compensate for missing documentation.
- Open vs. proprietary capability gap: Frontier models (Claude 3.5/3.7 Sonnet) handled complex schema reasoning and candidate filtering well (CFSA F1 ~0.81 on unseen SICdb), whereas open-source baselines (Llama-3.1-70B, Qwen2.5-72B) degraded substantially (F1 ~0.18–0.22), struggling with multi-step schema logic and query synthesis.
Key Results
- Cohort and Feature Extraction: CFSA achieved an F1 score of 0.94 on MIMIC-III, 0.93 on eICU, and 0.81 on the unseen SICdb. Existing approaches (such as DinSQL and ReAct) degraded heavily on complex schemas (e.g., DinSQL achieved 0.00 on eICU and 0.07 on SICdb).
- Code Mapping: CMA achieved F1 scores of 0.516 (MIMIC-III), 0.648 (eICU), and 0.536 (SICdb), outperforming ReAct (0.07–0.22) and standard in-context prompting (0.02–0.12).
- Human-Labor Reduction: EMR-AGENT reduced cohort-extraction logic errors across 210 tasks to 155 (a 54% to 63% reduction in manual debugging relative to baselines). In code mapping, it reduced high-cost manual code searches from 933 checks to 399 (a 57% human effort reduction).
Limitations and Future Work
- Clinical complexity: The benchmark currently evaluates rule-bound inclusion criteria (age, ICU duration, record counts) and standard laboratory/vital values. It does not yet cover complex clinical definitions (e.g., sepsis onset, mechanical ventilation staging) or medications requiring RxNorm/SNOMED dose-route normalizations.
- Need for expert oversight: The authors emphasize that EMR-AGENT is designed to accelerate human onboarding, not replace human verification. Clinicians must still validate queries to ensure patient safety before downstream model deployment.
- Propagation of historical biases: Automated extraction does not correct underlying demographic and institutional biases present in the raw medical records.
Summary updated