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

sub-job

SunLife · Load-Visit-Transform-Tables

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 deltas. Hoping this helps with processing timing as well as data integrity. -CW

called by Daily-Visits-Processing

sub-job SunLife/Load-Visit-Transform-Tables · db SunLife_ProdMirror · updated 2025-09-17

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

8 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 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