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

HL7 real-time feed · ClarityFlow pipeline

RIA · Flow-HL7-Processing

Overall Processing job for HL7 pipeline

pipeline RIA/Flow-HL7-Processing · db RIA_ProdMirror · updated 2026-07-13
classification: flow · 11 Flow markers

trigger

How it's scheduled

Real-time loop — every 1 min (~1,440×/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 1 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. It stands down while HL7 - Processing - RIA - Nightly runs (the heavier nightly pass).

Scheduled taskCadenceServerStateCoordination
\MDC\HL7 - Processing - RIAevery 1 min (from 00:01)prod-db-3enabledyields to HL7 - Processing - RIA - Nightly

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
LettersFutureHL7
→
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 statusBundlesCPT / 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

Flow-HL7-Processing

12 tasks · RIA
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=RIA_ProdMirror-abbadox-sftpremote directory=/Outbound/local directory={HL7Directory}
  2. 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
  3. HL7ProcessLog + HL7Deltasqlflow_hl7_processing__hl7processlog_hl7delta.sql
    ✨ Identifies new order messages from the archive and stages the latest updates for processing in the delta table.
  4. ▶Flow-HL7-Transform-Concisesub-job · 14↗HL7 Transformation Layer - more efficient run for 1 minute interval
    1. TruncateVisitDeltasqlflow_hl7_transform_concise__truncatevisitdelta.sql
      ✨ Clears the visit delta table to prepare for a fresh set of changed records in the current pipeline run.
      writes VisitDelta
    2. TransformVisitIDMapsqlflow_hl7_transform_concise__transformvisitidmap.sql
      ✨ Builds unique source identifiers for new patient visits based on patient IDs and scheduled dates from incoming message data.
      writes TransformVisitIDMap · reads HL7DeltaTransformVisitIDMap
    3. OrderSourceIDDeltasqlflow_hl7_transform_concise__ordersourceiddelta.sql
      ✨ Updates order-level changes and identifies visits where all associated orders have been cancelled to refresh the visit status.
      writes OrderSourceIDDeltaTransformOrderSourceIDMap · reads HL7DeltaTransformOrderSourceIDMapTransformVisitIDMap
      ↳ feeds from TransformVisitIDMap
    4. ImportHL7ADTCombinedsqlflow_hl7_transform_concise__importhl7adtcombined.sql
      ✨ Builds a consolidated table of the most recent patient demographic and insurance information from admission, discharge, and transfer messages.
      writes ImportHL7ADTCombined · reads HL7DeltaHL7Patient
    5. TransformPatientsqlflow_hl7_transform_concise__transformpatient.sql
      ✨ Updates or inserts patient demographic records by merging the latest data from combined messages while ensuring only newer information is saved.
      writes TransformPatient · reads HL7DeltaHL7OrderImportHL7ADTCombinedPatientTransformPatient
      ↳ feeds from ImportHL7ADTCombined
    6. ImportTransformPatientInsurancesqlflow_hl7_transform_concise__importtransformpatientinsurance.sql
      ✨ Builds a staging table of patient insurance updates by deduplicating coverages and mapping source insurance identifiers to standard system values.
      writes ImportTransformPatientInsurance · reads ImportHL7ADTCombinedInsuranceMap
      ↳ feeds from ImportHL7ADTCombined
    7. Merge TransformPatientInsurance+VisitDeltasqlflow_hl7_transform_concise__merge_transformpatientinsurance_visitdelta.sql
      ✨ Updates patient insurance records and flags future visits for reprocessing when insurance details change.
      writes TransformPatientInsuranceVisitDelta · reads InsuranceChangesTransformVisitimporttransformpatientinsurance
      ↳ feeds from ImportTransformPatientInsurance, MergeTransformVisit+VisitDelta
    8. ImportTransformVisitsqlflow_hl7_transform_concise__importtransformvisit.sql
      ✨ Loads visit data from HL7 orders into a staging table while mapping clinical order statuses to standardized appointment statuses.
      writes ImportTransformVisit · reads HL7OrderOrderSourceIDDeltaTransformOrderSourceIDMap
      ↳ feeds from OrderSourceIDDelta
    9. MergeTransformVisit+VisitDeltasqlflow_hl7_transform_concise__mergetransformvisit_visitdelta.sql
      ✨ Synchronizes visit details with the main transform table and tracks modified future appointments for downstream updates.
      writes TransformVisitVisitDelta · reads ChangedVisitsVisitDelta
      ↳ feeds from Merge TransformPatientInsurance+VisitDelta, MergeTransformVisitCharge, TruncateVisitDelta
    10. ImportTransformVisitInsurancesqlflow_hl7_transform_concise__importtransformvisitinsurance.sql
      ✨ Stages the most recent insurance information for specific visits by linking patient insurance records to flagged visit changes.
      writes ImportTransformVisitInsurance · reads HL7OrderTransformOrderSourceIDMapTransformPatientInsuranceVisitDelta
      ↳ feeds from Merge TransformPatientInsurance+VisitDelta, MergeTransformVisit+VisitDelta, MergeTransformVisitCharge, OrderSourceIDDelta, TruncateVisitDelta
    11. MergeTransformVisitInsurancesqlflow_hl7_transform_concise__mergetransformvisitinsurance.sql
      ✨ Updates the main insurance transformation table by replacing existing records with newly imported and mapped insurance information.
      writes TransformVisitInsurance · reads ImportTransformVisitInsurance
      ↳ feeds from ImportTransformVisitInsurance
    12. Recalculate Changed Bundlessqlflow_hl7_transform_concise__recalculate_changed_bundles.sqldisabled
      ✨ Identifies and recalculates expected charges from service bundles for future visits to ensure accurate cost estimates.
      writes OrderSourceIDDeltaVisitDelta · reads BundleChargeTypeHL7OrderImportTransformVisitChargeTransformInsuranceMapServiceTypeTransformOrderSourceIDMapTransformVisitTransformVisitInsurance
    13. ImportTransformVisitChargesqlflow_hl7_transform_concise__importtransformvisitcharge.sql
      ✨ Loads visit charges into a staging table by matching scheduled services against insurance-specific or default billing bundles.
      writes ImportTransformVisitCharge · reads ChargeTypeHL7OrderImportTransformVisitChargeOrderSourceIDDeltaTransformBundleMapTransformInsuranceMapServiceTypeTransformOrderSourceIDMapTransformVisitInsurance
      ↳ feeds from MergeTransformVisitInsurance, OrderSourceIDDelta
    14. MergeTransformVisitChargesqlflow_hl7_transform_concise__mergetransformvisitcharge.sql
      ✨ Updates the master visit charge table and flags visits for recalculation when their associated billing codes or units change.
      writes TransformVisitChargeVisitDelta · reads ChangedChargesImportTransformVisitChargeVisitDelta
      ↳ feeds from ImportTransformVisitCharge, Merge TransformPatientInsurance+VisitDelta, MergeTransformVisit+VisitDelta, TruncateVisitDelta
  5. LoadPatientsLoadPatients
    Upsert patient records into the patient dimension.
    · reads PatientTransformPatient
    custom patient query
  6. ▶Flow-HL7-Processing-Load-Entitiessub-job · 4↗Load Facility, Provider, InsuranceMap
    1. Load FacilityLoadEntities
      writes Facility · reads FacilityHL7Order
      entity type=Facilitycustom query
    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 HL7Order
      merge data source type=ThisDatabasetable name=Providermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    4. Load InsuranceMapLoadEntities
      writes InsuranceMap · reads HL7PatientInsuranceMap
      entity type=InsuranceMapcustom querykey prefix=IM
  7. ▶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
      ✨ Builds and updates time-based dimension members, including days, months, and years, for reporting and analytical hierarchies.
      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
      ✨ Loads facility and facility group hierarchies into the dimension table to organize location-based data for analysis.
      writes DimensionMember · reads DimensionMemberFacility
      ↳ feeds from 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
      ✨ Synchronizes visit type definitions and display names in the dimension table using data from HL7 messages and reference crosswalks.
      writes DimensionMember · reads HL7OrderRIACPTCrosswalk
    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 Modifier Dimensionsqlflow_hl7_processing_loaddimensionmembers__load_modifier_dimension.sqldisabled
      ✨ Loads and updates the Modifier dimension by collecting unique modifier codes from multiple charge record fields.
      writes DimensionMember · reads TransformCharge
    13. 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
    14. 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
    15. 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
  8. ▶Flow-HL7-Processing-LoadVisitssub-job · 24↗
    1. Load TempBatchVisitsqlflow_hl7_processing_loadvisits__load_tempbatchvisit.sqldisabled
      ✨ Identifies visits requiring insurance eligibility checks or re-checks based on service types and proximity to the scheduled appointment date.
      writes TempBatchVisitVisitEligServiceTypesVisitServiceTypes · reads DimensionMemberTempBatchVisitTransformVisitTransformVisitInsTransformVisitInsuranceTransformVisitInsuranceCoverageStatusVisitEligServiceTypesVisitServiceTypes
    2. Augment Visit Deltasqlflow_hl7_processing_loadvisits__augment_visit_delta.sqldisabled
      ✨ Updates the change tracking table with future visits when insurance mappings change to ensure updated coverage information is processed.
    3. ReprocessChangedConfigurationsReprocessChangedConfigurationsdisabled
      Identify and queue visits or accounts affected by configuration changes to ensure historical data reflects updated pricing or insurance rules.
      reprocess visitsDISABLED
    4. ReprocessChangedConfigs-AugmentVisitDeltasqlflow_hl7_processing_loadvisits__reprocesschangedconfigs_augmentvisitdelta.sqldisabled
      ✨ Flags visits requiring reprocessing from the queue and adds them to the change tracking table for updated data loading.
      writes VisitDeltaVisitKeyLookup · reads VisitDeltaVisitKeyLookup
    5. LoadVisitBatchLoadVisitBatch
      Load scheduled visits and associated charges into the processing pipeline using custom SQL queries for pricing estimation and quote generation.
      writes QuoteBatchVisitBatch · reads FacilityInsuranceMapPatientTransformVisitTransformVisitChargeTransformVisitInsuranceVisitVisitDelta
      ↳ feeds from Sync Visits and Quotes, Update Patient Email for Commercial Insurance
      custom visit querycustom charge queryauto service type querycustom coverage queryadd zero charge
    6. TEMPORARY-REMOVE WHEN PR 5672 RELEASEDsqlflow_hl7_processing_loadvisits__temporary_remove_when_pr_5672_released.sql
      ✨ Loads missing benefit application statuses for quotes into the batch table using existing records from the live status map.
      writes AutoApplyBenefitsStatusMapBatch · reads AutoApplyBenefitsStatusMapAutoApplyBenefitsStatusMapBatchQuoteBatch
      ↳ feeds from LoadVisitBatch
    7. AutoApplyBundlesToVisitBatchAutoApplyBundlesToVisitBatchdisabled
      Applies matching charge bundles to batch visits based on facility and insurance to ensure consistent pricing across service types.
      DISABLED
    8. CheckBenefitsflow
      Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
      auto query
    9. 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
    10. AutoApplyBenefitsAutoApplyBenefits
      Populates insurance benefits like copays and deductibles on batch visits using data from eligibility verification transactions.
    11. CalculateVisitsflow
      Compute the good-faith estimate (CalculateVisits).
      · reads DimensionMemberVisitBatch
      ↳ feeds from LoadVisitBatch
      custom querycalculate chargesbatch mode
    12. LogVisitChangeEventsBatchLogVisitChangeEventsBatch
      Compare batch visits against production records to detect and log specific property changes for auditing and compliance tracking.
    13. CheckVisitGFEComplianceBatchCheckVisitGFEComplianceBatchdisabled
      Audits Good Faith Estimate compliance for batch visits and identifies records requiring resends based on federal timing regulations.
      DISABLED
    14. Update Patient Email for Commercial InsuranceUpdateEntities
      writes Patient · reads VisitBatchpatient
      ↳ feeds from LoadVisitBatch
      entity type=Patientauto query
    15. ▶Load-CustomProperty-CoverageStatussub-job · 3↗
      1. Load Primary Coverage StatusUpdateCustomPropertyFromQuery
        · reads CustomPropertyMapEligibilityTxnVisitBatchVisitCoverageBatch
        entity=Visitcustom property key=PrimaryCoverageStatusauto querybatch mode
      2. Load Secondary Coverage StatusUpdateCustomPropertyFromQuery
        · reads CustomPropertyMapEligibilityTxnVisitBatchVisitCoverageBatch
        entity=Visitcustom property key=SecondaryCoverageStatusauto querybatch mode
      3. Load Tertiary Coverage StatusUpdateCustomPropertyFromQuerydisabled
        · reads CustomPropertyMapEligibilityTxnVisitBatchVisitCoverageBatch
        entity=Visitcustom property key=TertiaryCoverageStatusauto querybatch modeDISABLED
    16. AutoSendLettersAutoSendLetters
      Generates and sends patient cost estimate letters for visits in the batch table based on configured qualification and communication rules.
    17. AutoSendLettersForFutureVisitsAutoSendLettersForFutureVisitsdisabled
      Generates and sends cost estimate letters for upcoming scheduled visits based on patient preferences and configured qualification criteria.
      DISABLED
    18. Load TransformVisitInsuranceCoverageStatussqlflow_hl7_processing_loadvisits__load_transformvisitinsurancecoveragestatus.sql
      ✨ Updates insurance coverage statuses by analyzing recent eligibility transactions and mapping results to standardized active or inactive states.
      writes TempEligibilityStatsTransformVisitInsuranceCoverageStatus · reads TempEligibilityStatsTransformVisitInsuranceVisitBatch
      ↳ feeds from LoadVisitBatch
    19. 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
    20. ExportBatchVisitEligibilityflowdisabled
      Push the batch to the app / export results.
      · reads EligibilityTxnVisitBatch
      custom querytable name=BatchEligibility_Resultsarchive table name=BatchEligibility_ResultsArchiveDISABLED
    21. SearchForInsuranceflowdisabled
      Find coverage for visits missing insurance.
      · reads EligibilityTxnBatch
      custom querytable name=InsuranceSearch_Resultbatch modeDISABLED
    22. Generate-Reportsub-jobdisabled
    23. ▶Flow-HL7-Processing-LoadVisitFactsub-job · 1↗
      1. Load VisitFactsqlflow_hl7_processing_loadvisitfact__load_visitfact.sql
        ✨ Updates the visit fact table with current status, letter activity, eligibility results, and payment totals for recent appointments.
        writes VisitFactVisitFactDelta · reads EligibilityTxnPatientLetterPaymentTxnVisitVisitCoverageVisitFactDelta
    24. Update-Dashboard-Tablessub-jobdisabled
  9. Update HL7ProcessLogsqlflow_hl7_processing__update_hl7processlog.sql
    ✨ Marks the processing run as finished and saves the last processed archive key to track progress.
    writes HL7ProcessLogLastArchiveKey · reads HL7DeltaHL7ProcessLogHL7ProcessLogLastArchiveKey
  10. WorkqueueAssignmentWorkqueueAssignment
    Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.
  11. SendHL7Lettersflow
    Exports pending patient letters as HL7 messages with PDF attachments to a local directory for transmission to external systems.
    output file path=D:\ClientData\RIA\ORUpublish directory=D:\ClientData\RIA\ORUsending application=MDClaritysending facility=MDClarityreceiving application=Abbadox
  12. Generate and send ORMsExecuteProcess
    command="D:\ClientData\RIA\OutboundORM\py.bat"

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
auto_send_letters_for_future_visits
Pre-send letters for upcoming visits