Skip to main content

Advertisement

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