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

sub-job

Commonwealth · Load-Account-Transform-Tables

Athena Transforms

called by Daily-Accounts-Processing

sub-job Commonwealth/Load-Account-Transform-Tables · db Commonwealth_ProdMirror · updated 2026-01-22

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-Transform-Tables

12 tasks · Commonwealth
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. Check AccountDeltasqlload_account_transform_tables__check_accountdelta.sql
    ✨ Ensures the account delta table exists with the correct schema, including necessary columns and indexes for tracking changed records.
    · reads AccountDelta
    ↳ feeds from Create AccountDelta
  2. Create AccountDeltasqlload_account_transform_tables__create_accountdelta.sql
    ✨ Identifies and loads accounts, encounters, and claims updated since the last pipeline execution into the delta table.
    writes AccountDelta · reads ProcessLogSourceAppointmentSourceClaimSourceClinicalEncounterSourceERADetailSourcePatientInsuranceSourceTransaction
  3. Load TransformTransaction - Updated for Claims sans Visitssqlload_account_transform_tables__load_transformtransaction_updated_for_claims_sans_visits.sql
    ✨ Standardizes and loads financial transaction data, including payments and adjustments, for updated accounts into the transaction transformation table.
    writes TransformTransaction · reads AccountDeltaSourceClaimSourcePatientInsuranceSourceTransactionsourceclaimnote
    ↳ feeds from Create AccountDelta
  4. Load TransformRemitAdjustment - Updated for Claims sans Visitssqlload_account_transform_tables__load_transformremitadjustment_updated_for_claims_sans_visits.sql
    ✨ Processes and loads remittance adjustment details, including denial categories and group codes, from electronic remittance advice into the transformation table.
    writes TransformRemitAdjustment · reads AccountDeltaAdjustmentCodeRollupSourceClaimSourceERABatchSourceERADetailSourceERAKickSourceERARecordSourcePatientInsurance
    ↳ feeds from Create AccountDelta
  5. Load TransformCharge - Updated for Claims sans Visitssqlload_account_transform_tables__load_transformcharge_updated_for_claims_sans_visits.sql
    ✨ Transforms and loads charge-level details, including procedure codes, modifiers, and diagnosis information, for updated accounts into the charge table.
    writes TransformCharge · reads AccountDeltaSourceClaimSourceDepartmentSourceTimesheetEntryTransactionSourceTransactionSourceTransactionCharge
    ↳ feeds from Create AccountDelta
  6. Load TransformAccount - Updated for Claims sans Visitssqlload_account_transform_tables__load_transformaccount_updated_for_claims_sans_visits.sql
    ✨ Builds unified account records by merging clinical encounter and billing claim data for comprehensive financial tracking.
    writes TransformAccount · reads AccountDeltaSourceClaimSourceClinicalEncounterSourceDepartmentSourcePatientInsuranceSourceProvider
    ↳ feeds from Create AccountDelta
  7. Load TransformRemitAdjustmentAggregatesqlload_account_transform_tables__load_transformremitadjustmentaggregate.sql
    ✨ Aggregates insurance remittance adjustments and denial categories to summarize payment impacts at the encounter level.
    writes TransformRemitAdjustmentAggregate · reads AccountDeltaTransformChargeTransformRemitAdjustment
    ↳ feeds from Create AccountDelta, Load TransformCharge - Updated for Claims sans Visits, Load TransformRemitAdjustment - Updated for Claims sans Visits
  8. Load TransformChargeTransactionMeasuressqlload_account_transform_tables__load_transformchargetransactionmeasures.sql
    ✨ Calculates total financial measures per charge, including payments, adjustments, and denials, for detailed revenue analysis.
    writes TransformChargeTransactionMeasure · reads AccountDeltaTransformChargeTransformRemitAdjustmentAggregateTransformTransaction
    ↳ feeds from Create AccountDelta, Load TransformCharge - Updated for Claims sans Visits, Load TransformRemitAdjustmentAggregate, Load TransformTransaction - Updated for Claims sans Visits
  9. Load TransformPatientsqlload_account_transform_tables__load_transformpatient.sqldisabled
    ✨ Updates and maintains patient demographic information in the transformation layer to ensure accurate patient identification.
    writes TransformPatient · reads SourcePatient
  10. ExecuteMergeSql (TransformPatient)MergeSql
    writes TransformPatient · reads SourcePatient
    merge data source type=ThisDatabasetable name=TransformPatientmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
  11. Load TransformPatientInsurancesqlload_account_transform_tables__load_transformpatientinsurance.sqldisabled
    ✨ Manages patient insurance coverage records, updating active policies and removing expired or cancelled insurance information.
    writes TransformPatientInsurance · reads InsuranceMapSourcePatientInsuranceSourcePayer
  12. ExecuteMergeSql (TransformPatientInsurance)MergeSql
    writes TransformPatientInsurance · reads InsuranceMapSourcePatientInsuranceSourcePayer
    merge data source type=ThisDatabasetable name=TransformPatientInsurancemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock