Skip to content

Changes

Page history
Update Incremental Load authored by Henke, Elisa's avatar Henke, Elisa
Show whitespace changes
Inline Side-by-side
Incremental-Load.md
View page @ 54c34ab8
...@@ -2,7 +2,7 @@ ...@@ -2,7 +2,7 @@
The ETL job FHIR-to-OMOP can be executed as incremental load. The incremental load is used to load FHIR resources from FHIR Gateway to OMOP CDM that have been changed (inserts and updates) since the last bulk load or incremental load. To ensure that the ETL job only uses new or changed resources, the resources in the FHIR Gateway are filtered using the `last_updated_at` column. Because filtering is based on the **DATA_BEGINDATE** and **DATA_ENDDATE** parameters in the `sample.env` file, both parameters have to be adjusted before running the ETL job. The ETL job FHIR-to-OMOP can be executed as incremental load. The incremental load is used to load FHIR resources from FHIR Gateway to OMOP CDM that have been changed (inserts and updates) since the last bulk load or incremental load. To ensure that the ETL job only uses new or changed resources, the resources in the FHIR Gateway are filtered using the `last_updated_at` column. Because filtering is based on the **DATA_BEGINDATE** and **DATA_ENDDATE** parameters in the `sample.env` file, both parameters have to be adjusted before running the ETL job.
When the job runs as incremental load, all resources are processed in a random order. For this reason, processing FHIR resources is more complex than bulkload. The following sections provide an overview of the data flow and processing of resources depending on their type. When the job runs as incremental load, each FHIR resource read in must be checked to see if it already exists in OMOP CDM. For this reason, processing FHIR resources is more complex than bulkload. The following sections provide an overview of the data flow and processing of resources.
# Data flow # Data flow
...@@ -12,9 +12,9 @@ When the job runs as incremental load, all resources are processed in a random o ...@@ -12,9 +12,9 @@ When the job runs as incremental load, all resources are processed in a random o
<summary> Show data flow of Patient resources </summary> <summary> Show data flow of Patient resources </summary>
--- ---
When a Patient resource is read in, the job first checks whether this Patient resource already exists in OMOP CDM. This is done in the FHIR_ID_TO_OMOP_ID_MAP table using the `logical_id` and/or the `identifier`. If the Patient resource already exists in OMOP CDM, an update will take place in OMOP CDM based on the corresponding `person_id` (= `omop_id` from FHIR_ID_TO_OMOP_ID_MAP). If the Patient resource does not yet exist in OMOP CDM, it will be written to OMOP CDM as a new resource. When a Patient resource is read in, the job first checks whether this Patient resource already exists in OMOP CDM. This is done in the PERSON table using `fhir_logical_id` or `fhir_identifier`. If the Patient resource already exists in OMOP CDM, an update will take place in OMOP CDM based on the corresponding `person_id`. If the Patient resource does not yet exist in OMOP CDM, it will be written to OMOP CDM as a new resource.
![inkrementellesLaden-Patient](uploads/58d4c826a2220a50e2c2b866cdf55949/inkrementellesLaden-Patient.png) ![inkrementellesLaden-Patient.drawio](uploads/c4f9c9097c2aab97c0061ad7efc11f98/inkrementellesLaden-Patient.drawio.png)
</details> </details>
</p> </p>
...@@ -25,13 +25,13 @@ When a Patient resource is read in, the job first checks whether this Patient re ...@@ -25,13 +25,13 @@ When a Patient resource is read in, the job first checks whether this Patient re
<summary> Show data flow of Encounter resources </summary> <summary> Show data flow of Encounter resources </summary>
--- ---
When a Encounter resource is read in, the job first checks whether the referenced Patient resource already exists in OMOP CDM. This is done in the FHIR_ID_TO_OMOP_ID_MAP table using the `logical_id` and/or the `identifier`. If the referenced Patient resource already exists in OMOP CDM, the existing `person_id` will be used (= `omop_id` from FHIR_ID_TO_OMOP_ID_MAP). When an Encounter resource is read in, the job first checks whether the referenced Patient resource already exists in OMOP CDM. This is done in the PERSON table using `fhir_logical_id` or `fhir_identifier` from the referenced FHIR Patient resource. If the referenced Patient resource already exists in OMOP CDM, the existing `person_id` will be used.
Due to the fact that the resource types are read in random order, it may happen that the referenced Patient resource is not yet available in OMOP CDM. In this case, a dummy for this Patient resource is created and written to OMOP CDM. If the "real" Patient resource is to be written to OMOP CDM during incremental loading, only an update of the dummy takes place. If the referenced Patient resource is not yet available in OMOP CDM, a dummy for this Patient resource is created and written to OMOP CDM. If the "real" Patient resource is to be written to OMOP CDM during a next incremental loading, only an update of the dummy takes place.
Next, the job checks if the Encounter resource already exists in OMOP CDM. This is done in the FHIR_ID_TO_OMOP_ID_MAP table using the `logical_id` and/or the `identifier`. If the Encounter resource does not yet exist in OMOP CDM, it will be written to OMOP CDM as a new resource. If the Encounter resource already exists in OMOP CDM, an update will take place in OMOP CDM based on the corresponding `visit_occurrence_id` (= `omop_id` from FHIR_ID_TO_OMOP_ID_MAP). Next, the job checks if the Encounter resource already exists in OMOP CDM. This is done in the VISIT_OCCURRENCE table using `fhir_logical_id` or `fhir_identifier`. If the Encounter resource does not yet exist in OMOP CDM, it will be written to OMOP CDM as a new resource. If the Encounter resource already exists in OMOP CDM, an update will take place in OMOP CDM based on the corresponding `visit_occurrence_id`.
![inkrementellesLaden-Encounter](uploads/baec731d4a07c5ca816485a2f7b3d8d6/inkrementellesLaden-Encounter.png) ![inkrementellesLaden-Encounter.drawio](uploads/b07f5cb307bdd0081b0800efb7c75388/inkrementellesLaden-Encounter.drawio.png)
</details> </details>
</p> </p>
...@@ -44,15 +44,15 @@ Next, the job checks if the Encounter resource already exists in OMOP CDM. This ...@@ -44,15 +44,15 @@ Next, the job checks if the Encounter resource already exists in OMOP CDM. This
--- ---
### Medication ### Medication
When a Medication resource is read in, the job first checks whether this Medication resource already exists in OMOP CDM. This is done in the FHIR_ID_TO_OMOP_ID_MAP table using the `logical_id` and/or the `identifier`. If the Medication resource does not yet exist in OMOP CDM, it will be written to OMOP CDM as a new resource. If the Medication resource already exists in OMOP CDM, an update will take place in FHIR_ID_TO_OMOP_ID_MAP. When a Medication resource is read in, the job first checks whether this Medication resource already exists in OMOP CDM. This is done in the MEDICATION_ID_MAP table using `fhir_logical_id` or `fhir_identifier`. If the Medication resource does not yet exist in OMOP CDM, it will be written to OMOP CDM as a new resource. If the Medication resource already exists in OMOP CDM, an update will take place in MEDICATION_ID_MAP.
### MedicationAdministration/Medication Statement ### MedicationAdministration/Medication Statement
When a MedicationAdministration/MedicationStatement resource is read in, the job first checks whether the referenced Medication resource already exists in OMOP CDM. This is done in the FHIR_ID_TO_OMOP_ID_MAP table using the `logical_id` and/or the `identifier`. If the referenced Medication resource already exists in OMOP CDM, the ATC-code in `omop_table` is used for further processing of the MedicationAdministration/MedicationStatement resource. When a MedicationAdministration/MedicationStatement resource is read in, the job first checks whether the referenced Medication resource already exists in OMOP CDM. This is done in the MEDICATION_ID_MAP table using `fhir_logical_id` or `fhir_identifier`. If the referenced Medication resource already exists in OMOP CDM, the ATC-code in from the column `atc` is used for further processing of the MedicationAdministration/MedicationStatement resource.
Due to the fact that the resource types are read in random order, it may happen that the referenced Medication resource is not yet available in OMOP CDM. In this case, the Medication reference of the MedicationAdministration/MedicationStatement resource is set as `drug_source_value` in DRUG_EXPOSURE. After all resources have been processed, post-processing takes place. During post processing, the referenced Medication resource is searched in the FHIR_ID_TO_OMOP_ID_MAP table using the Medication reference in `drug_source_value`. If this Medication resource exists in OMOP CDM, an update in DRUG_EXPOSURE takes place. If the referenced Medication resource is not yet available in OMOP CDM, the Medication reference of the MedicationAdministration/MedicationStatement resource is set as `drug_source_value` in DRUG_EXPOSURE. After all resources have been processed, post-processing takes place. During a next incremental loading, the referenced Medication resource is searched in the MEDICATION_ID_MAP table using the Medication reference in `drug_source_value`. If this Medication resource exists in OMOP CDM, an update in DRUG_EXPOSURE takes place.
![inkrementellesLaden-Medication.drawio](uploads/9275198dc30a46dfdd46bf2da0678aac/inkrementellesLaden-Medication.drawio.png) ![inkrementellesLaden-Medication.drawio](uploads/e9738753528b7a4de08d982ee706f3d1/inkrementellesLaden-Medication.drawio.png)
</details> </details>
</p> </p>
...@@ -63,23 +63,23 @@ Due to the fact that the resource types are read in random order, it may happen ...@@ -63,23 +63,23 @@ Due to the fact that the resource types are read in random order, it may happen
<summary> Show data flow of Encounter, Procedure, Observation, MedicationAdministration, MedicationStatement and Condition resources </summary> <summary> Show data flow of Encounter, Procedure, Observation, MedicationAdministration, MedicationStatement and Condition resources </summary>
--- ---
When a Encounter/Procedure/Observation/MedicationAdministration/MedicationStatement/Condition resource is read in, the job first checks whether the referenced Patient resource and Encounter resource already exists in OMOP CDM. This is done in the FHIR_ID_TO_OMOP_ID_MAP table using the `logical_id` and/or the `identifier`. If the referenced Patient resource and Encounter resource already exists in OMOP CDM, the existing `person_id` and `visit_occurrence_id` will be used (= `omop_id` from FHIR_ID_TO_OMOP_ID_MAP). When an Encounter/Procedure/Observation/MedicationAdministration/MedicationStatement/Condition resource is read in, the job first checks whether the referenced Patient resource and Encounter resource already exists in OMOP CDM. This is done in PERSON and VISIT_OCCURRENCE table using `fhir_logical_id` or `fhir_identifier`. If the referenced Patient resource and Encounter resource already exists in OMOP CDM, the existing `person_id` and `visit_occurrence_id` will be used.
Due to the fact that the resource types are read in random order, it may happen that the referenced Patient resource and Encounter resource is not yet available in OMOP CDM. In this case, a dummy for the Patient resource and a dummy for the Encounter resource is created and written to OMOP CDM. If the "real" Patient resource and Encounter resource is to be written to OMOP CDM during incremental loading, only an update of the dummies takes place. If the referenced Patient resource and Encounter resource is not yet available in OMOP CDM, a dummy for the Patient resource and a dummy for the Encounter resource is created and written to OMOP CDM. If the "real" Patient resource and Encounter resource is to be written to OMOP CDM during a next incremental loading, only an update of the dummies takes place.
Next the job checks if the read resource (e.g. Condition) already exists in OMOP CDM. This is done in the following tables using specific columns: Next the job checks if the read resource (e.g. Condition) already exists in OMOP CDM. This is done analogously to Patient resources and Encounter resources using `fhir_logical_id` or `fhir_identifier`. For each resource type, the following OMOP CDM tables are used for the check:
| Resource type | Table | Column | | Resource type | Table |
| :------: | :------: | :------ | | :------: | :------: |
| Encounter | VISIT_DETAIL | person_id <br> visit_detail_start_datetime <br> visit_occurrence_id | | Encounter | VISIT_DETAIL |
| Procedure | PROCEDURE_OCCURRENCE | person_id <br> procedure_concept_id <br> procedure_datetime <br> procedure_source_concept_id | | Procedure | PROCEDURE_OCCURRENCE |
| Observation | OBSERVATION <br> MEASUREMENT | person_id <br> observation/measurement_concept_id <br> observation/measurement_datetime <br> observation/measurement_source_concept_id | | Observation | OBSERVATION <br> MEASUREMENT |
| MedicationAdministration, <br> MedicationStatement | DRUG_EXPOSURE | person_id <br> drug_concept_id <br> drug_exposure_start_datetime <br> drug_source_concept_id | | MedicationAdministration, <br> MedicationStatement | DRUG_EXPOSURE |
| Condition | CONDITION_OCCURRENCE <br> OBSERVATION <br> PROCEDURE_OCCURRENCE <br> MEASUREMENT | person_id <br> condition/observation/procedure/measurement_concept_id <br> condition_start_datetime / observation/procedure/measurement_datetime <br> condition/observation/procedure/measurement_source_concept_id | | Condition | CONDITION_OCCURRENCE <br> OBSERVATION <br> PROCEDURE_OCCURRENCE <br> MEASUREMENT |
If the read resource does not yet exist in OMOP CDM, it will be written to OMOP CDM as a new resource. If the read resource already exists in OMOP CDM, an update will take place in OMOP CDM based on the corresponding `visit_detail_id`/`procedure_occurrence_id`/`observation_id`/`measurement_id`/`drug_exposure_id`/`condition_occurrence_id`. If the read resource does not yet exist in OMOP CDM, it will be written to OMOP CDM as a new resource. If the read resource already exists in OMOP CDM, an update will take place in OMOP CDM based on the corresponding `visit_detail_id`/`procedure_occurrence_id`/`observation_id`/`measurement_id`/`drug_exposure_id`/`condition_occurrence_id`.
![inkrementellesLaden-Others.drawio](uploads/694ac2de6a6dbc74aea18aea7d8a2009/inkrementellesLaden-Others.drawio.png) ![inkrementellesLaden-Others.drawio](uploads/b3e6a9bb2b8c44b6f8c56a599d516344/inkrementellesLaden-Others.drawio.png)
</details> </details>
</p> </p>
\ No newline at end of file