Hospital Data Warehouse Architecture: A Practical Guide

The standard shape is three layers. Staging holds raw extracts from source systems in close to their original form, with no cleaning and no business logic — it is the landing strip and the audit trail of what the source actually sent. The warehouse layer is the conformed, modelled centre, where entities such as patient, encounter, doctor, service, and location are defined once and reconciled across systems. Marts sit on top and are shaped for a specific audience: an OPD mart, a revenue mart, a lab turnaround mart.

Keeping these separate is what makes the system debuggable. When a monthly admissions figure looks wrong, you can walk backwards through the mart to the warehouse to the raw staging extract and identify exactly where the number changed. Systems that skip staging and transform on the way in lose that ability permanently, because the evidence of what arrived was never kept.

Marts are also where you accept duplication on purpose. A finance mart and an operations mart may both contain admission counts, computed from the same warehouse fact table with the same definition. That is fine. What is not fine is two marts computing admissions from two different sources with two different rules, which is how a hospital ends up with three admission numbers and no way to choose between them.

Master data in a hospital changes constantly, and the change usually matters. A consultant moves from general medicine to critical care; a patient changes address, phone number, or payer category; a ward is reclassified from general to semi-private. If the warehouse simply overwrites the old value, every historical report silently rewrites itself, and last year's departmental numbers change every time somebody transfers.

The conventional treatment is a type-2 slowly changing dimension: instead of updating the row, you close the existing version with an end date and insert a new version with a new surrogate key. Facts join to the surrogate key that was current at the time of the event, so an admission from March 2025 keeps pointing at the department the consultant belonged to in March 2025. It costs storage and a little join complexity, and it is almost always worth it for doctor, department, ward, tariff, and payer.

Not every attribute deserves history. A corrected spelling of a patient's name is a fix, not a change, and versioning it just adds noise. Decide attribute by attribute: does anyone need to reproduce a past report using the old value? If yes, type 2. If it is a data-entry correction, overwrite it and log the correction separately.

Grain is the single most consequential modelling decision, and it is worth writing down in one sentence before any table is created: one row per what? One row per OPD visit, one row per IPD admission, one row per billed line item, one row per lab order, one row per bed-day. Every measure in that table must be true at that grain, and mixing grains is how you get double-counted revenue.

The common trap is a wide table that tries to be everything — an encounter row carrying admission attributes, billing totals, and lab counts. It works until a patient has two lab orders and the admission count doubles. Model the low-grain events separately and let the reporting layer aggregate; a fact table per business process, joined through conformed dimensions, is far more durable than one table that pretends the hospital has one process.

Bed-day facts deserve special attention in Indian hospitals because occupancy, room tariffs, and payer rules all turn on them. A separate daily snapshot fact — one row per patient per day per bed — answers occupancy, length-of-stay distribution, and ward utilisation questions that an admission-grain table simply cannot, without any of the awkward date arithmetic those questions otherwise require.

Full reloads are attractive because they are simple, and they stop being viable at roughly the point the hospital becomes interesting. Incremental loading means extracting only what changed since the last successful run, which requires the source to tell you what changed — a reliable modified timestamp, a change-data-capture stream, or at minimum an append-only event log. If the source has none of these, that gap is a real finding, and it usually needs fixing in the source rather than working around in the pipeline.

Hospital data arrives late as a matter of routine, not as an exception. A lab result is authorised two days after the sample; a discharge summary is completed a week after discharge; a claim is revised a month later. A pipeline that only ever appends new rows will systematically understate recent periods. Design for restatement: reload a trailing window on every run, and make the reporting layer honest about which periods are still open.

Make loads idempotent and observable. Every run should be safely repeatable without duplicating rows, and every run should record what it read, what it wrote, and what it rejected. A platform such as HealUDoc, which keeps operational modules on a shared data model, reduces the reconciliation burden between sources, but the discipline of logged, restatable loads still belongs to the warehouse team.

Most analytical questions in a hospital do not need to know who the patient is. Occupancy, turnaround time, case mix, revenue per bed-day, and no-show rates are all answerable from a pseudonymous patient key. Under the DPDP Act 2023, the sensible posture is that identifiers are collected and processed for care and billing, and analytics works on the minimum it needs — which is usually a surrogate key plus coarse demographics.

Practically, that means name, contact number, ABHA number, and full address stay in the warehouse's protected zone with restricted access, while marts expose only the surrogate key, age band, sex, and area-level geography. Date of birth becomes age at encounter; a pin code may be enough where a full address is not. Re-identification risk rises as you combine rare attributes, so treat small-cell suppression in published dashboards as part of the design rather than an afterthought.

The exception is the workflow where identification is the point — recalling patients for follow-up, chasing an incomplete claim, contacting a defaulter on a treatment protocol. Route those through the operational system with its own access controls and audit trail, not through a spreadsheet exported from a mart. Keeping the two paths distinct is what lets you give analysts broad query freedom without broadening exposure.

Do not attempt to model the whole hospital before delivering anything. Pick one decision that a named person makes on a known cadence — the weekly OPD capacity review, the monthly payer mix discussion — and build the thinnest vertical slice that answers it: the source extract, the staging table, the dimensions it needs, one fact table, one mart, one report. Ship it, then let the second use case reuse the dimensions you already conformed.

Each subsequent slice gets cheaper because the conformed dimensions accumulate. By the fourth or fifth, patient, doctor, department, date, and payer already exist and are already trusted, and the work reduces to a new fact table. That compounding is the actual return on warehouse investment, and it only appears if you resist the urge to build a complete model up front.

Retire the sources you replaced. If the warehouse now produces the monthly occupancy figure, the old departmental spreadsheet must stop being maintained, or the hospital will keep two numbers alive and trust neither. A warehouse that adds a reporting path without closing one has increased the confusion it was built to remove.