Generating the scripts

Two generators, one chain

Writing by hand the conversion of a patient record system to OMOP means hundreds of lines of SQL that repeat what Linkr already knows: the source database’s schema mapping says where its patients, stays and events are, the target database’s says where OMOP puts them, and a mapping project says which standard concept each of your codes stands for.

Each generator writes its files into the pipeline’s Scripts tab, numbered to interleave: the vocabulary first, then patients, stays, events, and the vocabulary prune last. A hand-written script slots in between by its number.

Generating the OMOP load scripts

What you need first

Both of the pipeline’s databases need a schema mapping:

• the source, so Linkr knows how to read its patients, stays and event tables;
• the target, so it knows where to write them. An empty OMOP database created with New from schema has one from the start — see Databases.

Without either, the dialog says so (“The source database has no schema mapping”, “The target database has no schema mapping: create it from an OMOP schema”) and offers nothing.

Opening the generator

In the Scripts tab, the file explorer’s bar has three buttons: New file, Upload files, and the wand Generate from the schemas. It opens the Generate the OMOP load scripts dialog.

The dialog reads top to bottom: two settings, the event tables to place, then the list of scripts to be written. Write N scripts creates them in the Scripts tab, without running anything.

The settings

• Concepts — how a source code becomes an OMOP concept_id:

The first two options read what the vocabulary script wrote into the target (see below): that is why 00_vocabulary.sql comes first. A code with no match gets concept 0, which is how OMOP says “unmapped”. The setting offered at first is Source ids, already OMOP when no mapping project is attached to the pipeline, the pipeline vocabulary otherwise: check that it matches the representation chosen in the Vocabulary tab.

• Type concept — the value written into every *_type_concept_id column, which says where the record comes from. 32817 means EHR, the electronic health record.

• Event and drug tables — one row per event table of the source, as its mapping names it (“Laboratory”, “Administrations”…), with the OMOP table to load it into. Linkr offers one: the target table bearing the same name in the target’s mapping, or drug_exposure for a table marked Drug. Failing that, the row starts on Do not load, which leaves it out; a warning says so under the script list.

What each script produces

One script per class of the source, in the order OMOP expects them:

Each script first empties its table (TRUNCATE), then fills it with a single INSERT … SELECT reading the source’s standard relation — the one its mapping defines, copied at the head of the query. Re-running the pipeline therefore reloads the table, with no duplicates.

-- Generated by Linkr from the source schema "ICU hospital — export": visit → visit_occurrence.
-- Edit freely: regenerating asks before overwriting an edited script.
-- linkr-generated: 5c1e09a4

TRUNCATE target.visit_occurrence;

INSERT INTO target.visit_occurrence (visit_occurrence_id, person_id, visit_concept_id, visit_start_date, visit_start_datetime, visit_end_date, visit_end_datetime, visit_type_concept_id, care_site_id)
WITH linkr_visit AS NOT MATERIALIZED (
  -- the source mapping's "stays" relation
)
SELECT
  s.visit_id AS visit_occurrence_id,
  s.patient_id AS person_id,
  0 AS visit_concept_id,
  CAST(s.start_datetime AS DATE) AS visit_start_date,
  s.start_datetime AS visit_start_datetime,
  COALESCE(CAST(s.end_datetime AS DATE), CAST(s.start_datetime AS DATE)) AS visit_end_date,
  s.end_datetime AS visit_end_datetime,
  32817 AS visit_type_concept_id,
  s.care_site_id AS care_site_id
FROM linkr_visit s;

Along the way, the generator applies the OMOP rules that are easy to forget:

• dates — a *_date column is taken from its *_datetime; an unknown end date becomes the start date, when the column is required;
• required columns — a *_concept_id nothing fills gets 0; any other NOT NULL column the source cannot fill gets NULL and a -- TODO(etl): … comment saying so, rather than an invented value;
• ids — an event with no id of its own in the source is numbered on each run, and a TODO points out that those keys will change from one load to the next.

Under the script list, orange warnings flag what could not be written: a table missing from the target’s DDL (only the columns its mapping names are then written), a class of the source the target’s mapping places nowhere, an event table left on Do not load.

Regenerating without losing your edits

A schema evolves, a mapping gets corrected: you regenerate. Every generated script carries in its header a -- linkr-generated: line followed by a fingerprint of its contents, which lets Linkr know whether anyone edited it since. The script list says what will happen to each:

• new — the file does not exist yet, it will be created;
• regenerated — it exists and has not been touched since it was generated: it will be replaced;
• edited — overwrite — it was edited by hand, or a hand-written script already has that name. It is left alone, unless you tick the box.

Adjusting a generated script is therefore safe: your changes survive regeneration as long as you leave the box unticked.

Generating the vocabulary from a mapping project

Aligning local codes onto standard concepts happens in a concept mapping project — an interface built for it, with suggestions and evaluation. The pipeline’s Vocabulary tab can read that work and turn it into SQL that loads those correspondences into the target database.

On the left, what goes in:

• From a concept mapping project — the project whose correspondences to read.
• Select statuses to export — the mappings kept by status (Approved, Unchecked…), with the rule to apply to approved ones: At least one approval, More approvals than rejections or No rejections.
• Include all source concepts — also loads the local codes no kept mapping covers. Without them, an unmapped code has no source_concept_id to write into OMOP.

On the right, what comes out:

• Vocabulary representation — CONCEPT + CONCEPT_RELATIONSHIP, the current OMOP representation; SOURCE_TO_CONCEPT_MAP, the legacy one; or both.
• Files to write — the CSV mapping export, the vocabulary script 00_vocabulary.sql, which copies the reference vocabulary and your local concepts into the target, and the prune script 99_prune_vocabulary.sql, which slims it down afterwards: it keeps only the concepts the OMOP tables actually use, their ancestors and what relates to them.

Under each file already present, a line says where it stands: Already up to date when it is identical to what would be generated, Differs from what would be generated when it was edited since — either way, you tick it to rewrite it. That way an adjustment is not overwritten unnoticed.

Going further

• ETL pipelines — what a pipeline is, and the source, target and vocabulary roles.
• Building and running a pipeline — running the generated scripts, then checking what was loaded.
• Schemas — the mappings the generator draws the load scripts from.
• Concept mapping — producing the correspondences the vocabulary consumes.