Data Pipeline

Data Pipeline for Health Data and Economic Models

A data pipeline is a workflow written as code, whose steps carry health data out of the systems that recorded them and turn them into the tables, parameters and reports an analysis needs. In health economics it usually starts with extracts of claims data, hospital episodes or electronic health record data, then cleans them, maps local codes to a standard and checks the result before costs or event rates reach a health economic model. Because every step can be run again, the same inputs can be rebuilt when a new data cut arrives, and a reviewer can trace a number in a submission back to the extract it came from. The term here means software for handling data, not the drug development pipeline that horizon scanning tracks.

Extract, transform and load of health records

Many pipelines in health research follow the pattern called extract, transform and load (ETL). Data are extracted from a source such as a primary care database, transformed into an agreed structure and coding, and loaded into a store that analysis code can query. The Book of OHDSI describes this for the OMOP Common Data Model of the Observational Health Data Sciences and Informatics (OHDSI) community. Its ETL restructures native data to that model and adds mappings to the OHDSI standard vocabularies, typically through automated scripts such as SQL. The authors stress that it should be repeatable, so that it can be rerun whenever the source data are refreshed.

OHDSI divides the work by role. Data experts and common data model experts design the ETL together, people with medical knowledge create the code mappings, a technical person implements it and everyone takes part in quality control. The book says the largest issues in ETL code tend to arise in the mapping of source codes to standard concepts. In a cost study this is where a diagnosis or procedure code decides whether a record, and so its cost, is counted at all.

The NICE real-world evidence framework (ECD9) says that substantial data curation, including cleaning, exclusions and transformations, is needed to prepare original data sources for analysis. Where data have been converted to a common data model, it asks for the model and its version to be reported with full details of the mapping and any information loss, so that the study can be reproduced.

Steps as a graph that reruns only what changed

Each step in a pipeline declares the inputs it reads and the outputs it writes, so the whole can be drawn as a directed acyclic graph in which an arrow joins each step to every step that uses its output. Landau describes the targets package for R as a pipeline toolkit that finds the dependencies among computational tasks by static code analysis and builds this graph. On later runs it skips tasks that are already in step with their upstream dependencies.

For a cost-effectiveness model the graph might start with a raw hospital extract, which yields cleaned episodes, a cost per episode and then a cost per cycle in each health state of a state transition model, before the results and the report. If a new schedule of unit costs is issued, only the costing step and the steps downstream of it need to run again.

The DARTH framework of Alarid-Escudero and colleagues gives a matching layout for decision models in R. Their framework divides a decision analysis into five components, namely model inputs, decision model implementation, calibration, validation and analysis. Raw files sit in a "data-raw" folder and are cleaned or processed into inputs held in a "data" folder, and file names carry the number of the component that uses them. The authors also recommend unit tests that check whether each function returns outputs of the right type, dimension and value.

Provenance, versions and the audit trail

A pipeline can record how each output was made as well as making it. The W3C PROV data model (PROV-DM) defines provenance as information about the entities, activities and people involved in producing a piece of data, which can be used to assess its quality, reliability or trustworthiness. In PROV terms an extract file and a table of transition probabilities are entities, each pipeline step is an activity that uses some entities and generates others, and the analyst or organisation responsible is an agent. A pipeline that logs these links can show which extract and which version of the code produced a cost figure in a submission.

Sandve and colleagues give ten rules for reproducible computational research. The first asks that, for every result, the analyst keeps track of how it was produced, including the name and version of each piece of software and the exact parameters and inputs. They argue that an executable description of the workflow cannot drift out of step with the analysis in the way manual notes can. Other rules call for avoiding manual data manipulation, version control of all custom scripts and recording random seeds. In a Monte Carlo simulation for probabilistic analysis the seed fixes which parameter values are drawn, so recording it, with the software versions, lets a reviewer rebuild the same simulated costs and QALYs exactly.

The NICE framework (ECD9) asks for the names and versions of data sources, the dates of data extraction and the software used, with its version and dependencies. It says analytical code should ideally be public or available on request, and that an audit trail of the analysis, where feasible, can show the study was done appropriately.

