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

sub-job

USDH · Load-Account-Data

Load Account-Level Tables

called by Daily-Processing

sub-job USDH/Load-Account-Data · db USDH_ProdMirror · updated 2025-12-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

Load-Account-Data

10 tasks · USDH
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. ReprocessChangedConfigurationsReprocessChangedConfigurationsdisabled
    Identify and queue visits or accounts affected by configuration changes to ensure historical data reflects updated pricing or insurance rules.
    reprocess accountsDISABLED
  2. LoadPatientsLoadPatients
    Upsert patient records into the patient dimension.
    · reads PatientTransformPatient
    custom patient query
  3. Load ChargeMeasuressqlload_account_data__load_chargemeasures.sql
    ✨ Aggregates financial metrics including paid, adjusted, and denied amounts at the charge level to support recovery insights and reporting.
    writes ChargeMeasures · reads ChargeMeasuresTempTransactionsAggTotalTransformAccountTransformCharge
  4. LoadAccountDataLoadAccountData
    Imports historical billing data from external systems into core account tables while preserving manual user modifications and application fields.
    · reads AccountChargeAdjustmentCodeRollupChargeMeasuresChargeTypeInsuranceMapPatientProviderTempTransactions
    ↳ feeds from Load ChargeMeasures
    custom account charge querycustom account querycustom account remit querycustom account insurance query
  5. ▶Load-Dimension-Memberssub-job · 16↗
    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 Dimensionssqlload_dimension_members__load_time_dimensions.sql
      ✨ Populates the time dimension table with day, month, and year members to support temporal reporting and analysis.
      writes DimensionMember · reads TransformAccount
    3. Load VisitStatus Dimensionsqlload_dimension_members__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 Dimensionsqlload_dimension_members__load_denialcategory_dimension.sql
      ✨ Loads denial category dimensions from adjustment code rollup data to categorize claim denials.
      writes DimensionMember · reads AdjustmentCodeRollup
    5. Load Provider + ProviderType Dimensionssqlload_dimension_members__load_provider_providertype_dimensions.sql
      ✨ Synchronizes provider and provider type dimensions by mapping individual providers to their respective specialty or classification categories.
      writes DimensionMember · reads DimensionMemberProvider
      ↳ feeds from Load AllowedStatus Dimension, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load Facility + FacilityGroup Dimensions, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load State Dimension, Load Time Dimensions, Load-Source-Insurance-Dimension, LoadInsuranceDimensionMembers
    6. Load Facility + FacilityGroup Dimensionssqlload_dimension_members__load_facility_facilitygroup_dimensions.sql
      ✨ Updates facility and facility group dimensions to organize locations within their parent organizational structures for reporting.
      writes DimensionMember · reads DimensionMemberFacility
      ↳ feeds from Load AllowedStatus Dimension, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider + ProviderType Dimensions, Load State Dimension, Load Time Dimensions, Load-Source-Insurance-Dimension, LoadInsuranceDimensionMembers
    7. Load FacilityType Dimensionssqlload_dimension_members__load_facilitytype_dimensions.sql
      ✨ Loads unique facility types into the dimension table to categorize locations by their specific service or operational model.
      writes DimensionMember · reads Facility
    8. Load Practice Dimensionssqlload_dimension_members__load_practice_dimensions.sqldisabled
      ✨ Loads practice dimension members from the practice map for entities that have associated encounter records.
      writes DimensionMember · reads PracticeMapsourceencounter
    9. Load PlaceOfService Dimensionssqlload_dimension_members__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
    10. Load VisitType Dimensionssqlload_dimension_members__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
    11. Load AccountStatus Dimensionsqlload_dimension_members__load_accountstatus_dimension.sqldisabled
      ✨ Updates account status dimensions using encounter status information to track the lifecycle of patient accounts.
      writes DimensionMember · reads SourceEncounter
    12. Load AllowedStatus Dimensionsqlload_dimension_members__load_allowedstatus_dimension.sql
      ✨ Builds allowed status dimensions to categorize how actual payments compare to expected insurance contract rates.
      writes DimensionMember
    13. Load DiagnosisCode Dimensionsqlload_dimension_members__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 AllowedStatus Dimension, Load DenialCategory Dimension, Load Facility + FacilityGroup Dimensions, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider + ProviderType Dimensions, Load State Dimension, Load Time Dimensions, Load-Source-Insurance-Dimension, LoadInsuranceDimensionMembers
    14. Load Modifier Dimensionsqlload_dimension_members__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
    15. Load-Source-Insurance-Dimensionsqlload_dimension_members__load_source_insurance_dimension.sql
      ✨ Updates the insurance dimension table with source insurance names and identifiers from EDI data for reporting.
      writes DimensionMember · reads AccountInsuranceInsuranceMap
    16. Load State Dimensionsqlload_dimension_members__load_state_dimension.sql
      ✨ Updates state dimensions using facility location data to allow for geographic segmentation of healthcare services and revenue.
      writes DimensionMember · reads Facility
  6. ▶Load-Custom-Propertiessub-job · 2↗
    1. Staging of Division Custom Prop Valuessqlload_custom_properties__staging_of_division_custom_prop_values.sql
      ✨ Stages account-level division properties by identifying practice ID changes that need to be updated in the custom property mapping.
      writes TempCustomProperty · reads TempCustomProperty
    2. UpdateCustomPropertyFromQueryUpdateCustomPropertyFromQuery
      · reads TempCustomProperty
      ↳ feeds from Staging of Division Custom Prop Values
      entity=Accountcustom property key=Divisionauto querybatch mode
  7. SyncBatchSyncBatch
    Synchronizes processed data from batch staging tables to production tables, updating visits, quotes, and custom property mappings.
    writes CustomPropertyMap · reads CustomPropertyMapBatch
    sync custom property map
  8. ▶Score-Accountssub-job · 17↗
    1. MedicareMedicare
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    2. RGAL - Capital BlueBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2024-01-01incrementalupdate dimensions
    3. RGAL - UHCBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    4. RGAL - CignaBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    5. RGAL - AetnaBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2024-01-01incrementalupdate dimensions
    6. RGAL - Highmark CommercialBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    7. RGAL - Independence CommercialBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    8. RGAL - Preferred Healthcare CommercialBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2024-10-01incrementalupdate dimensions
    9. DCDC - AetnaBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2024-01-01incrementalupdate dimensions
    10. DCDC - AmerihealthBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    11. DCDC - CignaBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    12. DCDC - DE First HealthBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    13. DCDC - HighmarkBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    14. DCDC - TricareBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    15. DCDC - UHCBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    16. DCDC - World Trade CenterBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2023-01-01incrementalupdate dimensions
    17. UPMCUPMC
      writes AccountEstimate · reads QuoteBatchVisitBatch
      min service date=2025-01-01incremental
  9. 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
  10. 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