Hospital data from ten sources, checked and ready to publish
A pipeline that merges structure and quality data on German hospitals, checks it and delivers it as schema-validated XML. Alongside it, the web interface the domain team uses to maintain the mappings the export is built from. The result is published in the Bundes-Klinik-Atlas, Germany's public hospital directory, among other places.
Starting point
Looking a hospital up in the Bundes-Klinik-Atlas gets you case numbers, quality results, certificates, emergency care level, accessibility details. None of it sits in one place. It comes from around ten sources: registers, analysis databases, an internal REST API, Excel lists someone keeps by hand, and one legally binding list that exists only as a PDF.
All of those sources use the same site ID and still disagree about which sites exist at all. One register says a site is closed when it is still operating. Another knows about closures the first one is missing. An error here is a false statement about a named hospital.
Goal
A data release that can be reproduced, is signed off by the domain team and conforms to the prescribed format. The format is the hard part: the XML schema comes from outside, is versioned there, and the file produced has to validate against it or it is worthless. Empty values are not allowed.
On top of that there are two groups of recipients who may see different amounts of the data. One run therefore produces two variants: a complete one, and one in which small case numbers are masked, because otherwise they could be traced back to individual patients.
Solution
The domain mapping logic does not live in the pipeline code. Which quality indicator belongs to which care occasion, which treatment card carries which certificates and minimum volumes, what order it all appears in: that sits as data in a database and is maintained through a Shiny app. The export run only reads it. Changes on the domain side therefore need no deployment, and that is the most important design decision in the project.
The run itself picks up the latest delivery per source from the most recent dated folder, merges, computes, checks and writes. An Excel checklist is produced alongside: per data block the source, missing values, missing and excluded sites, and any hospital closed since the last export. That sheet is what the domain team signs off on.
Out come the XML files in both variants, plus aggregates across the 16 federal states and the country as a whole, and Excel copies of the lists used, all in a folder named after the export date.
Outcome
“The automated data preparation for the Bundes-Klinik-Atlas lets us publish on an efficient cycle while maintaining the highest data quality. Integrating new data sources smoothly and keeping current information flowing is an important contribution to transparency in German healthcare.”
Where it got tricky
A very large XML against someone else's schema
The obvious approach, creating and filling a node per site and data block, does not scale at this volume: an XML tree gets more expensive node by node the bigger it already is. The second reflex, simply spreading the work across processes, makes it worse. Individual data sets run to more than 500 MB, and shipping those to every worker costs more than the parallelism saves. What solved it was building the tree in a different order and cutting the work packages differently. Schema validation sits in front of the write as a hard condition: a file that does not validate does not get written.
Ten sources that do not agree
Join on the site ID, trust the source, and you produce exactly the errors that cost most on a public site: a closed hospital is shown, an existing one is missing, a name is wrong. Sites also get merged, and when they do, some figures have to be summed while others carry over from the receiving site. There is no general solution for this, only a defensible order of rules and a check that every source delivers something for every expected site. If something is missing, it shows up in the checklist instead of slipping unnoticed into the export. Every special case sits in the code with its own marker and a reason, and that is the real maintenance work here.
Suppression the schema does not provide for
Small case numbers must not be published. Emitting them as NA violates the schema,
which allows no empty values. The mask therefore has to be a value the schema accepts and the
recipient reads as suppressed rather than as zero. The masking itself is quick to do. The hard
part is that every affected figure gets treated the same way across all data blocks and none of
them slips through.
Technical implementation
The maintenance interface is a Shiny app built from UI/server modules, one per domain table.
Editing runs through rhandsontable, because bulk maintenance with dropdowns, filters
and inserted rows sits closer to Excel than to a web form. Whether someone may write depends on
their user group; anyone with read-only rights gets the same tables rendered read-only. Writes
happen in transactions, and every change lands in the audit trail with its old and new value, the
user and a timestamp. An entry still referenced somewhere else cannot be deleted: the app restores
the row and says why.
The export builds the XML tree with xml2 and validates it against the supplied XSD
rather than rendering a template, so building and validating happen in one place.
Parallelisation runs on two backends, because the two workloads are shaped differently:
future with furrr for the XML export, mirai for the
statistical computation. data.table appears only in the hot path; everything else is
tidyverse throughout. A deliberate break with the house style at the one place where it pays
off.
Before the export, a check runs across all data blocks: structure, column names, value ranges,
permitted values and the relationships between blocks. It is built with checkmate
as an assertion collection and gathers every violation instead of stopping at the first one. For
a run that takes minutes and gets signed off by a person anyway, the complete error list is
worth more than a fast abort. The violations collect on a sheet of the checklist; the only hard
gate is the schema check.
Hosting and ongoing work
The export run is a script a person starts. No scheduler. For a data release that gets signed off by the domain team before publication anyway, that is defensible, but it remains a decision.
Every run records what it was built from: the source folders used with their dates and the git commit of the code, written both into the checklist and into an intermediate-results database that holds the unfiltered release. No credentials sit in the code.
Data maintenance is split in two, deliberately. The mappings run through the app, day to day, independently of any export. The input data arrives as dated folders on a network drive: someone has to put it there by hand, and from there it is picked up automatically.
Development and maintenance are mine. This grew over several assessment years, and every new year brings a piece of maintenance: collection, reporting and forecast years run offset from each other, certificate deadlines shift, the schema gets a new version. Those constants therefore sit together at the head of the script.