Worked example with a rounding fault in a cost table

The Book of OHDSI gives the case of a bug that rounded a cost column to whole units, so that 3.82 became 4.00. It notes that this would matter little for studies of drug exposures but would be a critical bug for health economists. All numbers in this example are illustrative and show how a check inside the pipeline can catch such a fault before it reaches a cost-of-illness study.

1. Source totals. A pipeline loads 10,000 dispensing records for 500 patients. Item A costs GBP 0.45 and appears in 6,000 records, item B costs GBP 3.82 in 3,000 records and item C costs GBP 27.50 in 1,000 records. The source costs are 6,000 × 0.45 = 2,700, 3,000 × 3.82 = 11,460 and 1,000 × 27.50 = 27,500, a total of 41,660 and a mean of 41,660/500 = GBP 83.32 per patient.

2. Faulty load. The transform step rounds unit costs to whole pounds, so 0.45 becomes 0, 3.82 becomes 4 and 27.50 becomes 28. The loaded costs are 6,000 × 0 = 0, 3,000 × 4 = 12,000 and 1,000 × 28 = 28,000, a total of 40,000 and a mean of 40,000/500 = GBP 80.00 per patient.

3. Reconciliation check. The pipeline compares the total cost loaded with the total in the source extract.

$$e = \frac{C_L - C_S}{C_S}$$

Here $e$ is the relative error, $C_L$ is the total cost after loading and $C_S$ is the total cost in the source extract. The result is $e$ = (40,000 − 41,660)/41,660 = −1,660/41,660 = −0.0398, an understatement of about 4%. If the pipeline stops whenever the difference exceeds 0.5%, the run halts here.

4. Item-level check. The total hides errors in opposite directions. Item A loses all of its cost, item B is overstated by 0.18/3.82 = 0.047, or 4.7%, and item C by 0.50/27.50 = 0.018, or 1.8%. OHDSI suggests unit tests built from small sets of records, each testing one rule in the ETL design, and a test record priced at GBP 0.45 would expose the fault at once.

5. Effect on a comparison. Suppose patients on strategy X use 24 packs of item A a year and patients on strategy Y use 3 packs of item B. The true annual costs are 24 × 0.45 = GBP 10.80 and 3 × 3.82 = GBP 11.46, so strategy Y costs 11.46 − 10.80 = GBP 0.66 more per patient. After the faulty load they are 0 and 3 × 4 = GBP 12.00, an apparent difference of GBP 12.00, which is 12.00/0.66 = 18 times the true one. A 4% error in the total becomes a large error in the incremental cost, because the fault falls unevenly on the items that separate the strategies.

Choices that change what reaches the model

Exclusions are the first choice that can move a result. The Book of OHDSI says it is acceptable to lose data that are not of research quality during the ETL, and its conventions drop a person who lacks a year of birth. ECD9 warns that decisions in data curation, including linkages, transformations and exclusions, may have substantial effects on study results, and asks for curation done before the evidence developer had access to be described where possible. An analyst who receives a curated extract needs to know what the upstream pipeline removed.

Code mapping coverage is a second choice. OHDSI advises mapping first the source codes that cover most of the data, so rare codes may stay unmapped and their records can drop out of an analysis as missing data. Each OMOP database is also built on a specific vocabulary version, and a newer version can change how source codes are mapped, so the version belongs in the record of every run.

Where the code runs is a third. Smith, Schneider and Mohammed describe Living HTA, in which the data owner hosts sensitive data behind an application programming interface, the consultant's model code is sent to the data, and a scheduled workflow calls the interface and writes a report. Their example runs on the first day of every month and whenever the source code changes. ECD9 describes federated data networks in which patient data stay with the data holders, who run standardised analyses before results are combined.

What a pipeline allows in HTA submissions and model review

A scripted pipeline lets an HTA reviewer check where an input came from by rerunning the code. Alarid-Escudero and colleagues note that sharing a model built by point-and-click does not by itself give transparency or reproducibility, because manual steps are not recorded. A pipeline that starts from the raw extract also lets a reviewer vary an exclusion or a mapping and see how far the incremental cost-effectiveness ratio moves, which extends sensitivity analysis to the data work.

