LLM-Assisted Clinical Data Harmonization: Combining Automated ETL Generation with Semantic Vocabulary Mapping for...

Transforming clinical data into the OMOP Common Data Model requires structural schema mapping and semantic vocabulary harmonization, demanding both technical and clinical expertise. We investigated Large Language Models for both tasks using the eICU Collaborative Research Database. For structural transformation, context-enriched prompting with White Rabbit profiling reports guided Gemini 2.5 Pro to generate PostgreSQL Extract-Transform-Load (ETL) scripts, populating eight OMOP core tables after five debugging iterations. For semantic mapping, a hybrid approach combining vector similarity search with LLM refinement targeted Logical Observation Identifiers Names and Codes (LOINC) and Anatomical Therapeutic Chemical (ATC) codes, validated by clinical experts from Charité - Universitätsmedizin Berlin. Our approach achieved 93.9% precision for medications (n=148) and 78.5-96.8% for laboratory terms (n=158) depending on consensus criteria. LLM refinement raised medication precision from 60.8% to 93.9% and laboratory precision from 62.0-88.6% to 78.5-96.8%. Together, these phases reduce manual effort in OMOP transformation across three clinical domains, while expert supervision remains essential.