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

HL7 real-time feed · ClarityFlow pipeline

EugeneGI · HL7-Processing-Nightly

Overall nightly processing job for HL7 pipeline. This is to auto generate letters as well as apply bundles and recheck any eligibility that might need rechecking

pipeline EugeneGI/HL7-Processing-Nightly · db EugeneGI_ProdMirror · updated 2025-11-21
classification: flow · 10 Flow markers

trigger

How it's scheduled

Daily batch — once at 19:34

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

Scheduled taskCadenceServerStateCoordination
\MDC\HL7 - Processing - EugeneGI - Nightlydaily at 19:34prod-db-1enabledstandalone

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
HL7 real-time feed
→
Eligibility & benefits
EligibilityBenefits
→
Estimate
BundlesEstimate
→
Letters
Letters
→
Work queue & sync
Work queueDashboardsSync

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

Coverage statusBundles

Payers in SQLMedicare

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

HL7-Processing-Nightly

11 tasks · EugeneGI
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 VisitDeltasqlhl7_processing_nightly__load_visitdelta.sql
    ✨ Identifies visits that have been updated since the last run and are scheduled for the future to target processing.
    writes VisitDelta · reads ProcessLogVisitVisitDelta
  2. BackupQuoteBeforesqlhl7_processing_nightly__backupquotebefore.sqldisabled
    ✨ Creates a dated backup of quote records for recently updated visits before new processing begins.
    · reads sp_executesql
  3. ▶Load-Visits-Load-Batchsub-job · 4↗
    1. Load TempBatchVisitsqlload_visits_load_batch__load_tempbatchvisit.sqldisabled
      ✨ Initializes the temporary batch table by identifying visits and insurance combinations that require eligibility verification based on business rules.
      writes TempBatchVisit · reads DimensionMemberEligibilityTxnTransformVisitTransformVisitInsuranceTransformVisitInsuranceCoverageStatusVisit
    2. Augment Visit Deltasqlload_visits_load_batch__augment_visit_delta.sqldisabled
      ✨ Adds visit records requiring new insurance eligibility lookups to the change tracking table for processing.
      writes VisitDelta · reads TempBatchVisitVisitDelta
    3. LoadVisitBatchLoadVisitBatch
      Load scheduled visits and associated charges into the processing pipeline using custom SQL queries for pricing estimation and quote generation.
      writes QuoteBatchVisitBatch · reads InsuranceMapPatientTransformVisitTransformVisitInsuranceVisitVisitDelta
      custom visit queryauto charge queryauto service type querycustom coverage queryadd zero charge
    4. ▶Flow-MM-HL7-Processing-CustomPropertysub-job · 8↗
      1. Load Primary Coverage StatusUpdateCustomPropertyFromQuery
        · reads TransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=PrimaryCoverageStatuscustom querybatch mode
      2. Load Secondary Coverage StatusUpdateCustomPropertyFromQuery
        · reads TransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=SecondaryCoverageStatuscustom querybatch mode
      3. Load Tertiary Coverage StatusUpdateCustomPropertyFromQuery
        · reads TransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=TertiaryCoverageStatuscustom querybatch mode
      4. LoadCustomNotesCustomPropertyLoadCustomNotesCustomProperty
        · reads BundleNotesMappingHL7VisitVisitBatch
        entity=Visitcustom property key=CustomNotescustom querybatch mode
      5. LoadPriorAuthCustomPropertyLoadPriorAuthCustomProperty
        · reads CustomPropertyMapTransformVisitInsuranceVisitbatchsourcepriorauthdetails
        entity=Visitcustom property key=PriorAuthcustom querybatch mode
      6. LoadMedicaidRolloverCustomPropertyLoadMedicaidRolloverCustomProperty
        · reads CustomPropertyMapTransformVisitInsuranceVisitBatch
        entity=Visitcustom property key=MedicaidRollovercustom querybatch mode
      7. Load Has Active Medicaid/Medicare InsuranceUpdateCustomPropertyFromQuery
        · reads DimensionMemberTransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=HasActiveMedicaid/MedicareInsurancecustom querybatch mode
      8. Update HasMisMatchedMemberID Custom Property after recheckUpdateCustomPropertyFromQuery
        · reads BatchVisitEligibilityEligibilityTxnVisitBatch
        entity=Visitcustom property key=HasMismatchedMemberIDcustom querybatch mode
  4. LoadPatients (Update ECC)LoadPatientsdisabled
    Upsert patient records into the patient dimension.
    · reads PatientVisitBatch
    auto patient queryDISABLED
  5. ▶Load-Visits-Bundlessub-job · 2↗
    1. AutoApplyBundlesToVisitBatchAutoApplyBundlesToVisitBatch
      Applies matching charge bundles to batch visits based on facility and insurance to ensure consistent pricing across service types.
    2. Augment TempBatchVisitsqlload_visits_bundles__augment_tempbatchvisit.sqldisabled
      ✨ Populates the temporary batch table with visit insurance details and flags records requiring new eligibility lookups based on current status.
      writes TempBatchVisit · reads DimensionMemberEligibilityTxnTempBatchVisitTransformVisitTransformVisitInsuranceTransformVisitInsuranceCoverageStatusVisitVisitBatch
  6. ▶Load-Visits-Benefits-To-AutoSendsub-job · 9↗
    1. CheckBenefitsflow
      Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
      · reads TempBatchVisitVisitBatch
      custom query
    2. Re-Check Benefitsflowdisabled
      Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
      · reads EligibilityStatusMapEligibilityTxnTempBatchVisitVisitBatch
      custom queryDISABLED
    3. AutoApplyBenefitsAutoApplyBenefits
      Populates insurance benefits like copays and deductibles on batch visits using data from eligibility verification transactions.
    4. CalculateVisitsflow
      Compute the good-faith estimate (CalculateVisits).
      · reads DimensionMemberVisitBatch
      custom querycalculate chargesbatch mode
    5. ExportBatchVisitEligibility before recheckflow
      Push the batch to the app / export results.
      · reads EligibilityTxnvisitbatch
      custom querytable name=BatchVisitEligibility
    6. Update HasMisMatchedMemberID Custom Property after recheckUpdateCustomPropertyFromQuery
      · reads BatchVisitEligibilityEligibilityTxnVisitBatch
      entity=Visitcustom property key=HasMismatchedMemberIDcustom querybatch mode
    7. LogVisitChangeEventsBatchLogVisitChangeEventsBatch
      Compare batch visits against production records to detect and log specific property changes for auditing and compliance tracking.
    8. Load TransformVisitInsuranceCoverageStatussqlload_visits_benefits_to_autosend__load_transformvisitinsurancecoveragestatus.sql
      ✨ Updates insurance coverage statuses by analyzing the latest eligibility transactions to determine if coverage is active, inactive, or errored.
      writes TempEligibilityStatsTransformVisitInsuranceCoverageStatus · reads TempEligibilityStatsTransformVisitInsuranceVisitBatch
    9. ▶Load-CustomProperty-CoverageStatussub-job · 4↗
      1. Load Primary Coverage StatusUpdateCustomPropertyFromQuery
        · reads CustomPropertyMapTransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=PrimaryCoverageStatuscustom querybatch mode
      2. Load Secondary Coverage StatusUpdateCustomPropertyFromQuery
        · reads CustomPropertyMapTransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=SecondaryCoverageStatuscustom querybatch mode
      3. Load Tertiary Coverage StatusUpdateCustomPropertyFromQuery
        · reads CustomPropertyMapTransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=TertiaryCoverageStatuscustom querybatch mode
      4. Load Has Active Medicaid/Medicare InsuranceUpdateCustomPropertyFromQuery
        · reads DimensionMemberTransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=HasActiveMedicaid/MedicareInsurancecustom querybatch mode
  7. BackupBatchsqlhl7_processing_nightly__backupbatch.sqldisabled
    ✨ Creates daily backup copies of visit and quote batch tables to preserve historical data before processing.
    · reads sp_executesql
  8. AutoSendLettersAutoSendLetters
    Generates and sends patient cost estimate letters for visits in the batch table based on configured qualification and communication rules.
  9. ▶Load-Visits-Sync-To-Endsub-job · 4↗
    1. Sync Visits and QuotesSyncBatch
      Synchronizes processed data from batch staging tables to production tables, updating visits, quotes, and custom property mappings.
      writes CustomPropertyMapQuoteVisitVisitChangeEvent · reads CustomPropertyMapBatchQuoteBatchVisitBatchVisitChangeEventBatch
      sync quotesync visitsync custom property mapsync visit change event
    2. ▶Load-Visit-Factsub-job · 1↗
      1. Load VisitFactsqlload_visit_fact__load_visitfact.sql
        ✨ Refreshes the visit fact table by recalculating statuses, payment flags, and letter activity for visits with recent updates or transactions.
        writes VisitFact · reads EligibilityStatusMapEligibilityTxnPatientLetterPaymentTxnVisit
    3. ▶Update-Dashboard-Tablessub-job · 1↗
      1. UpdateDashboardTablesUpdateDashboardTables
        Regenerates optimized aggregate tables for all dashboard reports and updates their definitions to reflect the latest source data.
    4. LoadVisitBatchAutoSendsqlload_visits_sync_to_end__loadvisitbatchautosend.sql
      ✨ Populates the auto-send queue with visit data to facilitate automated end-of-day processing and communication.
      writes VisitBatchAutoSend · reads VisitBatch
  10. WorkqueueAssignmentWorkqueueAssignment
    Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.
  11. BackupQuoteAftersqlhl7_processing_nightly__backupquoteafter.sqldisabled
    ✨ Backs up quote records linked to modified visits to maintain a snapshot of data after processing updates.
    · reads sp_executesql

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
CheckBenefits
Real-time eligibility (270/271) benefit lookup
ExportBatchVisitEligibility
Export eligibility results
ExportBatchVisitEligibility before recheck
ClarityFlow front-of-house step
Load TransformVisitInsuranceCoverageStatus
ClarityFlow front-of-house step
LoadVisitBatch
ClarityFlow front-of-house step
LoadVisitBatchAutoSend
ClarityFlow front-of-house step
auto_apply_bundles
Apply procedure bundles for the estimate
auto_benefits
Auto-apply parsed benefits to visits
auto_send_letters
Send GFE / patient letters