A lifecycle-aware framework for assessing data quality across sequential ETL transformations: application to the INTEGO-II primary care database (Preprint)
Preprint
- 5 August 2026
- preprint
- Published by JMIR Publications Inc. in JMIR Preprints
Abstract
BACKGROUND Routinely collected EHR data are increasingly repurposed for secondary use. As data move through multi-stage pipelines, data quality losses can be introduced at each transformation step. Yet most assessment approaches treat data quality as a static, post-hoc property of a final dataset, making transformation induced losses invisible. Emerging regulatory frameworks demand transparency and traceability across the full data lifecycle but provide limited operational guidance on how to achieve this. OBJECTIVE To introduce a lifecycle framework for assessing data quality across sequential ETL transformations and validate it using the Intego-II database. METHODS The framework comprised two components: information loss and selection bias. It was applied to Intego-II database by comparing three dataset representations within ETL Stage 2: pseudonymised source data, transformed data, and standardised data. Four indicators were evaluated across the condition, procedure, and drug exposure domains: patient coverage, domain coverage, temporal coverage, and mapping coverage. TPQAs evaluated selection bias using Cohen’s d and standardised mean differences. RESULTS Patient attrition of 31,6% (1,421,150 to 971,762) arose from SSN-based deduplication of legacy EHR records and eligibility-driven exclusion. ICD-10 to SNOMED CT mapping achieved 58,3% coverage, attributable to a decimal separator inconsistency affecting 3,871 codes and approximately 326,800 condition records (1,7%). Drug concept granularity was compressed by 99,9% due to three co-occurring failures: non-standard source codes, incomplete national mapping coverage, and structural ATC-to-RxNorm vocabulary gaps. TPQA-1 showed eligibility-driven attrition was demographically neutral (Cohen’s d = -0,003; SMD = 0,031). TPQA-2 identified vocabulary-driven drug domain loss disproportionately affecting older patients (Cohen’s d = -0,49). CONCLUSIONS Sequential ETL processing introduces heterogeneous, mechanism-specific data quality losses invisible to post-hoc assessment. The framework is reproducible, generalisable, and supports the transparency and traceability requirements of the European Health Data Space and EU AI Act.Keywords
This publication has 31 references indexed in Scilit:
- ATC-to-RxNorm mappings – A comparison between OHDSI Standardized Vocabularies and UMLS MetathesaurusInternational Journal of Medical Informatics, 2024
- 'Where have my patients gone?': A simulation study on real-world data processing in Clinical Data WarehousesHealth Policy and Technology, 2024
- The METRIC-framework for assessing data quality for trustworthy AI in medicine: a systematic reviewnpj Digital Medicine, 2024
- A Comprehensive Review of Bias in Deep Learning Models: Methods, Impacts, and Future DirectionsArchives of Computational Methods in Engineering, 2024
- Biases in Electronic Health Records Data for Generating Real-World Evidence: An OverviewJournal of Healthcare Informatics Research, 2023
- Fairness of artificial intelligence in healthcare: review and recommendationsJapanese Journal of Radiology, 2023
- Automating Electronic Health Record Data Quality AssessmentJournal of Medical Systems, 2023
- Common Problems, Common Data Model Solutions: Evidence Generation for Health Technology AssessmentPharmacoEconomics, 2020
- Effects of eHealth on hospital practice: synthesis of the current literatureAustralian Health Review, 2018
- Moving towards best practice when using inverse probability of treatment weighting (IPTW) using the propensity score to estimate causal treatment effects in observational studiesStatistics in Medicine, 2015