Azure Databases: EDH & RDC - ETL Enabling

EDH Job Status

• Before starting any ETL, you need to verify that all EDH ETL's are up to the current date.
• The reason for doing so is because, EDH Etl are the source of all RDC ETL.
• The EDH is SQL server database and all of its login credentials are as follow:
  • Hostname: dmp-a1-prd-sql-01.database.windows.net
  • DBname: dmp-dw
  • Username: rdc
  • Password: EDHPassword
• The query below allows you to validate all EDH process.

select '1', 'edh_patient_day', max(edh_udt_utc_ts)  from epic_clarity.edh_patient_day
union
select '99','dm_ed_enc', max(edh_udt_utc_ts) from epic_clarity.dm_ed_enc
union
select '48','patient', max(edh_udt_utc_ts) from epic_clarity.patient
union
select '2','srn_encounter', max(edh_udt_ts) from rdc_views.srn_encounter
union
select '52','social_hx', max(edh_udt_utc_ts) from epic_clarity.social_hx----52  social_hx   2021-11-16 21:03:38.600
union
select '52','pat_enc_2', max(edh_udt_utc_ts) from epic_clarity.pat_enc_2
union
select '61','edh_antibiotic', max(edh_udt_utc_ts) from epic_clarity.edh_antibiotic
union
select '46','clarity_ser_dept', max(edh_udt_utc_ts) from epic_clarity.clarity_ser_dept
union
select '46','clarity_ser_2', max(edh_udt_utc_ts) from epic_clarity.clarity_ser_2
union
select '48','bpa_act_smartdata', max(edh_udt_utc_ts) from epic_clarity.bpa_act_smartdata
union
select '52','v_order_medications', max(edh_udt_utc_ts) from epic_clarity.v_order_medications
union
select '47','or_case_all_proc', max(edh_udt_utc_ts) from epic_clarity.or_case_all_proc
union
select '49','identity_id', max(edh_udt_utc_ts) from epic_clarity.identity_id
order by 3

• The result from the screenshot be as follow:

• If you run the script on 1/16/2025, ensure that the dates of the etl job except for the first two are 1/16/2025 before processing.

RDC - Monitor EDH ETL.

• Before starting any RDC ETL, you need to verify that it's either active and currently_running.
• If the EDH jobs are still running it will show active = [v].
• If the EDH jobs are still running it will show currently_running = [v].
• The RDC prod Config is a postgresql database that will be use for this project:
  • Hostname: i2rdc3-prod-pg-flex-config.prod.i2rdc3.wustl.edu
  • DBname: postgres
  • Username: user's username
• The SQL statement shows the status.

select * from etl_run.config_main cm where department_name = 'rdc3';

RDC - Start ETL

• Once the job has been completed, you will need to run the following query to enable the RDC ETL in the RDC config database.

update etl_run.config_main set run_schedule = '0 22-23 * * 0-4' where department_name = 'rdc3';

RDC - Monitor active ETL

• Once the RDC ETL jobs have been enabled, you will need to this query to monitor the status in the RDC config database.

with a as
(
select
max(job_id) id
from
etl_run.controller_generated_sql cgs
)
select
count(case when parent_stage = 'Ingress' and complete = true then 1 end) ingress_done,
count(case when parent_stage = 'Ingress' and complete = false then 1 end) ingress_left,
(sum(case when parent_stage = 'Ingress' and complete = true then 1.0 end)/
sum(case when parent_stage = 'Ingress' then 1.0 end)*100)::decimal(5,2) || '%' ingress_status,
count(case when parent_stage = 'Stage.SH' and complete = true then 1 end) stage_done,
count(case when parent_stage = 'Stage.SH' and complete = false then 1.0 end) stage_left,
(sum(case when parent_stage = 'Stage.SH' and complete = true then 1.0 end)/
sum(case when parent_stage = 'Stage.SH' then 1.0 end)*100)::decimal(5,2) || '%' stage_status,
count(case when parent_stage = 'OmopStage.SH' and complete = true then 1 end) omopstage_done,
count(case when parent_stage = 'OmopStage.SH' and complete = false then 1 end) omopstage_left,
(sum(case when parent_stage = 'OmopStage.SH' and complete = true then 1.0 end)/
(sum(case when parent_stage = 'OmopStage.SH' then 1.0 end)*100)::decimal(5,2)) || '%' omopstage_status,
count(case when parent_stage = 'omop_staging/MERGE_OMOP' and complete = true then 1 end) mergeomop_done,
count(case when parent_stage = 'omop_staging/MERGE_OMOP' and complete = false then 1 end) mergeomop_left,
(sum(case when parent_stage = 'omop_staging/MERGE_OMOP' and complete = true then 1.0 end)/
sum(case when parent_stage = 'omop_staging/MERGE_OMOP' then 1.0 end) * 100)::decimal(5,2)|| '%' mergeomop_status,
max(job_id) job_id
from
etl_run.controller_generated_sql cgs2
where job_id = (select id from a);

• The script output will be as follow:

RDC - Disable ETL

• You can run the script below to stop or disable and RDC ETL process.

update etl_run.config_main set run_schedule = '0 25-26 * * *' where department_name = 'rdc3'; 

• Reference RDC ETL Control with Nifi for more information.