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

Batch / file feed · RevFind pipeline

JewishBoard · RevFind-Daily-Processing

RevFind-Daily-Processing

pipeline JewishBoard/RevFind-Daily-Processing · db JewishBoard_ProdMirror · updated 2025-11-10
classification: revfind · loads 1 visit table

Loads visit tables
Besides its back-office work, this RevFind pipeline also loads a visit dimension/fact table (Load VisitType Dimensions) — 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

Daily batch — once at 20:00

The Batch / file feed arrives as a scheduled drop, so one overnight pass is enough: it ingests the day's file and works the whole batch to process the visits. A tighter loop would just re-scan the same file.

Scheduled taskCadenceServerStateCoordination
\MDC\Daily - Processing - JewishBoardweekly at 20:00prod-db-1enabledstandalone

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

Denial / underpaymentCPT / 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).

revfind

RevFind-Daily-Processing

7 tasks · JewishBoard
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. ▶RevFind-Source-Importsub-job · 22↗
    1. DownloadDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=JewishBoard_ProdMirror-mdcssh-sftpremote directory=/local directory={SourceDataFolder}
    2. Charge ProcessDataFileProcessFiledisabled
      writes ImportChargeDetailsSourceChargeDetails
      archive directory={SourceDataArchive}base table name=ChargeDetailsrow terminator=Newlinecolumn separator=TabDISABLED
    3. BillingTxHistory ProcessDataFileProcessFiledisabled
      writes ImportBillingTxHistorySourceBillingTxHistory
      archive directory={SourceDataArchive}base table name=BillingTxHistoryrow terminator=Newlinecolumn separator=TabDISABLED
    4. Encounter ProcessDataFileProcessFiledisabled
      writes ImportEncounterSourceEncounter
      archive directory={SourceDataArchive}base table name=Encounterrow terminator=Newlinecolumn separator=PipeDISABLED
    5. ChargeMaster ProcessDataFileProcessFiledisabled
      writes ImportChargeMasterSourceChargeMaster
      archive directory={SourceDataArchive}base table name=ChargeMasterrow terminator=Newlinecolumn separator=TabDISABLED
    6. Insurance ProcessDataFileProcessFiledisabled
      writes ImportInsuranceSourceInsurance
      archive directory={SourceDataArchive}base table name=Insurancerow terminator=Newlinecolumn separator=TabDISABLED
    7. Location ProcessDataFileProcessFiledisabled
      writes ImportLocationSourceLocation
      archive directory={SourceDataArchive}base table name=Locationrow terminator=Newlinecolumn separator=TabDISABLED
    8. Patient ProcessDataFileProcessFiledisabled
      writes ImportPatientSourcePatient
      archive directory={SourceDataArchive}base table name=Patientrow terminator=Newlinecolumn separator=TabDISABLED
    9. PatientEncounterInsurance ProcessDataFileProcessFiledisabled
      writes ImportPatientEncounterInsuranceSourcePatientEncounterInsurance
      archive directory={SourceDataArchive}base table name=PatientEncounterInsurancerow terminator=Newlinecolumn separator=TabDISABLED
    10. Provider ProcessDataFileProcessFiledisabled
      writes ImportProviderSourceProvider
      archive directory={SourceDataArchive}base table name=Providerrow terminator=Newlinecolumn separator=TabDISABLED
    11. TransactionAdj ProcessDataFileProcessFiledisabled
      writes ImportTransactionPayAdjHistorySourceTransactionPayAdjHistory
      archive directory={SourceDataArchive}base table name=TransactionPayAdjHistoryrow terminator=Newlinecolumn separator=TabDISABLED
    12. ▶RevFind-Source-ChargeProcessDataFilesub-job · 1↗
      1. Charge ProcessDataFileProcessFile
        writes ImportChargeDetailsSourceChargeDetails
        file path={FilePath}archive directory={SourceDataArchive}base table name=ChargeDetailsrow terminator=Newlinecolumn separator=Tab
    13. ▶RevFind-Source-BillingTxProcessDataFilesub-job · 1↗
      1. BillingTxHistory ProcessDataFileProcessFile
        writes ImportBillingTxHistorySourceBillingTxHistory
        file path={FilePath}archive directory={SourceDataArchive}base table name=BillingTxHistoryrow terminator=Newlinecolumn separator=Tab
    14. ▶RevFind-Source-EncounterProcessDataFilesub-job · 1↗
      1. Encounter ProcessDataFileProcessFiledisabled
        writes ImportEncounterSourceEncounter
        file path={FilePath}archive directory={SourceDataArchive}base table name=Encounterrow terminator=Newlinecolumn separator=Pipe
    15. ▶RevFind-Source-ChargeMasterProcessDataFilesub-job · 1↗
      1. ChargeMaster ProcessDataFileProcessFile
        writes ImportChargeMasterSourceChargeMaster
        file path={FilePath}archive directory={SourceDataArchive}base table name=ChargeMasterrow terminator=Newlinecolumn separator=Tab
    16. ▶RevFind-Source-InsuranceProcessDataFilesub-job · 1↗
      1. Insurance ProcessDataFileProcessFile
        writes ImportInsuranceSourceInsurance
        file path={FilePath}archive directory={SourceDataArchive}base table name=Insurancerow terminator=Newlinecolumn separator=Tab
    17. ▶RevFind-Source-LocationProcessDataFilesub-job · 1↗
      1. Location ProcessDataFileProcessFile
        writes ImportLocationSourceLocation
        file path={FilePath}archive directory={SourceDataArchive}base table name=Locationrow terminator=Newlinecolumn separator=Tab
    18. ▶RevFind-Source-PatientProcessDataFilesub-job · 1↗
      1. Patient ProcessDataFileProcessFile
        writes ImportPatientSourcePatient
        file path={FilePath}archive directory={SourceDataArchive}base table name=Patientrow terminator=Newlinecolumn separator=Tab
    19. ▶RevFind-Source-PatEncInsProcessDataFilesub-job · 1↗
      1. PatientEncounterInsurance ProcessDataFileProcessFile
        writes ImportPatientEncounterInsuranceSourcePatientEncounterInsurance
        file path={FilePath}archive directory={SourceDataArchive}base table name=PatientEncounterInsurancerow terminator=Newlinecolumn separator=Tab
    20. ▶RevFind-Source-ProviderProcessDataFilesub-job · 1↗
      1. Provider ProcessDataFileProcessFile
        writes ImportProviderSourceProvider
        file path={FilePath}archive directory={SourceDataArchive}base table name=Providerrow terminator=Newlinecolumn separator=Tab
    21. ▶RevFind-Source-TranPayAdjProcessDataFilesub-job · 2↗
      1. dedupe txnsExecuteProcess
        command=powershell.exe
      2. TransactionAdj ProcessDataFileProcessFile
        writes ImportTransactionPayAdjHistorySourceTransactionPayAdjHistory
        file path={FilePath}archive directory={SourceDataArchive}base table name=TransactionPayAdjHistoryrow terminator=Newlinecolumn separator=Tab
    22. One-time load of SourcePayAdj to deal w dupessqlrevfind_source_import__one_time_load_of_sourcepayadj_to_deal_w_dupes.sql
      ✨ Deduplicates and merges payment and adjustment history into the source transaction table to ensure data integrity.
      writes SourceTransactionPayAdjHistory · reads ImportTransactionPayAdjHistoryJewishBoard
  2. ▶Load-Account-Transform-Tables-RevFindsub-job · 14↗Transforms-RevFind
    1. Fill AccountKeyMapsqlload_account_transform_tables_revfind__fill_accountkeymap.sql
      ✨ Maps patient and episode identifiers to unique account IDs to maintain consistent tracking across the data pipeline.
    2. Create AccountDeltasqlload_account_transform_tables_revfind__create_accountdelta.sql
      ✨ Identifies and flags accounts with recent billing or transaction updates to determine which records require processing in the current run.
    3. Load TransformPatientMergeSql
      writes TransformPatient · reads SourcePatient
      merge data source type=ThisDatabasetable name=TransformPatientmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    4. Load TransformLocationsqlload_account_transform_tables_revfind__load_transformlocation.sqldisabled
      ✨ Builds the location transformation table by mapping source locations to standardized addresses and place of service codes.
      writes TransformLocation · reads SourceLocationSourceLocationMap
    5. Load TransformProvidersqlload_account_transform_tables_revfind__load_transformprovider.sqldisabled
      ✨ Updates the provider transformation table with standardized names, credentials, and NPI identifiers from source systems.
      writes TransformProvider · reads SourceProvider
    6. Load TransformPayersqlload_account_transform_tables_revfind__load_transformpayer.sqldisabled
      ✨ Loads the payer transformation table with standardized insurance carrier names, plan types, and financial classifications.
      writes TransformPayer · reads SourcePayers
    7. Load TransformCharge (Initial, To Remove)sqlload_account_transform_tables_revfind__load_transformcharge_initial_to_remove.sqldisabled
      ✨ Loads and transforms charge-level data, including service details and insurance information, for accounts identified as having recent updates.
      writes TransformCharge · reads AccountDeltaAccountKeyMapSourceBillingTxHistorySourceLocationTransformChargemaster
    8. Merge TransformChargeMergeSql
      writes TransformCharge · reads AccountKeyMapSourceBillingTxHistorySourceChargeDetailsSourceInsuranceSourceLocationSourceTransactionPayAdjHistoryTransformChargemaster
      merge data source type=ThisDatabasetable name=TransformChargemerge mode=InsertUpdateHardDeletebulk copy timeout seconds=300bulk copy use table lock
    9. Load TransformTransactionsqlload_account_transform_tables_revfind__load_transformtransaction.sql
      ✨ Processes and categorizes financial transactions, such as payments and adjustments, while establishing their chronological order for each account.
      writes TransformTransaction · reads AccountDeltaAccountKeyMapAdjustmentCodeRollupSourceBillingTxHistorySourceTransactionPayAdjHistory
    10. (to delete once merge confirmed) Load TransformAccountsqlload_account_transform_tables_revfind__to_delete_once_merge_confirmed_load_transformaccount.sqldisabled
      ✨ Builds account-level summaries containing provider, facility, and patient identifiers to provide a high-level view of each encounter.
      writes TransformAccount · reads AccountKeyMapSourceBillingTxHistorySourceLocation
    11. Merge TransformAccountMergeSql
      writes TransformAccount · reads AccountKeyMapSourceBillingTxHistorySourceLocation
      merge data source type=ThisDatabasetable name=TransformAccountmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    12. Load TransformAccountInsurancesqlload_account_transform_tables_revfind__load_transformaccountinsurance.sqldisabled
      ✨ Loads insurance coverage details and coordination of benefits for each account to track payer information and member identifiers.
      writes TransformAccountInsurance · reads AccountKeyMapSourcePatientEncounterInsuranceTransformCharge
    13. Merge TransformAccountInsuranceMergeSql
      writes TransformAccountInsurance · reads AccountKeyMapSourcePatientEncounterInsuranceTransformCharge
      ↳ feeds from Merge TransformCharge
      merge data source type=ThisDatabasetable name=TransformAccountInsurancemerge mode=InsertUpdateHardDeletebulk copy timeout seconds=300bulk copy use table lock
    14. Load TransformChargeTransactionMeasuressqlload_account_transform_tables_revfind__load_transformchargetransactionmeasures.sql
      ✨ Aggregates transaction data into charge-level financial measures, calculating totals for payments, adjustments, and denials by insurance priority.
      writes TransformChargeTransactionMeasure · reads AccountDeltaTransformChargeTransformTransaction
      ↳ feeds from Load TransformTransaction, Merge TransformCharge
  3. ▶RevFind-Process-PatientsEntitysub-job · 1↗
    1. LoadPatientsLoadPatients
      Upsert patient records into the patient dimension.
      · reads PatientTransformPatient
      custom patient query
  4. ▶Load-Entities-RevFindsub-job · 2↗Load Facility, Provider
    1. Load FacilityLoadEntities
      writes Facility · reads FacilitySourceLocation
      entity type=Facilitycustom querykey prefix=F
    2. Merge ProviderMergeSql
      writes Provider · reads SourceBillingTxHistorySourceProvider
      merge data source type=ThisDatabasetable name=Providermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
  5. ▶Load-Dimension-Members-RevFindsub-job · 15↗
    1. LoadDimensionMembersLoadDimensionMembers
      Synchronize facility, provider, and insurance metadata into the dimension member table to refresh dashboard filters and reporting categories.
      writes DimensionMember
      load insuranceload quote insuranceload eligibility insuranceload facilityload provider
    2. Load Time Dimensionssqlload_dimension_members_revfind__load_time_dimensions.sql
      ✨ Builds and updates time-based dimension members for days, months, and years within the reporting period.
      writes DimensionMember · reads AccountKeyMap
    3. Load DenialCategory Dimensionsqlload_dimension_members_revfind__load_denialcategory_dimension.sql
      ✨ Loads denial category dimensions from adjustment code rollup data to categorize claim denials.
      writes DimensionMember · reads AdjustmentCodeRollup
    4. Load FacilityGroup Dimensionssqlload_dimension_members_revfind__load_facilitygroup_dimensions.sql
      ✨ Builds the facility group dimension by extracting unique parent identifiers from existing facility records.
      writes DimensionMember · reads DimensionMember
      ↳ feeds from Load AllowedStatus Dimension, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Source Insurance Dimension, Load Time Dimensions, LoadDimensionMembers
    5. Load FacilityType Dimensionssqlload_dimension_members_revfind__load_facilitytype_dimensions.sql
      ✨ Loads facility type dimension members from facility records to enable standardized categorization across the revenue cycle pipeline.
      writes DimensionMember · reads Facility
    6. Load Practice Dimensionssqlload_dimension_members_revfind__load_practice_dimensions.sqldisabled
      ✨ Loads practice dimension members from the practice map for entities that have associated encounter records.
      writes DimensionMember · reads PracticeMapsourceencounter
    7. Load PlaceOfService Dimensionssqlload_dimension_members_revfind__load_placeofservice_dimensions.sql
      ✨ Builds and updates the place of service dimension by mapping charge data to standardized codes and descriptive names.
      writes DimensionMember · reads POSCodesTransformCharge
    8. Load VisitType Dimensionssqlload_dimension_members_revfind__load_visittype_dimensions.sqldisabled
      ✨ Loads and synchronizes visit type dimensions from event mapping data while removing entries no longer present in the source.
      writes DimensionMember · reads SourceEventTransformEventMap
    9. Load AccountStatus Dimensionsqlload_dimension_members_revfind__load_accountstatus_dimension.sqldisabled
      ✨ Builds the account status dimension by extracting unique encounter status values from the source encounter records.
      writes DimensionMember · reads SourceEncounter
    10. Load AllowedStatus Dimensionsqlload_dimension_members_revfind__load_allowedstatus_dimension.sql
      ✨ Builds allowed status dimensions to categorize how actual payments compare to expected insurance contract rates.
      writes DimensionMember
    11. Load DiagnosisCode Dimensionsqlload_dimension_members_revfind__load_diagnosiscode_dimension.sql
      ✨ Loads diagnosis code dimensions from charge data and assigns them to categories using standardized reference data.
      writes DimensionMember · reads DimensionMemberTransformCharge
      ↳ feeds from Load AllowedStatus Dimension, Load DenialCategory Dimension, Load FacilityGroup Dimensions, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Source Insurance Dimension, Load Time Dimensions, LoadDimensionMembers
    12. Load Modifier Dimensionsqlload_dimension_members_revfind__load_modifier_dimension.sql
      ✨ Loads and updates procedure modifier dimension members using charge data and standard modifier descriptions.
      writes DimensionMember · reads ProcedureModifierTransformCharge
    13. Load State Dimensionsqlload_dimension_members_revfind__load_state_dimension.sqldisabled
      ✨ Updates state dimensions using facility location data to allow for geographic segmentation of healthcare services and revenue.
      writes DimensionMember · reads Facility
    14. Load Source Insurance Dimensionsqlload_dimension_members_revfind__load_source_insurance_dimension.sql
      ✨ Loads and updates source insurance dimension members by mapping insurance IDs and names from the insurance map table.
      writes DimensionMember · reads InsuranceMap
    15. Load ProcedureCode Dimensionsqlload_dimension_members_revfind__load_procedurecode_dimension.sql
      ✨ Builds procedure code dimension members by extracting codes from charge types and joining them with standard procedure descriptions.
      · reads ChargeTypeProcedureCode
  6. ▶Load-Insurance-Mapsub-job · 1↗
    1. Load InsuranceMapLoadEntities
      writes InsuranceMap · reads InsuranceMapSourceInsurance
      entity type=InsuranceMapcustom querykey prefix=IM
  7. ▶Load-Account-Data-RevFindsub-job · 4↗Load Account-Level Tables-RevFind
    1. Load ChargeMeasuresqlload_account_data_revfind__load_chargemeasure.sql
      ✨ Builds financial metrics for charges, including payments, adjustments, and denials, to track account-level charge performance.
      writes ChargeMeasures · reads AccountDeltaTransformChargeTransformChargeTransactionMeasure
    2. LoadAccountDataLoadAccountData
      Imports historical billing data from external systems into core account tables while preserving manual user modifications and application fields.
      · reads AccountChargeAccountDeltaChargeMeasuresChargeTypeFacilityInsuranceMapPatientTransformAccount
      ↳ feeds from Load ChargeMeasure
      custom account charge querycustom account querycustom account remit querycustom account insurance query
    3. ▶Load-Custom-Properties-2sub-job · 2↗
      1. UpdateCustomPropertyFromQuery: ClaimNumberUpdateCustomPropertyFromQuery
        · reads CustomPropertyMapTransformCharge
        entity=Accountcustom property key=CustomProperty-[id]auto query
      2. UpdateCustomPropertyFromQuery: ClaimDateUpdateCustomPropertyFromQuery
        · reads CustomPropertyMapTransformCharge
        entity=Accountcustom property key=CustomProperty-[id]auto query
    4. ▶Score-Accounts-RevFindsub-job · 2↗
      1. Score All Unscored AccountsProcessAccountDataFromQuerydisabled
        · reads AccountAccountChargeContractMap
        custom queryDISABLED
      2. Score All accountsBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        incremental