From EHR to insight: how a healthcare clinic brought its data to the cloud
A rehabilitation clinic with thousands of patient records, but no way to do anything with them outside the EHR system. No export, no API, no reporting capability worth the name. That was the starting point.
The problem
The clinic’s EHR system was designed for one thing: patient registration. And it did that well. But the moment the clinic wanted to answer questions like “what is the average treatment duration per diagnosis group?” or “which referrers generate the most patients?”, it hit a wall.
The system had no usable export function. No API. The only way to access data was through the application itself, screen by screen. For a clinic that wants to be data-driven, that is a dead end.
The clinic had already tried to reach a solution with the EHR vendor. The answer was always the same: “it’s on the roadmap.” But the roadmap wasn’t moving.
The approach
We built an EHR Connector: a custom component that bridges the gap between the closed EHR system and the cloud.
The architecture is deliberately simple:
Step 1: Secure connection. We established a site-to-site VPN tunnel between the clinic’s network and Google Cloud. All data remains encrypted in transit, and the connection is restricted to exactly the database ports required. No broad network access, only the strict minimum.
Step 2: Automated extraction. A Cloud Run service on Google Cloud connects to the EHR database through the VPN tunnel and extracts the relevant tables. This runs on a schedule: daily, in the early morning, when the load on the source system is minimal.
Step 3: Storage in BigQuery. The extracted data is written to BigQuery. Here it is normalized, cleaned, and prepared for analysis. BigQuery is the single source of truth; everything downstream starts from here.
Step 4: Reporting in Power BI. The clinic runs on Microsoft 365, so Power BI was the natural choice for the reporting layer. Power BI reads directly from BigQuery. No intermediate copies, no stale extracts.
The result is a Google Cloud & Azure architecture, with both platforms working together. The data pipeline runs on GCP, the reporting on the Microsoft ecosystem the clinic already had.
The result
Within four weeks, the clinic had working dashboards on current data. Treatment managers could run their own analyses for the first time (no IT tickets, no waiting. Referrer analysis, occupancy rates, treatment duration statistics) all available in Power BI, refreshed daily.
But the real value ran deeper. The clinic had, for the first time, its own copy of its data. Not locked in a vendor system, but in an open, queryable format. That meant not just reporting, but the ability to build models in the future, combine datasets, or integrate with other sources.
The lesson: certificates expire
Every technical solution has an Achilles’ heel. For VPN-based connections, it is certificate management.
Our VPN tunnel used mutual TLS authentication. That works reliably, until the certificate expires. And certificates always expire at the worst possible moment.
In the first months after go-live, the connector lost its connection one morning. The cause: an expired certificate on the clinic’s side. The data extraction failed silently: no error in the application, just a missing dataset in BigQuery.
The fix was threefold:
- Monitor the outcome. We don’t just detect whether the Cloud Run job runs, but whether data actually arrives. No data = alert.
- Certificate monitoring. A separate check that warns 30 days before a certificate expires.
- Documentation and ownership. Clearly document who is responsible for renewal, and when it needs to happen.
It sounds trivial, but certificate expiry is one of the most common causes of silent pipeline failures in secured connections. It is exactly the kind of risk you don’t see until it breaks.
What this demonstrates
EHR systems are notoriously closed. Vendors have little incentive to make data export easy: it keeps clients dependent. But that does not mean you are out of options.
With a targeted technical intervention (a VPN tunnel, an automated extractor, and scalable cloud storage) you can unlock data that would otherwise remain unreachable. Without needing the vendor, without disrupting the source system.
The investment is modest. The return is structural: a clinic that steers on data instead of gut feeling.