Smith and colleagues argue that a model report can then be updated without human intervention when the data change. They also note that the method needs skills in R, interfaces, automated reporting and GitHub Actions, while most of the industry still works in Microsoft Excel. A pipeline shows that the numbers follow from the stated code and data. It does not show that the code is right or that the data fit the decision, which are questions of model validation and data quality.

Data pipeline beside ETL, workflows and linkage

ETL is a common pattern inside a pipeline, and the pipeline is the whole chain of steps up to the report. A workflow management system, such as targets, is the software that runs a pipeline and tracks which steps are out of date. Record linkage, which joins records of the same person across datasets, is often one step within a pipeline, and so is named entity recognition when variables are taken from free text. Both have their own pages on HealthEconomics.wiki.

The end point need not be a table. A pipeline can turn records into a knowledge graph, which has its own page. HealthEconomics.wiki's own Health Economics Ontology (HEO) shows records turned into a published vocabulary. It is a vocabulary of health economics concepts taken from the wiki, each with a permanent web address, a name and a definition, written in SKOS and OWL and released in numbered versions.

Data extraction in a systematic review means recording study details and results on a structured form, a different sense from the first step of ETL. Data governance decides who may use which data and under which safeguards, and a pipeline is where those rules are carried out, for example by de-identification before data leave a secure environment.

Limits and common misreadings

The commonest misreading is that an automated pipeline is a correct one. Automation repeats a fault on every run, as the rounding example shows, so tests and reconciliation checks belong inside the pipeline. A second is that a pipeline is built once. The Book of OHDSI says the ETL is not a one-time expense and names the triggers for maintenance, which are changes in the source data, a bug in the ETL, a new vocabulary release and an update to the common data model.

Manual steps break the chain. Sandve and colleagues call manual changes to data inefficient, prone to error and hard to reproduce, copying and pasting included. A model whose inputs are typed by hand into a spreadsheet after a scripted extract has a pipeline only up to that point.

Tools do not remove the need for local knowledge. The OHDSI authors report that after several attempts they gave up on an ultimate user-friendly ETL tool, because such tools handle about 80% of an ETL while the rest needs low-level code specific to the source database.

Sources

• Observational Health Data Sciences and Informatics. Chapter 6, Extract Transform Load (chapter leads Blacketer C, Voss E). In: The Book of OHDSI. OHDSI; 2021. https://ohdsi.github.io/TheBookOfOhdsi/ExtractTransformLoad.html
• National Institute for Health and Care Excellence. NICE real-world evidence framework (ECD9). Published 23 June 2022, last updated September 2025. Chapters on assessing data suitability and on conduct of quantitative real-world evidence studies. https://www.nice.org.uk/corporate/ecd9
• Landau WM. The targets R package: a dynamic Make-like function-oriented pipeline toolkit for reproducibility and high-performance computing. Journal of Open Source Software. 2021;6(57):2959. https://doi.org/10.21105/joss.02959
• Alarid-Escudero F, Krijkamp EM, Pechlivanoglou P, Jalal H, Kao SZ, Yang A, Enns EA. A need for change! A coding framework for improving transparency in decision modeling. PharmacoEconomics. 2019;37(11):1329-1339. https://doi.org/10.1007/s40273-019-00837-x
• Moreau L, Missier P, editors. PROV-DM: The PROV Data Model. W3C Recommendation 30 April 2013. https://www.w3.org/TR/prov-dm/
• Sandve GK, Nekrutenko A, Taylor J, Hovig E. Ten simple rules for reproducible computational research. PLoS Computational Biology. 2013;9(10):e1003285. https://doi.org/10.1371/journal.pcbi.1003285
• Smith RA, Schneider PP, Mohammed W. Living HTA: automating health economic evaluation with R. Wellcome Open Research. 2022;7:194. https://doi.org/10.12688/wellcomeopenres.17933.2
• Baines D, Ashley M. Health Economics Ontology (HEO), version 1.3.1. Zenodo; 2026. https://doi.org/10.5281/zenodo.23516371