sub-job

OrthoAlliance · Load-Account-Data

Load Account-Level Tables

called by Daily-Accounts-Processing

sub-job OrthoAlliance/Load-Account-Data · db OrthoAlliance_ProdMirror · updated 2025-10-06

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

11 tasks · OrthoAlliance

sub-job / Flow action SQL / native step▶ expand a sub-job inline · ↗ open it as its own page · a typed step opens its doc

Load AccountChargesqlload_account_data__load_accountcharge.sql
✨ Calculates financial metrics for individual charges and loads detailed charge records for accounts identified in the current delta.
writes AccountChargeChargeMeasures · reads AccountDeltaChargeMeasuresChargeTypePatientProviderTransformAccountTransformCharge
Load Accountsqlload_account_data__load_account.sql
✨ Aggregates financial totals and denial categories to build account-level records, including insurance mapping and age-based plan logic.
writes Account · reads AccountChargeAccountDeltaAccountDetailInsuranceMapPatientSourceEncounterInsuranceTransformAccountTransformChargeTransactionMeasure
↳ feeds from Load AccountCharge, Load AccountDetail
Load AccountRemitsqlload_account_data__load_accountremit.sql
✨ Consolidates insurance remittance advice and posted transaction details into a unified table for account-level payment and adjustment tracking.
writes AccountRemit · reads AccountDeltaSourcePayerTransformChargeTransformRemitAdjustmentTransformTransaction
▶Load-Dimension-Memberssub-job · 19↗
1. Load Insurance Dimensionsqlload_dimension_members__load_insurance_dimension.sql
  ✨ Loads the insurance dimension with unique insurance keys and a placeholder for unmapped records to support payer-level analysis.
  writes DimensionMember · reads Insurance
2. Load EligibilityInsurance Dimensionsqlload_dimension_members__load_eligibilityinsurance_dimension.sql
  ✨ Loads insurance members that support electronic eligibility and benefits verification via API integration.
  writes DimensionMember · reads DimensionMemberInsurance
  ↳ feeds from Load AllowedStatus Dimension, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load Facility Dimensions, Load FacilityGroup Dimensions, Load FacilityType Dimensions, Load Insurance Dimension, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load ProviderType Dimension, Load QuoteInsurance Dimension, Load State Dimension, Load Time Dimensions, Load VisitStatus Dimension, Load VisitType Dimensions
3. Load QuoteInsurance Dimensionsqlload_dimension_members__load_quoteinsurance_dimension.sql
  ✨ Builds a dimension of insurance providers associated with active contracts to facilitate accurate patient cost estimates.
  writes DimensionMember · reads ContractMapInsurance
4. Load Time Dimensionssqlload_dimension_members__load_time_dimensions.sql
  ✨ Builds time-based dimension members for days, months, and years to support temporal reporting across the pipeline.
  writes DimensionMember · reads TransformVisit
5. Load VisitStatus Dimensionsqlload_dimension_members__load_visitstatus_dimension.sql
  ✨ Loads appointment status values into the visit status dimension to track patient encounter progress.
  writes DimensionMember · reads TransformVisit
6. 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
7. Load Provider Dimensionsqlload_dimension_members__load_provider_dimension.sql
  ✨ Loads provider dimension members and their classifications into the central dimension table for reporting and filtering.
  writes DimensionMember · reads Provider
8. Load ProviderType Dimensionsqlload_dimension_members__load_providertype_dimension.sql
  ✨ Builds the provider type dimension by extracting unique parent categories from existing provider dimension records.
  writes DimensionMember · reads DimensionMember
  ↳ feeds from Load AllowedStatus Dimension, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load EligibilityInsurance Dimension, Load Facility Dimensions, Load FacilityGroup Dimensions, Load FacilityType Dimensions, Load Insurance Dimension, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load QuoteInsurance Dimension, Load State Dimension, Load Time Dimensions, Load VisitStatus Dimension, Load VisitType Dimensions
9. Load Facility Dimensionssqlload_dimension_members__load_facility_dimensions.sql
  ✨ Builds facility dimension members by mapping source locations to specific regional and ownership categories for organizational reporting.
  writes DimensionMember · reads SourceLocationSourceLocationMap
