Reading from Synthea tables allergies.csv

Destination Field Source field Logic Comment field
observation_id
person_id patient Map by mapping person.person_source_value to patient. Find person.person_id by mapping allergies.patient to person.person_source_value.
observation_concept_id code Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from allergies a join cte_target_vocab_map ctvm on ctvm.source_code = a.code and ctvm.target_domain_id = ‘Observation’ and ctvm.target_vocabulary_id = ‘SNOMED’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL
observation_date start
observation_datetime start Use 00:00:00 as time.
observation_type_concept_id Set as 38000280 for all records.
value_as_number
value_as_string
value_as_concept_id
qualifier_concept_id
unit_concept_id
provider_id
visit_occurrence_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in AllVisitTable.sql.
visit_detail_id
observation_source_value code
observation_source_concept_id code Use code to lookup source_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from cte_source_vocab_map csvm join allergies a on csvm.source_code = a.code and csvm.source_vocabulary_id = ‘SNOMED’
unit_source_value
qualifier_source_value
observation_event_id
obs_event_field_concept_id
value_as_datetime

Reading from Synthea table conditions.csv

Destination Field Source field Logic Comment field
observation_id
person_id patient Map by mapping person.person_source_value to patient. Find person.person_id by mapping encouters.patient to person.person_source_value.
observation_concept_id code Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from conditions c join cte_target_vocab_map ctvm on ctvm.source_code = c.code and ctvm.target_domain_id = ‘Observation’ and ctvm.target_vocabulary_id = ‘SNOMED’
observation_date start
observation_datetime start
observation_type_concept_id Set as 38000280 for all records.
value_as_number
value_as_string
value_as_concept_id
qualifier_concept_id
unit_concept_id
provider_id
visit_occurrence_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in AllVisitTable.sql.
visit_detail_id
observation_source_value code
observation_source_concept_id code Use code to lookup source_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from cte_source_vocab_map csvm join conditions c on csvm.source_code = c.code and csvm.source_vocabulary_id = ‘SNOMED’
unit_source_value
qualifier_source_value
observation_event_id
obs_event_field_concept_id
value_as_datetime