Big data: a healthcare data warehouse
Designing a hospital warehouse from heterogeneous sources, and shaping the storage around the questions it will have to answer.
- Talend
- Hive
- HDFS
- Cloudera
- PostgreSQL
- HiveQL
- What it is
A data warehouse for a hospital group, fed by integration jobs from a relational database and open data files.
- What was hard
The sources share neither format, nor granularity, nor identifiers. The whole job is making them converge on a single model.
- What it proves
Every user requirement stays traceable through to the job that feeds it, and the partitioning is chosen to serve a named requirement.
A team project, within the big data block of the engineering programme. An academic case study: a hospital group wanting to steer its care activity.
Care data scattered everywhere
Care data exists, but not in one place. Consultation activity lives in a relational database. The reference lists of facilities and practitioners, the mortality data and the patient satisfaction surveys live in open data files published separately, with their own formats and their own identifiers.
No steering question can be answered without cross-referencing them, and the volume rules out doing it by hand: 28.4mrowsproductionTwenty-eight million rows scattered across dozens of files, to be brought together into one queryable base. spread over dozens of files, one of which alone, the national death registry, holds 25.1mrecordsproductionA file so heavy no spreadsheet can open it: that is where big data tooling stops being optional..
That is what changes the nature of the exercise: at this size you do not open a file, you stream through it.
Identifiers that do not answer each other
Requirements defined before the model. 8requirementstest setUser requirements traced through to the jobs: 8 requirements (test set). were set out up front, and each had to stay traceable through to the jobs: in the end, every integration job declares which requirements it feeds. That is what stops you building a warehouse nobody queries.
Identifiers that do not line up. One source’s facility identifiers are not another’s, and the geographical breakdowns differ from one file to the next.
A single working environment, the Hadoop distribution installed for the course. Its configuration is not documented in the deliverable, and the measurements below inherit that gap.
From modelling to the warehouse
Integration pipeline and warehouse
From heterogeneous sources to queries that hold up
- Relational database
- care, consultations, diagnoses
- Open data files
- facilities, practitioners, satisfaction
- Integration jobs
- extraction, selection and mapping of attributes
Distributed file system
- Star-schema warehouse
- one fact table, surrounded by its dimensions
- Partitioning and bucketing
- chosen to serve a named user requirement
Modelling in two stages. A conceptual model first, to understand the domain, then a star schema for querying: each consultation at the centre, and around it 9context tablestest setEvery consultation is stored with its full context, ready to be cross-referenced: who consulted, who treated, where, when, for which diagnosis. saying who consulted, who treated, where, when, for which diagnosis, with what satisfaction. Cross-referencing them becomes a plain join.
Graphical integration jobs. Each job reads a source, selects and maps the useful attributes, and writes to the distributed file system.
A partitioned and bucketed fact table. The consultation table is recreated with partitioning by diagnosis and patients distributed across buckets. The deliverable states how to generalise that scheme to the other tables; it does not do so.
documented trade-offA warehouse chosen over a data lake, because the sources are already structured; a star schema chosen over a snowflake, at the cost of storage redundancy compared qualitatively in the deliverable.
Shaping storage around the questions
Partitioning serves a named requirement, not a principle. The fact table is partitioned by diagnosis and its patients spread across buckets, and the deliverable says why: that is what should answer the requirement asking for a consultation rate by diagnosis over a period. The storage is shaped by the questions asked, which is the only way to size it without guessing.
The star schema, chosen after comparing three modelling options. A star schema denormalises the dimensions, so it duplicates information. In exchange, a query crosses fewer joins and the model can be read without a diagram in hand. The deliverable carries the comparison table, storage redundancy included: the trade-off is settled, not passed over in silence.
The transformations moved partway through the project. The first deliverable put them inside the visualisation tool. The second moves them into the integration jobs, and records the change of mind without justifying it. The reason, as I reconstruct it: doing transformations downstream means redoing them on every refresh, in a tool not built for it, and making them invisible to anyone who does not open the dashboard.
Internal rather than external tables, so that dropping a table takes its data with it: the life cycle stays controlled, at the price of an irreversible loss should anyone get it wrong.
What the project produced
A complete and traceable warehouse. The star schema is delivered with its 9context tablestest setEvery consultation is stored with its full context, ready to be cross-referenced: who consulted, who treated, where, when, for which diagnosis., the jobs that feed it exist, and each of the 8requirementstest setUser requirements traced through to the jobs: 8 requirements (test set). can be followed from the stated requirement through to the job serving it. That is what the project demonstrates, and it is the design work worth showing.
The performance measurement, however, demonstrates nothing
The deliverable measures two query times. A query filtered on a patient identifier and a practitioner identifier, on the flat table: 1.8secondstest setTwo-predicate query, unpartitioned table: 1.8 seconds (test set).. A query filtered on the patient identifier alone, on the partitioned table: 0.4secondstest setOne-predicate query, partitioned and bucketed table: 0.4 seconds (test set)..
These are two different queries. One carries two predicates, the other only one. A less selective query on an optimised table against a more selective query on a raw one: the comparison is not like-for-like, and the gap mixes the effect of the storage with the effect of the query. The deliverable nonetheless presents these two timings as an evaluation of partitioning.
And the filter does not fall on the partition key. The table is partitioned by diagnosis; both queries filter on the patient, which is the bucketing column. So partitioning is not what could explain a gain on these queries, supposing a gain were established at all.
I publish both numbers because they were measured, and this finding with them: a comparison protocol that changes two things at once measures neither.
What the measurement does not prove
The volume loaded is not documented. The timings above therefore have no scale: we know a query took a given time, we do not know over how many rows.
The first timing is a bound, not an exact measurement: the deliverable writes “more than”, followed by the value. The second is given as reached. A single run per query is reported, with no spread.
Encryption, federation and replication are described, not enabled. The corresponding section of the deliverable is documentary: nothing establishes that these mechanisms ever ran.
My individual contribution cannot be separated out. This is a collective deliverable, and I claim none of these numbers as my own. The work exists and is described here; the attribution is not.
If I started over
I would change one thing at a time. It is the project’s central defect, and I only saw it while rereading the deliverable for this page. Comparing an optimised table to a raw one requires running exactly the same query on both. Two different queries do not produce a measurement, they produce two numbers.
I would make the filter fall on the partition key. Measuring the effect of partitioning by diagnosis calls for a query that filters on the diagnosis. The protocol could not answer the question it was asking.
I would record the data volume with every measurement. A query time without a row count is not a result, it is an anecdote. It is the objection I raise elsewhere against a number published without a sample size, and it applies here.
Evidence
documented trade-offPartitioning by diagnosis and distribution of patients across buckets, chosen to serve a named user requirement rather than on principle: the storage is shaped by the questions the table will have to answer.documented trade-offA warehouse chosen over a data lake, because the sources are already structured; a star schema chosen over a snowflake, at the cost of storage redundancy compared qualitatively in the deliverable.documented trade-offThe transformations, first planned inside the visualisation tool, were moved into the integration jobs between the first and second deliverable, and the change of mind is written into the report.constraint metA four-person group project: the individual contribution cannot be separated out from the deliverables, so no figure is claimed as personal.Numbers and method
7 numbers and how each one was measured
- Rows of source data to integrate
- 28.4mrowsproductionTwenty-eight million rows scattered across dozens of files, to be brought together into one queryable base.Line count over each of the project's 39 data files, read as a stream in blocks, plus the non-empty rows of the spreadsheets. The care database dump, forty-five megabytes, comes on top and is not counted here.
- Death records in the heaviest file
- 25.1mrecordsproductionA file so heavy no spreadsheet can open it: that is where big data tooling stops being optional.Rows in the national death registry, header excluded. The file alone weighs close to two gigabytes, which rules out loading it into memory and forces stream processing.
- Total weight of the source data
- 2.12GBproductionTwo gigabytes of raw files to tame before the first query.Sum of the sizes of every file in the data folder, documentation included, measured on disk.
- Two-predicate query, unpartitioned table
- 1.8secondstest setA consultation query filtered on a patient identifier AND a practitioner identifier, run against the fact table stored flat, timing read off the graph produced by the cluster's monitoring tool.The deliverable writes "more than 1.8 seconds": that is a bound read off a graph, not an exact measurement. The volume of data loaded is not documented, and a single run is reported.
- One-predicate query, partitioned and bucketed table
- 0.4secondstest setA consultation query filtered on the patient identifier alone, run against the fact table recreated with partitioning by diagnosis and patients distributed across five buckets. Same measuring tool as the two-predicate measurement.This is not the query measured on the flat table: it carries one predicate instead of two. The two timings therefore do not form a matched comparison, and the gap between them does not measure the effect of partitioning.
- Context tables around the central table
- 9context tablestest setEvery consultation is stored with its full context, ready to be cross-referenced: who consulted, who treated, where, when, for which diagnosis.Count of the dimension tables in the delivered star schema: patient, practitioner, date, facility, diagnosis, death, satisfaction, location, hospitalisation, around a single fact table.
- User requirements traced through to the jobs
- 8requirementstest setRequirements defined ahead of the model, then attached one by one to the integration jobs: each job declares which requirements it feeds.