Background
The health network had grown to 14 facilities through a combination of organic expansion and acquisition over fifteen years. The acquired facilities brought their own technology platforms — different EHR vendors, different lab information systems, different scheduling tools. There was no integration layer connecting them. Patient data lived in six separate systems with no consistent patient identifier across them.
Clinical leadership had been requesting cross-site population health reporting for three years. The analytics team had built a set of manual extraction processes — pulling files from each system's export function, cleaning them in Excel, and reconciling patients across datasets using a combination of name, date of birth, and facility codes. It consumed three days of analyst time each month and produced reports the clinical team trusted inconsistently because the reconciliation methodology wasn't auditable.
The Technical Challenge
Six source systems with incompatible data models, three different HL7 versions in use across the EHR systems, proprietary export formats from the pharmacy and lab platforms, HIPAA privacy requirements constraining every integration decision, and a compliance team that needed to be able to demonstrate full audit trails of who accessed what patient data and when.
The data lake needed to be the canonical system for analytics — not a copy of production data, but the authoritative source for reporting. That required the transformation and canonicalization process to be reliable enough that clinical and compliance teams would trust it.
Architecture Decisions
We chose FHIR R4 as the canonical data model for patient records. This was not a foregone conclusion — the health network's internal team had proposed a custom relational schema. The argument for FHIR was long-term: the healthcare industry is converging on FHIR for interoperability, and building the internal data model on FHIR now meant integrations with new systems (and replacements of existing ones) would be cheaper over time. The counter-argument was complexity — FHIR's resource model is not intuitive for analysts trained on relational databases. We resolved this by keeping FHIR as the storage layer and building analyst-facing dimensional models in dbt that were structured conventionally.
Azure Data Lake Storage Gen2 was selected for storage based on the network's existing Azure footprint. Azure Data Factory handled ingestion and the initial format transformations from HL7 and proprietary formats. Apache Spark on Azure Synapse handled the larger transformation workloads. The dbt layer built the dimensional models used for reporting.
Compliance and Security
HIPAA requirements shaped every architectural decision. Patient data at rest was encrypted with Azure-managed keys. Data in transit used TLS 1.2 minimum. Access to patient records was role-based with row-level security implemented in the Synapse semantic layer — analysts could only see records from facilities they were authorized to access. Every query against the patient data layer was logged to an immutable audit table.
We engaged with the network's HIPAA privacy officer at the start of the programme, and their team reviewed the security controls at each phase before patient data was loaded. This added time but was correct — discovering compliance gaps after data is loaded is significantly more disruptive than addressing them during design.
Patient Matching
The most technically complex problem was patient matching across six systems. No patient had a consistent identifier across all sources. We implemented a probabilistic matching algorithm using a combination of demographic attributes — name (phonetic matching), date of birth, gender, and address — scored and threshold-controlled. Matches below the confidence threshold were queued for manual clinical review rather than automatically merged. The clinical informatics team validated the matching logic against a known dataset of 50,000 patient records before it was used in production.
Results
The platform went live across all 14 facilities in two phases over 14 months. The monthly regulatory reporting process, previously requiring three days of manual work, now runs in under four hours using automated pipelines that update overnight. Clinical analysts have a unified patient view for the first time, with cross-site visibility that enables population health analysis that was previously impossible to produce reliably.
The compliance team's audit requirements are met through automated reporting from the access logs — what was previously a manual process before each regulatory review is now a query.
The patient matching engine processes records from all six sources with a false-match rate the clinical informatics team validated at below 0.3% — acceptable for population-level analytics, with a clear process for cases that require manual review.
What We Would Do Differently
The dbt modelling layer was underspecified in the initial design. We had built the FHIR ingestion well, but the analyst-facing models needed significantly more iteration with clinical end users than we had planned for. The first version of the clinical dashboards missed several reporting dimensions that the clinical team considered essential. We should have embedded a clinical analyst in the design process from week one rather than treating the analytics layer as a downstream deliverable.