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

Batch / file feed · ClarityFlow pipeline

SunLife · Daily-Visits-Processing

pipeline SunLife/Daily-Visits-Processing · db SunLife_ProdMirror · updated 2025-09-17
classification: flow · 3 Flow markers

trigger

How it's scheduled

Daily batch — once at 10:30

The Batch / file feed arrives as a scheduled drop, so one overnight pass is enough: it ingests the day's file and works the whole batch to price and send the good-faith estimate letter. A tighter loop would just re-scan the same file.

Scheduled taskCadenceServerStateCoordination
\MDC\Daily - Processing - SunLifedaily at 10:30prod-db-3enabledstandalone

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
→
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).

BundlesFee schedule / contractCPT / procedure logic

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

10 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. ▶Load-ECW-Filessub-job · 12↗
    1. ▶Download-ECW-Filessub-job · 1↗
      1. Download ECW Files from SFTPDownload
        Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
        server=SunLife_ProdMirror-mdcssh-sftpremote directory=/local directory=D:\ClientData\SunLife
    2. Process ExportEncounterProcessFile
      writes ImportEncounterSourceEncounter
      archive directory=D:\ClientData\SunLife\Archivebase table name=Encounterrow terminator=Newline
    3. Process ExportPatientInsuranceProcessFile
      writes ImportPatientInsuranceSourcePatientInsurance
      archive directory=D:\ClientData\SunLife\Archivebase table name=PatientInsurancerow terminator=Newline
    4. Process ExportChargeItemsProcessFile
      writes ImportChargeItemsSourceChargeItems
      archive directory=D:\ClientData\SunLife\Archivebase table name=ChargeItemsrow terminator=Newline
    5. Process ExportDoctorsProcessFile
      writes ImportDoctorsSourceDoctors
      file path=D:\ClientData\SunLife\ExportDoctors.txtarchive directory=D:\ClientData\SunLife\Archivebase table name=Doctorsrow terminator=Newline
    6. Process ExportFacilitiesProcessFile
      writes ImportFacilitiesSourceFacilities
      archive directory=D:\ClientData\SunLife\Archivebase table name=Facilitiesrow terminator=Newline
    7. Process ExportFeeScheduleProcessFile
      writes ImportFeeScheduleSourceFeeSchedule
      archive directory=D:\ClientData\SunLife\Archivebase table name=FeeSchedulerow terminator=Newline
    8. Process ExportChargeMasterProcessFile
      archive directory=D:\ClientData\SunLife\Archiverow terminator=Newline
    9. Process ExportInsuranceProcessFile
      writes ImportInsuranceSourceInsurance
      archive directory=D:\ClientData\SunLife\Archivebase table name=Insurancerow terminator=Newline
    10. Process ExportPatientsProcessFile
      writes ImportPatientsSourcePatients
      archive directory=D:\ClientData\SunLife\Archivebase table name=Patientsrow terminator=Newline
    11. Process ExportVisitCodesProcessFile
      writes ImportVisitCodesSourceVisitCodes
      archive directory=D:\ClientData\SunLife\Archivebase table name=VisitCodesrow terminator=Newline
    12. Process Export VisitStatusProcessFile
      writes ImportVisitStatusSourceVisitStatus
      archive directory=D:\ClientData\SunLife\Archivebase table name=VisitStatusrow terminator=Newline
  2. ▶Load-Chargessub-job · 1↗Loads ChargeType and Chargemaster
    1. Load ChargeTypesqlload_charges__load_chargetype.sql
      ✨ Populates the charge type table with procedure codes and descriptions from the chargemaster to support accurate billing and claim reporting.
      writes ChargeType · reads ChargeTypeChargemasterSourceChargeItems
  3. ▶Load-InsuranceMapsub-job · 1↗
    1. Load InsuranceMapLoadEntities
      writes InsuranceMap · reads InsuranceMapSourceInsurance
      entity type=InsuranceMapcustom querykey prefix=IM
  4. ▶Load-Visit-Transform-Tablessub-job · 8↗Update [date]: removed the where clause limiting TransformPatientInsurance load to just null or future EndDates. This was deleting and preventing re-insertion of past EndDate records, which then would cause a loop where they always ended up in the patient insurance and visit delt
    1. Load PatientInsuranceDeltasqlload_visit_transform_tables__load_patientinsurancedelta.sql
      ✨ Flags patients with recent insurance or mapping changes to ensure their coverage information is updated in the pipeline.
      writes PatientInsuranceDelta · reads PatientInsuranceDelta
    2. Load VisitDeltasqlload_visit_transform_tables__load_visitdelta.sql
      ✨ Flags specific encounters for reprocessing based on recent source updates or changes to the patient's insurance profile.
      writes VisitDelta · reads PatientInsuranceDeltaSourceEncounterTransformVisitVisitDelta
      ↳ feeds from Load PatientInsuranceDelta, Load TransformVisit
    3. Load TransformPatientInsurancesqlload_visit_transform_tables__load_transformpatientinsurance.sql
      ✨ Updates transformed patient insurance records, including coverage order and mapping, for patients with identified changes.
      writes TransformPatientInsurance · reads InsuranceMapPatientInsuranceDeltaSourcePatientInsurance
      ↳ feeds from Load PatientInsuranceDelta
    4. Load TransformPatientssqlload_visit_transform_tables__load_transformpatients.sql
      ✨ Loads and updates patient demographic, contact, and guarantor information into the transformed patient table.
      writes TransformPatient · reads sourcepatients
    5. Load TransformVisitsqlload_visit_transform_tables__load_transformvisit.sql
      ✨ Builds and updates visit records by merging encounter details, patient identifiers, and appointment metadata into the transformation layer.
      writes TransformVisit · reads SourceEncountertransformpatientvisitdelta
      ↳ feeds from Load TransformPatients, Load VisitDelta
    6. Load TransformVisitInsurancesqlload_visit_transform_tables__load_transformvisitinsurance.sql
      ✨ Loads visit-specific insurance details by filtering patient coverage based on dental or non-dental visit types and coordination of benefits.
      writes transformvisitinsurance · reads DimensionMemberTransformPatientInsuranceTransformVisitVisitDelta
      ↳ feeds from Load TransformPatientInsurance, Load TransformVisit, Load VisitDelta
    7. Load TransformVisitChargesqlload_visit_transform_tables__load_transformvisitcharge.sql
      ✨ Generates visit charges by mapping diagnosis codes and patient demographics to specific service bundles and clinical item IDs.
      writes TransformVisitCharge · reads ChargeTypeImportSunlifeBundlesSourceEncounterTransformPatientTransformVisitVisitDelta
      ↳ feeds from Load TransformPatients, Load TransformVisit, Load VisitDelta
    8. Load TransformUpdatedVisitChargesqlload_visit_transform_tables__load_transformupdatedvisitcharge.sql
      ✨ Refines visit charges by deduplicating evaluation and management codes based on price rankings from the self-pay fee schedule.
      writes TransformUpdatedVisitCharge · reads ImportSunlifeBundlesSourceEncounterTransformSelfPayFeeScheduleTransformVisitChargeVisitDelta
      ↳ feeds from Load TransformVisitCharge, Load VisitDelta
  5. ▶Load-Relationssub-job · 1↗Loads relational tables like Provider
    1. Load Provider Relationsqlload_relations__load_provider_relation.sql
      ✨ Loads and updates provider details, including NPI and taxonomy codes, for active practitioners linked to visits.
      writes Provider · reads SourceDoctorsTransformVisit
  6. ▶Load-Entitiessub-job · 3↗
    1. Load FacilityLoadEntities
      writes Facility · reads SourceEncounterSourceFacilities
      entity type=Facilitycustom query
    2. Load PatientsLoadPatients
      Upsert patient records into the patient dimension.
      · reads PatientTransformPatient
      custom patient query
    3. Load BillTypeLoadEntitiesdisabled
      · reads ChargeType
      auto queryDISABLED
  7. ▶Load-Dimension-Memberssub-job · 16↗Loads Dimension Members
    1. Load Insurance Categorysqlload_dimension_members__load_insurance_category.sqldisabled
      ✨ Loads insurance categories into the dimension member table to support reporting by commercial and non-commercial insurance types.
      writes DimensionMember · reads InsuranceCategory
    2. Load Insurancesqlload_dimension_members__load_insurance.sql
      ✨ Loads insurance dimension members and categorizes them as medical or dental based on their display names.
      writes DimensionMember · reads Insurance
    3. Load EligibilityInsurancesqlload_dimension_members__load_eligibilityinsurance.sqldisabled
      ✨ Updates the eligibility insurance dimension by filtering insurance records for those containing valid electronic benefit API references.
      writes DimensionMember · reads Insurance
    4. Load Facilitysqlload_dimension_members__load_facility.sql
      ✨ Loads facility dimension members from source facility data to maintain a consistent list of healthcare locations.
      writes DimensionMember · reads FacilitySourceFacilities
    5. Load VisitStatussqlload_dimension_members__load_visitstatus.sql
      ✨ Loads visit status dimension members to track and report on the current state of patient encounters.
      writes DimensionMember · reads SourceVisitStatus
    6. Load Provider Dimensionsqlload_dimension_members__load_provider_dimension.sql
      ✨ Loads provider dimension members and maps them to their respective medical specialties for organizational reporting.
      writes DimensionMember · reads ProviderSourceDoctors
    7. Load ProviderType Dimensionsqlload_dimension_members__load_providertype_dimension.sql
      ✨ Builds provider type dimension members by extracting unique specialty categories from the existing provider dimension.
      writes DimensionMember · reads DimensionMember
      ↳ feeds from Load ChargeType, Load Facility, Load Insurance, Load Modifier, Load Procedure SubCategory, Load ProcedureCode, Load Provider Dimension, Load State DimensionMember, Load Time, Load VisitStatus, Load VisitType DimensionMember
    8. Load ChargeTypesqlload_dimension_members__load_chargetype.sql
      ✨ Loads charge type dimension members and assigns parent categories based on procedure code suffixes.
      writes DimensionMember · reads ChargeType
    9. Load Procedure SubCategorysqlload_dimension_members__load_procedure_subcategory.sql
      ✨ Builds the procedure subcategory dimension by extracting parent categories from existing charge type records.
      writes DimensionMember · reads DimensionMember
      ↳ feeds from Load ChargeType, Load Facility, Load Insurance, Load Modifier, Load ProcedureCode, Load Provider Dimension, Load ProviderType Dimension, Load State DimensionMember, Load Time, Load VisitStatus, Load VisitType DimensionMember
    10. Load ProcedureCodesqlload_dimension_members__load_procedurecode.sql
      ✨ Loads unique procedure code dimension members extracted from charge type records for billing and clinical tracking.
      writes DimensionMember · reads ChargeType
    11. Load Modifiersqlload_dimension_members__load_modifier.sql
      ✨ Updates the modifier dimension with codes and descriptions sourced from common procedure modifier reference data.
      writes DimensionMember · reads ProcedureModifier
    12. Load Timesqlload_dimension_members__load_time.sql
      ✨ Builds time dimension members for days, months, and years to support temporal analysis of encounter data.
      writes DimensionMember · reads SourceEncounter
    13. Load EligibilityServiceTypesqlload_dimension_members__load_eligibilityservicetype.sqldisabled
      ✨ Loads the EligibilityServiceType dimension with standardized medical service categories used for insurance benefit verification.
      writes DimensionMember
    14. Load PayerType Dimensionsqlload_dimension_members__load_payertype_dimension.sqldisabled
      ✨ Updates the PayerType dimension to include specific members for self-pay classifications within the reporting system.
      writes DimensionMember
    15. Load VisitType DimensionMembersqlload_dimension_members__load_visittype_dimensionmember.sql
      ✨ Builds or updates visit type dimension members to categorize encounters within the reporting system.
      writes DimensionMember · reads SourceVisitCodes
    16. Load State DimensionMembersqlload_dimension_members__load_state_dimensionmember.sql
      ✨ Updates state dimensions using facility location data to allow for geographic segmentation of healthcare services and revenue.
      writes DimensionMember · reads Facility
  8. ▶Load-Visitssub-job · 9↗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 inv
    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.
  9. Data-Integrity-Reportsub-job
  10. ▶Refresh-Reports-Dailysub-job · 1↗
    1. Refresh UnmatchedEncountersReportsqlrefresh_reports_daily__refresh_unmatchedencountersreport.sql
      ✨ Builds a report identifying upcoming patient encounters where diagnosis codes do not match any predefined service bundles.
      writes UnmatchedEncountersReport · reads DimensionMemberImportSunlifeBundlesPatientSourceEncounterVisit

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
LoadVisitBatch
ClarityFlow front-of-house step
auto_send_letters
Send GFE / patient letters