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

Batch / file feed · RevFind pipeline

RIA · Daily-Processing-RevFind

Daily processing for RevFind MSN files

pipeline RIA/Daily-Processing-RevFind · db RIA_ProdMirror · updated 2025-09-17
classification: revfind · loads 3 visit tables

Loads visit tables
Besides its back-office work, this RevFind pipeline also loads a visit dimension/fact table (Load VisitStatus Dimension, Load VisitType Dimensions, UpdateVisitCustomPropertyFromQuery) — the shared rows Flow builds on. It runs no front-of-house work (no eligibility, estimate, or letters), so it stays RevFind.

trigger

How it's scheduled

Monthly sweep

A periodic re-check — eligibility and benefits drift between visits, so this re-verifies coverage on a monthly cadence rather than per-appointment.

Scheduled taskCadenceServerStateCoordination
\MDC\Monthly - Processing - RIAmonthly at 07:30prod-db-3enabledstandalone

shape

The RevFind back-office spine

RevFind pipelines share a back-office shape: ingest → load accounts → reconcile remits → model contracts → surface underpayments. Lit stages are the ones this pipeline runs.

Ingest
Batch / file feed
→
Accounts / AR
ACCT
→
Remit (835)
→
Contract model
→
Payer rules
PAYR
→
Work queue & sync

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

Expected reimbursementDenial / underpaymentCPT / procedure logic

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

revfind

Daily-Processing-RevFind

