Clinical data warehouse: design notes from production

A clinical data warehouse is a long-running database that normalizes patient, encounter, and claim records with full history and serves read replicas to analytics teams. What separates it from a generic warehouse is not the schema, it is the constraints: history that can never be silently rewritten, ETL that survives partner outages without duplicating data, and schema evolution that leaves an audit trail a reviewer can follow.

These notes come from a warehouse we built for a healthcare operator and then lived with through years of feature work, growing from hundreds of thousands of records to many millions, the build described in our clinical data warehouse case. The earlier system it replaced had the three classic diseases: ETL race conditions, normalization drift between jobs, and stored-procedure bloat that made every schema change a risk event. Each design decision below exists because one of those diseases taught it.

What the clinical part changes

A generic warehouse optimizes for query speed and storage cost. A clinical warehouse optimizes for a different property: the ability to prove things. An auditor, a regulator, or a partner may ask how a value came to be, what it was before, and who changed the schema underneath it. Those questions reshape everything downstream.

Referential integrity stops being a nice-to-have: a claim row pointing at a missing encounter is not a data-quality footnote, it is a defect in the record of care. History becomes mandatory: overwriting a patient record in place destroys exactly the information a dispute needs. And reproducibility becomes a design input: quality measures computed from the warehouse, HEDIS numerators and denominators being the sharpest example, must be regenerable from inputs, which means the warehouse cannot quietly lose or reshape what it ingested.

Normalization with history

Clinical sources disagree about shape, coding, and identity, so the warehouse normalizes: one patient model, one encounter model, one claim model. The design decision that matters is doing this without discarding the past. Every normalized record preserves its history: what the values were, when they changed, and which batch changed them.

History-preserving normalization is what makes the warehouse safe to correct. Upstream corrections, and clinical feeds correct themselves constantly, apply as new states rather than destructive updates, so an analyst can reconstruct what the data looked like at the time any past decision or report was made. The alternative, in-place updates, means every correction silently invalidates every report generated before it, and nobody can say which ones.

Normalization logic itself must live in one place. The drift we inherited came from parallel ETL jobs each carrying its own slightly different normalization; two jobs disagreeing about how to fold patient identities produced records that matched or did not depending on which job ran first. One code path per entity, shared by all feeds, ended that class of defect.

ETL that survives partner outages

Warehouse feeds come from external partners, and external partners fail: missed windows, half-delivered batches, replayed files. The property that makes this survivable is idempotency: every batch carries an idempotency key, and applying the same batch twice produces the same state as applying it once.

With idempotent batches, recovery is boring, which is the goal. A missed window resumes without producing duplicates; a partner that re-sends yesterday's file changes nothing; an operator can re-run a suspect batch as a diagnostic without fear. Without idempotency, every outage becomes a manual reconciliation project, and reconciliation projects in patient data are where integrity quietly dies.

The batches run as a fleet of background services rather than one monolithic job, so one partner's outage does not stall the others, and per-feed diagnostics make a stuck batch a visible event instead of a silent gap discovered at report time.

Forward-only schema evolution

Years of feature work means hundreds of schema changes, and the rule that kept them safe is forward-only migrations with an audit trail. No rollback that rewrites data: in an audited system, a rollback that mutates historical records is indistinguishable from tampering. A bad release is corrected by another forward migration, and the trail shows both steps honestly.

The companion rule: stored procedures ship from the codebase, never from the database UI. A procedure edited live in production has no review, no history, and no honest relationship to what the repository claims runs. Versioning procedures like any other code, reviewed, deployed, diffable, ended the gap between what the database ran and what anyone believed it ran. Between the two rules, every schema state the warehouse has ever been in is explainable, which is exactly what a review asks for.

Serving analytics without hurting ops

The warehouse serves two masters with incompatible access patterns: operational processes writing and reading transactionally, and analytics teams running heavy scans. The resolution is structural: read replicas for analytics, so an analyst's ten-minute query never contends with the operational path. Analysts get freedom, operations gets latency, and neither negotiates with the other.

Downstream consumption then has its own shapes. Batch reporting straight from replicas, templated clinical report generation across whole cohorts in our practice. And where many internal teams need live reads across sources rather than history, that is a different tool with different trade-offs: an aggregation hub in front, warehouse behind, each doing the job the other should not.

None of these decisions is glamorous, and that is the honest summary of clinical data warehousing: the wins come from properties, idempotency, history, forward-only change, replica isolation, chosen early and never traded away under deadline. Traded away, each one converts into a reconciliation project with patient data on the table. Choosing them early is what our healthcare integration practice is for.