Upgrading an OMOP CDM from 5.3 to 5.4 with Ignifyr

OMOP CDM 5.4 has been the recommended version for a while, but many databases are still on 5.3. The upgrade is mostly renamed and added columns, plus a few new tables, and it is usually done with hand-written SQL. With Ignifyr Enterprise, where both CDM versions are built in, the upgrade is a set of mappings from 5.3 tables to 5.4 tables, tested on sample rows and run as a job.

Two versions of the same tables

Ignifyr Enterprise ships the table definitions of OMOP CDM 5.3, 5.4 and 6.0, generated from the official OHDSI specifications. In the schema picker they appear under OMOP CDM, one node per version, with every table of that version below it. A mapping can read from a 5.3 table and write to the 5.4 table of the same name, and the editor shows, for each target column, its type, whether it is required and the OHDSI guidance.

The columns of the 5.3 visit_occurrence table, with the OHDSI guidance of each, in the mapping editor.

What changes between 5.3 and 5.4

Comparing the two bundled definitions gives the list of what a 5.3 to 5.4 upgrade has to deal with:

• Renamed columns in visit_occurrence and visit_detail: admitting_source_* becomes admitted_from_*, discharge_to_* becomes discharged_to_*, and visit_detail_parent_id becomes parent_visit_detail_id.
• New columns: procedure_end_date and procedure_end_datetime in procedure_occurrence; unit_source_concept_id, measurement_event_id and meas_event_field_concept_id in measurement; the event links of observation and note; production_id and the unit columns of device_exposure; the country and coordinates of location.
• New tables: cohort, episode and episode_event; attribute_definition is gone.
• cdm_source gets a cdm_version_concept_id, and several of its columns become required.

Everything else is a one-to-one copy, column by column. Ignifyr does not make you type those copies: the target template lists every column of the 5.4 table, and the ones with the same name on both sides are filled with a plain reference to the source column.

The mapping

Create a mapping with the source schema OMOP CDM › 5.3 › VisitOccurrence and the target OMOP CDM › 5.4 › VisitOccurrence. The template that Ignifyr generates names the target table and its CDM version in meta.profile; the renamed columns are the only lines that differ from a copy.

{
  "resourceType": "VisitOccurrence",
  "meta": {
    "profile": ["https://ignifyr.io/omop/cdm/StructureDefinition/VisitOccurrence|5.4"]
  },
  "visit_occurrence_id": "{{visit_occurrence_id}}",
  "person_id": "{{person_id}}",
  "visit_concept_id": "{{visit_concept_id}}",
  "visit_start_date": "{{visit_start_date}}",
  "visit_start_datetime": "{{? visit_start_datetime}}",
  "visit_end_date": "{{visit_end_date}}",
  "visit_end_datetime": "{{? visit_end_datetime}}",
  "visit_type_concept_id": "{{visit_type_concept_id}}",
  "provider_id": "{{? provider_id}}",
  "care_site_id": "{{? care_site_id}}",
  "visit_source_value": "{{? visit_source_value}}",
  "visit_source_concept_id": "{{? visit_source_concept_id}}",
  "admitted_from_concept_id": "{{? admitting_source_concept_id}}",
  "admitted_from_source_value": "{{? admitting_source_value}}",
  "discharged_to_concept_id": "{{? discharge_to_concept_id}}",
  "discharged_to_source_value": "{{? discharge_to_source_value}}",
  "preceding_visit_occurrence_id": "{{? preceding_visit_occurrence_id}}"
}

• The {{? ...}} expressions leave optional columns empty when the source value is null.
• The ids are copied as they are, so the foreign keys between the tables stay valid.
• For a new required column, such as cdm_version_concept_id in cdm_source, the editor flags the column until the template fills it.

Tables without changes get the same kind of mapping with no renamed lines. The new tables are left empty, unless you have data for them: a cohort table, for example, can be filled from the cohort definitions of your 5.3 database by one more mapping.

Test, then run against the 5.4 database

Create the empty 5.4 database with the OHDSI DDL scripts and load the vocabulary. In the job, the source is the 5.3 database, and the sink is the OMOP Database sink pointing at the 5.4 database, with the CDM version set to 5.4. Before it writes anything, the sink checks that every mapped table exists in the database and belongs to CDM 5.4, and that the mappings produce every required column. The tables are then written in foreign-key order, so that person rows exist before the visit_occurrence rows that point to them. Rows that do not fit their table, because a value does not convert or exceeds the column length, are reported per source row while the valid rows are written.

The OMOP Database sink: database URL, credentials, the schema that holds the CDM tables and the CDM version.

Run the mappings on a sample first. The mapping tester validates every produced row against the 5.4 table definition, inside Ignifyr, and shows missing required columns or wrong types next to the 5.3 row they came from. When the sample is clean, run the job on the whole database; a large database is split into batches, by person_id ranges for example, and the Kibana dashboards follow each batch.

Not only upgrades

The same setup, a mapping that targets an OMOP table and a job with the OMOP Database sink, loads any source into OMOP: a hospital database, CSV exports, or a FHIR server. The difference is that the source columns then come from your own schema, inferred from the source, and that the concept ids are filled through concept maps or a terminology service. And the direction can be reversed as well; our post on mapping an OMOP CDM to FHIR shows the same tables as sources.

Planning a CDM upgrade or an OMOP ETL? Read the FAQ on OMOP support or request a demo.