🔗 Cross-Vendor EHR Data Migration Mapping Simulator

Schema Discovery & Field Inventory

Every EHR migration begins the same way: before a single field can be mapped, both systems must be fully inventoried. Legacy Vendor A's database — often decades old, shaped by years of ad-hoc customization — is profiled table by table alongside Modern Vendor B's FHIR-aligned resource model. This discovery phase determines the true scope of the migration, which is almost always larger than the initial project estimate.

• 400–1,200: Typical legacy tables profiled (per hospital instance)
• 8,000–20,000: Distinct field-level elements (columns, custom fields, flags)
• 4–10 weeks: Discovery duration (mid-size) (before mapping starts)
• 15–30%: Undocumented custom fields (typical for 10+ yr legacy systems)

Profiling the legacy source schema

Legacy Vendor A systems are frequently relational databases designed around clinical workflows from the 1990s–2000s, long before FHIR or even HL7 v2 messaging standardized much of anything. Discovery work typically uncovers:

• Denormalized tables where a single "PATIENT_MASTER" table holds demographics, insurance, and administrative flags all in one wide row • Free-text fields used as a catch-all where structured data should exist — a RX_FREE_TEXT column holding medication orders as unparsed strings because the original data-entry workflow never enforced a formulary lookup • Local, homegrown code sets for encounter types, provider roles, or problem categories that were never mapped to any national standard • Years of accumulated customization: hospital-specific fields bolted on by prior IT teams, many undocumented, some no longer written to but still holding historically significant data • Deprecated coding systems still present in older records — ICD-9-CM diagnosis codes sitting alongside newer ICD-10-CM codes in the same problem-list table, reflecting the 2015 US transition

A thorough discovery pass runs automated schema crawlers against the production database (read-only, off-hours) to enumerate every table, column, data type, nullability constraint, and — critically — actual observed value distributions, since documented field purpose and actual field usage diverge more often than migration teams expect.

It is common for a "field inventory" to double in size between the initial vendor-provided data dictionary and the actual discovery pass — undocumented custom fields, especially free-text overflow columns, are the single largest source of migration scope creep.

Understanding the modern target schema

Modern Vendor B, by contrast, is typically built around a FHIR R4 resource model or a proprietary schema that closely mirrors FHIR structure even where it doesn't expose a FHIR API directly. Target-side discovery focuses on:

• Resource definitions and required elements — a target Patient resource may mandate identifier, name, and birthDate as must-support elements under a US Core profile, meaning incomplete source data cannot simply be dropped • Terminology bindings — Observation.code strictly requires LOINC, Condition.code requires SNOMED CT or ICD-10-CM, MedicationRequest requires RxNorm — these bindings are usually far stricter than anything the legacy system enforced • Extension points — where the target schema allows custom extensions for data that doesn't fit a standard element, versus data that has genuinely no home in the new model and must be handled as an exception • Referential structure — the target schema is relationship-heavy (Observation references Patient and Encounter; MedicationRequest references Patient and Practitioner) in ways the legacy flat-table schema often is not, requiring the migration to synthesize relationships that were only implicit before

This stage produces the field inventory canvas: every source field rendered as a card in the left-hand schema column, every target field as a card in the right-hand column — the raw material the automated mapping engine consumes in Stage 2.

Scoping decisions that shape everything downstream

Discovery is also where a migration team makes consequential scoping calls that determine the entire project timeline:

• Historical depth — does the migration carry forward 3 years of history, 10 years, or the full legacy archive back to system go-live? Deeper history means proportionally more edge-case data quality problems surfacing later • Active vs. inactive records — inactive/discharged patient records may be migrated with a lighter-touch mapping than active patients requiring day-one clinical accuracy • In-scope resource types — a full clinical migration (problems, meds, labs, notes, immunizations, allergies) versus a narrower financial/administrative migration have very different field counts and mapping complexity • Regulatory retention requirements — state medical record retention statutes and HIPAA's documentation requirements set a floor on how much history must remain accessible somewhere, even if not fully migrated into the new system's live workflow

