1. Executive Snapshot
| Industry | Healthcare: radiology physician group (about 70 radiologists, South Texas) |
|---|---|
| Engagement | Clinical data platform modernization covering discovery, architecture, pipeline hardening, security and validation |
| Tech Stack | BigQuery, Cloud Healthcare API (HL7v2 and FHIR stores), Pub/Sub, Cloud Run (services and jobs), Cloud Workflows, Cloud Scheduler, Cloud Build, Artifact Registry, Cloud SQL for PostgreSQL, Cloud Monitoring and Logging, IAM, dbt |
| Key Results | 99.98% of HL7 accessions reach the analytics layer, with zero loss from the cleaned layer to the reporting layer · Performance Bridge logic reproduced in BigQuery to within 0.02% · Cloud Composer (about $520/month always-on) avoided in favor of Cloud Workflows (about $2/month) |
2. The Context & Challenge
The Legacy State
The client is a radiology group of about 70 physicians. It reads studies for a regional hospital system and a network of outpatient imaging centers. Every study a radiologist reads drives productivity credit, shift attribution, and RVU-based reporting. When D3V arrived, the data behind those numbers was spread across systems that didn’t agree with each other.
- Two clinical pipelines running in parallel. The legacy path moved HL7 messages through Corepoint, the Cloud Healthcare API, a custom Go service, a PostgreSQL database, Sling replication, and Dagster/dbt before landing in BigQuery. A newer medallion path (Lead → Bronze → Silver → Gold) was deployed and running every 15 minutes. Six independent Cloud Scheduler cron triggers ran the stack, and nothing coordinated them when a job failed.
- A third-party black box. Performance Bridge, a commercial radiology analytics product, supplied historical exam data and the business rules that defined a billable “case.” Its logic was undocumented. An on-premises PostgreSQL warehouse was tangled up with Performance Bridge replication. Vendor support was slow enough that the client’s engineers had to copy tables into BigQuery by hand so D3V could analyze them.
- Timestamps that couldn’t be trusted. In the reporting table, 6.83% of exams showed a start time later than their end time, compared with 0.33% on the legacy path. 74.6% of those inversions were off by more than a full day.
- Radiologists losing credit for their reads. When one radiologist dictated a single report covering several exams, only the last exam in the message received the report link. The other exams lost their radiologist attribution. Mapping radiologist NPIs from hospital badge numbers was a manual process, and nothing monitored it.
- Pipelines outside managed infrastructure. One billing-system pipeline (IMAGINE RIS, four databases) ran by hand from a workstation, with no scheduler, no audit trail, and no visible cost.
The Business Impact
- Data integrity directly affected people. A missing radiologist link means real clinical work goes uncredited, and that carries productivity and billing consequences. Inverted timestamps silently corrupt turnaround-time analytics.
- PHI exposure risk. Patient identifiers (MRN and date of birth) sat in broadly readable analytics tables with no column-level controls. A former employee still had project-level read access. Every monitoring alert in the project targeted the legacy pipeline, so if the new pipeline failed, nobody would be told.
- Cost leaks nobody could see. A paused Datastream CDC stream had been holding PostgreSQL write-ahead logs. The Cloud SQL disk grew about 5 GB per day, from 11.3 GB to 322.5 GB in 62 days.
- Strategic lock-in. Leadership wanted to remove Performance Bridge, consolidate on BigQuery, and design the platform so AI agents could reason over it later. None of that could happen on a foundation nobody could verify.
3. The Solution Architecture
The Strategy: Prove First, Then Harden
D3V’s discovery showed the client’s in-house team had already built a medallion architecture that was mostly sound. The real gaps were unverified correctness, missing orchestration guarantees, and governance. D3V re-scoped the work around a simple rule: every claim is backed by a live BigQuery query, and no production change happens without client sign-off. The client’s engineers shipped the fixes, and D3V supplied the diagnosis, design and validation. That made the knowledge transfer part of the delivery itself.
The target architecture, now in production:
Corepoint (on-prem) → MLLP → Cloud Healthcare API (HL7v2 store) → Pub/Sub
→ HL7 ingestion service (Cloud Run, Go) → raw HL7 layer (order + report tables) [raw HL7 retention]
Cloud Scheduler (*/15) → Cloud Workflows orchestration workflow
├── parallel: hospital-feed bronze job | scheduling-data bronze job (Cloud Run Jobs)
├── silver transformation job (Cloud Run Job, dbt) - halts on upstream failure
└── gold transformation job (Cloud Run Job, dbt) → gold case & schedule tables
→ separate PHI dataset (PHI-isolated)
AGFA PACS → Cloud Healthcare API (FHIR store, streaming to BigQuery) → PACS FHIR tables
Cloud Build → Artifact Registry → Cloud Run Jobs (Git-driven CI/CD)
Orchestration decision. D3V compared three options: 1 - keep the Scheduler-to-Cloud-Run model as it was, 2 - move to Cloud Composer, or 3 - add Cloud Workflows on top of Cloud Run. Composer would have added about $520/month in baseline cost without removing any Cloud Run execution cost, and it would have reversed the client’s recent migration off Dagster. D3V recommended Cloud Workflows. The workflow runs the bronze jobs in parallel, stops before silver or gold if anything upstream fails, wraps each job in structured error handling that writes to Cloud Logging, and runs under its own service account. The client then removed the old cron triggers. Projected cost is about $2/month.
The Engineering Hook: Wrong Timestamps Are Worse Than No Timestamps
The client’s working theory for the inverted exam times was a field-mapping bug: read OBR.7 instead of ORC.9, and the problem goes away. D3V tested that theory against the live data before anyone changed code.
- Across 1,222,023 in-progress order messages,
OBR.7was a byte-for-byte copy ofOBR.6, the scheduled appointment time, 100% of the time. Switching fields would have fixed 272 of 639,877 inverted accessions (0.04%). - The report-message fallback worked even less: only 22 of 621,673 candidates came from before the scan ended. That field holds the radiologist’s dictation time, not the scan start.
- Conclusion: the hospital’s RIS doesn’t send the actual scan start time in any HL7 order field. The pipeline was writing a future appointment date as the exam start, and a MERGE rule (
COALESCE(T, S)) kept that bad value permanently.
D3V recommended an unpopular but correct policy: write NULL. A NULL can be diagnosed; a wrong timestamp silently corrupts every turnaround metric built on it. The client deployed that change on Aug 10.
The diagnosis also pointed to where the real times were. Corepoint’s Performance Bridge preprocessing route already extracted true exam times, including a composite timestamp field from one RIS that the standard parser on the GCP path destroyed. The client’s team built a parallel feed: a second MLLP adapter, Healthcare API store, Pub/Sub topic and Cloud Run service feeding a separate exam-time layer. That parallel feed was the client’s own design, and D3V’s diagnosis is what made it possible. It loaded with zero inversions across all source types.
A second hard problem: knowing which business rules to keep. Performance Bridge’s warehouse applied four exclusion rules to decide what counts as a case, and replacing the vendor seemed to mean copying them exactly. D3V reproduced the vendor’s reportable-exam logic in BigQuery to within 0.02% across 9,044,277 exams. It then found that one rule removed zero exams on its own, and that about 257,000 of the warehouse’s exclusions came from a replication floor (data starting at 2022-01-01) combined with a NULL-predicate side effect, not from any business intent. Simulating both mechanisms explained 90.5% of the gap. Copying the vendor’s rules exactly would have carried over an artifact roughly 15 times larger than the only rule that mattered.
D3V recommended exposing the legacy rules as boolean flags on the case table rather than filters. Each report opts in, the implicit convention becomes explicit, and case counts stay reversible. The cutover plan also surfaced a dependency missing from every earlier version: a daily feed from the Performance Bridge server supplies report text to MIPS quality reporting. Switching the vendor off without rerouting that feed would have broken the practice’s screening-measure submissions.
Attribution fixes, validated end to end. The multi-exam attribution bug was fixed in the bronze layer with a window-function propagation that propagates the report link across every exam sharing an HL7 message control ID (MSH-10). D3V validated it across 116,718 multi-exam messages covering 247,677 exam slots and confirmed zero remaining orphans. D3V’s NPI audit found 405 of 407 badge numbers mapping correctly (99.5%), traced the 12 uncredited scans to two radiologists missing from the master roster, and added a dbt audit model that flags new unmapped badge numbers on every silver run.
Query-Driven Partitioning & Clustering
Most partitioning advice starts from the schema. D3V started from actual usage: 30 days of INFORMATION_SCHEMA.JOBS history for every table of 1 GB or more. For each table it measured how often the table was queried, how many GiB each query read, and what share of the table each query scanned.
- 92.3% of the 96 GB bronze footprint sat in tables with no partitioning. 117 of 128 base tables had neither partitioning nor clustering.
- The worst case was the hospital report table (2.15 GiB). Every one of its 4,843 queries read the full table, totaling 45,830 GiB in 30 days, more than 21,000 times the table’s own size.
- Nine tables were flagged. Eight had no partitioning or clustering. The ninth, the gold case table, was partitioned on a different key than recommended.
BigQuery can’t change a table’s partitioning in place, so D3V wrote a runbook for each pipeline type. Each runbook includes baseline counts, a verification query and a rollback:
- IMAGINE replication: Sling’s partition configuration plus one full refresh.
- Incremental hospital-feed bronze job: create-table-as-select and rename, with the orchestrator paused.
- Silver: a dbt config change plus a rebuild.
The client’s engineer ran them. By Aug 26, all 16 tables of 1 GB or more across the Lead, Bronze, Silver and Gold layers were partitioned, with row counts verified (for example, 9,629,841 rows for the Performance Bridge history table). The gold case table kept its existing date-of-service layout, which already matched how its dashboards query it. D3V’s follow-up review caught two remaining gaps: the PACS FHIR tables were partitioned but not clustered, and the QGenda schedule was partitioned on the wrong column. The review also trimmed the proposed clustering keys to the columns queries actually filter on.
Security & Compliance (The Trust Layer)
D3V assessed the Bronze, Silver and Gold layers against GCP and HIPAA-aligned practice. It used live INFORMATION_SCHEMA, IAM policy exports and Google’s IAM Recommender, not documentation alone.
- Least-privilege identities. The silver and gold jobs ran as the default Compute Engine service account, holding
EditorplusService Account Admin. That combination allows privilege escalation to full project control, and the IAM Recommender showed 11,737 of 11,754 permissions unused. D3V’s plan proposed two dedicated service accounts. The client expanded it to the whole pipeline: 8 new least-privilege service accounts were created and deployed, and the legacy accounts were disconnected. - PHI isolation. Patient identifiers (MRN and date of birth) were removed from the broadly consumed gold cases table and moved to a separate PHI dataset holding the same 9,833,862 rows. Restricting that dataset’s IAM grants completes the boundary, and that step is on the cutover checklist.
- Access hygiene. D3V found that a former employee’s still-active project role gave standing read access to PHI in a dataset created after he left. It also found two service accounts with 100% unused
Editorpermissions that still had write access to the reporting tables. Both findings went into the client’s risk register. - Alerting that reaches the right people. All four of the project’s earlier alerts targeted legacy resources, and three sent notifications only to the former employee. They were replaced with 17 enabled Cloud Monitoring policies routed to current staff.
- Change control. Container deployments moved from manual bash scripts to Cloud Build → Artifact Registry → Cloud Run Jobs, triggered from the client-owned GitHub repository and logging only to Cloud Logging.
- Encryption made a deliberate choice. D3V confirmed Google-managed encryption on all 137 bronze tables and documented the option of customer-managed keys (CMEK) for the PHI datasets, along with its operational risks. That turned an unexamined default into a recorded decision.
4. The Results
Data Integrity & Performance
- 99.98% of 182,000 HL7 accessions in a 30-day window reached the reporting layer: 40 lost between Lead and Silver, 0 lost between Silver and Gold.
- 0 null accessions and 0 duplicate accessions in the reporting table over 365 days. Final-report timestamps were 100% populated for the last 90 days.
- 0 remaining attribution orphans across 247,677 exam slots in multi-exam reports.
- 99.85% NPI fill rate on finalized exams in the cleaned layer, and about 0.15% null radiologist NPI in the last 90 days of the reporting table.
- 0 timestamp inversions on the initial load of the new exam-time feed, compared with a 6.83% inversion rate before remediation.
- About 2M report messages verified as single-path (100%
ORU^R01from PowerScribe), ruling out suspected double-ingestion.
Scale
- About 10.1M HL7 order messages and about 2.0M report messages retained in the raw HL7 layer. 9.83M case rows in the reporting layer, covering July 2021 to the present.
- 137 bronze tables (96 GB) audited across 10 source datasets: hospital HL7, PACS FHIR, QGenda, two billing systems, Performance Bridge, and CMS reference data.
- All 20 Performance Bridge tables in BigQuery, with the vendor’s core logic reproduced to within 0.02%.
- 16 tables of 1 GB or more partitioned and clustered across the Lead, Bronze, Silver and Gold layers, all with row counts verified.
Cost
- About $520/month in Cloud Composer baseline cost avoided. Cloud Workflows orchestration is projected at about $2/month.
- The nine flagged tables drove about 55,300 GiB of query scans in 30 days, and about 83% of that came from a single table read in full on every query. Eight of the nine were repartitioned; the ninth kept a layout that already fit its queries.
- The full production platform is projected at about $1,387/month in total GCP spend for a 70-radiologist group.
- A Cloud SQL storage leak of about 5 GB/day (growing past $50/month and never shrinking on its own) identified and tied to a superseded pipeline flagged for decommissioning.
Operations & Self-Sufficiency
- 17 Cloud Monitoring alert policies enabled across jobs, services, workflows, Pub/Sub, compute and the HA VPN link, replacing alerts that had been routed to a former employee.
- 19 evidence-backed technical documents delivered into the client’s repository.
- At wrap-up, the client’s data engineering lead said a formal handoff phase was unnecessary because the documentation already lived in the repository, which was the engagement’s goal from the start.
“Everything that we’ve done so far for the bronze, silver, and gold has been perfect.” (Data Engineering Lead, Aug 12, 2026 sprint review)
