Table 2 Example of a D-ETL rule that loads data into the Care_site table in OMOP from a claims-based source CSV file
From: Dynamic-ETL: a hybrid approach for health data extraction, transformation and loading
Rule Order | Rule Description | Target Table | Target Column | Map Type | Map Order | Source Table | Source Value |
|---|---|---|---|---|---|---|---|
1 | Medical_claims to Care_site | Care_site | PRIMARY | 1 | Medical_claims | medical_claims.billing_provider_id, medical_claims.place_of_service_code, provider.provider_organization_type | |
1 | Medical_claims to Care_site | Care_site | JOIN | 2 | provider | medical_claims.billing_provider_id = provider.provider_id | |
1 | Medical_claims to Care_site | Care_site | WHERE | 3 | provider.provider_organization_type in (‘1’, ‘2’) | ||
1 | Medical_claims to Care_site | Care_site | care_site_source_value | VALUE | 4 | medical_claims.billing_provider_id || ‘-’ ||medical_claims.place_of_service_code||’-’ ||provider.provider_organization_type | |
1 | Medical_claims to Care_site | Care_site | organization_source_value | VALUE | 5 | NULL | |
1 | Medical_claims to Care_site | Care_site | place_of_service_source_value | VALUE | 6 | medical_claims.place_of_service_code | |
1 | Medical_claims to Care_site | Care_site | care_site_address_1 | VALUE | 7 | provider.provider_address_first_line | |
1 | Medical_claims to Care_site | Care_site | care_site_address_2 | VALUE | 8 | provider.provider_street | |
1 | Medical_claims to Care_site | Care_site | care_site_city | VALUE | 9 | provider.provider_city | |
1 | Medical_claims to Care_site | Care_site | care_site_state | VALUE | 10 | provider.provider_state | |
1 | Medical_claims to Care_site | Care_site | care_site_zip | VALUE | 11 | provider.provider_zip | |
1 | Medical_claims to Care_site | Care_site | care_site_county | VALUE | 12 | NULL |