Legacy Pipeline Atlas worker-1snapshot 2026-07-15

sub-job

SeaRad · Load-Transform-HL7

Update on [date] --> Added ECC=1 in Loadpatients task as per ticket# https://mdclarity.zendesk.com/agent/tickets/14141

called by Parse-HL7

sub-job SeaRad/Load-Transform-HL7 · db SeaRad_ProdMirror · updated 2025-09-17

task tree

What the sub-job runs

Tasks in order, each with what it does — typed stepsfrom the MDClarity task docs (click to open the reference), sub-jobsyou can expand inline (▶) to reveal their own tasks, and SQL steps summarized by Gemini (✨). Every step shows the tables it writes/reads and which earlier step it feeds from(sqlglot lineage).

sub-job

Load-Transform-HL7

12 tasks · SeaRad
sub-job / Flow action SQL / native step▶ expand a sub-job inline · ↗ open it as its own page · a typed step opens its doc
  1. TransformPatientsqlload_transform_hl7__transformpatient.sql
    ✨ Updates patient demographic and contact information by extracting and standardizing data from incoming HL7 ADT and order messages.
    writes TransformPatient · reads HL7DeltaHL7OrderHL7PatientTransformPatient
  2. TransformPatientInsurancesqlload_transform_hl7__transformpatientinsurance.sql
    ✨ Identifies insurance changes from HL7 messages and updates standardized patient insurance records, including coordination of benefits and group details.
    writes PatientIDInsuranceDeltaTransformPatientInsurance · reads HL7DeltaHL7PatientInsuranceMapPatientIDInsuranceDeltaTransformPatientInsurance
  3. Update PatientDateAppointmentIDMapsqlload_transform_hl7__update_patientdateappointmentidmap.sql
    ✨ Builds a mapping between patients and scheduled dates to generate unique internal identifiers for incoming HL7 messages.
    writes PatientDateAppointmentIDMap · reads HL7DeltaPatientDateAppointmentIDMap
  4. Update OrderSourceIDDeltasqlload_transform_hl7__update_ordersourceiddelta.sql
    ✨ Updates order mappings and identifies appointments impacted by new messages, flagging those where all associated orders are cancelled.
    writes OrderSourceIDDeltaOrderSourceIDMap · reads HL7DeltaOrderSourceIDMapPatientDateAppointmentIDMapPatientIDInsuranceDeltaTransformVisit
    ↳ feeds from Merge TransformVisit, TransformPatientInsurance, Update PatientDateAppointmentIDMap
  5. Load ImportTransformVisitsqlload_transform_hl7__load_importtransformvisit.sql
    ✨ Loads visit details into a staging table, mapping facility, provider, and service type information for upcoming appointment processing.
    writes ImportTransformVisit · reads HL7OrderOrderSourceIDDeltaOrderSourceIDMap
    ↳ feeds from Update OrderSourceIDDelta
  6. Merge TransformVisitsqlload_transform_hl7__merge_transformvisit.sql
    ✨ Merges staged visit data into the main transformation table by replacing existing records with updated appointment and facility information.
    writes TransformVisit · reads ImportTransformVisit
    ↳ feeds from Load ImportTransformVisit
  7. Load ImportTransformVisitInsurancesqlload_transform_hl7__load_importtransformvisitinsurance.sql
    ✨ Loads insurance coverage details from HL7 orders into a staging table, organizing primary, secondary, and tertiary payers for each visit.
  8. Merge TransformVisitInsurancesqlload_transform_hl7__merge_transformvisitinsurance.sql
    ✨ Updates the main insurance transformation table by replacing existing coverage records with the latest staged insurance information for each visit.
    writes TransformVisitInsurance · reads ImportTransformVisitInsurance
  9. Load ImportTransformVisitChargesqlload_transform_hl7__load_importtransformvisitcharge.sql
    ✨ Loads procedure charges into a staging table, cross-walking clinical codes to standard billing formats and service types for cost estimation.
    writes ImportTransformVisitCharge · reads HL7OrderOrderSourceIDDeltaOrderSourceIDMapSeaRadCPTCrosswalk
    ↳ feeds from Update OrderSourceIDDelta
  10. Merge TransformVisitChargesqlload_transform_hl7__merge_transformvisitcharge.sql
    ✨ Merges staged procedure charges into the main transformation table, ensuring the most recent billing codes are associated with each visit.
    writes TransformVisitCharge · reads ImportTransformVisitCharge
    ↳ feeds from Load ImportTransformVisitCharge
  11. LoadPatientsLoadPatients
    Upsert patient records into the patient dimension.
    · reads PatientTransformPatient
    ↳ feeds from TransformPatient
    auto patient query
  12. Update HL7ProcessLogsqlload_transform_hl7__update_hl7processlog.sql
    ✨ Updates the HL7 process log to mark the most recent active processing task as completed.
    · reads HL7ProcessLog