TL;DR
- Healthcare data modernization means moving clinical data off legacy SQL Server and Oracle databases into cloud-native stores, then reshaping it into a research-ready model such as OMOP CDM 5.3.1, so analysts can query patients, encounters, and drug exposures without relearning every source system.
- The hard part is rarely the storage swap. It is the ingestion boundary: legacy pipelines infer schemas at read time, so a renamed column travels silently into the warehouse and breaks downstream mappings weeks later.
- We ran a real modernization assessment against the Databricks OMOP CDM accelerator in Forge. It scored 68/100 (Established), with Data Weight at 58 and Future Proofing at 60 as the weakest dimensions.
- Three findings drove the entire remediation plan: unsafe archive extraction (tarfile.extractall with no member validation), inferSchema=True on every source CSV, and configuration passed between notebooks through a /tmp JSON file.
- The fix pattern is validate before mutation: a typed config contract, an explicit file manifest with declared schemas, and quarantine for malformed rows, all gated before a single Bronze write happens.
- Sequencing matters more than tooling. The plan runs as six phases over roughly four months, with legacy and modernized paths running side by side in report-only mode before any gate becomes blocking.
Hospital and payer data teams rarely get to modernize on a clean sheet. The patient records live in a SQL Server instance someone configured in 2011, the claims extracts arrive as flat files, and the analytics team has built a decade of reporting on top of both. Replacing that stack outright is not an option when the reports feed quality measures and regulatory submissions.
Healthcare data modernization, done well, is a sequence of contract changes rather than a rewrite. This post walks through what that sequence looks like, using a real assessment we ran on an open-source Databricks OMOP CDM accelerator: what the scan found, why each finding matters specifically for clinical data, and the order in which the fixes have to land.
What healthcare data modernization actually changes in your stack
Healthcare data modernization moves clinical and claims data from row-oriented legacy databases to cloud-native lakehouse storage, then standardizes it into a common data model so analysis no longer depends on source-system quirks. Three things change at once, and conflating them is the most common reason these programs stall.
Storage changes from SQL Server, Oracle, or Netezza to object storage with a table format on top, usually Delta Lake or Iceberg. This buys ACID writes, schema enforcement, time travel, and the ability to restore a table to a prior version, which matters when a bad ETL run corrupts six months of encounters.
Shape changes from source-native tables to a standard model. In observational health research, that model is almost always OMOP CDM, maintained by OHDSI. Version 5.3.1 defines tables such as person, visit_occurrence, condition_occurrence, and drug_era, as well as a vocabulary layer that maps local codes to standard concepts.
Governance shifts from database-level permissions to catalog-level controls, including lineage, audit records, and classification. For PHI, this is not optional, and it is the piece most teams defer until a compliance review forces it.
| Layer | Legacy state | Cloud-native target | Why it matters clinically |
| Storage | SQL Server / Oracle tables | Delta Lake on object storage | Time travel lets you restore a table after a bad ETL run |
| Schema | Source-native, per-system | OMOP CDM 5.3.1 | Cohort logic written once runs against any OMOP site |
| Vocabulary | Local codes, ad hoc crosswalks | OHDSI standard concepts | ICD-10 and SNOMED map to one concept space |
| Ingestion | Inferred at read time | Declared manifest and schemas | A renamed column fails at intake, not in a cohort query |
| Governance | Database grants | Catalog ACLs, lineage, audit | Minimum-necessary access is provable, not assumed |
Reading a modernization assessment before you plan the work
Before sequencing any migration, it helps to have an evidence-backed picture of where the current system is fragile. We ran the Databricks OMOP CDM accelerator through Forge, which scans a repository and scores it across eight dimensions of change friction.
The repository is small: 23 files, 11 of them code, organized as a numbered notebook chain from 00-setup.py through 7-sample-omop-queries.sql. It ingests Synthea synthetic patient CSVs into Bronze Delta tables, provisions OMOP 5.3.1 tables, loads vocabularies, and runs example cohort analytics.
The composite score came back 68 out of 100, rated “Established.” That is a fair result for a well-built demo, and the dimension spread is where the useful signal lives.
| Dimension | Score | Maturity | What drove it |
| System Gravity | 88 | Advanced | No circular dependencies, dependency balance 1.0, max call depth 2 |
| Semantic Clarity | 74 | Established | Strong OMOP vocabulary alignment, some version-naming drift |
| Trust Boundaries | 72 | Established | No exposed APIs, secrets in GitHub Actions, but unsafe archive extraction |
| Logic Narrative | 68 | Established | Readable notebook sequence, implicit config handoff |
| Cognitive Load | 64 | Established | Config repetition, six near-duplicate CI workflow files |
| Hidden Gems | 62 | Established | SolAccUtil and multi-cloud CI worth extracting |
| Future Proofing | 60 | Established | Hardcoded constants, tests that are queries rather than assertions |
| Data Weight | 58 | Developing | inferSchema scans, SELECT * views, full-file ingestion |
Data Weight scoring lowest is the pattern to notice. The architecture is clean, the dependency graph is healthy, and the code reads well. What is weak is everything touching the shape and movement of data, which is precisely the risk surface that matters when the input stops being synthetic and starts being patient records.
Three findings that decide whether legacy data migration is safe
The scan surfaced ten findings. Three of them are structural, in the sense that fixing them changes how every later stage behaves, and the rest largely follow from them.
Unvalidated archive extraction opens a filesystem trust boundary
SolAccUtil.load_remote_data in 00-setup.py downloads a remote archive and, when unpack=True, calls tarfile.extractall directly into the DBFS-backed project path. No member validation runs first.
A tar archive can contain members with absolute paths or ../ segments. Extraction then writes outside the intended directory. The finding carries CWE-22 and OWASP-A05 references. For a demo that pulls a known Synthea bundle from a fixed URL, the practical risk is low; the moment a team repoints this helper to an internal data drop, the utility becomes the weakest link in the intake path.
The fix is bounded extraction: resolve each member’s target path, confirm it stays under the destination, reject absolute paths, parent traversal, and unexpected symlinks, and cap total member count and uncompressed size before writing anything.
Schema inference lets source drift travel into storage
1-data-ingest.py enumerates every file under the configured path with dbutils.fs.ls, reads each with spark.read.csv(…, header=True, inferSchema=True), and writes straight to Bronze Delta.
Two costs follow: inference triggers an extra full scan per file, which is a performance tax that grows with the dataset. The higher cost is silent: when a source system renames birthdate to birth_date, inference happily produces a table with the new column, the Bronze write succeeds, and the failure surfaces later as an OMOP person table with null birth dates. Nothing in the pipeline flagged the change when it entered.
Declared schemas invert this, and the manifest specifies which files are expected and which columns each must carry, so an unexpected shape fails at intake with a message naming the file and the field.
Configuration passed through a temp file creates order dependence
00-setup.py writes /tmp/{project_name}_configs.json. Downstream notebooks read the same path for base_path, delta_path, and data_path, an implicit contract with no schema, no version, and no validation.
Run the ingestion notebook before setup, and you get a confusing late failure rather than a clear early one. The same finding appears across three dimensions in the scan: Logic Narrative flags it as tribal knowledge, Cognitive Load flags it as configuration sprawl, and System Gravity flags it as an accidental gravity well.
Each finding becomes a work order with acceptance criteria and explicit blockers. WO-009 cannot start until the ingestion characterization and typed config work land first.
Building the ingestion contract that catches drift at intake
The remediation for all three findings shares one principle: validate before mutation. Every check runs before data reaches storage, so a failed run leaves the lakehouse unchanged.
In the generated plan, this became a synthea_contracts component with four parts:
- An expected-file manifest naming every source file the ETL depends on: patients, encounters, conditions, procedures, observations, medications, providers, organizations, payers, payer_transitions, careplans, allergies, devices, imaging_studies, immunizations, and supplies.
- Explicit Spark StructType schemas per file, validated by column name rather than ordinal position, since CSV column order is not guaranteed stable across exports.
- Required-field declarations separating columns that must be present from optional ones that are allowed only when explicitly declared.
- A strictness mode. strict blocks the run and fails the release gate; report-only writes a validation report without blocking, which is what makes side-by-side migration possible.
Malformed rows do not fail the whole file. They route to a rejected_records table alongside a validation_report, so an engineer can inspect what was dropped and why. Error messages name the file, field, expected type, and observed condition, and deliberately avoid dumping row values because, in a non-synthetic deployment, those values are PHI.
Every check runs before storage is touched. A failed run leaves the lakehouse exactly as it was.
The typed config loader follows the same shape: a versioned JSON or YAML contract with a JSON Schema, a Python loader that validates the contract version and required keys before any notebook touches data, and a config_contract_mode=legacy flag so the old /tmp handoff continues to work during cutover.
Sequencing the migration so nothing cuts over blind
The plan runs six phases from August to December, and the ordering encodes a specific claim: some changes are independent, and some must move together.
Safe extraction, typed configuration, and ingestion validation can each land on their own. Runtime certification, workflow packaging, and governance cutover cannot, because they all touch identity, cluster policy, table locations, and release gates simultaneously.
| Phase | Window | Scope | Exit criteria |
| 1. Baseline inventory | Aug 18 – Aug 28 | Inventory notebooks, runtimes, row counts, CI actions | Current workflow reproduced, rollback snapshots recorded |
| 2. Trust boundary and config | Aug 29 – Sep 12 | Safe extraction, typed loader, structured errors | Unsafe archive cases fail closed, config errors stop before mutation |
| 3. Validated Bronze ingestion | Sep 13 – Oct 4 | Manifest, explicit schemas, quarantine, drift report | 100% required files validated, 0 unapproved columns accepted |
| 4. Runtime and CI | Oct 5 – Oct 26 | Certify DBR baseline, Python 3.12, dependency manifest | Full notebook chain passes on certified runtime |
| 5. Mapping and governance | Oct 27 – Nov 17 | Named projections, mapping regression, catalog grants, audit | Mapping regression passes, non-synthetic still blocked |
| 6. Workflow promotion | Nov 18 – Dec 9 | Promote bundle dev → test → prod, enable blocking gates | RTO 4h and RPO 1h restore drill completed |
Coexistence is comparison-based rather than dual-write. Legacy Bronze and validated Bronze write to separate paths during migration, and cutover is a config change rather than a code deploy. Validation starts advisory in Phase 3 and only becomes release-blocking in Phase 4, after two consecutive green release candidates.
Why the ordering is not arbitrary. The first three phases are reversible on their own; the last three share enough surface that they have to cut over together.
That last detail is what separates a migration plan from a wish. Gates that block before anyone trusts them get disabled under deadline pressure, and once disabled they rarely come back on.
The generated plan, with the exit criterion that gates each phase.
Governance controls that gate non-synthetic clinical data
Everything above works on synthetic Synthea data. The moment real patient records are entered, a different set of requirements applies, and the plan treats this as a hard gate rather than a later enhancement: non-synthetic deployment is denied by default until the controls are documented and enabled.
The specific requirements the plan encodes:
- Classification. Every data category is labeled Public, Internal, Confidential, or Restricted. Synthea sample data is non-PHI synthetic; institutional patient records are Restricted.
- PHI exclusion from logs. Identifiers are masked in logs, error messages contain no raw row samples, and a log-scanning gate fails the release if PHI patterns are detected.
- Audit records. Actor, timestamp, resource, operation, and change summary are captured for material data mutations, retained for a minimum of one year, with clinical access events retained considerably longer under the organization’s own policy.
- Least privilege. Analysts query released OMOP outputs but cannot modify ingestion configuration; jobs run as service principals rather than user tokens.
- Retention and purge. Each category has a defined retention period enforced through physical deletion or cryptographic erasure, not through soft deletion alone.
Mapping quality gets gated too. Named ETL projections replace SELECT * temp views, each populated OMOP domain carries documented source inputs and mapping behavior, and unmapped concept rates above an agreed threshold fail the release. An informaticist reviewing a mapping report before promotion catches semantic gaps that no row-count check will ever surface.
What to carry into your own modernization program
The specifics above come from one open-source accelerator, but the failure modes generalize to most hospital and payer migrations we see.
- Score before you scope. The dimension spread indicated that Data Weight was the weak axis, meaning ingestion work outranked refactoring work. Without that, the obvious-looking task list would have started with splitting large SQL files.
- Treat the intake boundary as the security boundary. Archive extraction and schema inference both looked like conveniences. Both are the point where untrusted external shape crosses into governed storage.
- Make implicit contracts explicit and versioned. A /tmp JSON file works until two people run notebooks in different orders. A validated contract with a version field fails loudly and early.
- Run new and old side by side before gating. Report-only mode, separate output paths, and row-count reconciliation give you evidence that the modernized path matches before it replaces anything.
- Gate non-synthetic data explicitly. Default-deny with a documented control checklist keeps a demo-grade pipeline from quietly becoming a PHI pipeline.
Legacy SQL and Oracle stores will continue to serve healthcare workloads for years. Modernization is worth doing when it converts invisible assumptions, inferred schemas, temp-file handoffs, and unvalidated archives into contracts a reviewer can read and a pipeline can enforce.
FAQs
What is OMOP CDM and why standardize on it?
OMOP CDM is the Observational Medical Outcomes Partnership Common Data Model, maintained by the OHDSI community. It defines a fixed table structure for clinical data: person, visit_occurrence, condition_occurrence, drug_exposure, and roughly 40 more, plus a vocabulary layer that maps local codes to standard concepts. Standardizing on it means cohort definitions and analysis packages written at one institution run unchanged at another. Version 5.3.1 is the release the Databricks accelerator targets.
How long does healthcare data modernization take?
The plan generated for this accelerator spans roughly four months across six phases, for a 23-file repository processing synthetic data. Hospital and payer migrations involving multiple source systems, real PHI, and existing regulatory reporting typically run considerably longer. The phase structure is the transferable part: baseline, harden boundaries, validate ingestion, certify runtime, gate mappings and governance, then promote.
Can we modernize without moving off SQL Server or Oracle first?
Yes, and phasing it that way is usually safer. The ingestion contract, validation, and quarantine layers sit between the source and the target, so they can be built and run in report-only mode while the legacy database remains the system of record. The storage cutover then becomes a config change against a pipeline that has already proven it produces matching row counts.
What breaks most often during a legacy-to-cloud data migration?
Schema drift that nobody catches at intake. When a pipeline infers structure at read time, a renamed or retyped column produces a successful write but an incorrect downstream result. The failure surfaces weeks later as nulls in a cohort query, by which point the cause is several ETL runs back. Declared schemas with named-column validation convert that silent corruption into an immediate, legible failure.
How do we prove HIPAA and SOC 2 alignment for a modernized pipeline?
Through evidence of the pipeline’s emissions rather than documentation written after the fact: data classification per category, audit records capturing actor and operation for material mutations, log scanning that fails a release when PHI patterns appear, catalog-level grants demonstrating least privilege, and documented retention with enforced purge. Making these release gates, rather than review checklists, ensures that evidence exists for every promotion.
