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

sub-job

OrthoAlliance · RevFind-SBO-Transform-Accounts

Athena Transforms

called by RevFind-SBO-Daily-Processing-Accounts

sub-job OrthoAlliance/RevFind-SBO-Transform-Accounts · db OrthoAlliance_ProdMirror · updated 2026-06-02

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

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
  1. Create AccountDeltasqlrevfind_sbo_transform_accounts__create_accountdelta.sql
    ✨ Identifies new or modified accounts, encounters, and claims to determine which records require incremental processing in the pipeline.
    writes AccountDeltaOrthos · reads AccountDeltaDateOrthosAccountDeltaOrthosSourceAppointmentOrthosSourceClaimOrthosSourceClinicalEncounterOrthosSourceERADetailOrthosSourcePatientInsuranceOrthosSourceTransactionOrthos
    ↳ feeds from Update AccountDeltaDate
  2. Load TransformTransaction - Updated for Claims sans Visitssqlrevfind_sbo_transform_accounts__load_transformtransaction_updated_for_claims_sans_visits.sql
    ✨ Populates transformed transaction records for payments and adjustments, linking them to specific accounts and insurance payers.
    writes TransformTransactionOrthos · reads AccountDeltaOrthosSourceClaimOrthosSourcePatientInsuranceOrthosSourceTransactionOrthos
    ↳ feeds from Create AccountDelta
  3. Load TransformRemitAdjustment - Updated for Claims sans Visitssqlrevfind_sbo_transform_accounts__load_transformremitadjustment_updated_for_claims_sans_visits.sql
    ✨ Loads transformed remittance adjustments from electronic remittance advice data, including denial categories and adjustment amounts for specific claims.
    writes TransformRemitAdjustmentOrthos · reads AccountDeltaOrthosAdjustmentCodeRollupSourceClaimOrthosSourceERABatchOrthosSourceERADetailOrthosSourceERAKickOrthosSourceERARecordOrthosSourcePatientInsuranceOrthos
    ↳ feeds from Create AccountDelta
  4. Load TransformCharge - Updated for Claims sans Visitssqlrevfind_sbo_transform_accounts__load_transformcharge_updated_for_claims_sans_visits.sql
    ✨ Builds transformed charge records containing service dates, procedure codes, and financial amounts for clinical encounters and claims.
    writes TransformChargeOrthos · reads AccountDeltaOrthosSourceClaimOrthosSourceDepartmentOrthosSourceTransactionOrthos
    ↳ feeds from Create AccountDelta
  5. Load TransformAccount - Updated for Claims sans Visitssqlrevfind_sbo_transform_accounts__load_transformaccount_updated_for_claims_sans_visits.sql
    ✨ Consolidates account-level details, including balances, provider assignments, and service dates, for encounters and claims identified as changed.
    writes TransformAccountOrthos · reads AccountDeltaorthosSourceClaimOrthosSourceClinicalEncounterOrthosSourceDepartmentOrthosSourcePatientInsuranceOrthosSourceProviderOrthos
    ↳ feeds from Create AccountDelta
  6. Load TransformRemitAdjustmentAggregatesqlrevfind_sbo_transform_accounts__load_transformremitadjustmentaggregate.sql
    ✨ Builds an aggregate table of remittance adjustments, identifying initial and current denial categories and amounts for specific account encounters.
    writes TransformRemitAdjustmentAggregateOrthos · reads AccountDeltaOrthosTransformChargeOrthosTransformRemitAdjustmentOrthos
    ↳ feeds from Create AccountDelta, Load TransformCharge - Updated for Claims sans Visits, Load TransformRemitAdjustment - Updated for Claims sans Visits
  7. Load TransformChargeTransactionMeasuressqlrevfind_sbo_transform_accounts__load_transformchargetransactionmeasures.sql
    ✨ Aggregates transaction and remittance measures, including payments, adjustments, and denials, at the charge level for updated accounts.
    writes TransformChargeTransactionMeasureOrthos · reads AccountDeltaOrthosTransformChargeOrthosTransformRemitAdjustmentAggregateOrthosTransformTransactionOrthos
    ↳ feeds from Create AccountDelta, Load TransformCharge - Updated for Claims sans Visits, Load TransformRemitAdjustmentAggregate, Load TransformTransaction - Updated for Claims sans Visits
  8. Load TransformPatientsqlrevfind_sbo_transform_accounts__load_transformpatient.sql
    ✨ Updates or inserts patient demographic information, including contact details and addresses, from source data into the transformed patient table.
    writes TransformPatientOrthos · reads SourcePatientOrthos
  9. Load TransformPatientInsurancesqlrevfind_sbo_transform_accounts__load_transformpatientinsurance.sql
    ✨ Synchronizes patient insurance coverage details, including coordination of benefits and policy identifiers, while removing outdated or expired records.
    writes TransformPatientInsurance · reads InsuranceMapSourcePatientInsuranceOrthosSourcePayerOrthos
  10. TransformAccountInsurance - ExecuteMergeSqlMergeSql
    writes TransformAccountInsuranceOrthos · reads AccountDeltaOrthosInsuranceMapSourcePatientInsuranceOrthosSourcePayerOrthosTransformAccountOrthos
    ↳ feeds from Create AccountDelta, Load TransformAccount - Updated for Claims sans Visits
    merge data source type=ThisDatabasetable name=TransformAccountInsuranceOrthosmerge mode=InsertUpdateHardDeletebulk copy timeout seconds=300bulk copy use table lock
  11. Update AccountDeltaDatesqlrevfind_sbo_transform_accounts__update_accountdeltadate.sql
    ✨ Resets and updates the account delta tracking table with the current system date and time.
    writes AccountDeltaDateOrthos