Clinical data warehouse

The problem

A healthcare operator ran a clinical data system that needed to grow from a few hundred thousand records to many millions, while preserving referential integrity, audit history, and read replicas for analytics. Earlier ETL jobs had race conditions, normalization drift, and stored-procedure bloat that slowed feature work and made schema changes risky.

The approach

We built a .NET data warehouse on MSSQL with a fleet of Windows background services for ETL: patient, encounter, and claim records normalized with full history; ETL batches against external partners with idempotency keys; stored procedures shipped from the codebase, not the database UI; read-only replicas for analytics teams. Schema migrations follow a forward-only pattern with audit trail, so a release never silently rewrites historical data.

Stack and engineering choices

• .NET ETL services
• MSSQL transactional core
• Forward-only schema migrations
• Idempotent partner batches
• Read replicas for analytics
• Code-managed stored procedures
• History-preserving normalization

Outcome

The warehouse scaled through years of feature work without losing referential integrity. Analytics teams hit replicas without slowing operational queries; stored procedures are versioned and reviewed like any other code. ETL batches handle partner outages gracefully via idempotency keys, so a missed window does not produce duplicates on resume.