10. Load FacilityGroup Dimensionssqlload_dimension_members__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 EligibilityInsurance Dimension, Load Facility Dimensions, Load FacilityType Dimensions, Load Insurance Dimension, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load ProviderType Dimension, Load QuoteInsurance Dimension, Load State Dimension, Load Time Dimensions, Load VisitStatus Dimension, Load VisitType Dimensions
11. Load FacilityType Dimensionssqlload_dimension_members__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
12. 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
13. 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
14. Load VisitType Dimensionssqlload_dimension_members__load_visittype_dimensions.sql
  ✨ Loads unique visit type dimension members from appointment data to standardize encounter classifications across the system.
  writes DimensionMember · reads HL7Visit
15. 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
16. 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
17. 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 EligibilityInsurance Dimension, Load Facility Dimensions, Load FacilityGroup Dimensions, Load FacilityType Dimensions, Load Insurance Dimension, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load ProviderType Dimension, Load QuoteInsurance Dimension, Load State Dimension, Load Time Dimensions, Load VisitStatus Dimension, Load VisitType Dimensions
18. 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
19. 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
Clean Estimatessqlload_account_data__clean_estimates.sql
✨ Removes outdated non-scenario insurance estimates and associated charge calculations for encounters identified in the delta table.
writes AccountEstimateAccountEstimateCalculationAccountEstimateCharge · reads AccountEstimateEncounterDelta
▶Score-Accountssub-job · 9↗
1. Score AetnaBatchProcessAccounts
  writes AccountEstimate · reads QuoteBatchVisitBatch
  min service date=2022-07-01incremental
2. Score AnthemBatchProcessAccounts
  writes AccountEstimate · reads QuoteBatchVisitBatch
  min service date=2022-01-01incremental
3. Score CignaBatchProcessAccounts
  writes AccountEstimate · reads QuoteBatchVisitBatch
  min service date=2022-01-01incremental
4. Score Humana MedicareBatchProcessAccounts
  writes AccountEstimate · reads QuoteBatchVisitBatch
  min service date=2022-01-01incremental
5. Score MedicareBatchProcessAccounts
  writes AccountEstimate · reads QuoteBatchVisitBatch
  min service date=2023-01-01incremental
6. Score MMOBatchProcessAccounts
  writes AccountEstimate · reads QuoteBatchVisitBatch
  min service date=2023-03-01incremental
7. Score UHCBatchProcessAccounts
  writes AccountEstimate · reads QuoteBatchVisitBatch
  min service date=2022-08-01incremental
8. BatchProcessAccountDataBatchProcessAccountData
  Calculates patient account estimates across multiple facilities and insurance types to update pricing after contract or configuration changes.
  writes AccountEstimate · reads QuoteBatchVisitBatch
  min service date=2023-01-01incremental
9. BatchProcessAccountData (Catch All)BatchProcessAccounts
  writes AccountEstimate · reads QuoteBatchVisitBatch
  incremental
Score-Medi-Cal-Scenariosub-jobdisabled
▶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
Load AccountDetailsqlload_account_data__load_accountdetail.sql
✨ Builds a comprehensive account summary by joining patient, insurance, and financial data to track balances, denials, and estimated opportunities.
writes AccountDetail · reads AccountAccountChargeAccountDeltaAccountEstimateDimensionMemberPatientSourceEncounterInsuranceSourcePayer
↳ feeds from Clean Estimates, Load Account, Load AccountCharge
Load AccountChargeDetailsqlload_account_data__load_accountchargedetail.sql
✨ Loads granular charge-level details, including procedure codes, modifiers, and provider information, for comprehensive reporting on account financial activity.
writes AccountChargeDetail · reads AccountAccountChargeAccountDeltaAccountEstimateCalculationAccountEstimateChargeDimensionMemberPatientSourceEncounterInsurance
↳ feeds from Clean Estimates, Load Account, Load AccountCharge
Update Modifier 22 Custom PropertyUpdateCustomPropertyFromQuery
· reads AccountChargeAccountDelta
↳ feeds from Load AccountCharge
entity=Accountcustom property key=Modifier22custom query