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

sub-job

SunLife · Load-Visits

Runs visit and eligibility processing Update [date]: Disabling the CheckVisitGFEComplianceBatch task because it is blowing up processing (ran for >24 hours before being killed) and because no one knows what it does. Can re-enable if it is determined to be necessary and we can invest time to figure out how to properly fix it. -CW

called by Daily-Visits-Processing

sub-job SunLife/Load-Visits · db SunLife_ProdMirror · updated 2026-03-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-Visits

9 tasks · SunLife
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. LoadVisitBatchLoadVisitBatch
    Load scheduled visits and associated charges into the processing pipeline using custom SQL queries for pricing estimation and quote generation.
    writes QuoteBatchVisitBatch · reads PatientTransformUpdatedVisitChargeTransformVisitTransformVisitInsuranceVisitVisitDeltavisitdelta
    ↳ feeds from Sync QuoteBatch,VisitBatch,CustomPropertyMapBatch
    custom visit querycustom charge queryauto service type queryauto coverage queryadd zero charge
  2. CalculateVisitsflow
    Compute the good-faith estimate (CalculateVisits).
    · reads DimensionMemberVisitBatch
    ↳ feeds from LoadVisitBatch
    custom querycalculate chargesbatch mode
  3. ▶Load-Custom-Propertiessub-job · 11↗Load custom properties from Load-Visits
    1. UpdateVisitCustomPropertyFromQueryUpdateVisitCustomPropertyFromQuerydisabled
      · reads InsuranceSearch_ResultTransformVisitInsuranceCoverageStatusVisitBatch
      custom visit update custom property queryDISABLED
    2. Custom Properties: OtherActiveCoverageUpdateVisitCustomPropertyFromQuerydisabled
      · reads TransformVisitInsuranceCoverageStatusVisitBatch
      custom visit update custom property queryDISABLED
    3. Custom Properties: OtherInactiveCoverageUpdateVisitCustomPropertyFromQuerydisabled
      · reads TransformVisitInsuranceCoverageStatusVisitBatch
      custom visit update custom property queryDISABLED
    4. Custom Properties: Update MostRecentBatch to clear the previous recordsUpdateVisitCustomPropertyFromQuerydisabled
      · reads CustomPropertyMapVisit
      custom visit update custom property queryDISABLED
    5. Custom Properties: MostRecentBatchUpdateVisitCustomPropertyFromQuerydisabled
      auto visit update custom property queryDISABLED
    6. Custom Properties: OAS ReasonUpdateCustomPropertyFromQuerydisabled
      · reads CustomPropertyMapsourceappointmentchaintransformcustompropertiesvisitbatchvisitdelta
      entity=Visitcustom property key=OASReasonauto querybatch modeDISABLED
    7. Custom Properties: Has BundleUpdateCustomPropertyFromQuerydisabled
      · reads CustomPropertyMapTransformVisitChargeVisitBatchvisitdelta
      entity=Visitcustom property key=HasBundleauto querybatch modeDISABLED
    8. Custom Properties: GFE ReasonUpdateCustomPropertyFromQuerydisabled
      · reads CustomPropertyMaptransformcustompropertiesvisitbatchvisitdelta
      entity=Visitcustom property key=GFEReasoncustom querybatch modeDISABLED
    9. Custom Properties: Set LetterPrinted to NoUpdateCustomPropertyFromQuerydisabled
      · reads CustomPropertyMapVisitBatch
      entity=Visitcustom property key=LetterPrintedcustom querybatch modeDISABLED
    10. UpdateCustomPropertyFromQuery (GFEDueDate)UpdateCustomPropertyFromQuerydisabled
      · reads SourceEncounterTransformVisitVisitBatch
      entity=Visitcustom property key=GFEDueDatecustom querybatch modeDISABLED
    11. Custom Properties: HasOBPackageUpdateCustomPropertyFromQuery
      · reads TransformVisitInsuranceVisitBatch
      entity=Visitcustom property key=HasOBPackagecustom querybatch mode
  4. LogVisitChangeEventsBatchLogVisitChangeEventsBatch
    Compare batch visits against production records to detect and log specific property changes for auditing and compliance tracking.
  5. CheckVisitGFEComplianceBatchCheckVisitGFEComplianceBatchdisabled
    Audits Good Faith Estimate compliance for batch visits and identifies records requiring resends based on federal timing regulations.
    DISABLED
  6. AutoSendLettersAutoSendLettersdisabled
    Generates and sends patient cost estimate letters for visits in the batch table based on configured qualification and communication rules.
    DISABLED
  7. Sync QuoteBatch,VisitBatch,CustomPropertyMapBatchSyncBatch
    Synchronizes processed data from batch staging tables to production tables, updating visits, quotes, and custom property mappings.
    writes CustomPropertyMapQuoteVisitVisitChangeEvent · reads CustomPropertyMapBatchQuoteBatchVisitBatchVisitChangeEventBatch
    ↳ feeds from LoadVisitBatch
    sync quotesync visitsync custom property mapsync removed custom property mapssync visit change event
  8. ▶Load-Visit-Factsub-job · 2↗
    1. Create Visit Factsqlload_visit_fact__create_visit_fact.sql
      ✨ Builds the visit fact table structure to store comprehensive encounter metrics, insurance status, and payment details.
    2. Load Visit Factsqlload_visit_fact__load_visit_fact.sql
      ✨ Loads visit fact data by identifying and refreshing records with recent activity in payments, eligibility, or letters.
      writes VisitFactVisitFactDelta · reads ActivityLogCustomPropertyMapEligibilityStatusMapEligibilityTxnPatientLetterPaymentTxnVisitVisitFactDelta
  9. UpdateDashboardTablesUpdateDashboardTables
    Regenerates optimized aggregate tables for all dashboard reports and updates their definitions to reflect the latest source data.