This is an old revision of the document!
Data ETL
The most important impact the Standardized Vocabularies have on the ETL process from raw to CDM-formatted data is the Domain of each Concept. Irrespective from which source table a record comes, or what coding scheme it is represented by, the destination table will be determined by the domain_id of the respective Concept. Any ETL will have to follow the following logic for process every record in the source data:
- ** Retrieve the source code** from the record.
- Find the Concept that corresponds to the source code. In most cases, that is done by looking up the source code in the concept_code field with the correct content of the vocabulary_id field in the CONCEPT table. Sometimes, the source code needs to be manipulated to find the right match. For example, ICD-9-CM codes have a dot after the 2nd or 3rd character, but in source data they are often stored without the dot. Or NDC codes come in a variety of formats (with dashes and asterisks or without, 9 or 11 digit), making the mapping process more complicated. Look into the specification of each vocabulary for specific recommendations of the lookup process.
- Map to a Standard Concept (standard_concept='S') by retrieving all the active (invalid_reason field should be NULL) records from the CONCEPT_RELATIONSHIP table. Use concept_id_1 for the Concept you want to map and concept_id_2 for the destination Concept, with relationship_id='Maps to'. If the source code is a local code and you have a SOURCE_TO_CONCEPT_MAP table for these, determine the destination Concept from the target_concept_id field. The destination Concept could be the Concept itself if it happens to be a Standard Concept, or a Concept in another vocabulary. In most cases, a source code maps to a single destination Standard Concept, but in some cases it could be two or three.
- Write a record in the corresponding CDM table for each destination Standard Concept based on the content of the domain_id field. Most Standardized Clinical Data Tables require a start_date and a Type Concept (see below). Some have more fields that need consideration. See the details of each table in the CDM Specifications. The corresponding table/field combination for each Domain is as follows:
| domain_id | CDM table | Field | Comment |
|---|---|---|---|
| Generic | Any | Any | Generic Concepts can be in any field that ends in concept_id. |
| Gender | PERSON | gender_concept_id | |
| Race | PERSON | race_concept_id | |
| Ethnicity | PERSON | ethnicity_concept_id | |
| Visit | VISIT_OCCURRENCE | visit_concept_id | |
| Procedure | PROCEDURE_OCCURRENCE | procedure_concept_id | |
| Modifier | PROCEDURE_OCCURRENCE | modifier_concept_id | |
| Drug | DRUG_EXPOSURE | drug_concept_id | |
| Route | DRUG_EXPOSURE | route_concept_id | |
| Unit | MEASUREMENT or OBSERVATION or SPECIMEN | unit_concept_id | Units are used in different contexts. * |
| Device | DEVICE_EXPOSURE | device_concept_id | |
| Condition | CONDITION_OCCURRENCE | condition_concept_id | |
| Measurement | MEASUREMENT | measurement_concept_id | |
| Meas Value Operator | MEASUREMENT | operator_concept_id | |
| Meas Value | MEASUREMENT | value_as_concept_id | |
| Observation | OBSERVATION | observation_concept_id | |
| Relationship | FACT_RELATIONSHIP | relationship_concept_id | |
| Place of Service | CARE_SITE | place_of_service_concept_id | |
| Provider Specialty | PROVIDER | specialty_concept_id | |
| Currency | VISIT_COST or PROCEDURE_COST or DRUG_COST or DEVICE_COST | currency_concept_id | Currency values could appear in any of the *_COST tables. * |
| Revenue Code | PROCEDURE_COST | revenue_code_concept_id | |
| Specimen | SPECIMEN | specimen_concept_id | |
| Spec Anatomic Site | SPECIMEN or MEASUREMENT or OBSERVATION | anatomic_site_concept_id or value_as_concept_id or value_as_concept_id | Anatomical site Concepts can be used to characterize the origin of a Specimen, or the result of a Measurement or Observation. * |
| Spec Disease Status | SPECIMEN | disease_status_concept_id |
- For some Source Concepts, the Standardized Vocabularies do not provide a mapping to a Standard Concept (there is no record in the CONCEPT_RELATIONSHIP or SOURCE_TO_CONCEPT_MAP tables), usually because the Source Concept is too generic or otherwise ill-defined. In these cases, the domain_id field of the Source Concept itself should be used to place the record in the right table. Some Source Concepts have combination Domains, such as “Device/Procedure”. In these cases, write a record into each of the combination Domain table (in this case to the DEVICE_OCCURRENCE and PROCEDURE_OCCURRENCE tables). As the Standard Concept write Concept 0 (concept_id=0) into the respective *_concept_id field.
If the Domain of the destination Concept is “Domain” or “Metadata”, an error has occurred in the construction of the mapping table. Please report in the CDM-Builder forum if you believe there is such an error in the Standardized Vocabularies data.
The same is true if you find a Type Concept Domain, like “Obs Period Type”, “Death Type”, “Visit Type”, “Procedure Type”, etc.These Type Concepts are valid concepts and have to be placed into the respective *_type_concept_id field of the respective CDM tables. However, they should never be introduced as part of a mapping process, as there is no equivalent information in the source data, because they denote the origin of where a current record is coming from. Type Concepts are hard-wired in the ETL process, because it depends on the structure of the source data how to assign Type Concepts to records. Please also report if you find a situation like that.