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

sub-job

PAA · Load-Phygen-Files

The Load SourceAppointment File file mask has a temp hack pattern match (? x12) to exclude the Endo file that is being sent with the same beggining of the file name ("AnFutSched").

called by Daily-Processing

sub-job PAA/Load-Phygen-Files · db PAA_ProdMirror · updated 2026-07-08

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-Phygen-Files

15 tasks · PAA
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. DownloadDownload
    Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
    server=PAA_ProdMirror-mdcssh-sftpremote directory=/local directory={SourceDataFolder}remote archive directory=/StagingProcessingTest/
  2. ▶Load-Appointment-Filesub-job · 4↗
    1. ScanDataFileScanDataFiledisabled
      Analyzes a delimited file's structure to automatically infer column definitions and save a reusable specification for future imports.
      data file spec key=ImportAppointmenttable name=ImportAppointmentrow terminator=Linefeedcolumn separator=CommaDISABLED
    2. Clean AnFutSched FileExecuteProcess
      command=powershell.exe
    3. Load AppointmentLoadFile
      writes ImportAppointment
      data file spec=ImportAppointmentfile path={FilePath}import mode=Replacearchive file path={SourceDataArchive}
    4. Load SourceAppointmentsqlload_appointment_file__load_sourceappointment.sql
      ✨ Loads appointment data into the source table while mapping payer details and refreshing records from the import staging area.
      writes SourceAppointment · reads ImportAppointmentMappedSourceAppointment
      ↳ feeds from Load Appointment
  3. ▶Load-Appointment-File-HollySpringssub-job · 1↗
    1. Clean-And-Load-HollySprings-Filesub-job
  4. ▶Load-Appointment-File-Mobilesub-job · 3↗
    1. Load PAA Mobile AppointmentLoadFile
      writes ImportMobileAppointment
      data file spec=ImportMobileAppointmentfile path={FilePath}import mode=Replacearchive file path={SourceDataArchive}
    2. OLD - Attempt to Match Existing SourceAppointment Tablesqlload_appointment_file_mobile__old_attempt_to_match_existing_sourceappointment_table.sqldisabled
      ✨ Loads mobile appointment data into the source table by mapping fields from the mobile import staging area for processing.
      writes SourceAppointment · reads ImportMobileAppointment
    3. Load SourceAppointmentMobilesqlload_appointment_file_mobile__load_sourceappointmentmobile.sql
      ✨ Builds the mobile-specific source appointment table by transforming and importing data from the mobile application staging area.
      writes SourceAppointmentMobile · reads ImportMobileAppointment
      ↳ feeds from Load PAA Mobile Appointment
  5. ▶Load-Appointment-File-EdgeWatersub-job · 1↗
    1. ▶Clean-And-Load-Edgewater-Filesub-job · 3↗
      1. Execute updateEdgewaterFile.ps1ExecuteProcessdisabled
        command=powershell.exeDISABLED
      2. Load EdgerwaterFutSched FileLoadFile
        writes ImportEdgeWaterAppointment
        data file spec=ImportEdgeWaterAppointmentfile path={SourceDataFolder}\{FileName}.csvimport mode=Replacearchive file path={SourceDataArchive}
      3. Load SourceEdgewaterAppointmentsqlclean_and_load_edgewater_file__load_sourceedgewaterappointment.sql
        ✨ Loads external appointment and surgical case data into the staging area to prepare for downstream processing and estimation.
        writes SourceEdgeWaterAppointment · reads ImportEdgeWaterAppointment
        ↳ feeds from Load EdgerwaterFutSched File
  6. ▶Load-Appointment-File-Pinehurstsub-job · 2↗
    1. Load AppointmentLoadFile
      writes ImportAppointmentPinehurst
      data file spec=ImportAppointmentPinehurstfile path={FilePath}import mode=Replacearchive file path={SourceDataArchive}
    2. Load SourceAppointmentsqlload_appointment_file_pinehurst__load_sourceappointment.sql
      ✨ Loads Pinehurst-specific appointment data into the source table while mapping payer identifiers and names from the import staging area.
      writes SourceAppointment · reads ImportAppointmentSourceAppointment
  7. ▶Load-EndoAppointment-Filesub-job · 3↗
    1. Load EndoAppointmentLoadFiledisabled
      writes ImportEndoscopyCases
      data file spec=ImportEndoscopyCasesfile path={FilePath}import mode=Replacearchive file path={SourceDataArchive}DISABLED
    2. Load SourceEndoAppointmentsqlload_endoappointment_file__load_sourceendoappointment.sqldisabled
      ✨ Loads endoscopy appointment details and parsed insurance payor information into the source table for cost estimate processing.
      writes SourceEndoAppointment · reads ImportEndoScopyCasesSourceEndoAppointment
    3. ProcessDataFileProcessDataFile
      Import delimited files by automatically detecting their structure and merging the data into source tables.
      writes ImportAppointmentEndoscopySourceAppointmentEndoscopy
      file path={SourceDataFolder}\{FileName}.csvarchive directory={SourceDataArchive}base table name=AppointmentEndoscopyrow terminator=Linefeedcolumn separator=Comma
  8. ▶Load-Appointment-File-Meditechsub-job · 3↗
    1. ProcessDataFile (disabled bcs of dupes issue)ProcessFiledisabled
      writes ImportAppointmentMeditechSourceAppointmentMeditech
      file path={FilePath}archive directory={SourceDataArchive}base table name=AppointmentMeditechrow terminator=Newlinecolumn separator=Comma
    2. LoadDataFileLoadDataFile
      Imports delimited data files into database tables using predefined specifications for validation, loading, and optional file archiving.
      writes ImportAppointmentMeditech
      data file spec=ImportAppointmentMeditechfile path={FilePath}import mode=Replacearchive file path={SourceDataArchive}
    3. ExecuteMergeSqlExecuteMergeSql
      Synchronizes data from external sources into target tables by performing batched insert, update, and delete operations based on keys.
      writes SourceAppointmentMeditech · reads ImportAppointmentMeditech
      ↳ feeds from LoadDataFile
      merge data source type=ThisDatabasetable name=SourceAppointmentMeditechmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
  9. ▶Load-Bundles-Filesub-job · 2↗
    1. Load BundlesLoadFile
      writes ImportBundlesCombined
      data file spec=ImportBundlesCombinedfile path={FilePath}import mode=Replacearchive file path={SourceDataArchive}
    2. Load SourceBundlesCombinedsqlload_bundles_file__load_sourcebundlescombined.sql
      ✨ Updates the combined bundles source table by merging imported surgical bundle details, procedure codes, and surgeon information.
      writes SourceBundlesCombined · reads ImportBundlesCombined
      ↳ feeds from Load Bundles
  10. ▶Load-EpicFacilityReference-Filesub-job · 2↗
    1. Load EpicFacilityReferenceLoadFile
      writes ImportEpicFacilityReference
      data file spec=ImportEpicFacilityReferencefile path={FilePath}import mode=Replacearchive file path={SourceDataArchive}
    2. Load SourceEpicFacilityReferencesqlload_epicfacilityreference_file__load_sourceepicfacilityreference.sql
      ✨ Updates facility identifiers and aliases between Epic and internal systems to maintain consistent facility cross-referencing.
      writes SourceEpicFacilityReference · reads ImportEpicFacilityReference
      ↳ feeds from Load EpicFacilityReference
  11. ▶Load-EpicEndoFacilityReference-Filesub-job · 3↗
    1. Scan EpicEndoFacilityReferenceScanFiledisabled
      data file spec key=ImportEpicEndoFacilityReferencetable name=ImportEpicEndoFacilityReferencerow terminator=Newlinecolumn separator=CommaDISABLED
    2. Load EpicEndoFacilityReferenceLoadFile
      writes ImportEpicEndoFacilityReference
      data file spec=ImportEpicEndoFacilityReferencefile path={FilePath}import mode=Replace
    3. Load SourceEpicFacilityReferencesqlload_epicendofacilityreference_file__load_sourceepicfacilityreference.sql
      ✨ Updates the mapping between procedure rooms and their corresponding Epic facility aliases to ensure accurate facility identification.
      writes SourceEpicEndoFacilityReference · reads ImportEpicEndoFacilityReference
      ↳ feeds from Load EpicEndoFacilityReference
  12. ▶Load-EpictoBFSInsuranceMapping-Filesub-job · 2↗
    1. Load EpictoBFSInsuranceMappingLoadFile
      writes ImportEpictoBFSInsuranceMapping
      data file spec=ImportEpictoBFSInsuranceMappingfile path={FilePath}import mode=Replace
    2. Load SourceEpictoBFSInsuranceMappingsqlload_epictobfsinsurancemapping_file__load_sourceepictobfsinsurancemapping.sql
      ✨ Loads insurance plan and payor mapping data to translate Epic insurance records into standardized billing system formats.
      writes SourceEpictoBFSInsuranceMapping · reads ImportEpictoBFSInsuranceMappingSourceEpictoBFSInsuranceMapping
      ↳ feeds from Load EpictoBFSInsuranceMapping
  13. ▶Load-FeeSchedule-Filesub-job · 2↗
    1. Load EpicFacilityReferenceLoadFile
      writes ImportFeeSchedule
      data file spec=ImportFeeSchedulefile path={FilePath}import mode=Replace
    2. Load SourceFeeSchedulesqlload_feeschedule_file__load_sourcefeeschedule.sql
      ✨ Loads fee schedule data into the source table, formatting procedure codes and updating records based on unique identifiers.
      writes SourceFeeSchedule · reads ImportFeeScheduleSourceFeeSchedule
      ↳ feeds from Load EpicFacilityReference
  14. ▶Load-PhygenFacility-Filesub-job · 2↗
    1. Load PhygenFacilityLoadFile
      writes ImportPhygenFacility
      data file spec=ImportPhygenFacilityfile path={FilePath}import mode=Replacearchive file path={SourceDataArchive}
    2. Load SourcePhygenFacilitysqlload_phygenfacility_file__load_sourcephygenfacility.sql
      ✨ Updates the facility source table by replacing existing records with the latest facility and profit center information.
      writes SourcePhygenFacility · reads ImportPhygenFacility
      ↳ feeds from Load PhygenFacility
  15. ▶Load-PhygenSurgeon-Filesub-job · 2↗KB [date] : Had to update import table to LineFeed vs New line based on how we were receiving the file.
    1. Load PhygenSurgeonsLoadFile
      writes ImportPhygenSurgeons
      data file spec=ImportPhygenSurgeonsfile path={FilePath}import mode=Replacearchive file path={SourceDataArchive}
    2. Load SourcePhygenSurgeonssqlload_phygensurgeon_file__load_sourcephygensurgeons.sql
      ✨ Loads surgeon data into the source table, deduplicating by NPI to retain only the most recent records.
      writes SourcePhygenSurgeons · reads ImportPhygenSurgeons
      ↳ feeds from Load PhygenSurgeons