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

sub-job

PAA · Load-Visit-Transform-Tables

Loads transform tables for Visits - Load TransformAppointment unpivots the case# (if there is more than 1 Scheduled CPT code) Load TransformCharge is a intermidate table before loading the TransformVisitCharge that pulls together CPT codes, BaseUnits, 75th Percentile time units, UnltrasoundCPTCode, BlockCPTCode, Ultrasound $ amount, Block $ amount that is needed for Total billing amount calculation. Edgewater implementation notes No changes to 1. TransformSurgicalCPT 2. TransformVisitCharge

called by Daily-Processing, Daily-Processing-PAAMobile

sub-job PAA/Load-Visit-Transform-Tables · db PAA_ProdMirror · updated 2025-12-31

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-Visit-Transform-Tables

12 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. Load VisitDeltasqlload_visit_transform_tables__load_visitdelta.sql
    ✨ Identifies new or updated appointments across multiple data sources to build a delta table for downstream processing.
    writes VisitDelta · reads VisitDelta
  2. Load PatientDeltasqlload_visit_transform_tables__load_patientdelta.sql
    ✨ Extracts and maps unique patient identifiers from various appointment sources to identify patient records requiring updates.
    writes PatientDelta · reads SourceAppointmentSourceAppointmentMeditechSourceAppointmentMobileSourceHollySpringsAppointmentSourceedgewaterAppointmentVisitDelta
    ↳ feeds from Load VisitDelta
  3. Load TransformAppointmentCPTUnpivotsqlload_visit_transform_tables__load_transformappointmentcptunpivot.sql
    ✨ Unpivots and cleans procedure codes from multiple appointment sources for visits identified in the delta table.
    writes TransformAppointmentCPTUnpivot · reads SourceAppointmentSourceAppointmentMeditechSourceAppointmentMobileSourceHollySpringsAppointmentSourceedgewaterAppointmentVisitDelta
    ↳ feeds from Load VisitDelta
  4. Load TransformVisitDiagnosisCodessqlload_visit_transform_tables__load_transformvisitdiagnosiscodes.sql
    ✨ Parses and loads cleaned diagnosis codes from appointment sources into the transformation table for updated visits.
    writes TransformVisitDiagnosisCodes · reads SourceAppointmentSourceAppointmentMobileVisitDelta
    ↳ feeds from Load VisitDelta
  5. Load TransformVisitInsurancesqlload_visit_transform_tables__load_transformvisitinsurance.sql
    ✨ Maps and loads primary and secondary insurance details, including payor names and member IDs, for updated visit records.
    writes TransformVisitInsurance · reads InsuranceMapSourceAppointmentSourceAppointmentMeditechSourceAppointmentMobileSourceHollySpringsAppointmentSourceedgewaterAppointmentVisitDelta
    ↳ feeds from Load VisitDelta
  6. Load TransformSurgicalCPT (excluding PAA Mobile, EdgeWater)sqlload_visit_transform_tables__load_transformsurgicalcpt_excluding_paa_mobile_edgewater.sql
    ✨ Calculates surgical CPT rates and units for standard data sources using fee schedules and bundled service logic.
    writes TransformSurgicalCPT · reads CPTWithModifierEndoscopyCPTSourceBundlesCombinedSourceEpicFacilityReferenceSourceEpictoBFSInsuranceMappingSourcePhygenFacilityTransformAppointmentCPTUnpivotTransformFeeSchedule
    ↳ feeds from Load TransformAppointmentCPTUnpivot, Load TransformVisitInsurance, Load VisitDelta
  7. Load TransformSurgicalCPT (PAA EdgeWater)sqlload_visit_transform_tables__load_transformsurgicalcpt_paa_edgewater.sql
    ✨ Calculates surgical CPT rates and units specifically for Edgewater source data using fee schedules and bundled service logic.
    writes TransformSurgicalCPT · reads BFSInsuranceMapCPTWithModifierEndoscopyCPTInsuranceMapSourceBundlesCombinedSourcePhygenFacilityTransformAppointmentCPTUnpivotTransformFeeSchedule
    ↳ feeds from Load TransformAppointmentCPTUnpivot, Load TransformVisitInsurance, Load VisitDelta
  8. Load TransformSurgicalCPT (PAA Mobile)sqlload_visit_transform_tables__load_transformsurgicalcpt_paa_mobile.sql
    ✨ Calculates surgical CPT rates and units for mobile source data using fee schedules and bundled service logic.
    writes TransformSurgicalCPT · reads BFSInsuranceMapCPTWithModifierEndoscopyCPTInsuranceMapSourceBundlesCombinedSourcePhygenFacilitySourceSelfPayMobileRatesTransformAppointmentCPTUnpivot
    ↳ feeds from Load TransformAppointmentCPTUnpivot, Load TransformVisitInsurance, Load VisitDelta
  9. Load TransformSurgicalCPT (Meditech)sqlload_visit_transform_tables__load_transformsurgicalcpt_meditech.sql
    ✨ Calculates surgical CPT rates and units for Meditech source data using fee schedules and bundled service logic.
    writes TransformSurgicalCPT · reads CPTWithModifierEndoscopyCPTInsuranceMapSourceBundlesCombinedSourcePhygenFacilityTransformAppointmentCPTUnpivotTransformFeeScheduleTransformVisitInsurance
    ↳ feeds from Load TransformAppointmentCPTUnpivot, Load TransformVisitInsurance, Load VisitDelta
  10. Load TransformPatientsqlload_visit_transform_tables__load_transformpatient.sql
    ✨ Consolidates and loads patient demographic information, including contact details and addresses, from various appointment sources into the transformation table.
    writes TransformPatient · reads PatientDeltaSourceAppointmentSourceAppointmentMeditechSourceAppointmentMobileSourceHollySpringsAppointmentSourceedgewaterAppointment
    ↳ feeds from Load PatientDelta
  11. Load TransformVisitChargesqlload_visit_transform_tables__load_transformvisitcharge.sql
    ✨ Generates visit charges by combining surgical CPT data with diagnosis coverage flags to determine final billable units and types.
    writes TransformVisitChargetempTransformSurgicalCPT · reads FullyCoveredDiagnosisTempTransformSurgicalCPTTransformVisitDiagnosisCodesVisitDelta
    ↳ feeds from Load TransformVisitDiagnosisCodes, Load VisitDelta
  12. Load TransformVisitsqlload_visit_transform_tables__load_transformvisit.sql
    ✨ Builds the standardized visit transformation table by consolidating appointment data from multiple source systems and mapping facility and provider details.
    writes TransformVisit · reads PatientDeltaSourceAppointmentSourceAppointmentMeditechSourceAppointmentMobileSourceEpicFacilityReferenceSourceHollySpringsAppointmentSourcePhygenFacilitySourcePhygenSurgeons
    ↳ feeds from Load PatientDelta, Load VisitDelta