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

sub-job

SSR · Load-Source-Visit-Tables

called by Load-Visits

sub-job SSR/Load-Source-Visit-Tables · db SouthSoundRadiology_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-Source-Visit-Tables

18 tasks · SSR
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. Load Appointment Deltasqlload_source_visit_tables__load_appointment_delta.sql
    ✨ Identifies new or updated future appointments and unscheduled visits since the last data load for processing.
    writes TempAppointmentDelta
  2. Load TempVisitAggregatesqlload_source_visit_tables__load_tempvisitaggregate.sql
    ✨ Groups appointment changes by patient, facility, and date to prepare for visit-level aggregation and processing.
    writes TempVisitAggregate · reads SourcePatientAppointmentTempAppointmentDelta
    ↳ feeds from Load Appointment Delta
  3. Load TempNewVisitAppointmentMapsqlload_source_visit_tables__load_tempnewvisitappointmentmap.sql
    ✨ Maps changed appointments to their existing visit identifiers to track relationships between appointments and visits.
    writes TempNewVisitAppointmentMap
  4. Load TempNewVisitAppointmentMatchsqlload_source_visit_tables__load_tempnewvisitappointmentmatch.sql
    ✨ Identifies visits where all associated appointments share consistent patient, facility, and scheduling details.
    writes TempNewVisitAppointmentMatch
  5. Update VisitID on TempVisitAggregatesqlload_source_visit_tables__update_visitid_on_tempvisitaggregate.sql
    ✨ Assigns existing visit identifiers to the aggregated appointment data based on matching patient and facility details.
    · reads TempNewVisitAppointmentMatchTempVisitAggregate
    ↳ feeds from Load TempNewVisitAppointmentMatch, Load TempVisitAggregate
  6. Load TempVisitMatchsqlload_source_visit_tables__load_tempvisitmatch.sql
    ✨ Matches new appointment aggregates to existing visits based on patient, facility, and scheduling status.
    writes TempVisitMatch
  7. Update TempVisitAggregate with matched VisitIDsqlload_source_visit_tables__update_tempvisitaggregate_with_matched_visitid.sql
    ✨ Updates the temporary visit aggregate table with matched visit identifiers using date and order status to prioritize the best match.
    · reads SourceVisitTempVisitAggregateTempVisitMatch
    ↳ feeds from Load SourceVisit, Load TempVisitAggregate, Load TempVisitMatch
  8. Load TempVisitAppointmentMapsqlload_source_visit_tables__load_tempvisitappointmentmap.sql
    ✨ Builds a temporary mapping table between visit aggregates and source appointments based on patient, date, facility, and status.
    writes TempVisitAppointmentMap
  9. Load TempSourceVisitsqlload_source_visit_tables__load_tempsourcevisit.sql
    ✨ Loads a temporary source visit table by aggregating appointment details, arrival times, and determining insurance categories for each visit.
    writes TempSourceVisit
  10. Load SourceVisitsqlload_source_visit_tables__load_sourcevisit.sql
    ✨ Loads new visit records into the main source visit table for entries that do not yet have an assigned identifier.
    writes SourceVisit · reads TempSourceVisitTempVisitAggregate
    ↳ feeds from Load TempSourceVisit, Load TempVisitAggregate
  11. Update SourceVisitsqlload_source_visit_tables__update_sourcevisit.sql
    ✨ Updates existing source visit records with the latest scheduling, facility, and insurance information from the temporary source table.
    · reads SourceVisitTempSourceVisitTempVisitAggregate
    ↳ feeds from Load SourceVisit, Load TempSourceVisit, Load TempVisitAggregate
  12. Update TempAggregate VisitID for new Visitssqlload_source_visit_tables__update_tempaggregate_visitid_for_new_visits.sql
    ✨ Updates the temporary aggregate table with visit identifiers for newly created records to ensure consistency across the pipeline.
    · reads SourceVisitTempVisitAggregate
    ↳ feeds from Load SourceVisit, Load TempVisitAggregate
  13. Load SourceVisitAppointmentMapsqlload_source_visit_tables__load_sourcevisitappointmentmap.sql
    ✨ Loads the permanent visit-to-appointment mapping table by replacing existing records with updated data from the temporary mapping table.
    writes SourceVisitAppointmentMap · reads TempVisitAggregateTempVisitAppointmentMap
    ↳ feeds from Load TempVisitAggregate, Load TempVisitAppointmentMap
  14. Update Visit Statussqlload_source_visit_tables__update_visit_status.sql
    ✨ Updates visit statuses based on appointment priority and archives any visits that are no longer associated with an active appointment.
    · reads SourcePatientAppointmentSourceVisitSourceVisitAppointmentMapTempAppointmentDeltaVisitStatusMap
    ↳ feeds from Load Appointment Delta, Load SourceVisit, Load SourceVisitAppointmentMap
  15. Load SourceVisitChargessqlload_source_visit_tables__load_sourcevisitcharges.sql
    ✨ Builds the source visit charge table by mapping appointment procedures and additional charges to standardized service types for each visit.
    writes SourceVisitCharge · reads ChargeTypeSourcePatientAppointmentSourceVisitAppointmentMapTempVisitAggregate
    ↳ feeds from Load SourceVisitAppointmentMap, Load TempVisitAggregate
  16. *DISABLED-ARCHIVED* Load SourceVisitChargessqlload_source_visit_tables__disabled_archived_load_sourcevisitcharges.sqldisabled
    ✨ Populates visit charges using exam-specific data and hardcoded service type logic for various imaging procedure categories.
    writes SourceVisitCharge · reads ChargeTypeSourceExamsSourcePatientAppointmentSourceVisitAppointmentMapTempVisitAggregate
  17. Load SourceVisitInsurancesqlload_source_visit_tables__load_sourcevisitinsurance.sql
    ✨ Loads primary and secondary insurance details for visits, including member IDs, group numbers, and coordination of benefits information.
    writes SourceVisitInsurance · reads SourceCarrierMapSourcePatientAppointmentSourceVisitSourceVisitAppointmentMapTempVisitAggregate
    ↳ feeds from Load SourceVisit, Load SourceVisitAppointmentMap, Load TempVisitAggregate
  18. *DISABLED - MOVED TO LOAD-VISITS* Insert to VisitLoadHistorysqlload_source_visit_tables__disabled_moved_to_load_visits_insert_to_visitloadhistory.sqldisabled
    ✨ Records the current timestamp to track when the visit data loading process was last executed.
    writes VisitLoadHistory