etl-to-omop-cdm

name	etl-to-omop-cdm
description	Map OpenMed-extracted, terminology-coded conditions, drugs, and measurements into OMOP CDM v5.4 clinical tables (condition_occurrence, drug_exposure, measurement) for OHDSI/ATLAS analytics. Use when the user wants to load NLP-derived facts into an OMOP database, build an OHDSI ETL from clinical notes, populate condition_occurrence or drug_exposure from text, or standardize note-derived findings to OMOP standard concepts. Covers the source-to-standard concept mapping pattern, required vs optional CDM fields, type concepts for NLP-derived rows, and the user-supplied OHDSI vocabulary (CONCEPT/CONCEPT_RELATIONSHIP). Consumes coded OpenMed analyze_text output (after SNOMED/RxNorm/LOINC linking) and produces OMOP-conformant rows.
license	Apache-2.0
metadata	{"project":"OpenMed","category":"analytics-reporting","pairs":"after","version":"1.0"}

ETL to OMOP CDM

The OMOP Common Data Model (CDM) is the OHDSI standard for observational health data. This skill maps OpenMed-derived clinical facts — entities from analyze_text that you have already linked to a source terminology — into the OMOP clinical event tables condition_occurrence, drug_exposure, and measurement. The NLP runs on-device; OMOP loading is a downstream, deterministic transform.

When to use this skill

After you have (a) extracted entities with OpenMed and (b) coded them to a source vocabulary (ICD-10-CM / SNOMED for conditions, RxNorm for drugs, LOINC for labs — see the linking skills). Use this skill to turn those coded facts into OMOP rows. It is not a clinical NER skill and not a code-linking skill; it assumes both are done.

Quick start

import openmed

note = "Assessment: type 2 diabetes mellitus. Started metformin 500 mg PO BID. HbA1c 8.2%."
result = openmed.analyze_text(note, output_format="dict")
# result["entities"] -> [{text,label,confidence,start,end}, ...]

# You then code each entity to a SOURCE concept using the OHDSI vocabulary you
# downloaded (see linking-umls-concepts / normalizing-rxnorm / mapping-loinc),
# and map SOURCE -> STANDARD via CONCEPT_RELATIONSHIP ('Maps to').
fact = {
    "person_id": 1001,
    "domain": "Condition",
    "source_code": "E11.9",          # ICD-10-CM, from your coding step
    "source_vocabulary": "ICD10CM",
    "source_concept_id": 45533010,    # OHDSI CONCEPT for E11.9 (lookup)
    "standard_concept_id": 201826,    # 'Maps to' -> SNOMED 'Type 2 diabetes mellitus'
    "start_date": "2024-03-12",       # from building-patient-timelines
    "char_span": (fact_start, fact_end),
}

OpenMed never ships UMLS/SNOMED/RxNorm/LOINC content. You supply the OHDSI vocabulary bundle (Athena download) and do the lookups under your own license. OpenMed provides the spans and labels.

The source → standard pattern (the heart of OMOP)

Every clinical event row carries two concept ids:

1. *_source_concept_id — the OHDSI CONCEPT for your original code (e.g. the ICD-10-CM or RxNorm code your linking step produced).
2. *_concept_id — the standard concept, obtained by following CONCEPT_RELATIONSHIP.relationship_id = 'Maps to' from the source concept. Conditions standardize to SNOMED, drugs to RxNorm, measurements to LOINC. If no mapping exists, set the standard id to 0.

Domain → table → fields

See references/omop_cdm_v5_4_fields.md for the full per-table field list. Core mapping by OpenMed entity domain:

OpenMed entity domain	OMOP table	Standard vocab	Key date / value fields
Disease / Condition	condition_occurrence	SNOMED	condition_start_date, optional condition_end_date
Drug / Medication	drug_exposure	RxNorm	drug_exposure_start_date, drug_exposure_end_date, quantity, sig
Lab / Measurement	measurement	LOINC	measurement_date, value_as_number, unit_concept_id, value_as_concept_id

Type concepts: mark rows as NLP-derived

Every event row needs a *_type_concept_id recording provenance. For facts derived from clinical text, OHDSI uses the type concept 32831 "EHR episode record" / "Note" family — specifically prefer a "...from note" / "NLP"-flavored standard type concept from the Type Concept vocabulary in your bundle. Do not invent ids; resolve the type concept against the vocabulary you loaded so cohort builders can filter NLP-derived rows.

Workflow

1. Extract & code. analyze_text → entities; link each to a source code (linking skills). Resolve source_concept_id and the 'Maps to' standard concept from your Athena vocabulary.
2. Resolve dates. Attach start (and end, where known) dates per building-patient-timelines. OMOP date fields are DATE; keep the matching *_datetime only if you truly have a time.
3. Assign person_id. Join to your person table by an internal key — not by any PHI string. De-identify upstream.
4. Build rows with required keys, the source+standard concept pair, the NLP type concept, and a unique surrogate *_occurrence_id / *_exposure_id / measurement_id.
5. Stage *_source_value (the raw surface string, after de-id) for QA traceability — but never put raw PHI there.
6. Conform & validate. Run OHDSI Achilles/DataQualityDashboard on the loaded CDM before analytics.

Hand-off to / from OpenMed

• From OpenMed: analyze_text entities (offsets + labels), deidentify upstream, and the per-domain linking skills (linking-umls-concepts, normalizing-rxnorm, mapping-loinc, mapping-to-snomed, coding-icd10).
• To OHDSI: loaded condition_occurrence / drug_exposure / measurement rows are consumed by ATLAS, Achilles, and cohort definitions — and by computing-ecqms for measure denominators/numerators.

Edge cases & gotchas

• No license bundling. OpenMed does not include SNOMED/RxNorm/LOINC/UMLS. Download the OHDSI vocabularies from Athena and run lookups under your own agreement. This is a hard rule.
• Unmapped → standard concept 0. When 'Maps to' yields nothing, set *_concept_id = 0 and keep the source ids. Never fabricate a standard id.
• Domain routing follows the standard concept's domain, not the source code's apparent type. An ICD-10 code can map to a SNOMED concept whose domain_id is Observation or Measurement — load it into the table the standard concept dictates.
• NLP rows are lower-assurance. Tag them with the NLP/from note type concept and carry confidence (e.g. in a companion table) so analysts can threshold. Don't silently mix them with structured EHR rows.
• Dates are required and must be valid. Undated note facts can't populate a *_start_date; route them to your "needs review" staging, not into the CDM with a placeholder date.
• measurement units and values. Parse value_as_number + unit (mapped to a unit_concept_id); for qualitative results use value_as_concept_id.

Standards & references

• OMOP CDM v5.4 specification: https://ohdsi.github.io/CommonDataModel/cdm54.html
• OMOP standardized vocabularies & the 'Maps to' relationship: https://ohdsi.github.io/CommonDataModel/vocabulary.html
• OHDSI Athena vocabulary download (user-supplied): https://athena.ohdsi.org/
• The Book of OHDSI (ETL & vocabulary chapters): https://ohdsi.github.io/TheBookOfOhdsi/
• Data Quality Dashboard: https://ohdsi.github.io/DataQualityDashboard/
• OpenMed source: openmed/processing/ (analyze_text output shape).