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

Batch / file feed · RevFind pipeline

USHP · RevFind-Daily-Processing-Accounts

Overall Processing job for RevFind Batch pipeline

pipeline USHP/RevFind-Daily-Processing-Accounts · db USHP_ProdMirror · updated 2026-04-20
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

Daily batch — once at 06:45

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 - USHPdaily at 06:45prod-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
Dashboards

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

Good-faith estimateFee schedule / contractDenial / underpaymentCPT / procedure logic

Payers in SQLAetnaAnthemCignaMedicaidMedicare

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-Accounts

13 tasks · USHP
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-ProcessNextGenDatasub-job · 19↗
    1. DownloadDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=USHP_ProdMirror-mdcssh-sftpremote directory=/MDClarity/local directory={SourceDataFolder}remote archive directory=/Archive/
    2. Clean Appointment ExtractExecuteProcess
      command=powershell.exe
    3. Clean PatientInsurance ExtractExecuteProcess
      command=powershell.exe
    4. Clean ChargeMaster ExtractExecuteProcess
      command=powershell.exe
    5. Dedupe Charges FIle (temporarily-[id])ExecuteProcess
      command=powershell.exe
    6. Process ExportAdjustmentProcessFile
      writes ImportAdjustmentSourceAdjustment
      archive directory={SourceDataArchive}base table name=Adjustmentrow terminator=Newlinecolumn separator=Pipe
    7. Load ExportAppointmentProcessFile
      writes ImportAppointmentSourceAppointment
      archive directory={SourceDataArchive}base table name=Appointmentrow terminator=Newlinecolumn separator=Pipe
    8. Load ExportChargeProcessFile
      writes ImportChargeSourceCharge
      archive directory={SourceDataArchive}base table name=Chargerow terminator=Newlinecolumn separator=Pipe
    9. Load ImportChargeMasterProcessFile
      writes ImportChargeMasterSourceChargeMaster
      archive directory={SourceDataArchive}base table name=ChargeMasterrow terminator=Newlinecolumn separator=Pipe
    10. Load ExportEncounterProcessFile
      writes ImportEncounterSourceEncounter
      archive directory={SourceDataArchive}base table name=Encounterrow terminator=Newlinecolumn separator=Pipe
    11. Load ImportEncounterInsurance DataFileLoadFile
      writes ImportEncounterInsurance
      data file spec=ImportEncounterInsuranceimport mode=Replacearchive file path={SourceDataArchive}
    12. Load SourceEncounterInsurancesqlrevfind_source_processnextgendata__load_sourceencounterinsurance.sql
      ✨ Loads encounter-specific insurance coverage details, including payer information and policy identifiers, from imported source data.
      writes sourceencounterinsurance · reads ImportEncounterInsurance
      ↳ feeds from Load ImportEncounterInsurance DataFile
    13. Load ExportEventProcessFile
      writes ImportEventSourceEvent
      file path={SourceDataFolder}\TempExportEvent.txtarchive directory={SourceDataArchive}base table name=Eventrow terminator=Newlinecolumn separator=Pipe
    14. Load ExportLocationProcessFile
      writes ImportLocationSourceLocation
      archive directory={SourceDataArchive}base table name=Locationrow terminator=Newlinecolumn separator=Pipe
    15. Load ExportPatientProcessFile
      writes ImportPatientSourcePatient
      archive directory={SourceDataArchive}base table name=Patientrow terminator=Newlinecolumn separator=Pipe
    16. Load ExportPatientInsuranceProcessFile
      writes ImportPatientInsuranceSourcePatientInsurance
      archive directory={SourceDataArchive}base table name=PatientInsurancerow terminator=Newlinecolumn separator=Pipe
    17. Load ExportPayerProcessFile
      writes ImportPayerSourcePayer
      file path={SourceDataFolder}\TempExportPayer.txtarchive directory={SourceDataArchive}base table name=Payerrow terminator=Newlinecolumn separator=Pipe
    18. Load ExportProviderProcessFile
      writes ImportProviderSourceProvider
      archive directory={SourceDataArchive}base table name=Providerrow terminator=Newlinecolumn separator=Pipe
    19. Process ExportTransactionDetail DataFileProcessFile
      writes ImportTransactionDetailSourceTransactionDetail
      archive directory={SourceDataArchive}base table name=TransactionDetailrow terminator=Newlinecolumn separator=Pipe
  2. ▶RevFind-Source-ProcessNextGenData-FLsub-job · 19↗
    1. Download FLDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=USHP_ProdMirror-mdcssh-sftpremote directory=/Stuart101124/local directory={SourceDataFolder}\Stuart101124remote archive directory=/Archive/
    2. Clean AppointmentFL ExtractExecuteProcess
      command=powershell.exe
    3. Clean PatientInsuranceFL ExtractExecuteProcess
      command=powershell.exe
    4. Clean ChargeMasterFL ExtractExecuteProcess
      command=powershell.exe
    5. Clean ExportAdjustment (FL-specific)ExecuteProcess
      command=powershell.exe
    6. Process ExportAdjustmentFLProcessFile
      writes ImportAdjustmentFLSourceAdjustmentFL
      archive directory={SourceDataArchive}\Stuart101124base table name=AdjustmentFLrow terminator=Linefeedcolumn separator=Pipe
    7. Load ExportAppointmentFLProcessFile
      writes ImportAppointmentFLSourceAppointmentFL
      archive directory={SourceDataArchive}\Stuart101124base table name=AppointmentFLrow terminator=Linefeedcolumn separator=Pipe
    8. Load ExportChargeFLProcessFile
      writes ImportChargeFLSourceChargeFL
      archive directory={SourceDataArchive}\Stuart101124base table name=ChargeFLrow terminator=Linefeedcolumn separator=Pipe
    9. Load ImportChargeMasterFLProcessFile
      writes ImportChargeMasterFLSourceChargeMasterFL
      archive directory={SourceDataArchive}\Stuart101124base table name=ChargeMasterFLrow terminator=Linefeedcolumn separator=Pipe
    10. Load ExportEncounterFLProcessFile
      writes ImportEncounterFLSourceEncounterFL
      archive directory={SourceDataArchive}\Stuart101124base table name=EncounterFLrow terminator=Linefeedcolumn separator=Pipe
    11. Load ImportEncounterInsuranceFL DataFileLoadFile
      writes ImportEncounterInsuranceFL
      data file spec=ImportEncounterInsuranceFLimport mode=Replacearchive file path={SourceDataArchive}\Stuart101124
    12. Load SourceEncounterInsuranceFLsqlrevfind_source_processnextgendata_fl__load_sourceencounterinsurancefl.sql
      ✨ Loads Florida encounter insurance data from import tables into the source staging area for further processing.
      writes sourceencounterinsuranceFL · reads ImportEncounterInsuranceFL
      ↳ feeds from Load ImportEncounterInsuranceFL DataFile
    13. Load ExportEventFLProcessFile
      writes ImportEventFLSourceEventFL
      archive directory={SourceDataArchive}\Stuart101124base table name=EventFLrow terminator=Linefeedcolumn separator=Pipe
    14. Load ExportLocationFLProcessFile
      writes ImportLocationFLSourceLocationFL
      archive directory={SourceDataArchive}\Stuart101124base table name=LocationFLrow terminator=Linefeedcolumn separator=Pipe
    15. Load ExportPatientFLProcessFile
      writes ImportPatientFLSourcePatientFL
      archive directory={SourceDataArchive}\Stuart101124base table name=PatientFLrow terminator=Linefeedcolumn separator=Pipe
    16. Load ExportPatientInsuranceFLProcessFile
      writes ImportPatientInsuranceFLSourcePatientInsuranceFL
      archive directory={SourceDataArchive}\Stuart101124base table name=PatientInsuranceFLrow terminator=Linefeedcolumn separator=Pipe
    17. Load ExportPayerFLProcessFile
      writes ImportPayerFLSourcePayerFL
      archive directory={SourceDataArchive}\Stuart101124base table name=PayerFLrow terminator=Linefeedcolumn separator=Pipe
    18. Load ExportProviderFLProcessFile
      writes ImportProviderFLSourceProviderFL
      archive directory={SourceDataArchive}\Stuart101124base table name=ProviderFLrow terminator=Linefeedcolumn separator=Pipe
    19. Process ExportTransactionDetailFL DataFileProcessFile
      writes ImportTransactionDetailFLSourceTransactionDetailFL
      archive directory={SourceDataArchive}\Stuart101124base table name=TransactionDetailFLrow terminator=Linefeedcolumn separator=Pipe
  3. ▶RevFind-Source-ProcessNextGenData-DEsub-job · 18↗
    1. Download DEDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=USHP_ProdMirror-mdcssh-sftpremote directory=/USHP126051/local directory={SourceDataFolder}\USHP126051remote archive directory=/Archive/
    2. Clean AppointmentDE ExtractExecuteProcess
      command=powershell.exe
    3. Clean PatientInsuranceDE ExtractExecuteProcess
      command=powershell.exe
    4. Clean ChargeMasterDE ExtractExecuteProcess
      command=powershell.exe
    5. Process ExportAdjustmentDEProcessFile
      writes ImportAdjustmentDESourceAdjustmentDE
      archive directory={SourceDataArchive}\USHP126051base table name=AdjustmentDErow terminator=Linefeedcolumn separator=Pipe
    6. Load ExportAppointmentDEProcessFile
      writes ImportAppointmentDESourceAppointmentDE
      archive directory={SourceDataArchive}\USHP126051base table name=AppointmentDErow terminator=Linefeedcolumn separator=Pipe
    7. Load ExportChargeDEProcessFile
      writes ImportChargeDESourceChargeDE
      archive directory={SourceDataArchive}\USHP126051base table name=ChargeDErow terminator=Linefeedcolumn separator=Pipe
    8. Load ImportChargeMasterDEProcessFile
      writes ImportChargeMasterDESourceChargeMasterDE
      archive directory={SourceDataArchive}\USHP126051base table name=ChargeMasterDErow terminator=Linefeedcolumn separator=Pipe
    9. Load ExportEncounterDEProcessFile
      writes ImportEncounterDESourceEncounterDE
      archive directory={SourceDataArchive}\USHP126051base table name=EncounterDErow terminator=Linefeedcolumn separator=Pipe
    10. Load ImportEncounterInsuranceDE DataFileLoadFile
      writes ImportEncounterInsuranceDE
      data file spec=ImportEncounterInsuranceDEimport mode=Replacearchive file path={SourceDataArchive}\USHP126051
    11. Load SourceEncounterInsuranceDEsqlrevfind_source_processnextgendata_de__load_sourceencounterinsurancede.sql
      ✨ Loads Delaware encounter insurance data from import tables into the source staging area for further processing.
      writes sourceencounterinsuranceDE · reads ImportEncounterInsuranceDE
      ↳ feeds from Load ImportEncounterInsuranceDE DataFile
    12. Load ExportEventDEProcessFile
      writes ImportEventDESourceEventDE
      archive directory={SourceDataArchive}\USHP126051base table name=EventDErow terminator=Linefeedcolumn separator=Pipe
    13. Load ExportLocationDEProcessFile
      writes ImportLocationDESourceLocationDE
      archive directory={SourceDataArchive}\USHP126051base table name=LocationDErow terminator=Linefeedcolumn separator=Pipe
    14. Load ExportPatientDEProcessFile
      writes ImportPatientDESourcePatientDE
      archive directory={SourceDataArchive}\USHP126051base table name=PatientDErow terminator=Linefeedcolumn separator=Pipe
    15. Load ExportPatientInsuranceDEProcessFile
      writes ImportPatientInsuranceDESourcePatientInsuranceDE
      archive directory={SourceDataArchive}\USHP126051base table name=PatientInsuranceDErow terminator=Linefeedcolumn separator=Pipe
    16. Load ExportPayerDEProcessFile
      writes ImportPayerDESourcePayerDE
      archive directory={SourceDataArchive}\USHP126051base table name=PayerDErow terminator=Linefeedcolumn separator=Pipe
    17. Load ExportProviderDEProcessFile
      writes ImportProviderDESourceProviderDE
      archive directory={SourceDataArchive}\USHP126051base table name=ProviderDErow terminator=Linefeedcolumn separator=Pipe
    18. Process ExportTransactionDetailDE DataFileProcessFile
      writes ImportTransactionDetailDESourceTransactionDetailDE
      archive directory={SourceDataArchive}\USHP126051base table name=TransactionDetailDErow terminator=Linefeedcolumn separator=Pipe
  4. ▶RevFind-Transform-LoadEntitiessub-job · 4↗Load Facility, Provider, InsuranceMap
    1. Load VA Facility Practice MapProcessFiledisabled
      writes ImportVAFacilityPracticeSourceVAFacilityPractice
      base table name=VAFacilityPracticerow terminator=Newlinecolumn separator=CommaDISABLED
    2. Load FacilityLoadEntities
      writes Facility · reads ImportFacilityConfigSourceLocationfacility
      entity type=Facilitycustom querykey prefix=F
    3. Merge ProviderMergeSql
      writes Provider · reads SourceProvidersourceappointmentsourceencounter
      merge data source type=ThisDatabasetable name=Providermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    4. Load InsuranceMap TableLoadEntities
      writes InsuranceMap · reads InsuranceMapSourcePayer
      entity type=InsuranceMapcustom querykey prefix=IM
  5. ▶RevFind-Transform-LoadEntities-FLsub-job · 4↗Load Facility, Provider, InsuranceMap
    1. Load VA Facility Practice MapProcessFiledisabled
      writes ImportVAFacilityPracticeSourceVAFacilityPractice
      base table name=VAFacilityPracticerow terminator=Newlinecolumn separator=CommaDISABLED
    2. Load FacilityLoadEntities
      writes Facility · reads ImportFacilityConfigSourceLocationFLfacility
      entity type=Facilitycustom querykey prefix=F
    3. Merge ProviderMergeSql
      writes Provider · reads SourceProviderFLsourceappointmentFLsourceencounterFL
      merge data source type=ThisDatabasetable name=Providermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    4. Load InsuranceMap TableLoadEntities
      writes InsuranceMap · reads InsuranceMapSourcePayerFL
      entity type=InsuranceMapcustom querykey prefix=IM
  6. ▶RevFind-Transform-LoadEntities-DEsub-job · 4↗Load Facility, Provider, InsuranceMap
    1. Load VA Facility Practice MapProcessFiledisabled
      writes ImportVAFacilityPracticeSourceVAFacilityPractice
      base table name=VAFacilityPracticerow terminator=Newlinecolumn separator=CommaDISABLED
    2. Load FacilityLoadEntities
      writes Facility · reads ImportFacilityConfigSourceLocationDEfacility
      entity type=Facilitycustom querykey prefix=F
    3. Merge ProviderMergeSql
      writes Provider · reads SourceProviderDEsourceappointmentDEsourceencounterDE
      merge data source type=ThisDatabasetable name=Providermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    4. Load InsuranceMap TableLoadEntities
      writes InsuranceMap · reads InsuranceMapSourcePayerDE
      entity type=InsuranceMapcustom querykey prefix=IM
  7. ▶RevFind-Transform-Accounts-MultiSourcesub-job · 13↗NextGen Transforms-RevFind
    1. Create AccountDeltasqlrevfind_transform_accounts_multisource__create_accountdelta.sql
      ✨ Identifies new or updated encounters across multiple source systems to determine which accounts require processing in the current delta.
    2. Load TransformTransactionsqlrevfind_transform_accounts_multisource__load_transformtransaction.sql
      ✨ Transforms and loads transaction details for delta accounts, categorizing payments, adjustments, and refunds across all source systems.
      writes TransformTransaction · reads AccountDelta
    3. Load TransformRemitAdjustmentsqlrevfind_transform_accounts_multisource__load_transformremitadjustment.sql
      ✨ Processes and categorizes remittance adjustments for delta accounts, mapping reason codes to standardized denial and adjustment categories.
      writes TransformRemitAdjustment · reads AccountDelta
    4. Load TransformChargesqlrevfind_transform_accounts_multisource__load_transformcharge.sql
      ✨ Transforms and loads charge-level data for delta accounts, including service dates, procedure codes, and balances across multiple sources.
      writes TransformCharge · reads AccountDeltaSourceChargeSourceChargeDESourceChargeFL
    5. Load TransformAccountsqlrevfind_transform_accounts_multisource__load_transformaccount.sql
      ✨ Consolidates encounter-level data into a standardized account format, capturing provider, facility, and balance information for all delta records.
      writes TransformAccount · reads AccountDeltaSourceEncounterSourceEncounterDESourceEncounterFL
    6. Load TransformRemitsqlrevfind_transform_accounts_multisource__load_transformremit.sqldisabled
      ✨ Builds the remit transformation table by consolidating electronic remittance advice adjustments and transaction-level payment data for updated encounters.
      writes TransformRemit · reads EncounterDeltaTransformRemitTransformRemitAdjustmentTransformTransactionPaymentAdjustment
    7. Create TempZeroBalanceDatesqlrevfind_transform_accounts_multisource__create_tempzerobalancedate.sqldisabled
      ✨ Calculates the specific date an encounter reached a zero balance by comparing cumulative transaction amounts against total charges.
      writes TempZeroBalanceDate · reads EncounterDeltaSourceTransactionDetailTransformAccountCharge
    8. Load TransformRemitOrdersqlrevfind_transform_accounts_multisource__load_transformremitorder.sqldisabled
      ✨ Determines the sequence of insurance remittances for each account to identify initial, current, and post-zero-balance payment activities.
      writes TransformRemitOrder · reads EncounterDeltaTempZeroBalanceDateTransformRemitTransformRemitOrder
    9. Load TransformAccountInsurancesqlrevfind_transform_accounts_multisource__load_transformaccountinsurance.sql
      ✨ Maps insurance coverage and coordination of benefits to accounts, incorporating the latest remittance information for delta processing.
      writes TransformAccountInsurance · reads AccountDeltaSourceEncounterInsuranceSourceEncounterInsuranceDESourceEncounterInsuranceFLTransformAccountTransformRemitAdjustment
      ↳ feeds from Load TransformAccount, Load TransformRemitAdjustment
    10. ?? Load TransformPatientResponsibilityChargessqlrevfind_transform_accounts_multisource__load_transformpatientresponsibilitycharges.sqldisabled
      ✨ Flags specific charges as patient responsibility based on source system indicators and insurance remittance transfer records.
      writes TransformPatientResponsibilityCharge · reads EncounterDeltaSourceEncounterTransformAccountChargeTransformPatientResponsibilityChargeTransformRemit
    11. Load TransformRemitAdjustmentAggregatesqlrevfind_transform_accounts_multisource__load_transformremitadjustmentaggregate.sql
      ✨ Builds aggregated remittance adjustment data by calculating initial and current denial totals and categories for each charge and encounter.
      writes TransformRemitAdjustmentAggregate · reads AccountDeltaTransformChargeTransformRemitAdjustment
      ↳ feeds from Load TransformCharge, Load TransformRemitAdjustment
    12. Load TransformChargeTransactionMeasuressqlrevfind_transform_accounts_multisource__load_transformchargetransactionmeasures.sql
      ✨ Loads charge-level transaction measures by aggregating adjustments, payments, refunds, and remittance data from multiple source systems.
      writes TransformChargeTransactionMeasure · reads AccountDeltaSourceEncounterSourceEncounterDESourceEncounterFLTransformChargeTransformRemitAdjustmentAggregateTransformTransaction
      ↳ feeds from Load TransformCharge, Load TransformRemitAdjustmentAggregate, Load TransformTransaction
    13. Load TransformPatientsqlrevfind_transform_accounts_multisource__load_transformpatient.sql
      ✨ Merges and updates patient demographic information from multiple source systems into a unified patient transformation table.
      writes TransformPatient · reads SourcePatientSourcePatientDESourcePatientFL
  8. ▶RevFind-Transform-ChargeDimensions-MultiSourcesub-job · 9↗
    1. Load TempChargeSchedulessqlrevfind_transform_chargedimensions_multisource__load_tempchargeschedules.sql
      ✨ Builds a temporary table of charge schedules by identifying distinct start and end dates from multiple source chargemasters.
      writes TempChargeSchedules
    2. Load FeeScheduleRecordsqlrevfind_transform_chargedimensions_multisource__load_feeschedulerecord.sql
      ✨ Loads fee schedule records with pricing, modifiers, and priority levels mapped from various source chargemasters and temporary schedules.
      writes FeeScheduleRecord · reads SourceChargeMasterSourceChargeMasterDESourceChargeMasterFLTempChargeSchedules
      ↳ feeds from Load TempChargeSchedules
    3. Load ScheduleMapsqlrevfind_transform_chargedimensions_multisource__load_schedulemap.sql
      ✨ Maps charge schedules to specific facilities and insurance categories to define active pricing periods for professional and facility fees.
      writes ScheduleMap · reads FacilityTempChargeSchedules
      ↳ feeds from Load TempChargeSchedules
    4. Load ContractMapsqlrevfind_transform_chargedimensions_multisource__load_contractmap.sqldisabled
      ✨ Updates the contract map to define fee logic and billing types for facilities and professional services.
      writes ContractMap · reads Facility
    5. Load ScheduleLoadEntities
      writes Schedule · reads TempChargeSchedules
      ↳ feeds from Load TempChargeSchedules
      entity type=Schedulecustom query
    6. Merge ChargeTypeMergeSql
      writes ChargeType · reads SourceChargeMasterSourceChargeMasterDESourceChargeMasterFL
      merge data source type=ThisDatabasetable name=ChargeTypemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    7. Load ChargeType Dimensionsqlrevfind_transform_chargedimensions_multisource__load_chargetype_dimension.sql
      ✨ Loads charge type members into the dimension table to support hierarchical reporting and data categorization.
      writes DimensionMember · reads ChargeType
      ↳ feeds from Merge ChargeType
    8. Load ProcedureCode Dimensionsqlrevfind_transform_chargedimensions_multisource__load_procedurecode_dimension.sql
      ✨ Builds the procedure code dimension by extracting codes from charge types and mapping them to standard descriptions and categories.
      writes DimensionMember · reads ChargeTypeDimensionMemberProcedureCode
      ↳ feeds from Load ChargeType Dimension, Load ProcedureCategory and Subcategory DM, Merge ChargeType
    9. Load ProcedureCategory and Subcategory DMsqlrevfind_transform_chargedimensions_multisource__load_procedurecategory_and_subcategory_dm.sql
      ✨ Builds the procedure category dimension by extracting unique parent groupings from existing procedure code members.
      writes DimensionMember · reads DimensionMember
      ↳ feeds from Load ChargeType Dimension, Load ProcedureCode Dimension
  9. LoadPatientsLoadPatients
    Upsert patient records into the patient dimension.
    · reads PatientTransformPatient
    custom patient query
  10. ▶RevFind-Process-LoadAccounts-MultiSourcesub-job · 12↗Load Account-Level Tables-RevFind KB [date] : Added Medicare Scenario scoring
    1. ReprocessChangedConfigurationsReprocessChangedConfigurations
      Identify and queue visits or accounts affected by configuration changes to ensure historical data reflects updated pricing or insurance rules.
      reprocess accounts
    2. Add ReprocessChangedConfig Accounts to AccountDeltasqlrevfind_process_loadaccounts_multisource__add_reprocesschangedconfig_accounts_to_accountdelta.sql
      ✨ Adds accounts flagged for reprocessing due to configuration changes into the account delta table across multiple source systems.
      writes AccountDelta · reads AccountDeltaAccountsToReprocessSourceEncounterSourceEncounterDESourceEncounterFLTransformAccount
    3. Load ChargeMeasuresqlrevfind_process_loadaccounts_multisource__load_chargemeasure.sql
      ✨ Loads calculated financial metrics, including payments, adjustments, and denial categories, into the charge measures table for accounts in the delta.
      writes ChargeMeasures · reads AccountDeltaTransformChargeTransformChargeTransactionMeasure
      ↳ feeds from Add ReprocessChangedConfig Accounts to AccountDelta
    4. LoadAccountDataLoadAccountData
      Imports historical billing data from external systems into core account tables while preserving manual user modifications and application fields.
      · reads AccountChargeAccountDeltaChargeMeasuresChargeTypeFacilityInsuranceMapPatientTransformAccount
      ↳ feeds from Add ReprocessChangedConfig Accounts to AccountDelta, Load ChargeMeasure
      custom account charge querycustom account querycustom account remit querycustom account insurance query
    5. ▶RevFind-Process-LoadDimensionMembers-MultiSourcesub-job · 19↗
      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 Dimensionssqlrevfind_process_loaddimensionmembers_multisource__load_time_dimensions.sql
        ✨ Builds and updates time-based dimensions for days, months, and years to support temporal reporting across the pipeline.
        writes DimensionMember · reads SourceAppointment
      3. Load VisitStatus Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_visitstatus_dimension.sqldisabled
        ✨ Loads appointment status values into the visit status dimension to track patient encounter progress.
        writes DimensionMember · reads TransformVisit
      4. Load DenialCategory Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_denialcategory_dimension.sql
        ✨ Loads denial category dimensions from adjustment code rollup data to categorize claim denials.
        writes DimensionMember · reads AdjustmentCodeRollup
      5. Load Provider Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_provider_dimension.sql
        ✨ Loads provider dimension members and their classifications into the central dimension table for reporting and filtering.
        writes DimensionMember · reads Provider
      6. Load ProviderType Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_providertype_dimension.sql
        ✨ Builds the provider type dimension by extracting and mapping parent specialty groupings from existing provider records.
        writes DimensionMember · reads DimensionMember
        ↳ feeds from Load AccountStatus Dimension, Load AllowedStatus Dimension, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load Facility Dimensions, Load FacilityGroup Dimensions, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load ProviderDegree Dimension, Load SourceInsurance Dimension, Load State Dimensions, Load Time Dimensions, LoadInsuranceDimensionMembers
      7. Load ProviderDegree Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_providerdegree_dimension.sql
        ✨ Loads the provider degree dimension by mapping professional credentials from source provider records into the standardized dimension table.
        writes DimensionMember · reads SourceProviderprovider
      8. Load Facility Dimensionssqlrevfind_process_loaddimensionmembers_multisource__load_facility_dimensions.sql
        ✨ Updates the facility dimension with organizational names and group mappings to enable facility-level reporting and analysis.
        writes DimensionMember · reads Facility
      9. Load FacilityGroup Dimensionssqlrevfind_process_loaddimensionmembers_multisource__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 AccountStatus Dimension, Load AllowedStatus Dimension, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load Facility Dimensions, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load ProviderDegree Dimension, Load ProviderType Dimension, Load SourceInsurance Dimension, Load State Dimensions, Load Time Dimensions, LoadInsuranceDimensionMembers
      10. Load FacilityType Dimensionssqlrevfind_process_loaddimensionmembers_multisource__load_facilitytype_dimensions.sql
        ✨ Loads unique facility types into the dimension table to allow for the categorization of different healthcare service locations.
        writes DimensionMember · reads Facility
      11. Load Practice Dimensionssqlrevfind_process_loaddimensionmembers_multisource__load_practice_dimensions.sqldisabled
        ✨ Loads practice dimension members from the practice map for entities that have associated encounter records.
        writes DimensionMember · reads PracticeMapsourceencounter
      12. Load PlaceOfService Dimensionssqlrevfind_process_loaddimensionmembers_multisource__load_placeofservice_dimensions.sql
        ✨ Loads the place of service dimension by synchronizing standardized facility codes and names from common reference data.
        writes DimensionMember · reads POSCodes
      13. Load VisitType Dimensionssqlrevfind_process_loaddimensionmembers_multisource__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
      14. Load AccountStatus Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_accountstatus_dimension.sql
        ✨ Consolidates encounter statuses from multiple source systems into a unified account status dimension for standardized reporting.
        writes DimensionMember · reads SourceEncounterSourceEncounterDESourceEncounterFL
      15. Load AllowedStatus Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_allowedstatus_dimension.sql
        ✨ Builds allowed status dimensions to categorize how actual payments compare to expected insurance contract rates.
        writes DimensionMember
      16. Load DiagnosisCode Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_diagnosiscode_dimension.sql
        ✨ Builds diagnosis code dimensions by extracting codes from account charges and mapping them to standardized medical categories.
        writes DimensionMember · reads AccountChargeDimensionMember
        ↳ feeds from Load AccountStatus Dimension, Load AllowedStatus Dimension, Load DenialCategory Dimension, Load Facility Dimensions, Load FacilityGroup Dimensions, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load ProviderDegree Dimension, Load ProviderType Dimension, Load SourceInsurance Dimension, Load State Dimensions, Load Time Dimensions, LoadInsuranceDimensionMembers
      17. Load Modifier Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_modifier_dimension.sql
        ✨ Loads the procedure modifier dimension by extracting unique codes from account charges and mapping them to their descriptive labels.
        writes DimensionMember · reads AccountChargeProcedureModifier
      18. Load State Dimensionssqlrevfind_process_loaddimensionmembers_multisource__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
      19. Load SourceInsurance Dimensionsqlrevfind_process_loaddimensionmembers_multisource__load_sourceinsurance_dimension.sql
        ✨ Maps and updates the source insurance dimension using mapped insurance names and their respective data sources.
        writes DimensionMember · reads InsuranceMap
    6. Clean Estimatessqlrevfind_process_loadaccounts_multisource__clean_estimates.sqldisabled
      ✨ Removes existing non-scenario insurance estimates and related charge calculations for accounts currently being processed in the pipeline.
      writes AccountEstimateAccountEstimateCalculationAccountEstimateCharge · reads AccountDeltaAccountEstimate
    7. ▶Job-[id]sub-job · 3↗
      1. Add DOB to ChargeCodes for Medicaid Patientssqljob_[id]__add_dob_to_chargecodes_for_medicaid_patients.sql
        ✨ Appends patient age to charge codes for Medicaid-related accounts in the current processing delta to support specific billing requirements.
        · reads AccountChargeTransformAccountInsuranceaccountaccountDeltainsurancemappatient
        ↳ feeds from Merge AccountDeltaRouting Table
      2. Merge AccountDeltaRouting Tablesqljob_[id]__merge_accountdeltarouting_table.sql
        ✨ Updates account and insurance tables with the final routed insurance keys determined during the custom routing process.
        writes AccountAccountInsurance · reads AccountDeltaRoutingaccountaccountDelta
      3. Add non-verified contract labels for unsure contractssqljob_[id]__add_non_verified_contract_labels_for_unsure_contracts.sql
        ✨ Bulk applies non-verified contract labels and corresponding notes to specific Medicare and Medicaid accounts within the account delta.
        writes AccountLabelAccountNoteDeltaInsertedLabelsDeltaLabelIDsDeltaLabels · reads AccountDeltaAccountDeltaRoutingAccountLabelDeltaInsertedLabelsDeltaLabelIDsDeltaLabelsaccount
        ↳ feeds from Merge AccountDeltaRouting Table
    8. ▶Score-Accounts-RevFindsub-job · 11↗
      1. BCBSBCBSdisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incrementalupdate dimensionsDISABLED
      2. Cigna MedicareBatchProcessAccountsdisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incrementalupdate dimensionsDISABLED
      3. HumanaHumanadisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incrementalupdate dimensionsDISABLED
      4. Humana MedicareBatchProcessAccountsdisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incrementalupdate dimensionsDISABLED
      5. MedicareMedicaredisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incrementalupdate dimensionsDISABLED
      6. UHCUHCdisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incrementalupdate dimensionsDISABLED
      7. UHC MedicareBatchProcessAccountsdisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incrementalupdate dimensionsDISABLED
      8. AetnaAetnadisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incrementalupdate dimensionsDISABLED
      9. Aetna MedicareBatchProcessAccountsdisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        min service date=2023-01-01incrementalupdate dimensionsDISABLED
      10. ProcessAccountDataProcessAccountDatadisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        incrementalDISABLED
      11. ProcessAccountDataFromQueryProcessAccountDataFromQuery
        · reads AccountAccountChargeContractMap
        custom query
    9. ▶Score-Accounts-RevFind-Scenariosub-job · 1↗
      1. Score All Accounts Medicare ScenarioBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        scenario insurance=Medicareincrementalupdate dimensions
    10. ▶Load-Custom-Properties-RevFindsub-job · 2↗Load custom properties from Load-Visits-RevFind
      1. UpdateVisitCustomPropertyFromQueryUpdateVisitCustomPropertyFromQuerydisabled
        · reads InsuranceSearch_ResultTransformVisitInsuranceCoverageStatusVisitBatch
        custom visit update custom property queryDISABLED
      2. LoadCustomNotesCustomPropertyLoadCustomNotesCustomPropertydisabled
        · reads BundleNotesMappingHL7DeltaHL7VisitVisitBatch
        entity=Visitcustom property key=CustomNotescustom querybatch modeDISABLED
    11. Load AccountDetailsqlrevfind_process_loadaccounts_multisource__load_accountdetail.sqldisabled
      ✨ Updates the account detail table with comprehensive patient, insurance, and financial metrics for reporting and analysis.
      writes AccountDetail · reads AccountAccountChargeAccountDeltaAccountEstimateDimensionMemberPatientSourceEncounterInsuranceSourcePayer
    12. Load AccountChargeDetailsqlrevfind_process_loadaccounts_multisource__load_accountchargedetail.sqldisabled
      ✨ Updates granular charge-level details including procedure codes, modifiers, and payment information for individual service line items.
      writes AccountChargeDetail · reads AccountAccountChargeAccountDeltaAccountEstimateCalculationAccountEstimateChargeDimensionMemberPatientSourceEncounterInsurance
  11. ▶Revfind-Score-Accountssub-job · 2↗
    1. Score AccountsBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2024-01-01incremental
    2. Score Medicare ScenarioBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2024-01-01scenario insurance=Medicareincremental
  12. Update-Dashboard-Tablessub-job
  13. Data-Integrity-Reportsub-job