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

Batch / file feed · ClarityFlow pipeline

SSR · Daily-Processing

pipeline SSR/Daily-Processing · db SouthSoundRadiology_ProdMirror · updated 2025-09-17
classification: flow · 7 Flow markers

trigger

How it's scheduled

Real-time loop — every 30 min (~33×/day)

New appointments land continuously on the Batch / file feed. Front-of-house work is time-boxed — the estimate and letter have to reach the patient before the visit — so the pipeline polls every 30 minutes to check eligibility, price the good-faith estimate, and send the patient letter within minutes of an appointment being booked, rather than waiting for an overnight batch.

Scheduled taskCadenceServerStateCoordination
\MDC\Daily - Processing - SSRevery 30 min (from 07:05)prod-db-1enabledstandalone

shape

Against the canonical Flow spine

Every Flow pipeline is a variation on one shape. Lit stages are the ones this pipeline runs; the labels match the capability lens.

Ingest
Batch / file feed
→
Eligibility & benefits
EligibilityBenefits
→
Estimate
Estimate
→
Letters
Letters
→
Work queue & sync
DashboardsSync

computed concepts · from the SQL

What it computes

Business concepts mined from this pipeline's SQL text — the meaning behind the typed steps. Each links to the concept lens (who else computes it).

CPT / procedure logic

Payers in SQLMedicaid

task tree

What the pipeline 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).

flow

Daily-Processing

5 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. Enqueue-Schedule-Data-Filessub-jobdisabled
  2. ▶Load-Schedule-Data-Filessub-job · 1↗
    1. ▶Load-Schedule-Data-Filesub-job · 9↗
      1. Load Schedule Data FileLoadFile
        writes ImportScheduleData
        data file spec=ImportScheduleDatafile path={FilePath}import mode=Replace
      2. Load SourcePatientAppointmentsqlload_schedule_data_file__load_sourcepatientappointment.sql
        ✨ Transforms and loads imported schedule data into the source appointment table, mapping patient demographics, insurance details, and procedure information.
        writes SourcePatientAppointment · reads ImportScheduleData
        ↳ feeds from Load Schedule Data File
      3. Load TempPatientDeltasqlload_schedule_data_file__load_temppatientdelta.sql
        ✨ Creates a temporary list of unique patient IDs from the imported schedule data to identify records requiring updates.
        writes TempPatientDelta
      4. Load Patients - Oldsqlload_schedule_data_file__load_patients_old.sqldisabled
        ✨ Updates the master patient table with demographic information derived from the earliest recorded appointment for each patient in the delta.
        writes PatientTempNextPatientAppointmentDemographics · reads TempNextPatientAppointmentDemographics
      5. Load TempNextPatientAppointmentDemographicssqlload_schedule_data_file__load_tempnextpatientappointmentdemographics.sql
        ✨ Extracts the most recent demographic information for patients in the delta into a temporary table based on their latest appointment.
        writes TempNextPatientAppointmentDemographics
      6. Load PatientsLoadPatients
        Upsert patient records into the patient dimension.
        · reads PatientTempNextPatientAppointmentDemographics
        ↳ feeds from Load TempNextPatientAppointmentDemographics
        auto patient query
      7. Load ImportScheduleDataArchivesqlload_schedule_data_file__load_importscheduledataarchive.sql
        ✨ Archives current schedule data into a historical storage table with a timestamp for record-keeping.
        writes ImportScheduleDataArchive · reads ImportScheduleData
        ↳ feeds from Load Schedule Data File
      8. Delete History from ImportScheduleDataArchivesqlload_schedule_data_file__delete_history_from_importscheduledataarchive.sql
        ✨ Deletes archived schedule data older than thirty days to maintain the size of the historical storage table.
        writes ImportScheduleDataArchive
      9. Archive FileExecuteProcess
        command=cmd.exe
  3. ▶Load-Common-Dimensionssub-job · 3↗
    1. Load Procedure Code Dimensionssqlload_common_dimensions__load_procedure_code_dimensions.sql
      ✨ Synchronizes procedure code dimension members by merging updates and new records from the common data source into the local repository.
      writes DimensionMember · reads DimensionMember
      ↳ feeds from Load State Dimensions, Load Time Dimensions
    2. Load Time Dimensionssqlload_common_dimensions__load_time_dimensions.sql
      ✨ Generates and merges time-based dimension members for days, months, and years based on the range of available appointment dates.
      writes DimensionMember · reads SourcePatientAppointment
    3. Load State Dimensionssqlload_common_dimensions__load_state_dimensions.sql
      ✨ Updates state dimensions using facility location data to allow for geographic segmentation of healthcare services and revenue.
      writes DimensionMember · reads Facility
  4. ▶Load-Visitssub-job · 27↗
    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.
  5. Data-Integrity-Reportsub-job

markers

Why it classified as flow

Front-of-house actions found anywhere in the tree (including nested sub-jobs).

CalculateVisits
Compute the good-faith estimate
CheckBenefits
Real-time eligibility (270/271) benefit lookup
ExportBatchVisitEligibility
Export eligibility results
Update-Visit-Customproperties-PrimaryCoverageStatus
ClarityFlow front-of-house step
Update-Visit-Customproperties-SecondaryCoverageStatus
ClarityFlow front-of-house step
auto_benefits
Auto-apply parsed benefits to visits
auto_send_letters
Send GFE / patient letters