4 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. ▶Process-MSN-Files-RevFindsub-job · 3↗Download and Process MSN files
    1. Download (old)Downloaddisabled
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=RIA_ProdMirror-mdcssh-sftpremote directory=/MSN Files/local directory={SourceDataFolderRevFind}DISABLED
    2. DownloadDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=RIA_ProdMirror-msnftp-sftpremote directory=/local directory={SourceDataFolderRevFind}
    3. ▶Load-MSN-ProcessDataFile-RevFindsub-job · 1↗Load MSN Files
      1. Process MSNProcessFile
        writes ImportMSNFilesSourceMSNFiles
        file path={FilePath}archive directory={SourceDataArchiveRevFind}base table name=MSNFilesrow terminator=Newlinecolumn separator=Comma
  2. ▶Load-Account-Transform-Tablessub-job · 8↗
    1. TransformDatasqlload_account_transform_tables__transformdata.sql
      ✨ Standardizes and cleans raw account data by casting types and parsing facility, insurance, and transaction details into a structured format.
      writes TransformData · reads SourceMSNFiles
    2. AccountDeltasqlload_account_transform_tables__accountdelta.sql
      ✨ Identifies and stores a list of account IDs that have been modified since the last successful pipeline execution.
    3. TransformAccountMapsqlload_account_transform_tables__transformaccountmap.sql
      ✨ Maps accounts to patient and insurance identifiers, establishing a primary record for each account based on the latest transaction data.
      writes TransformAccountMap · reads AccountDeltaTransformData
      ↳ feeds from TransformData
    4. TransformChargesqlload_account_transform_tables__transformcharge.sql
      ✨ Builds a structured table of individual charges, including procedure codes, modifiers, and balances, for accounts identified in the delta.
      writes TransformCharge · reads AccountDelta
    5. TransformAccountsqlload_account_transform_tables__transformaccount.sql
      ✨ Loads high-level account information, including primary insurance and facility details, for the most recent records in the account map.
      writes TransformAccount · reads AccountDelta
    6. TransformTransactionsqlload_account_transform_tables__transformtransaction.sql
      ✨ Builds the transaction transformation table by mapping insurance levels and ranking payments and refunds for updated accounts.
      writes TransformTransaction · reads AccountDeltaTransformAccountMapTransformData
      ↳ feeds from TransformAccountMap, TransformData
    7. TransformChargeTransactionMeasuresqlload_account_transform_tables__transformchargetransactionmeasure.sqldisabled
      ✨ Updates charge transaction measures by identifying initial and current denial categories and amounts for updated accounts.
      writes TransformChargeTransactionMeasure · reads AccountDeltaTransformChargeTransformTransaction
    8. TransformChargeTransactionMeasuresqlload_account_transform_tables__transformchargetransactionmeasure_1.sql
      ✨ Populates charge transaction measures with detailed financial metrics, including initial and current denial data and allowed dates.
      writes TransformChargeTransactionMeasure · reads AccountDeltaTransformChargeTransformTransaction
      ↳ feeds from TransformCharge, TransformTransaction
  3. ▶Load-Entities-RevFindsub-job · 2↗Load Facility, Provider
    1. Load FacilityLoadEntities
      writes Facility · reads FacilityTransformData
      entity type=Facilitycustom query
    2. Load Providersqlload_entities_revfind__load_provider.sql
      ✨ Loads and updates provider information, including NPIs and names, using the most recent transaction data.
      writes Provider · reads TransformData
  4. ▶Load-Account-Datasub-job · 9↗Load Account-Level Tables
    1. LoadPatientsLoadPatients
      Upsert patient records into the patient dimension.
      · reads PatientTransformAccountMap
      custom patient query
    2. Load ChargeMeasuressqlload_account_data__load_chargemeasures.sql
      ✨ Calculates and loads financial metrics, including payments, adjustments, and allowed amounts, into the charge measures table for updated accounts.
      writes ChargeMeasures · reads AccountDeltaTransformChargeTransformChargeTransactionMeasure
    3. LoadAccountDataLoadAccountData
      Imports historical billing data from external systems into core account tables while preserving manual user modifications and application fields.
      · reads AccountChargeAccountDeltaChargeMeasuresChargeTypeInsuranceMapPatientProviderTransformAccount
      ↳ feeds from Load ChargeMeasures
      custom account charge querycustom account querycustom account remit querycustom account insurance query
    4. ▶Load-Dimension-Memberssub-job · 15↗
      1. Load Insurance Dimensionsqlload_dimension_members__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 Dimensionsqlload_dimension_members__load_eligibilityinsurance_dimension.sqldisabled
        ✨ Loads insurance members that support electronic eligibility and benefits verification via API integration.
        writes DimensionMember · reads DimensionMemberInsurance
      3. Load QuoteInsurance Dimensionsqlload_dimension_members__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 Dimensionssqlload_dimension_members__load_time_dimensions.sql
        ✨ Loads and updates time-based dimension members for days, months, and years into the dimension member table.
        writes DimensionMember · reads TransformVisit
      6. Load VisitStatus Dimensionsqlload_dimension_members__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 Dimensionsqlload_dimension_members__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 Dimensionssqlload_dimension_members__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 Diagnosis Code Dimension, Load Modifier Dimension, Load Place of Service Dimension, Load Provider Dimension, Load RadioIsotopes Code Dimension, Load SourceInsurance Dimension, Load State Dimensions, Load Time Dimensions, Load VisitStatus Dimension, Load VisitType Dimensions, LoadInsuranceDimensionMembers
      9. Load VisitType Dimensionssqlload_dimension_members__load_visittype_dimensions.sql
        ✨ Populates the visit type dimension by mapping service IDs and CPT codes from order and crosswalk data.
        writes DimensionMember · reads HL7OrderRIACPTCrosswalk
      10. Load Diagnosis Code Dimensionsqlload_dimension_members__load_diagnosis_code_dimension.sql
        ✨ 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 Dimensionsqlload_dimension_members__load_radioisotopes_code_dimension.sql
        ✨ 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 Dimensionsqlload_dimension_members__load_modifier_dimension.sql
        ✨ 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 Dimensionsqlload_dimension_members__load_place_of_service_dimension.sql
        ✨ Builds and updates the PlaceOfService dimension using unique location codes extracted from charge transaction records.
        writes DimensionMember · reads TransformCharge
      14. Load SourceInsurance Dimensionsqlload_dimension_members__load_sourceinsurance_dimension.sql
        ✨ 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 Dimensionssqlload_dimension_members__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. ▶Load-Custom-Propertiessub-job · 2↗Load custom properties from Load-Visits
      1. UpdateVisitCustomPropertyFromQueryUpdateVisitCustomPropertyFromQuerydisabled
        · reads InsuranceSearch_ResultTransformVisitInsuranceCoverageStatusVisitBatch
        custom visit update custom property queryDISABLED
      2. LoadCustomNotesCustomPropertyLoadCustomNotesCustomPropertydisabled
        · reads BundleNotesMappingHL7DeltaHL7VisitVisitBatch
        entity=Visitcustom property key=CustomNotescustom querybatch modeDISABLED
    6. ▶Score-Accountssub-job · 42↗
      1. AetnaAetna
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      2. Aetna Medicare ECFOI, MICTIC, RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      3. Aetna Medicare IMIBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-03-15incremental
      4. Avmed MedicareBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-01-01incremental
      5. BCBSBCBS
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-06-27incremental
      6. BCBS Medicare ECFOIBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-06-27incremental
      7. BCBS Medicare IMIBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      8. BCBS Medicare MICTICBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-01-01incremental
      9. BCBS Medicare RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      10. Beech Street RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2006-11-01incremental
      11. CarePlusCarePlus
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-01-01incremental
      12. CarePlus RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-05-01incremental
      13. CariskCarisk
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-05-01incremental
      14. Cigna ECFOI, MICTIC, RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2020-01-01incremental
      15. Cigna IMIBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-12-15incremental
      16. CurativeCurative
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2025-09-14incremental
      17. Devoted Health RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2022-12-01incremental
      18. Employee Benefit Management Services RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2020-10-01incremental
      19. Evolutions Healthcare Systems RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-10-16incremental
      20. Florida HealthBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2020-01-01incremental
      21. Florida Health MedicareBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2020-01-01incremental
      22. Freedom Health MedicareBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-01-01incremental
      23. Health First RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2018-08-01incremental
      24. Hospice of Marion County RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2020-01-01incremental
      25. HumanaHumana
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      26. Humana MedicareBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      27. MedicareMedicare
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-01-01incremental
      28. MultiPlan RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2011-12-31incremental
      29. Oscar RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2020-01-01incremental
      30. Prime HealthBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      31. Prime Health Workers CompBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      32. Self PayBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      33. SimPlan RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2021-04-01incremental
      34. Tricare RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2017-03-01incremental
      35. Trustmark Health Benefits RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2020-11-01incremental
      36. United HealthcareBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      37. United Healthcare Medicare IMI & ECFOIBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incremental
      38. United Healthcare Medicare MICTICBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-01-01incremental
      39. VA Community Care Network IMIBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-01-01incremental
      40. VA Community Care Network RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2021-08-01incremental
      41. Volusia Health Network RAOC / RIABatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2020-01-01incremental
      42. WellCareWellCare
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2024-01-01incremental
    7. Load AccountDetailsqlload_account_data__load_accountdetail.sqldisabled
      ✨ Builds a comprehensive account summary table by combining patient, insurance, and financial data for detailed encounter reporting.
      writes AccountDetail · reads AccountAccountChargeAccountDeltaAccountEstimateDimensionMemberPatientSourceEncounterInsuranceSourcePayer
    8. Load AccountChargeDetailsqlload_account_data__load_accountchargedetail.sqldisabled
      ✨ Loads granular charge-level details including procedure codes, modifiers, and payment metrics for individual service line analysis.
      writes AccountChargeDetail · reads AccountAccountChargeAccountDeltaAccountEstimateCalculationAccountEstimateChargeDimensionMemberPatientSourceEncounterInsurance
    9. Load Recovery Reportsqlload_account_data__load_recovery_report.sql
      ✨ Builds a temporary report comparing estimated and actual insurance payments to track recovered revenue from account appeals.
      writes TempChargeRecoveryReport · reads AccountAccountAppealStatusAccountAppealSummaryAccountChargeAccountEstimateChargeChargeTypeFacilityPatient