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

sub-job

USHP · RevFind-Process-LoadAccounts-MultiSource

Load Account-Level Tables-RevFind KB [date] : Added Medicare Scenario scoring

called by RevFind-Daily-Processing-Accounts

sub-job USHP/RevFind-Process-LoadAccounts-MultiSource · db USHP_ProdMirror · updated 2026-03-10

task tree

What the sub-job 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).

sub-job

RevFind-Process-LoadAccounts-MultiSource

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