OHDSI Standardized Vocabularies v5.0

The dm+d vocabulary refresh process follows these nine sequential steps:

1. Data Extraction and Initial Correction: Initial retrieval and cleaning of raw data.
2. Device Segregation: Separating device-related data.
3. Ingredients Processing: Processing ingredient information.
4. Prepare for drug_concept_stage Population: Preparing data structures for drug concepts.
5. Populate drug_concept_stage: Filling the drug_concept_stage table.
6. Populate pc_stage (pack content): Filling the pc_stage table.
7. Populate internal_relationship_stage: Filling the internal_relationship_stage table.
8. Prepare for ds_stage (drug strength) Population: Preparing data structures for drug strength.
9. Populate ds_stage: Filling the ds_stage table.

The maps_for_load_stage.sql script is a critical component of the CIEL vocabulary load pipeline. Its primary purpose is to build the canonical CIEL to OMOP Standard mapping set, which is used to populate the concept_relationship_stage table.

It processes raw mapping candidates from ciel_mapping_lookup, applies prioritization and QA rules, and assigns each mapping to a ranked bucket with an explicit rule_applied label. The final output is a single, consolidated staging table: maps_for_load_stage.

When defining relationships between ICD codes and replacement concepts, follow these semantic rules for relationship_id usage:

• "Maps to": Use exclusively for 1-to-1 FULL equivalent mappings.
• "Maps to" + "Maps to value": Use for Observations and Measurements that include specific results.
• "Is a": Use as a temporary relationship for validation checks. This is applicable for 1-to-1 PARTIAL equivalent mappings and 1-to-many mappings.

Important Implementation Note: While you may maintain a manual table using 'Is a' relationships, you must convert 'Is a' to 'Maps to' during the insertion process into the concept_relationship_manual table (for example, by using a CASE WHEN statement in your SQL).

The CDISC update process utilizes two specific tables for Metadata Vocabulary processing:

• cdisc_mapped: A table created manually outside the Load Stage (LS) containing manual mapping results.
• cdisc_automapped: A table created inside the Load Stage (LS) containing automated mapping results.

The maps_for_load_stage.sql script follows specific defensive programming patterns to ensure data integrity:

• NULL Safety: Uses NOT EXISTS instead of NOT IN to avoid common SQL pitfalls associated with NULL values.
• Error Mitigation: Employs explicit blacklists for known problematic CIEL codes and crosswalks.
• Hierarchical Mapping: Separates direct mappings (Rank 1) from crosswalk-based inferences (Rank 2) to maintain a clear hierarchy of confidence.

APIgrabber populates several target tables using specific jobs. Each job corresponds to a specific API endpoint and data type:

To ensure the logging of changes in manual tables functions correctly, each relationship can only be used in one specific direction. These valid directions are defined within the ProcessManualRelationships function.

You can inspect the primary relationship IDs for existing relationships using the following SQL query:

select devv5.GetPrimaryRelationshipID(r.relationship_id) correct_rel, r.relationship_id from devv5.relationship r;