Getting discovery wrong — under-scoping the field inventory — is the leading cause of EHR migrations that blow through budget and timeline, since every field discovered after mapping has begun forces rework across every downstream stage.

Automated Field Mapping via Similarity & Terminology Matching

With both schemas fully inventoried, an automated mapping engine proposes source-to-target field correspondences at scale. Modern mapping tools combine string similarity, data-type compatibility scoring, sample-value overlap, and terminology-aware matching to resolve the large majority of straightforward one-to-one field mappings without human involvement — reserving analyst time for the genuinely ambiguous cases.

• 50–70%: Auto-mapped at high confidence (of total field inventory)
• 4+: Matching techniques combined (name, type, value, terminology)
• >90%: Confidence threshold (auto-accept) (else routed to manual review)
• ~500 fields/hr: Processing throughput (engine-proposed candidates)

How the similarity-matching engine scores candidate mappings

For every source field, the engine scores candidate target fields across several independent signals and combines them into a single confidence score:

• Lexical similarity — token-level and edit-distance comparison between field names, normalized for common abbreviation patterns (PT → Patient, DOB → birthDate, RX → medication); handles the reality that legacy field names are often cryptic all-caps abbreviations while target field names follow verbose FHIR-style dot notation • Data-type compatibility — a source VARCHAR(10) date-as-string field is a plausible match for a target date-typed element only if sample values actually parse as valid dates; a source NUMBER field is never proposed as a match for a target boolean flag • Value-set overlap — for coded fields, the engine samples actual distinct values from the source column and checks what fraction resolve against the target field's bound terminology (e.g. do the codes in DX_CODE_ICD9 largely resolve as valid ICD-9-CM codes, confirming this is genuinely a diagnosis-code field and not a mislabeled column) • Structural context — a source field that always co-occurs with recognized demographic fields in the same table is weighted toward demographic target candidates over clinical ones, using the surrounding schema as evidence

