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

Batch / file feed · ClarityFlow pipeline

Wildflower · Flow-Processing

Overall Processing Job for Flow Pipeline

pipeline Wildflower/Flow-Processing · db Wildflower_ProdMirror · updated 2025-09-17
classification: flow · 9 Flow markers

trigger

How it's scheduled

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

New appointments land continuously on the Batch / file 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\Batch - Processing - Wildflower - Flowevery 10 min (from 00:01)prod-db-1disabledstandalone

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
EligibilityBenefitsDiscovery
→
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 statusCPT / 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-Processing

7 tasks · Wildflower
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. ▶Flow-Source-ImportFilessub-job · 4↗
    1. DownloadDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=Wildflower_ProdMirror-mdcssh-sftpremote directory=/local directory={SourceDataFolder}
    2. ▶Flow-Source-ProcessDataFilePatientsub-job · 1↗
      1. Process Patient FilesProcessFile
        writes ImportPatientSourcePatient
        file path={FilePath}archive directory={SourceDataArchive}base table name=Patientrow terminator=Linefeedcolumn separator=Comma
    3. ▶Flow-Source-ProcessDataFileVisitsub-job · 1↗
      1. Process Visit FileProcessFile
        writes ImportVisitSourceVisit
        file path={FilePath}archive directory={SourceDataArchive}base table name=Visitrow terminator=Linefeedcolumn separator=Comma
    4. ▶Flow-Source-ProcessDataFileVisitInsurancesub-job · 1↗
      1. Process VisitInsurance FileProcessFile
        writes ImportVisitInsuranceSourceVisitInsurance
        file path={FilePath}archive directory={SourceDataArchive}base table name=VisitInsurancerow terminator=Linefeedcolumn separator=Comma
  2. ▶Flow-Transform-LoadTransformTablessub-job · 6↗
    1. Truncate VisitDeltasqlflow_transform_loadtransformtables__truncate_visitdelta.sql
      ✨ Clears the visit delta table to prepare for a fresh set of changed records in the current pipeline run.
      writes VisitDelta
    2. VisitDelta + Logssqlflow_transform_loadtransformtables__visitdelta_logs.sql
      ✨ Identifies new or updated visit records and logs the batch process to track incremental data changes.
      writes BatchProcessLogVisitDelta · reads BatchProcessLogSourceVisitSourceVisitInsuranceVisitDelta
      ↳ feeds from Truncate VisitDelta
    3. Transform Patientsqlflow_transform_loadtransformtables__transform_patient.sql
      ✨ Updates patient demographic information and contact details while standardizing gender values for the transformation layer.
      writes TransformPatient · reads BatchProcessLogSourcePatient
      ↳ feeds from VisitDelta + Logs
    4. Transform Visitsqlflow_transform_loadtransformtables__transform_visit.sql
      ✨ Refreshes visit details like status, time, and provider for records identified as changed in the current batch.
      writes TransformVisit · reads SourceVisitVisitDelta
      ↳ feeds from Truncate VisitDelta, VisitDelta + Logs
    5. Transform VisitInsurancesqlflow_transform_loadtransformtables__transform_visitinsurance.sql
      ✨ Updates insurance coverage details for changed visits by mapping source insurance identifiers to standardized internal values.
      writes TransformVisitInsurance · reads InsuranceMapSourceVisitInsuranceVisitDelta
      ↳ feeds from Truncate VisitDelta, VisitDelta + Logs
    6. Transform VisitChargesqlflow_transform_loadtransformtables__transform_visitcharge.sql
      ✨ Calculates and updates visit charges by mapping CPT codes and modifiers to standardized service types and charge definitions.
      writes TransformVisitCharge · reads ChargeTypeSourceVisitVisitDelta
      ↳ feeds from Truncate VisitDelta, VisitDelta + Logs
  3. Execute LoadPatientsLoadPatients
    Upsert patient records into the patient dimension.
    · reads PatientTransformPatient
    custom patient query
  4. ▶Flow-Transform-LoadDimensionMemberssub-job · 8↗
    1. 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
    2. Load Time Dimensionssqlflow_transform_loaddimensionmembers__load_time_dimensions.sql
      ✨ Builds and updates time-based dimension members for days, months, and years based on the range of scheduled appointment dates.
      writes DimensionMember · reads TransformVisit
    3. Load VisitStatus Dimensionsqlflow_transform_loaddimensionmembers__load_visitstatus_dimension.sql
      ✨ Loads appointment status values into the visit status dimension to track patient encounter progress.
      writes DimensionMember · reads TransformVisit
    4. Load Provider Dimensionsqlflow_transform_loaddimensionmembers__load_provider_dimension.sql
      ✨ Updates provider and provider type dimensions to maintain a hierarchical relationship between individual clinicians and their professional categories.
      writes DimensionMember · reads Provider
    5. Load Facility Dimensionssqlflow_transform_loaddimensionmembers__load_facility_dimensions.sql
      ✨ Builds and updates facility dimension members to maintain a consistent list of healthcare locations for reporting.
      writes DimensionMember · reads Facility
    6. Load VisitType Dimensionssqlflow_transform_loaddimensionmembers__load_visittype_dimensions.sql
      ✨ Loads unique visit types into the dimension table to standardize appointment categories for downstream processing.
      writes DimensionMember · reads TransformVisit
    7. Load SourceInsurance Dimensionssqlflow_transform_loaddimensionmembers__load_sourceinsurance_dimensions.sqldisabled
      ✨ Loads source insurance and insurance parent dimensions to map specific insurance packages to their broader reporting categories.
      writes DimensionMember · reads AccountInsuranceDimensionMemberInsuranceMapSourcePayer
    8. Load State Dimensionssqlflow_transform_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
  5. ▶Flow-Processing-LoadVisitssub-job · 23↗
    1. Load TempBatchVisitsqlflow_processing_loadvisits__load_tempbatchvisit.sql
      ✨ Identifies upcoming visits requiring insurance eligibility verification based on service types, appointment dates, and previous coverage status.
      writes TempBatchVisitVisitEligServiceTypesVisitServiceTypes · reads DimensionMemberTempBatchVisitTransformVisitTransformVisitInsuranceTransformVisitInsuranceCoverageStatusVisitEligServiceTypesVisitServiceTypes
      ↳ feeds from Load TransformVisitInsuranceCoverageStatus, WildFlower Augment TempBatchVisit
    2. Augment Visit Deltasqlflow_processing_loadvisits__augment_visit_delta.sql
      ✨ Updates the visit delta table by adding records flagged as requiring new insurance eligibility lookups from the batch process.
      writes VisitDelta · reads TempBatchVisitVisitDelta
      ↳ feeds from Augment VisitDelta for ReprocessChangedConfig, Load TempBatchVisit, WildFlower Augment TempBatchVisit
    3. WildFlower Augment TempBatchVisitsqlflow_processing_loadvisits__wildflower_augment_tempbatchvisit.sql
      ✨ Augments the eligibility check batch with additional visits from specific source systems that lack recent or successful coverage verification.
      writes TempBatchVisit · reads DimensionMemberTempBatchVisitTransformVisitTransformVisitInsuranceTransformVisitInsuranceCoverageStatusVisitDelta
      ↳ feeds from Augment Visit Delta, Augment VisitDelta for ReprocessChangedConfig, Load TempBatchVisit, Load TransformVisitInsuranceCoverageStatus
    4. ReprocessChangedConfigurationsReprocessChangedConfigurations
      Identify and queue visits or accounts affected by configuration changes to ensure historical data reflects updated pricing or insurance rules.
      reprocess visits
    5. Augment VisitDelta for ReprocessChangedConfigsqlflow_processing_loadvisits__augment_visitdelta_for_reprocesschangedconfig.sql
      ✨ Updates insurance mappings for reprocessed visits and adds them to the delta table for downstream processing.
      writes VisitDeltaVisitKeyLookup · reads VisitDeltaVisitKeyLookup
      ↳ feeds from Augment Visit Delta
    6. LoadVisitBatchLoadVisitBatch
      Load scheduled visits and associated charges into the processing pipeline using custom SQL queries for pricing estimation and quote generation.
      writes QuoteBatchVisitBatch · reads FacilityPatientTransformVisitTransformVisitChargeTransformVisitInsuranceVisitVisitDelta
      ↳ feeds from Augment Visit Delta, Augment VisitDelta for ReprocessChangedConfig, Sync Visits and Quotes
      custom visit querycustom charge querycustom service type querycustom coverage queryadd zero charge
    7. load-custom-propertiessub-job
    8. AutoApplyBundlesToVisitBatchAutoApplyBundlesToVisitBatchdisabled
      Applies matching charge bundles to batch visits based on facility and insurance to ensure consistent pricing across service types.
      DISABLED
    9. CheckBenefitsflow
      Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
      · reads TempBatchVisitVisitBatch
      ↳ feeds from Load TempBatchVisit, LoadVisitBatch, WildFlower Augment TempBatchVisit
      custom query
    10. 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
    11. AutoApplyBenefitsAutoApplyBenefits
      Populates insurance benefits like copays and deductibles on batch visits using data from eligibility verification transactions.
    12. CalculateVisitsflow
      Compute the good-faith estimate (CalculateVisits).
      · reads DimensionMemberVisitBatch
      ↳ feeds from LoadVisitBatch
      custom querycalculate chargesbatch mode
    13. AutoSendLettersAutoSendLetters
      Generates and sends patient cost estimate letters for visits in the batch table based on configured qualification and communication rules.
    14. LogVisitChangeEventsBatchLogVisitChangeEventsBatch
      Compare batch visits against production records to detect and log specific property changes for auditing and compliance tracking.
    15. Load TransformVisitInsuranceCoverageStatussqlflow_processing_loadvisits__load_transformvisitinsurancecoveragestatus.sql
      ✨ Loads insurance coverage status by calculating the latest eligibility transaction results for visits in the current batch.
      writes TempEligibilityStatsTransformVisitInsuranceCoverageStatus · reads TempEligibilityStatsTransformVisitInsuranceVisitBatch
      ↳ feeds from LoadVisitBatch
    16. ▶Load-CustomProperty-CoverageStatussub-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
    17. 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
    18. ExportBatchVisitEligibilityflowdisabled
      Push the batch to the app / export results.
      · reads EligibilityTxnVisitBatch
      custom querytable name=BatchEligibility_Resultsarchive table name=BatchEligibility_ResultsArchiveDISABLED
    19. SearchForInsuranceflowdisabled
      Find coverage for visits missing insurance.
      · reads EligibilityTxnBatch
      custom querytable name=InsuranceSearch_Resultbatch modeDISABLED
    20. WorkqueueAssignmentWorkqueueAssignment
      Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.
    21. Generate-Reportsub-jobdisabled
    22. Load-Visit-Factsub-jobdisabled
    23. Update-Dashboard-Tablessub-jobdisabled
  6. Update Logssqlflow_processing__update_logs.sql
    ✨ Updates the batch process log to mark the current processing cycle as successfully finished.
    · reads BatchProcessLog
  7. WorkqueueAssignmentWorkqueueAssignment
    Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.

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
auto_apply_bundles
Apply procedure bundles for the estimate
auto_benefits
Auto-apply parsed benefits to visits
auto_send_letters
Send GFE / patient letters