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

sub-job

SSR · Load-Visits

called by Daily-Processing

sub-job SSR/Load-Visits · db SouthSoundRadiology_ProdMirror · updated 2026-05-07

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-Visits

27 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-Source-Visit-Tablessub-job · 18↗
    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
  2. Load TempVisitDeltasqlload_visits__load_tempvisitdelta.sql
    ✨ Builds a temporary list of unique visit identifiers to track which records require processing or updates in the current batch.
    writes TempVisitDelta
  3. Match Manually-Created Visits to SourceVisitsUpdateEntities
    writes Visit · reads PatientSourceVisitTempVisitDeltaVisit
    ↳ feeds from Load TempVisitDelta, Sync Batch
    entity type=Visitcustom query
  4. Log Insert Time for VisitBatchsqlload_visits__log_insert_time_for_visitbatch.sql
    ✨ Captures the start time of the current visit batch processing for logging and performance monitoring purposes.
    writes TempVisitBatchInsertTime
  5. Load TempVisitServiceTypesqlload_visits__load_tempvisitservicetype.sql
    ✨ Determines the appropriate eligibility service type for each visit based on insurance provider and associated charge information.
    writes TempVisitServiceType
  6. LoadVisitsLoadVisits
    Load scheduled visits and associated charges into the processing pipeline using custom SQL queries for pricing estimation and quote generation.
    writes QuoteBatchVisitBatch · reads PatientSourceVisitSourceVisitChargeSourceVisitInsuranceTempVisitDeltaTempVisitServiceTypeVisit
    ↳ feeds from Load TempVisitDelta, Load TempVisitServiceType, Match Manually-Created Visits to SourceVisits, Sync Batch
    custom visit queryauto charge querycustom service type querycustom coverage queryadd zero charge
  7. Load TempBatchVisitssqlload_visits__load_tempbatchvisits.sqldisabled
    ✨ Builds a temporary table of scheduled visits with insurance details and batch flags to determine eligibility check requirements.
    writes TempBatchVisits
  8. CheckBenefitsflow
    Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
    auto query
  9. Wait for 30 secondssqlload_visits__wait_for_30_seconds.sqldisabled
    ✨ Pauses the pipeline execution for thirty seconds to allow preceding processes or external dependencies to complete.
  10. Re-Check Benefitsflowdisabled
    Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
    · reads CommonDataEligibilityTxnTempBatchVisitsTempVisitBatchInsertTime
    custom queryDISABLED
  11. AutoApplyBenefitsAutoApplyBenefits
    Populates insurance benefits like copays and deductibles on batch visits using data from eligibility verification transactions.
  12. CalculateVisitsflow
    Compute the good-faith estimate (CalculateVisits).
    · reads DimensionMemberTempVisitDeltaVisitBatch
    ↳ feeds from Load TempVisitDelta, Load Visits
    custom querycalculate chargesupdate dimensionsbatch mode
  13. Load TempAuthFlagssqlload_visits__load_tempauthflags.sql
    ✨ Builds a temporary table flagging visits that require or have obtained prior authorization based on appointment and visit data.
    writes TempAuthFlags
  14. Load TempVisitCoverageFlagsqlload_visits__load_tempvisitcoverageflag.sql
    ✨ Builds a temporary table identifying the most recent insurance coverage and eligibility status for upcoming scheduled visits.
    writes TempVisitCoverageFlag · reads EligibilityStatusMapEligibilityTxnSourceVisitInsuranceTempVisitDeltaVisitBatch
    ↳ feeds from Load TempVisitDelta, Load Visits
  15. ExportBatchVisitEligibilityflow
    Push the batch to the app / export results.
    · reads EligibilityTxnvisitbatch
    ↳ feeds from Load Visits
    auto querytable name=BatchVisitEligibility
  16. Update-Visit-Customproperties-PrimaryCoverageStatusUpdate-Visit-Customproperties-PrimaryCoverageStatus
    · reads CustomPropertyMapEligibilityTxnVisitBatchVisitCoverageBatch
    ↳ feeds from Load Visits, Sync Batch
    entity=Visitcustom property key=PrimaryCoverageStatuscustom querybatch mode
  17. Update-Visit-Customproperties-SecondaryCoverageStatusUpdate-Visit-Customproperties-SecondaryCoverageStatus
    · reads CustomPropertyMapEligibilityTxnVisitBatchVisitCoverageBatch
    ↳ feeds from Load Visits, Sync Batch
    entity=Visitcustom property key=SecondaryCoverageStatuscustom querybatch mode
  18. Update-Visit-Customproperties-AuthorizationStatusUpdate-Visit-Customproperties-AuthorizationStatus
    · reads TempAuthFlagsVisitBatch
    ↳ feeds from Load TempAuthFlags, Load Visits
    entity=Visitcustom property key=AuthorizationStatuscustom querybatch mode
  19. Update-Visit-Customproperties-HasMedicarePartCCoverageUpdate-Visit-Customproperties-HasMedicarePartCCoverage
    · reads EligibilityTxnVisitBatch
    ↳ feeds from Load Visits
    entity=Visitcustom property key=HasMedicarePartCCoveragecustom querybatch mode
  20. Update-Visit-Customproperties-HasMismatchedMemberIDUpdate-Visit-Customproperties-HasMismatchedMemberID
    · reads BatchVisitEligibilityEligibilityTxnVisitBatch
    ↳ feeds from Load Visits
    entity=Visitcustom property key=HasMismatchedMemberIDcustom querybatch mode
  21. AutoSendLettersAutoSendLetters
    Generates and sends patient cost estimate letters for visits in the batch table based on configured qualification and communication rules.
  22. Sync BatchSyncBatch
    Synchronizes processed data from batch staging tables to production tables, updating visits, quotes, and custom property mappings.
    writes CustomPropertyMapQuoteVisitVisitChangeEvent · reads CustomPropertyMapBatchQuoteBatchVisitBatchVisitChangeEventBatch
    ↳ feeds from Load Visits
    sync quotesync visitsync custom property mapsync visit change event
  23. Extract Detailed Benefit Returnflow
    Push the batch to the app / export results.
    · reads EligibilityTxnSourceBenefitDetail
    ↳ feeds from Load SourceBenefitDetail
    custom querytable name=TempBenefitDetail
  24. Load SourceBenefitDetailsqlload_visits__load_sourcebenefitdetail.sql
    ✨ Loads detailed insurance benefit information into the source table and ranks eligibility transactions by their most recent status.
    writes SourceBenefitDetail · reads EligibilityTxnSourceBenefitDetailTempBenefitDetailVisit
    ↳ feeds from Match Manually-Created Visits to SourceVisits, Sync Batch
  25. Load VisitLoadHistorysqlload_visits__load_visitloadhistory.sql
    ✨ Records the current timestamp to track when the visit data loading process was last executed.
    writes VisitLoadHistory
  26. ▶Load-Visit-Factsub-job · 2↗
    1. Create Visit Factsqlload_visit_fact__create_visit_fact.sql
      ✨ Creates the VisitFact table to store aggregated visit data, including scheduling, insurance, and payment status, if it does not exist.
    2. Load Visit Factsqlload_visit_fact__load_visit_fact.sql
      ✨ Updates the visit fact table with recent activity regarding payments, eligibility requests, and patient letters for reporting and analysis.
      writes VisitFact · reads EligibilityStatusMapEligibilityTxnPatientLetterPaymentTxnVisit
  27. UpdateDashboardTablesUpdateDashboardTables
    Regenerates optimized aggregate tables for all dashboard reports and updates their definitions to reflect the latest source data.