Optimizing an LLM-Based Clinical Data Querying System Using Metadata Enrichment and Task Decomposition

Accessing complex clinical registries traditionally requires SQL programming expertise, limiting data accessibility for non-technical researchers. In this paper, we designed and evaluated whether a text-to-SQL solution based on large language models (LLMs) could enable natural language querying of a real-world clinical registry under strict privacy and security constraints. Using self-hosted, open-source LLMs, we developed a multi-layered optimization framework incorporating metadata enrichment, query decomposition, hybrid retrieval, and SQL self-correction. We assessed its performance across 600 queries spanning one-, two-, and three-field complexity using execution-based validation. Accuracy was improved from 88.0% to 94.5% for one-field queries and from 10.0% to 82.0% for three-field queries. Real-world testing by data scientists revealed domain-specific challenges related to coded variables, clinical ambiguity, and multi-step reasoning. We summarize key technical and operational lessons learned and discuss implications for safe, scalable deployment of LLM-assisted analytic tools in clinical registry environments.