Validating the extract, transform, load process used to populate a large clinical research database

Abstract

Background Informaticians at any institution that are developing clinical research support infrastructure are tasked with populating research databases with data extracted and transformed from their institution’s operational databases, such as electronic health records (EHRs). These data must be properly extracted from these source systems, transformed into a standard data structure, and then loaded into the data warehouse while maintaining the integrity of these data. We validated the correctness of the extract, load, and transform (ETL) process of the extracted data of West Virginia Clinical and Translational Science Institute’s Integrated Data Repository, a clinical data warehouse that includes data extracted from two EHR systems. Methods Four hundred ninety-eight observations were randomly selected from the integrated data repository and compared with the two source EHR systems. Results Of the 498 observations, there were 479 concordant and 19 discordant observations. The discordant observations fell into three general categories: a) design decision differences between the IDR and source EHRs, b) timing differences, and c) user interface settings. After resolving apparent discordances, our integrated data repository was found to be 100% accurate relative to its source EHR systems. Conclusion Any institution that uses a clinical data warehouse that is developed based on extraction processes from operational databases, such as EHRs, employs some form of an ETL process. As secondary use of EHR data begins to transform the research landscape, the importance of the basic validation of the extracted EHR data cannot be underestimated and should start with the validation of the extraction process itself.

Independent risk factors for surgical site infection after cesarean delivery in a rural tertiary care medical center

“…In an effort to validate coding, our institution performs an annual audit for code selection as part of each reviewed encounter and uses a baseline of ≥80 % as the minimal threshold. Furthermore, we have validated the correctness of our institutional data base elements to our EMRs and found a concordance rate of >96 %, and after correcting for the apparent discordances, the database was found to be 100 % accurate [30]. Labor management guidelines that promote vaginal delivery are intended to reduce the number of unnecessary CDs.…”

Validation of asthma recording in electronic health records: a systematic review

“…Unless the data are validated for research, the quality of studies generated from EHRs may be debatable. 6 – 9 Furthermore, the validity of different disease definitions is not always the same in a given dataset. Some diseases (such as asthma) might be coded using less specific symptoms, whereas the validity of diagnoses with very specific symptoms (such as tension pneumothorax) is likely to be better.…”

Clinical Data Warehouse a Review

“…The complexity of clinical data structure and diversity of medical operations requires implementation of complex ETL before the data are loaded into CDW storage. Different medical departments require various tools to connect different data sources and deal with a variety of data formats produced to apply ETLs [10,23]. To establish and implement successful CDWs, many approaches are available such as requirements on users, information, regularity and ethics.…”