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

HL7 real-time feed · ClarityFlow pipeline

SWOrofacial · Flow-HL7-Processing

Overall Processing job for HL7 pipeline

pipeline SWOrofacial/Flow-HL7-Processing · db SWOrofacial_ProdMirror · updated 2026-07-01
classification: flow · 10 Flow markers

trigger

How it's scheduled

Real-time loop — every 10 min (~144×/day)

New appointments land continuously on the HL7 real-time feed. Front-of-house work is time-boxed — the estimate and letter have to reach the patient before the visit — so the pipeline polls every 10 minutes to check eligibility, price the good-faith estimate, and send the patient letter within minutes of an appointment being booked, rather than waiting for an overnight batch.

Scheduled taskCadenceServerStateCoordination
\MDC\HL7 - Processing - SWOroFacialevery 10 min (from 00:01)prod-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
HL7 real-time feed
→
Eligibility & benefits
EligibilityBenefitsDiscovery
→
Estimate
BundlesEstimate
→
Letters
LettersHL7
→
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 status

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

Flow-HL7-Processing

13 tasks · SWOrofacial
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. DownloadDownload
    Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
    server=SWOrofacial_ProdMirror-advancedmd-sftpremote directory=/local directory={HL7Download}
  2. Clean and Move HL7ExecuteProcess
    command="D:\ClientData\SWOroFacial\py.bat"
  3. LoadHL7FilesLoadHL7Files
    Parses and imports patient demographics, visits, and charges from HL7 message files located in a specified directory.
    message folder path={HL7Directory}message processed folder path={HL7Archive}add timestamp to file name
  4. HL7ProcessLog + HL7Deltasqlflow_hl7_processing__hl7processlog_hl7delta.sql
    ✨ Identifies new scheduling messages from a specific source and stages the most recent updates for processing.
    writes HL7ADTBatchHL7DeltaHL7ProcessLogHL7ProcessLogArchiveKeysHL7SIUBatch · reads HL7ADTBatchHL7ArchiveHL7SIUBatchTransformPatientTransformVisit
  5. ▶Flow-HL7-Processing-Load-Entitiessub-job · 4↗Load Facility, Provider, InsuranceMap
    1. Load FacilityLoadEntities
      writes Facility · reads FacilityHL7Visit
      entity type=Facilitycustom querykey prefix=F
    2. Load Providersqlflow_hl7_processing_load_entities__load_provider.sqldisabled
      ✨ Loads provider information from HL7 order messages into the master provider table using the most recent record available.
      writes Provider · reads HL7Order
    3. Load ProviderMergeSql
      writes Provider · reads HL7VisitProvider
      merge data source type=ThisDatabasetable name=Providermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    4. Load InsuranceMapLoadEntities
      writes InsuranceMap · reads HL7PatientHL7VisitInsuranceMap
      entity type=InsuranceMapcustom querykey prefix=IM
  6. ▶Flow-HL7-Transformsub-job · 9↗HL7 Transformation Layer
    1. TruncateVisitDeltasqlflow_hl7_transform__truncatevisitdelta.sql
      ✨ Clears the visit delta table to prepare for a fresh set of changed records in the current pipeline run.
      writes VisitDelta
    2. ImportHL7ADTCombinedsqlflow_hl7_transform__importhl7adtcombined.sql
      ✨ Builds a combined view of patient demographics and insurance by merging the latest visit and patient records from HL7 messages.
      writes ImportHL7ADTCombined · reads HL7DeltaHL7PatientHL7Visit
    3. Merge TransformPatientMergeSql
      writes TransformPatient · reads ImportHL7ADTCombined
      ↳ feeds from ImportHL7ADTCombined
      merge data source type=ThisDatabasetable name=TransformPatientmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    4. ImportTransformPatientInsurancesqlflow_hl7_transform__importtransformpatientinsurance.sql
      ✨ Loads patient insurance details by unpivoting multiple coverage layers from combined HL7 data and mapping them to standardized insurance entities.
      writes ImportTransformPatientInsurance · reads ImportHL7ADTCombinedInsuranceMap
      ↳ feeds from ImportHL7ADTCombined
    5. TransformPatientInsurance+VisitDeltasqlflow_hl7_transform__transformpatientinsurance_visitdelta.sql
      ✨ Updates patient insurance records and flags future visits in the delta table when insurance changes are detected.
      writes TransformPatientInsuranceVisitDelta · reads InsuranceChangesTransformVisitimporttransformpatientinsurance
      ↳ feeds from ImportTransformPatientInsurance, MergeTransformVisit+VisitDelta
    6. ImportTransformVisitsqlflow_hl7_transform__importtransformvisit.sql
      ✨ Loads visit and appointment data from HL7 messages, determining the current status based on message type and schedule date.
      writes ImportTransformVisit · reads FacilityHL7ArchiveHL7DeltaHL7Visit
    7. MergeTransformVisit+VisitDeltasqlflow_hl7_transform__mergetransformvisit_visitdelta.sql
      ✨ Updates the main visit transformation table and flags new or modified visits in the delta table for downstream processing.
      writes TransformVisitVisitDelta · reads ImportTransformVisitVisitDelta
      ↳ feeds from ImportTransformVisit, TransformPatientInsurance+VisitDelta, TruncateVisitDelta
    8. ImportTransformVisitInsurancesqlflow_hl7_transform__importtransformvisitinsurance.sql
      ✨ Loads visit-specific insurance records by joining the latest patient insurance data with visits identified in the delta table.
      writes ImportTransformVisitInsurance · reads TransformPatientInsuranceTransformVisitVisitDelta
      ↳ feeds from MergeTransformVisit+VisitDelta, TransformPatientInsurance+VisitDelta, TruncateVisitDelta
    9. MergeTransformVisitInsurancesqlflow_hl7_transform__mergetransformvisitinsurance.sql
      ✨ Updates the visit insurance transformation table by replacing existing records with the latest imported insurance data for each visit.
      writes TransformVisitInsurance · reads ImportTransformVisitInsurance
      ↳ feeds from ImportTransformVisitInsurance
  7. LoadPatientsLoadPatients
    Upsert patient records into the patient dimension.
    · reads PatientTransformPatient
    custom patient query
  8. ▶Flow-HL7-Processing-LoadDimensionMemberssub-job · 15↗
    1. Load Insurance Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_insurance_dimension.sqldisabled
      ✨ Populates the insurance dimension with unique carrier keys to enable filtering and analysis by payer.
      writes DimensionMember · reads Insurance
    2. Load EligibilityInsurance Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_eligibilityinsurance_dimension.sqldisabled
      ✨ Loads insurance members that support electronic eligibility and benefits verification via API integration.
      writes DimensionMember · reads DimensionMemberInsurance
    3. Load QuoteInsurance Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_quoteinsurance_dimension.sqldisabled
      ✨ Builds a dimension of insurance providers associated with active contracts to facilitate accurate patient cost estimates.
      writes DimensionMember · reads ContractMapInsurance
    4. LoadInsuranceDimensionMembersLoadInsuranceDimensionMembers
      Synchronize insurance-specific dimension members from the insurance table to update contract and eligibility filtering options across the application.
      writes DimensionMember
      load insuranceload quote insuranceload eligibility insurance
    5. Load Time Dimensionssqlflow_hl7_processing_loaddimensionmembers__load_time_dimensions.sql
      ✨ Populates time dimension hierarchies for days, months, and years using appointment timestamps to support temporal data analysis.
      writes DimensionMember · reads TransformVisit
    6. Load VisitStatus Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_visitstatus_dimension.sql
      ✨ Builds the visit status dimension by extracting unique appointment statuses from transformed visit data.
      writes DimensionMember · reads TransformVisit
    7. Load Provider Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_provider_dimension.sql
      ✨ Synchronizes provider details into the dimension member table to enable provider-based filtering and grouping in reports.
      writes DimensionMember · reads Provider
    8. Load Facility Dimensionssqlflow_hl7_processing_loaddimensionmembers__load_facility_dimensions.sql
      ✨ Updates the facility dimension table by merging new or changed facility and facility group hierarchy information.
      writes DimensionMember · reads DimensionMemberFacility
      ↳ feeds from FacilityType, Load Provider Dimension, Load State Dimensions, Load Time Dimensions, Load VisitStatus Dimension, Load VisitType Dimensions, LoadInsuranceDimensionMembers
    9. Load VisitType Dimensionssqlflow_hl7_processing_loaddimensionmembers__load_visittype_dimensions.sql
      ✨ Updates the visit type dimension table by merging unique appointment types and descriptions extracted from HL7 message data.
      writes DimensionMember · reads HL7Visit
    10. Load Diagnosis Code Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_diagnosis_code_dimension.sqldisabled
      ✨ Loads and updates the DiagnosisCode dimension by extracting unique primary, secondary, and tertiary diagnosis codes from charge data.
      writes DimensionMember · reads TransformCharge
    11. Load RadioIsotopes Code Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_radioisotopes_code_dimension.sqldisabled
      ✨ Flags and updates the RadioIsotopes dimension to indicate the presence of radioisotope codes within the source risk data.
      writes DimensionMember · reads SourceCareRiskRadioIsotopeCodes
    12. Load Place of Service Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_place_of_service_dimension.sqldisabled
      ✨ Builds and updates the PlaceOfService dimension using unique location codes extracted from charge transaction records.
      writes DimensionMember · reads TransformCharge
    13. Load SourceInsurance Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_sourceinsurance_dimension.sqldisabled
      ✨ Loads and updates the SourceInsurance dimension with mapped insurance provider details for specific data sources and active accounts.
      writes DimensionMember · reads AccountInsuranceInsuranceMap
    14. Load State Dimensionssqlflow_hl7_processing_loaddimensionmembers__load_state_dimensions.sql
      ✨ Updates state dimensions using facility location data to allow for geographic segmentation of healthcare services and revenue.
      writes DimensionMember · reads Facility
    15. FacilityTypesqlflow_hl7_processing_loaddimensionmembers__facilitytype.sql
      ✨ Loads facility type dimension members from facility records to enable standardized categorization across the revenue cycle pipeline.
      writes DimensionMember · reads Facility
  9. ▶Flow-HL7-Processing-LoadVisitssub-job · 21↗
    1. Augment Visit Deltasqlflow_hl7_processing_loadvisits__augment_visit_delta.sqldisabled
      ✨ Updates the visit delta table by adding records flagged as requiring new insurance eligibility lookups from the batch process.
      writes VisitDelta · reads TempBatchVisitVisitDelta
    2. ReprocessChangedConfigurationsReprocessChangedConfigurations
      Identify and queue visits or accounts affected by configuration changes to ensure historical data reflects updated pricing or insurance rules.
      reprocess visits
    3. Augment VisitDelta - ReprocessChangedConfigssqlflow_hl7_processing_loadvisits__augment_visitdelta_reprocesschangedconfigs.sql
      ✨ Updates insurance mappings for queued visits and adds modified records to the change tracking table for reprocessing.
      writes VisitDeltaVisitKeyLookup · reads TransformVisitVisitDeltaVisitKeyLookup
    4. 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
      ↳ feeds from Augment VisitDelta - ReprocessChangedConfigs, Sync Visits and Quotes
      custom visit queryauto charge querycustom service type querycustom coverage queryadd zero charge
    5. Flow-HL7-Processing-LoadCustomPropertiessub-jobdisabled
    6. AutoApplyBundlesToVisitBatchAutoApplyBundlesToVisitBatch
      Applies matching charge bundles to batch visits based on facility and insurance to ensure consistent pricing across service types.
    7. CheckBenefitsflow
      Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
      auto query
    8. 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
    9. AutoApplyBenefitsAutoApplyBenefits
      Populates insurance benefits like copays and deductibles on batch visits using data from eligibility verification transactions.
    10. CalculateVisitsflow
      Compute the good-faith estimate (CalculateVisits).
      · reads DimensionMemberVisitBatch
      ↳ feeds from LoadVisitBatch
      custom querycalculate chargesbatch mode
    11. WorkqueueAssignmentWorkqueueAssignmentdisabled
      Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.
      DISABLED
    12. AutoSendLettersAutoSendLettersdisabled
      Generates and sends patient cost estimate letters for visits in the batch table based on configured qualification and communication rules.
      DISABLED
    13. LogVisitChangeEventsBatchLogVisitChangeEventsBatch
      Compare batch visits against production records to detect and log specific property changes for auditing and compliance tracking.
    14. Load TransformVisitInsuranceCoverageStatussqlflow_hl7_processing_loadvisits__load_transformvisitinsurancecoveragestatus.sql
      ✨ Updates visit insurance coverage status by calculating the latest eligibility transaction results and mapping them to standardized status categories.
      writes TempEligibilityStatsTransformVisitInsuranceCoverageStatus · reads TempEligibilityStatsTransformVisitInsuranceVisitBatch
      ↳ feeds from LoadVisitBatch
    15. ▶Flow-HL7-Processing-CustomPropertyCoverageStatussub-job · 3↗
      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
    16. 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
      ↳ feeds from LoadVisitBatch
      sync quotesync visitsync custom property mapsync visit change event
    17. ExportBatchVisitEligibilityflowdisabled
      Push the batch to the app / export results.
      · reads EligibilityTxnVisitBatch
      custom querytable name=BatchEligibility_Resultsarchive table name=BatchEligibility_ResultsArchiveDISABLED
    18. SearchForInsuranceflowdisabled
      Find coverage for visits missing insurance.
      · reads EligibilityTxnBatch
      custom querytable name=InsuranceSearch_Resultbatch modeDISABLED
    19. Generate-Reportsub-jobdisabled
    20. Load-Visit-Factsub-jobdisabled
    21. Update-Dashboard-Tablessub-jobdisabled
  10. Update HL7ProcessLogsqlflow_hl7_processing__update_hl7processlog.sql
    ✨ Updates the HL7 process log status to completed and records the last processed archive key for tracking.
    writes HL7ProcessLogLastArchiveKey · reads HL7DeltaHL7ProcessLogHL7ProcessLogLastArchiveKey
    ↳ feeds from HL7ProcessLog + HL7Delta
  11. WorkqueueAssignmentWorkqueueAssignment
    Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.
  12. ▶Flow-HL7-Processing-LoadVisit-Factsub-job · 1↗
    1. VisitFact Loadsqlflow_hl7_processing_loadvisit_fact__visitfact_load.sql
      ✨ Updates the visit fact table by refreshing records that have recent activity in payments, eligibility checks, or patient communications.
      writes VisitFact · reads EligibilityStatusMapEligibilityTxnPatientLetterPaymentTxnVisit
  13. SendHL7Lettersflowdisabled
    Exports pending patient letters as HL7 messages with PDF attachments to a local directory for transmission to external systems.
    output file path={HL7SendLettersOutput}publish directory={HL7SendLettersOutput}sending application=MDClaritysending facility=MDClarityreceiving application={HL7SendingApplicationName}

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
Load TransformVisitInsuranceCoverageStatus
ClarityFlow front-of-house step
LoadVisitBatch
ClarityFlow front-of-house step
SearchForInsurance
Insurance discovery for visits missing coverage
SendHL7Letters
Emit letters back over the HL7 interface
auto_apply_bundles
Apply procedure bundles for the estimate
auto_benefits
Auto-apply parsed benefits to visits
auto_send_letters
Send GFE / patient letters