Each signal contributes a partial score; the combined confidence determines whether the mapping is auto-accepted (routed straight to the ETL stage), proposed with a partial/fuzzy indicator (routed to manual review with the engine's best guess pre-filled), or left unmapped entirely when no candidate clears a minimum plausibility floor.

Mapping automation level in this simulator controls how aggressively the engine auto-accepts borderline matches — pushed too high, it silently accepts wrong mappings; pushed too low, it dumps easy, obviously-correct matches into the manual review queue and wastes analyst time.

Terminology-aware matching for coded clinical data

Structural similarity alone is insufficient for clinical coded fields — a diagnosis field and a procedure field can look structurally identical (both short alphanumeric codes) while meaning completely different things. Terminology-aware matching adds a clinical semantics layer:

• Code-system fingerprinting — sampling values from a coded column against known code-system patterns (ICD-9-CM format vs. ICD-10-CM format vs. SNOMED CT numeric identifiers vs. local homegrown codes) to identify which terminology, if any, the source field actually uses • Cross-terminology crosswalk awareness — where the source uses ICD-9-CM and the target requires ICD-10-CM, the engine checks whether a reliable crosswalk exists (the CMS General Equivalence Mappings, GEMs, provide many-to-many ICD-9-to-ICD-10 crosswalks) before proposing the mapping as high-confidence, since some ICD-9 codes map ambiguously to multiple ICD-10 codes and cannot be auto-resolved • Local-code detection — when a coded field's values don't match any recognized national terminology pattern, the engine flags it as a local/proprietary code set requiring a custom value-set crosswalk to be built by an analyst rather than reused from a public GEMs-style table • LOINC and RxNorm matching for labs and medications — vendor lab result and medication fields are checked against LOINC and RxNorm reference tables respectively; a field where 95% of sampled values resolve to valid LOINC codes is proposed as a high-confidence Observation.code mapping, while ambiguous or free-text medication fields are routed for manual normalization

What automated mapping reliably gets right — and what it doesn't

Automated mapping is highly reliable for a specific, well-bounded class of fields: structured demographic elements (name, birth date, sex, address components), fields already using a recognized national terminology with high value-set coverage, and fields with an unambiguous one-to-one structural correspondence between schemas.

It reliably struggles with:

• Free-text fields masquerading as structured data — a RX_FREE_TEXT column cannot be automatically parsed into a discrete RxNorm-coded MedicationRequest; the engine correctly flags these as requiring either NLP-assisted extraction or full manual re-entry, never silently guessing • One-to-many and many-to-one mappings — a single legacy VITALS_BP_STR field holding "120/80" as one string must split into two target Observation instances (systolic, diastolic); a many-to-one case might combine separate legacy ALLERGY_FLAG and ALLERGY_SEVERITY columns into one target AllergyIntolerance resource • Semantic drift — a field named similarly in both systems that actually captures different clinical concepts (a legacy "PROBLEM_LIST_TXT" that mixes active and resolved problems in one field, versus a target model that requires clinicalStatus to be explicit) • Fields with sparse or non-representative sample data, where the confidence score is legitimately low simply because there isn't enough evidence to decide either way

The practical result: automation compresses what would be weeks of manual, field-by-field mapping into a fraction of the effort, but it does not eliminate the need for expert review — it concentrates that review time on the fields that actually need clinical judgment.

Manual Review & Adjudication of Ambiguous Mappings

Every automated mapping engine, however sophisticated, produces a residue of fields it cannot confidently resolve. This stage is where a migration analyst — usually a clinical informaticist paired with a data engineer — works through the queue of partial matches, conflicting candidates, and entirely unmapped fields, applying domain knowledge no algorithm has access to.

• 20–40%: Fields requiring manual review (of total inventory, typical)
• 8–25 min: Avg review time per field (varies by ambiguity)
• 150–400 hrs: Analyst hours (mid-size migration) (across full review queue)
• 2–8%: Fields reclassified as unmappable (no viable target exists)

The review queue — what lands here and why

The manual review queue is populated by the automated engine with every field that failed to clear the auto-accept confidence threshold, sorted and annotated with the engine's reasoning:

• Low-confidence single candidates — the engine found one plausible target but the combined confidence score fell below the auto-accept bar, often because sample data was sparse or the field name was too generic to disambiguate • Multiple competing candidates — the engine found two or more target fields that scored similarly, such as a source PROVIDER_ID_OLD field that could plausibly map to either Practitioner.identifier or PractitionerRole.identifier depending on whether the legacy ID represented the person or their role assignment • Free-text and semi-structured fields — flagged as needing either a defined extraction/parsing strategy or a decision to migrate as unstructured DocumentReference content rather than discrete data • Zero-candidate fields — no target field scored above the minimum plausibility floor at all, meaning either the target schema genuinely has no equivalent concept, or the field represents something so hospital-specific it needs a custom extension

Each queue item is presented with the engine's top candidate(s), the underlying evidence (name similarity score, sample value overlap, data-type match), and a side-by-side preview of actual sampled source values versus the target field's expected value shape — giving the analyst the same evidence trail the algorithm used, not just its conclusion.

Adjudication patterns analysts apply

Experienced migration analysts develop a repeatable set of resolution patterns for the recurring categories of ambiguity:

1. Splitting composite fields — a legacy VITALS_BP_STR holding "120/80" as one string is split into two target Observation resources bound to distinct LOINC codes (8480-6 systolic, 8462-4 diastolic), with a documented transformation rule the ETL stage will implement mechanically

2. Merging redundant fields — when two legacy columns (e.g. a coded ALLERGY_FLAG plus a free-text ALLERGY_NOTES field) together represent one target AllergyIntolerance concept, the analyst defines a combination rule rather than mapping them as two unrelated targets

3. Deprecation and exception handling — fields genuinely without a home in the target schema (a legacy administrative flag with no FHIR or vendor-schema equivalent) are formally marked "not migrated — archived," with the decision documented for the eventual reconciliation audit rather than silently dropped

4. Extension mapping — hospital-specific data with real ongoing clinical value but no standard target element is mapped into a vendor-supported custom extension, preserving the data while flagging it as non-portable to any third system later

5. Free-text triage — free-text fields are routed one of three ways depending on clinical criticality: NLP-assisted structured extraction (for high-value fields like medication free text, using a clinical NLP pipeline to propose RxNorm codes for human confirmation), migration as unstructured narrative into a DocumentReference or Composition resource, or in the lowest-priority cases, retained only in a read-only historical archive outside the live target system

The single highest-risk category in manual review is the "zero-candidate field with real clinical content" — data that clearly matters (an old but still-referenced problem-list annotation, a legacy-only allergy severity scale) but has no obvious target home. These require the most conservative handling: default to preserving the data via extension or archive rather than discarding it.

Governance around manual mapping decisions

Because manual mapping decisions directly determine what clinical data does or doesn't survive the migration, mature migration programs wrap this stage in explicit governance:

• Dual review for high-risk categories — allergy, medication, and problem-list mappings typically require sign-off from both a data engineer (technical correctness) and a clinical informaticist or physician champion (clinical correctness), since a wrong mapping here has direct patient-safety implications • Mapping decision logs — every manual adjudication is recorded with the analyst's rationale, timestamp, and reviewer sign-off, forming an audit trail that both supports the post-migration reconciliation (Stage 5) and provides a defensible record if a data-loss question arises months later • Sampling-based spot audits — rather than reviewing every one of hundreds of manually adjudicated mappings, a migration QA lead re-reviews a statistically representative sample to catch systematic misjudgments before they propagate through the ETL stage at scale • Change control — once a mapping is approved, it is version-controlled; any later revision (discovered during ETL testing, for instance) goes through the same review-and-signoff process rather than being silently patched

Data Transformation, ETL & Value-Set Crosswalks

With every field mapping reviewed and approved, the migration moves from planning into execution: an ETL (Extract, Transform, Load) pipeline mechanically applies every approved mapping and transformation rule at full data volume, converting legacy values into target-schema-conformant records while a validation layer checks every output record against the target system's structural and terminology constraints before it is ever loaded.

• 2–50M: Typical record volume (rows across full patient history)
• 15–40: Value-set crosswalks built (per migration, avg complexity)
• 3–12%: Validation failure rate (1st pass) (records requiring rework)
• 3–6: ETL test cycles before cutover (dry runs against full volume)

The ETL pipeline — extract, transform, load, at volume

The Extract phase pulls data from Legacy Vendor A's production database (typically via a read replica to avoid impacting live clinical operations), applying the field inventory from Stage 1 to select exactly the columns and tables in scope.

The Transform phase is where every approved mapping from Stages 2–3 becomes executable logic:

• Direct field copies for one-to-one structural matches with compatible data types • Value-set crosswalks for coded fields — ICD-9-CM to ICD-10-CM via CMS General Equivalence Mappings (GEMs), local proprietary encounter-type codes to FHIR Encounter.class ActCode values, legacy medication free text resolved to RxNorm concepts via the NLP extraction pipeline approved in Stage 3 • Structural transforms — splitting the composite blood-pressure string into two Observation instances, merging the allergy flag and free-text note into one AllergyIntolerance resource, synthesizing explicit resource references (Observation.subject, Observation.encounter) that were only implicit foreign keys in the legacy flat schema • Data-quality normalization — standardizing date formats, resolving inconsistent capitalization in coded fields, deduplicating patient records that exist multiple times in the legacy system under slightly different demographic spellings

The Load phase writes transformed records into the target system, typically via the vendor's bulk-load API or FHIR $import-equivalent mechanism, in dependency order — Patient records before the Observation and MedicationRequest records that reference them, since the target schema's referential model requires parent resources to exist before children can point to them.

Value-set crosswalks — the hidden complexity multiplier

Crosswalks between coding systems are rarely a clean one-to-one lookup table, and this is where migrations most often underestimate effort:

• ICD-9-CM to ICD-10-CM — the CMS GEMs crosswalk is explicitly many-to-many for a meaningful fraction of codes; a single legacy ICD-9 code can map to several plausible ICD-10 codes depending on clinical context the crosswalk table alone cannot resolve, requiring either a default "most common mapping" heuristic (accepting some imprecision) or a flag for clinical review on ambiguous cases • Local encounter-type and department codes to standard ActCode/class values — since these were often defined ad hoc by the original implementation team, a crosswalk table has to be hand-built by interviewing long-tenured staff who remember what "ENC_TYPE 7" actually meant • Legacy free-text medication orders to RxNorm — even with NLP assistance, free-text-to-RxNorm resolution has a meaningful error and no-match rate; unresolved entries fall back to a documented manual coding queue rather than being silently loaded as unmapped text • Allergy severity scales — many legacy systems used a locally-defined 1–4 severity scale with no standard equivalent; migration teams typically crosswalk to the closest FHIR AllergyIntolerance.reaction.severity value (mild/moderate/severe) while documenting the scale conversion for clinical staff so nothing is silently reinterpreted

Every crosswalk is itself a mapping artifact requiring its own review and versioning discipline — a wrong crosswalk entry doesn't just mis-map one field, it silently mis-transforms every record that passes through it.

GEMs-based ICD-9-to-ICD-10-CM crosswalks are officially maintained by CMS and the CDC's National Center for Health Statistics, but even the official crosswalk explicitly documents combination and scenario cases where a single legacy code cannot be automatically resolved to one correct target code — these are exactly the cases a migration validation layer must catch rather than pass through silently.

Validation before load — catching errors before they become production data

A validation layer sits between Transform and Load, checking every candidate output record against the target schema before it is committed:

• Structural validation — required elements present (a target Patient resource missing a mandated identifier fails validation rather than loading incomplete), data types correct, cardinality constraints respected (a single-value field not accidentally populated with a list) • Terminology validation — every coded value checked against its bound value set; a transformed Condition.code that doesn't resolve to a valid SNOMED CT or ICD-10-CM concept is rejected and routed to an exception queue rather than loaded as an invalid code • Referential integrity — every resource reference (Observation.subject, MedicationRequest.requester) resolves to a record that actually exists in the target load batch, catching orphaned records before they become broken links in production • Business-rule validation — target-system-specific rules beyond bare FHIR conformance, such as a vendor requirement that active MedicationRequest resources carry a valid prescriber NPI

Records failing validation are not discarded — they are logged to an exception report with the specific rule violated and routed back through Stage 3's review process if the failure indicates a mapping gap, or corrected as a data-quality fix if the failure traces to a genuine error in the source data itself. Migration teams typically run 3–6 full-volume dry-run ETL cycles against a non-production target instance, progressively driving the validation failure rate down before the real cutover.

Cutover, Reconciliation & Post-Migration Audit

The final stage is where months of mapping and transformation work meets the unforgiving reality of a live clinical system going dark on one platform and live on another. Cutover is a tightly choreographed, time-boxed event; reconciliation and audit are the ongoing discipline that proves — with evidence, not assurance — that no clinically significant data was lost in the transition.

• 8–48 hrs: Typical cutover freeze window (read-only legacy, no new writes)
• 100%: Reconciliation checks run (record-count + checksum, every table)
• 2–6 weeks: Post-go-live hypercare period (elevated support staffing)
• 1–7 yrs: Legacy system retained (read-only) (per state retention statute)

The cutover freeze and final delta load

Cutover begins with a data freeze: the legacy system is placed into read-only mode (or, for a true hard cutover, taken fully offline for clinical use) for a defined window — commonly overnight or over a weekend to minimize clinical disruption. During this freeze:

1. A final delta ETL run captures every record created or modified since the last full migration dry-run, ensuring the target system reflects the legacy system's true final state rather than a snapshot from days or weeks earlier 2. Active in-flight clinical work — orders placed, results pending, encounters in progress — requires special handling, since these records exist in an intermediate state that a batch ETL process must map correctly or explicitly flag for manual continuation in the new system 3. Downstream interfaces (lab systems, pharmacy systems, billing clearinghouses, HIE connections) are cut over in coordinated sequence, since leaving even one interface pointed at the decommissioned legacy system creates a silent data gap 4. A go/no-go checkpoint reviews validation pass rates, reconciliation status, and interface readiness against pre-defined thresholds before the target system is opened to live clinical use — migration teams that skip this checkpoint under schedule pressure are disproportionately represented among post-go-live incident reports

Reconciliation — proving completeness, not assuming it

Reconciliation is the systematic, evidence-based comparison of source and target data, run against every migrated table rather than a sample, because clinical data migrations cannot tolerate silent gaps:

• Record-count reconciliation — for every resource type, the count of migrated target records is compared against the count of in-scope source records, with any discrepancy investigated and explained (a legitimate exclusion per the Stage 1 scoping decision, or a genuine migration defect) rather than dismissed • Checksum / hash-based spot verification — for a statistically significant sample of individual patient records, key field values are hashed and compared between source and target to catch transformation errors that preserved record counts but corrupted content • Clinically-weighted sampling — reconciliation sampling is deliberately weighted toward high-risk categories identified during manual review (Stage 3): allergy records, active medication lists, and problem lists receive disproportionately thorough verification relative to lower-risk administrative fields • Exception resolution tracking — every discrepancy found during reconciliation is logged, assigned an owner, and tracked to resolution or documented acceptance before the migration is declared closed, producing the evidentiary record referenced in the next section

Reconciliation is not a formality performed after the fact — it is the artifact that lets a health system's leadership, and if necessary regulators or plaintiffs' counsel in a later dispute, verify with evidence that the migration did what it claimed to do rather than merely trusting the ETL pipeline's self-reported success.

Post-migration audit and legacy system retention

Even a clean cutover with a fully reconciled load is followed by a defined post-migration audit and support period, not treated as instantly closed:

• Hypercare period — elevated clinical and technical support staffing for 2–6 weeks post-go-live, since real clinical usage surfaces mapping edge cases that no test cycle fully anticipated; a field that looked correctly migrated in aggregate reconciliation can still be wrong in a specific clinical workflow only exercised in live use • Formal audit report — documenting the full mapping decision trail from Stage 1 discovery through Stage 4 transformation rules, the reconciliation results, every logged exception and its resolution, and explicit sign-off from clinical and technical leadership — this becomes the permanent record referenced if a data-integrity question arises years later • Legacy system retention — the decommissioned legacy system is not immediately destroyed; it is retained in read-only form for a period set by state medical record retention statutes (commonly several years, occasionally longer for pediatric records) and by HIPAA's own documentation requirements, providing a fallback source of truth if a gap is discovered after go-live • Continuous improvement feedback — mapping and transformation rules developed during this migration are captured as reusable artifacts, since most health systems eventually face a second migration (a subsequent vendor change, a merger integrating another hospital's legacy system) where the crosswalks and adjudication patterns built here materially reduce the effort of the next one

A migration is considered successfully closed only when reconciliation is complete, the audit report is signed off, and the hypercare period has passed without unresolved high-severity data-integrity findings — not simply when the final ETL job exits with a success status code.

Migration governance checkpoints by risk tier