Electronic health record systems (EHRs) are widely used by physicians and other members of care teams in decision making. Nearly ubiquitous EHR adoption in the U.S. and elsewhere over the last decade spurred the current emphasis on the secondary use of healthcare data for research [1], and the widespread development of institutional patient data repositories for research at academic health centers and larger health systems [2, 3] A data warehouse is a collection of data that is subject-oriented, integrated, time-variant, and non-volatile that can be used to produce useful information for management decision making [4]. Organizations have developed clinical data warehouses (CDWs) to integrate, manage, and centrally provide access to EHR data (often incorporating other types of patient data) to researchers and other stakeholders.

Learning health systems require data from healthcare organizations, including internal and external clinical, operational, and financial data, which implies that healthcare data warehousing is becomming an expected capability of healthcare organizations (HCOs). In Academic Medical Centers (AMCs) where these data are also needed to support research, we would expect heightened emphasis on CDW methods. Despite considerable effort within HCOs to develop CDWs, data warehousing is not a common topic within biomedical literature. We conducted a scoping review to gain insight into the state of CDW methods, technological developments, current foci, impacts, and potential gaps from the literatures as implemented as a CDW. A scoping review, as defined by the Canadian Institutes of Health Research, is an “exploratory project that systematically maps the literature available on a topic, identifying key concepts, theories, sources of evidence and gaps in the research [5, 6, 7, 8].” Scoping reviews are undertaken prior to a full synthesis when a domain’s literature is large, or conversely, as was the case here, when there is a lack of literature, to specify what is known [7]. We chose to search solely PubMed, the world’s largest freely accessible online biomedical citation database [9], precisely because it is where the most healthcare-related journals are indexed, including those for clinical research informatics, a domain in which CDWs are a focus. The scholarly literature on CDWs at HCOs indexed within PubMed is likely to be more comprehensive than elsewhere, day [10].

The focus of the study was to achieve the following goals:

The PubMed query returned 1,763 citations for the decade spanning 2011–2021 (Figure 1). Ninety-eight citations were excluded in the first-round review (30 were non-English, 66 had no full-text). Another 1,527 citations were excluded in the second-round abstract review (1,092 articles were not related to CDW; 435 described only the use of data from a CDW. For the remaining 140 citations, full-text articles were reviewed by 4 reviewers, and 3 articles were excluded. Finally, 137 articles were included, abstracted, and analyzed (Appendix 1).

As shown in Figure 2, article numbers increased from 2011 to 2015. They dropped in 2016 after the former high in 2015, but rose again through 2021. For 2021, we collected articles until September. More articles are expected in the rest of the year during the COVID pandemic. Between 2011 and 2021, CDW-focused articles in the biomedical literature averaged 12.5 per year ranging from 5 to 21.

The 137 articles abstracted were published in 55 journals and proceedings from three conferences. Sixteen journals published two or more included CDW articles (69% of the included CDW articles) (Figure 3). Studies in Health Technology and Informatics (30 included CDW articles) also publishes conference papers from multiple informatics meetings. The six publications with the most CDW articles (68/137) are informatics journals, as are several others.

From a geographical perspective, North America and Europe lead with the highest contributions in articles numbers, having 61 and 53 respectively. They are followed by Asia with 13, Australia with 5, South America with 4, and Africa with 1. Notably, eight research groups have contributed more than two articles each. These groups include those led by Frank Puppe, James J. Cimino, Raphael W. Majeed, Andrew Post, Shawn Murphy, Griffin M. Weber, Michael Marschollek, and Michael G. Kahn.

The review highlights areas of CDW focus in the literature, and exemplar projects, with brief explanation of key CDW concepts. Given the necessity of data for health care quality improvement and performance measurement since the landmark 1999 report To Err Is Human: Building a Safer Health System, and for clinical research since the CTSA program, the lack of emphasis on data warehousing is striking. Several factors may be at play. The international Good Clinical Practice (GCP) standard for clinical research did not emphasize data handling until the 2018 revision. Similarly, a recent systematic review of Data Management Plan requirements found an overwhelming emphasis on data sharing with little attention devoted to data collection and processing [150]. Others have attributed this to a lingering perception of data collection and handling as clerical and largely ignore their impact on research results [150, 151]. As evidenced by the literature, the lack of scientific consideration is accompanied by a lack of evidence-based practice recommendations and lack of defined professional competencies, with the latter recently called to attention by the Healthcare Data Analytics Association (HDAA) [152]. These findings, consistent with recent qualitative and survey work within the CTSA organizations, underscore the gap between the touted importance of data to results and the lack of attention and resources necessary to ensure that data are capable of supporting research.

CDW architecture: CDW architecture depends heavily on the specific data domains, eg, intensive care unit, dentistry, or specific disease registries, it is designed to store. No one design fits all needs and the literature bears out variability across the spectrum from, (1) building a data warehouse from scratch – the most frequently reported, (2) customizing an existing architecture for a specific domain, or (3) using a commercial data warehousing product. Since we did not include the terms of disease-specific data registry or commercial architecture in the query, the architecture reported here could under-represent the disease-specific or commercial CDWs. The majority, 88% (121) of the articles reported whether they were commercial or public architectures [153]. The degree to which further details on products or schemas was reported, varied across the literature; for example, one study described the usage of facts and dimensions in building their data warehouse to process data from electronic systems [106, 107] and another reported the usage of existing warehouse architecture using Microsoft products in their implementation [134]. In addition, one article reported the commercial product name INDEPTH for their warehouse but very limited details on the architecture were disclosed [37]. Of the public architectures, the majority used i2b2, which is similar to the star and snowflake schemas [154], followed by domain-specific data repository models that used relational tables [4].

Data model: Some CDWs are built on a generic relational database data model (as discussed in the architecture section). However, many are now constructed using a common data model (CDM) and open-source clinical data frameworks devised for standard representation of health information that provides better data analysis and data exchange. However, the term “common data model” was not included in the query. The data model reported here is only based on the literature as implemented as a CDW. It could therefore under-represent the other CDMs, except i2b2. In our results, 35 studies utilized CDMs that include OMOP, CDISC operation data model, and i2b2 one use of which is as an open-source clinical data model (see Table 2). Three studies extended the i2b2 querying mechanism to support data analysis of diverse underlying CDMs, eg, OMOP and PCORnet, which provided a means to separate data model and querying techniques [60, 91, 122]. One utilized an i2b2 application programming interface (API) and extended it to query OMOP and PCORNet data models, with no significant performance degradation in their evaluation [60]. Another highlighted the lack of querying interface for OMOP and developed an algorithm that can translate any given i2b2 query to run against OMOP data model programmatically [91]. That algorithm took advantage of i2b2’s webclient and its metadata, which prevented the need for a full i2b2 data model installation. The third used i2b2’s new multi-fact table to use OMOP CDM as a source for i2b2 observational data without changes to the i2b2 source code [122]. That use case demonstrated the combination of OMOP CDM, data management of i2b2, and an interface that allows querying of both i2b2 and OMOP. No CDM, however, covers all use cases and needs, so custom in-house and proprietary models specifically designed for the disease domain and type of data are still developed [155]. Two studies implemented a custom data model to capture perioperative anesthesia and omics data [86, 146]S.

Data domain: CDWs are generally built to provide a holistic view of patient care by the inclusion and sometimes the integration of other data. This can include external data, such as claims; molecular data, such as metabolomics; multi-level data, such as viral sequences; environmental measures, such as air and water quality; place-based social measures, such as neighborhood transience; and unstructured data [156]. We found that seven studies [19, 29, 31, 62, 115, 119, 144] developed a system that combined longitudinal clinical data with unstructured documents while four [49, 97, 136, 145] used imaging data along with longitudinal clinical data. In contrast, two studies focused on single clinical specialty for better management and querying [22, 109]. To facilitate clinical and translational research, however, combining different data domains is important, and only one study by Cimino included comprehensive domain representation ranging from imaging, unstructured, molecular, blood bank, and longitudinal clinical data [135]. An area for additional work is developing methodologies for data integration.

Data integration/ETL: Data intake from source systems and their integration into a data warehouse occurs through a process commonly known as ETL. ETL is a sequential process that starts with identifying needed clinical variables from the source systems, includes mapping the data into the warehouse data model, and then implementing computer programs that use the mapping to write the external data to the warehouse tables. Common ETL operations include deidentification, assignment of unique identifiers, reformatting data, recoding data to standard terminologies and ontologies, and data linkage. A similar process is often followed to deliver data from an institution’s data warehouse for secondary use; in this case, the warehouse data are often mapped to a CDM that supports pooling data from multiple institutions. ETL processes have received the most attention in the literature. A systematic review focused on ETL and discussed ETL steps in detail [149]. Nineteen additional studies discussed ETL and focused on: terminology mapping [39, 46, 55, 73, 92, 106, 107, 124], data deidentification [129], specific data type extraction [15, 87], unstructured data ETL [61, 84, 109], automatically generated ETL [40, 46, 114, 124], re-use ETL [135], real-time ETL [88, 129], and ETL efficiency [86]. Adding these health care-specific practices to general data warehousing frameworks may provide a foundation for health care-informed ETL best practices in health care data warehousing. Alternatively, others such as a FHIR-to-i2b2 transformation toolkit [123], which directly transforms primary EHR data from standardized FHIR resources into I2b2 CDM, focused on automating ETL processes and decreasing the laborious creation and maintenance of ETL processes.

Data quality: Clinical decision-making and research that relies on data from CDWs require high-quality data. Quality management was an important area for CDW development. However, only 9 per cent (13) of the articles reported data quality assessment (DQA) effort (11 case studies and 2 discussion papers). Rule-based [20, 34, 66, 98, 137] and redundancy-based [20, 58, 134, 147] methods were the most commonly used DQA methods (in 5 and 4 articles, respectively). Crowdsourcing [34], data profiling [32] and distribution method [67] were reported by one article each. Seven articles assessed different data quality dimensions, including completeness [20, 58, 66, 98, 147], validity [20, 32, 58], consistency [66, 98], accuracy [98, 147], plausibility [66, 67] and timeliness [20]. Different DQA processes were identified from nine articles, including routine DQA in the CDW [20, 32, 34, 67, 98, 147, DQA during data extraction [58], comparing data in the CDW with its source data [134], and DQA at data entry [137]. Only two articles reported having processes in place and commitments from source system owners to help to investigate, remediate, or make changes to prevent future occurrence of data quality problems [20, 98].

Semantic data representation: The key to successfully integrate and represent clinical data is through the use of semantic interoperability methods that describe complex health care information in computable ways [157, 158]. Standardized clinical terminologies and ontologies are ways that provide semantic representation of clinical concepts to achieve automated interoperability and to enable the best possible use of such data [159, 160]. However, a challenge is that any one terminology or ontology is not suitable for all purposes. The majority of the studies in our review used or extended i2b2 ontology in their work because of its simple, flexible, open-source data representation [161]. Two studies [15, 33] adopted the Protégé ontology framework for building a custom ontology whereas one study [135] developed a custom tool to address the practical needs of real-world data and to address the limitations of the i2b2 ontology eg, support for multiple hierarchies. Use of standard terminologies is vital to facilitate data sharing for clinical research and automated clinical decision support [162]. The majority of the studies used ICD-9 and ICD-10 standard terminologies for concept mapping. Systemized Nomenclature of Medicine – Clinical Terms (SNOMED-CT), Logical Observation Identifiers Names and Codes (LOINC), and Unified Medical Language System (UMLS) were also used frequently. One study [133] used ICD-9, SNOMED, UMLS, and LOINC for their mapping needs, and two studies used both ICD and CPT [29, 32]. To fully exploit the potential of both standard terminologies and ontologies, five studies developed custom ontologies that incorporated standard terminologies such as ICD, UMLS, and SNOMED [14, 28, 35, 125, 149]. One study described the limitations of terminology mapping tools in Intensive Care Units (ICU) and created a concept vocabulary of 942 clinical parameters to link concepts during the COVID-19 emergency [87]. Additional work is needed to build easy-to-use flexible terminology mapping tools for streamlined concept mapping in ICU settings. Four studies used document-based clinical document architecture (CDA) and other HL7 data standards [19, 35, 106, 107] and one study [57] used Clinical Data Interchange Standards Consortium (CDISC) data standards rather than terminologies and ontologies for knowledge representation and sharing. A likely future direction for CDWs is more HL7 FHIR data standards-based representation work for interoperability.

CDW governance structure: Among the articles that mentioned governance structure, two main types of structure were identified, including extant EHR governance (focus on data itself) and warehouse-specific governance (including data management and data warehouse procedures) with nine and 10 articles reported, respectively. No articles after 2015 referred to extant EHR governance structures, but five reported warehouse-specific ones, which is a more comprehensive and specific solution to satisfy data-use related regulatory requirements.

End-user support and documentation: CDW end-users are not familiar with the technical aspects of their projects and lack of data literacy, which is a challenge for CDW staff [105]. Many articles discussed query interfaces or tools to support end-users. However, only two articles mentioned the need to improve end-users’ data literacy with respect to knowledge about clinical data, use of EHR data in research, and protection of patient privacy, which highlights an area for more work [71, 105].

Staff training: Although two articles mentioned that they provided staff training through a series of programs and workshops to support data literacy [37, 114], none reported needs of assessment results, position-specific training requirements, or training plan details. To our knowledge, there are no publicly shared training modules for health care data warehousing professionals. HDAA has recently undertaken a campaign to identify professional competencies for healthcare data warehousing [152].

Financial sustainability: Only two (2/137) of the articles mentioned CDW cost models or sustainability [71, 138]. However, given that 48 articles mentioned grant funding, usually in the Acknowledgements section, it is likely that CDWs lack long-term funding and sustainability. One article discussed two approaches that enterprise data warehouses for research use to fund themselves: fee-for-service and full-time equivalent funding, but the article notes that the ability to fulfill requests does not keep up with demand, which indicates financial and staffing deficits given demand [71]. In light of the cost pressures in AMCs, a shift in CTSA emphasis, from clinical and translational research (CTR) support to clinical and translational science (CTS), and the growing need to provide data to support the development of clinical researchers and the sustainability of clinical research programs needs to be prioritized. In addition, information on the real costs and benefits of data warehousing to institutions is drastically and urgently needed.

Geographical differences: We categorized and analyzed the articles based on geographical distribution. North America and Europe lead the research in CDWs, though their emphases differ as a result of variations in health care systems, research focus, regulatory environments, and data infrastructure. In North America, CDW research concentrates on integrating CDWs within large, complex health care delivery systems, including hospital networks, insurance companies, and research institutions. Many CDWs are developed by individual health care organizations or through collaborations between academic and private sectors. In contrast, European CDW research places emphasis on national or regional health care systems, aiming for unified data integration across public health networks. This approach is often supported by government-led initiatives or by large-scale public health projects, fostering uniform standards and interoperability across countries. This leads to differences in innovation. In North America, there is a high emphasis on leveraging advanced analytics, artificial intelligence (AI), and machine learning to derive insights from clinical data. In contrast, Europe has a strong focus on public health informatics, population health management, and the integration of social determinants of health into their CDW research and applications.

Diversity of contributing groups: The diversity of contributing research groups is notable in the field, as evidenced by the author distribution across 137 articles. Eight key research groups stand out, each contributing three to five articles. In addition, numerous other groups have provided valuable insights, contributing fewer than two articles each. This distribution highlights the essential need for cross-validation and collaborative efforts to enhance the robustness and generalizability of findings across different contexts and settings. Such collaboration among diverse research groups is crucial for advancing the field.

Gap analysis: We reviewed 2021 to 2022 HDAA conference presentations and 2023 HDAA conference submitter-identified tags to distill 30 current topics at the applied practitioner forefront [2]. The 50 presentations in 2021 and 2022 conferences covered 26 topics. Among these, predictive analytics (17), machine learning (8) and staffing (4) were the top 3 topics. Data lakes, data fabric, Microsoft Azure Cognitive Service and ChatGPT-4 were added in 2023 as new tags. We matched these topical categories to our included articles. Articles could be matched to more than one category. Figure 4 shows where the published literature aligns with topics from HDAA and where there are particular publication gaps. Thirteen topics were covered by at least 10 articles, and 17 topics by less than 10, of which, equity, work-life balance, career development, and specific data technologies or services (eg, data fabric, data mesh, data lakes, Microsoft Azure Cognitive Service, ChatGPT-4) were not covered by any articles. Some topics have minimal or non-published articles due to the emphasis of the query, such as data lakes and Microsoft Azure Cognitive Service. However, these topics should be discussed in the literature as do similar articles involving technical developments and CDW-related sociotechnical issues related to staff, end-users, and decision-makers. To reach a state of health care data analytics maturity, more work and published dissemination on data quality improvement and approaches for predictive analytics are needed [163].

Lastly, the research methods found in the literature were largely single site demonstrations or opinion articles based on experiences at a single site. To wit, all were one site/project case studies, discussions, or reviews except for six articles: five qualitative studies and a survey. Four of the five covered single aspects: two single-site/project articles [70, 100] and two multi-site articles [110, 116]. Only two were multi-site, multi-aspect studies, a survey [3] and one qualitative study [71]. To increase the evidence-base for CDW practice, more multi-site, multi-aspect CDW benchmarking and reporting are needed.

In recent years, specific requirements for CDWs have emerged, driven by the unique needs of clinical research and the growing complexity of data. Best practices specific to CDWs have been evolving to meet the challenges of managing clinical research data. These practices include: (1) data integration and interoperability to facilitate seamless integration of diverse clinical data sources and ensure interoperability within the CDW; (2) data governance to ensure data integrity, confidentiality, and compliance with regulatory requirements within the CDW environment; (3) data quality to implement procedures to ensure high-quality, accurate, and reliable clinical data within the CDW; and (4) metadata management to maintain comprehensive metadata to support data discoverability, provenance tracking, and data lineage within the CDW.

Despite the established best practices and newly articulated requirements, significant gaps remain in the CDW literature that need to be addressed to enhance the efficacy of clinical research. The gap analysis revealed the following issues. First, while general data integration practices are well-defined, there is a lack of detailed strategies tailored to integrating diverse clinical data specific to research needs. Second, current data quality practices may not sufficiently address the nuances of clinical research data, such as the need for high accuracy in longitudinal studies. Third, many CDWs are not designed to scale efficiently with the rapidly growing data volumes and evolving research methodologies. Fourth, the lack of robust metadata and provenance tracking systems can hinder data discoverability and reproducibility in clinical research. Last, ethical considerations and patient consent protocols are not always integrated seamlessly into data management practices. By addressing these gaps and by implementing the recommended best practices, we can significantly enhance the utility and reliability of CDWs in clinical research settings, ultimately leading to more robust and impactful research outcomes.

Limitations: There are several limitations in this review that we wish to acknowledge. First, we only searched the query in PubMed as it is a comprehensive resource in the clinical arena. We could retrieve more related literature with other search engines. Second, we included the terms of medical record in the query to cover more related literatures. However, this process might miss the articles that did not mention the terms of medical record and get noisy articles. Beside medical records, the specific data registry, such as cancer, cardiovascular, and COVID data, are important considerations, but it was beyond the scope of this review. Adding terms of specific disease registry would return more CDW articles. Third, we emphasized the literature as implemented as a CDW, especially academic-implemented CDWs, due to only including the terms of i2b2 in the query. Doing this led to an over-representation of i2b2 use and an under-representation other CDMs, commercial CDWs, and specific methodologies, such as data model and data quality. This bias could be corrected by including the terms of CDM, commercial CDWs, OMOP or OHDSI (Observational Health Data Sciences and Informatics), or by expanding the review to include EHR-published papers (such as EHRN.org). Last, we did the final query run in September 2021. However, an explosion of publications related to CDW happened after 2021 as a result of the COVID pandemic. The query needs to be modified and re-run to get more recent and comprehensive information.

The study summarizes the studies from 2011 to 2021 with important topics in CDW. It indicates the topics that have been significantly developed and the aspects that need additional focus and reporting in CDW between existing general data management best practices and recently articulated requirements for research data. More multi-site and multi-aspect studies are needed to foster maturity at CDWs. The findings of this scoping review will help readers to understand current CDW methodologies and improvement foci and we hope will inspire HCOs to invest in CDW deployment, ongoing optimization for expanded utility of existing CDWs, and increased CDW benchmarking and knowledge-sharing.

What is known about this problem:

What this article contributes:

The additional files for this article can be found as follows: