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

Batch / file feed · RevFind pipeline

OrthoAlliance · RevFind-SBO-Daily-Processing-Accounts

Process Athena Accounts

pipeline OrthoAlliance/RevFind-SBO-Daily-Processing-Accounts · db OrthoAlliance_ProdMirror · updated 2025-09-17
classification: revfind · 0 Flow markers

trigger

How it's scheduled

Daily batch — once at 09:15

The Batch / file feed arrives as a scheduled drop, so one overnight pass is enough: it ingests the day's file and works the whole batch to process the visits. A tighter loop would just re-scan the same file.

Scheduled taskCadenceServerStateCoordination
\MDC\Daily - Processing - OrthoAlliancedaily at 09:15prod-db-1enabledstandalone

shape

The RevFind back-office spine

RevFind pipelines share a back-office shape: ingest → load accounts → reconcile remits → model contracts → surface underpayments. Lit stages are the ones this pipeline runs.

Ingest
Batch / file feed
Accounts / AR
ACCT
Remit (835)
Contract model
CTR
Payer rules
Work queue & sync
Sync

computed concepts · from the SQL

What it computes

Business concepts mined from this pipeline's SQL text — the meaning behind the typed steps. Each links to the concept lens (who else computes it).

task tree

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

revfind

RevFind-SBO-Daily-Processing-Accounts

9 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. RevFind-SBO-Source-LoadAthenaDatasub-job · 26Adding 'Orthos' suffix to all source tables
    1. Merge SourceClaimMergeSql
      writes SourceClaimOrthos · reads CLAIM
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceClaimOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    2. Merge SourceClinicalEncounterMergeSql
      writes SourceClinicalEncounterOrthos · reads CLINICALENCOUNTER
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceClinicalEncounterOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    3. Merge SourceAppointmentMergeSql
      writes SourceAppointmentOrthos · reads APPOINTMENTCLAIM
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceAppointmentOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    4. Merge SourceTransactionMergeSql
      writes SourceTransactionOrthos · reads CLAIMTRANSACTION
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceTransactionOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    5. Merge SourceTransactionChargeMergeSqldisabled
      writes SourceTransactionCharge · reads SOURCETRANSACTION
      merge data source type=ThisDatabasetable name=SourceTransactionChargemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    6. Merge SourceTransationPaymentMergeSqldisabled
      writes SourceTransactionPayment · reads SOURCETRANSACTION
      merge data source type=ThisDatabasetable name=SourceTransactionPaymentmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    7. Merge SourceTransactionAdjustmentMergeSqldisabled
      writes SourceTransactionAdjustment · reads SOURCETRANSACTION
      merge data source type=ThisDatabasetable name=SourceTransactionAdjustmentmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    8. Merge SourceTransactionTransferMergeSqldisabled
      writes SourceTransactionTransfer · reads SOURCETRANSACTION
      merge data source type=ThisDatabasetable name=SourceTransactionTransfermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    9. Merge SourceERABatchMergeSql
      writes SourceERABatchOrthos · reads ERABATCH
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceERABatchOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    10. Merge SourceERARecordMergeSql
      writes SourceERARecordOrthos · reads ERARECORD
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceERARecordOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    11. Merge SourceERADetailMergeSql
      writes SourceERADetailOrthos · reads ERADETAILERARECORD
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceERADetailOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    12. Merge SourceERAKickMergeSql
      writes SourceERAKickOrthos · reads ERAKICK
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceERAKickOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    13. Merge SourceClaimNoteMergeSqldisabled
      writes SourceClaimNote · reads CLAIMNOTE
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceClaimNotemerge mode=InsertUpdatebulk copy timeout seconds=300
    14. Merge SourceDenialClaimNoteMergeSqldisabled
      writes SourceDenialClaimNote · reads DENIALCLAIMNOTE
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceDenialClaimNotemerge mode=InsertUpdatebulk copy timeout seconds=300
    15. Merge SourcePatientMergeSql
      writes SourcePatientOrthos · reads CLAIMPATIENT
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourcePatientOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    16. Merge SourcePayerMergeSql
      writes SourcePayerOrthos · reads INSURANCEPACKAGEPAYER
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourcePayerOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    17. Merge SourcePatientInsuranceMergeSql
      writes SourcePatientInsuranceOrthos · reads CLAIMPATIENTINSURANCE
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourcePatientInsuranceOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    18. Merge SourceProviderMergeSql
      writes SourceProviderOrthos · reads PROVIDER
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceProviderOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    19. Merge SourceDepartmentMergeSql
      writes SourceDepartmentOrthos · reads DEPARTMENT
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceDepartmentOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    20. Merge SourceFeeScheduleProcedureMergeSql
      writes SourceFeeScheduleProcedureOrthos · reads FEESCHEDULEPROCEDURE
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceFeeScheduleProcedureOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    21. Merge SourceProcedureCodeMergeSql
      writes SourceProcedureCodeOrthos · reads PROCEDURECODE
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceProcedureCodeOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    22. Merge SourceFeeScheduleMergeSql
      writes SourceFeeScheduleOrthos · reads FEESCHEDULE
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceFeeScheduleOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    23. Merge SourceProcedureCodeModifierMergeSql
      writes SourceProcedureCodeModifierOrthos · reads PROCEDURECODEMODIFIER
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceProcedureCodeModifierOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300
    24. Merge SourceFeeScheduleDepartmentMergeSql
      writes SourceFeeScheduleDepartment · reads FEESCHEDULEDEPARTMENT
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceFeeScheduleDepartmentmerge mode=InsertUpdatebulk copy timeout seconds=300
    25. Merge FEESCHEDALLOWABLECATEGORYMergeSql
      writes SourceFeeSchedAllowableCategory · reads FEESCHEDALLOWABLECATEGORY
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceFeeSchedAllowableCategorymerge mode=InsertUpdatebulk copy timeout seconds=300
    26. Merge SourceAppointmentTypeMergeSql
      writes SourceAppointmentType · reads APPOINTMENTTYPE
      merge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceAppointmentTypemerge mode=InsertUpdatebulk copy timeout seconds=300
  2. RevFind-SBO-Transform-LoadInsuranceMapsub-job · 3
    1. Load-InsuranceMapLoad-InsuranceMapdisabled
      writes InsuranceMap · reads InsuranceMapSourcePayer
      entity type=InsuranceMapauto querykey prefix=IMDISABLED
    2. Load-InsuranceMap-With-Import-TableLoad-InsuranceMap-With-Import-Tabledisabled
      writes InsuranceMap · reads InsuranceMapSourceInsuranceMapImportSourcePayer
      entity type=InsuranceMapcustom querykey prefix=IMDISABLED
    3. Load-InsuranceMap (InsPackageID)LoadEntities
      writes InsuranceMap · reads InsuranceMapSourcePayerOrthos
      entity type=InsuranceMapcustom querykey prefix=IM
  3. RevFind-SBO-Transform-ChargeTypesub-job · 6Load the Charge Types from ChargeMaster + CPT Crosswalk
    1. Merge ChargeTypeMergeSql
      writes ChargeType · reads ChargeTypeSourceProcedureCodeOrthosSourceTransactionOrthos
      merge data source type=ThisDatabasetable name=ChargeTypemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    2. Load TransformAthenaFeeScheduleMapsqlrevfind_sbo_transform_chargetype__load_transformathenafeeschedulemap.sql
      Builds a mapping of fee schedules to departments, identifying self-pay and chargemaster designations from source payer and schedule data.
      writes TransformAthenaFeeScheduleMap · reads SourceFeeSchedAllowableCategorySourceFeeScheduleDepartmentSourceFeeScheduleOrthosSourcePayerOrthosTransformAthenaFeeScheduleMap
    3. Update-ProcedureCode-DimensionMembersqlrevfind_sbo_transform_chargetype__update_procedurecode_dimensionmember.sql
      Updates the procedure code dimension with member IDs, descriptions, and category hierarchies to support clinical service reporting.
      writes DimensionMember · reads ChargeTypeProcedureCode
      ↳ feeds from Merge ChargeType
    4. Update-ChargeType-DimensionMembersqlrevfind_sbo_transform_chargetype__update_chargetype_dimensionmember.sql
      Updates the charge type dimension with standardized codes and display names for consistent reporting across the pipeline.
      writes DimensionMember · reads ChargeType
      ↳ feeds from Merge ChargeType
    5. Update ChargeMaster SchedulesUpdateFeePricingdisabled
      writes FeePricingRecord · reads SourceChargeMasterOrthos
      schedule insert type=Replaceschedule type=Chargessource table=SourceChargeMasterOrthosDISABLED
    6. Load Charges SchedulesLoadFeeSchedulesdisabled
      Generate time-based fee schedules from pricing records to establish effective date ranges for facility and insurance billing combinations.
      writes ScheduleScheduleMap · reads FeePricingRecord
      DISABLED
  4. RevFind-SBO-Transform-Accountssub-job · 11Athena Transforms
    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
  5. RevFind-SBO-Process-PatientsEntitysub-job · 1
    1. Execute LoadPatientsLoadPatients
      Upsert patient records into the patient dimension.
      · reads PatientTransformPatientOrthos
      custom patient query
  6. RevFind-SBO-Process-LoadEntitiessub-job · 2Load Facility, Provider
    1. Load FacilityLoadEntities
      writes Facility · reads FacilitySourceDepartmentOrthos
      entity type=Facilitycustom query
    2. Load Providersqlrevfind_sbo_process_loadentities__load_provider.sql
      Loads and updates provider entity records including names, NPIs, and taxonomy codes from the source provider data.
      writes Provider · reads SourceProviderOrthos
  7. RevFind-SBO-Process-DimensionMemberssub-job · 1
    1. Load Modifier Dimensionsqlrevfind_sbo_process_dimensionmembers__load_modifier_dimension.sql
      Loads and updates the modifier dimension table with procedure code modifier IDs and descriptions from source data.
      writes DIMENSIONMEMBER · reads SOURCEPROCEDURECODEMODIFIERORTHOS
  8. RevFind-SBO-Process-LoadAccountsub-job · 10Load Account-Level Tables
    1. Augment AccountDeltaReprocessChangedConfigs
      reprocess accounts
    2. Augment AccountDelta ReprocessChangedConfigurationssqlrevfind_sbo_process_loadaccount__augment_accountdelta_reprocesschangedconfigurations.sql
      Adds accounts requiring reprocessing into the change tracking table to ensure updated configurations are applied during the pipeline.
      writes AccountDeltaOrthos · reads AccountDeltaOrthosAccountsToReprocessTransformAccountOrthos
    3. Load ChargeMeasuressqlrevfind_sbo_process_loadaccount__load_chargemeasures.sql
      Builds a temporary table of charge-level financial metrics, calculating adjusted, paid, patient, and denied amounts for processed accounts.
      writes ChargeMeasuresOrthos · reads ChargeMeasuresOrthos
    4. LoadAccountDataLoadAccountData
      Imports historical billing data from external systems into core account tables while preserving manual user modifications and application fields.
      · reads AccountChargeAccountDeltaOrthosChargeMeasuresOrthosChargeTypeInsuranceMapPatientProviderSourcePatientInsuranceOrthos
      ↳ feeds from Augment AccountDelta ReprocessChangedConfigurations, Load ChargeMeasures
      custom account charge queryauto account querycustom account remit querycustom account insurance query
    5. RevFind-SBO-Process-LoadCustomPropertiessub-job · 2
      1. Load Claim ID Custom PropertyUpdateCustomPropertyFromQuery
        · reads AccountDeltaOrthosTransformAccountOrthos
        entity=Accountcustom property key=CustomProperty-[id]auto querybatch mode
      2. 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
    6. RevFind-SBO-Process-LoadDimensionMemberssub-job · 10
      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_sbo_process_loaddimensionmembers__load_time_dimensions.sql
        Loads and updates time dimensions for days, months, and years based on the range of service dates in the system.
        writes DimensionMember · reads TransformAccountOrthos
      3. Load Provider Dimensionsqlrevfind_sbo_process_loaddimensionmembers__load_provider_dimension.sql
        Updates provider and provider type dimensions with names and classifications to organize data by clinical staff.
        writes DimensionMember · reads Provider
      4. Load Facility Dimensionssqlrevfind_sbo_process_loaddimensionmembers__load_facility_dimensions.sql
        Loads facility dimension members by extracting facility keys, groups, and names from the facility source table.
        writes DimensionMember · reads Facility
      5. Load FacilityGroup Dimensionsqlrevfind_sbo_process_loaddimensionmembers__load_facilitygroup_dimension.sqldisabled
        Builds the facility group dimension by identifying unique parent groups from facility dimension records.
        writes DimensionMember · reads DimensionMember
      6. Load FacilityType Dimensionsqlrevfind_sbo_process_loaddimensionmembers__load_facilitytype_dimension.sql
        Loads facility type dimension members from facility records to enable standardized categorization across the revenue cycle pipeline.
        writes DimensionMember · reads Facility
      7. Load SourceInsurance Dimensionssqlrevfind_sbo_process_loaddimensionmembers__load_sourceinsurance_dimensions.sql
        Synchronizes the source insurance dimension table with mapped insurance provider IDs and display names from the insurance mapping table.
      8. Load PlaceOfService Dimensionsqlrevfind_sbo_process_loaddimensionmembers__load_placeofservice_dimension.sql
        Builds or updates the place of service dimension by extracting unique location values from transformed charge data.
        writes DimensionMember · reads TransformChargeOrthos
      9. Load DiagnosisCode Dimensionsqlrevfind_sbo_process_loaddimensionmembers__load_diagnosiscode_dimension.sql
        Builds or updates the diagnosis code dimension by consolidating unique primary, secondary, and tertiary codes from transformed charges.
        writes DimensionMember · reads TransformChargeOrthos
      10. Load State Dimensionssqlrevfind_sbo_process_loaddimensionmembers__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
    7. RevFind-SBO-Process-ScoreAccountssub-job · 1
      1. ProcessAccountDataFromQueryProcessAccountDataFromQuery
        · reads AccountDeltaOrthosAccountDetailAccountInsuranceContractMap
        custom query
    8. AccountDetailsqlrevfind_sbo_process_loadaccount__accountdetail.sql
      Updates the account detail table with comprehensive encounter, insurance, and financial summary data for accounts in the current delta.
      writes AccountDetail · reads AccountAccountChargeAccountDeltaOrthosAccountEstimateDimensionMemberPatientSourcePayerTransformAccountInsuranceOrthos
      ↳ feeds from Augment AccountDelta ReprocessChangedConfigurations
    9. AccountChargeDetailsqlrevfind_sbo_process_loadaccount__accountchargedetail.sql
      Populates the account charge detail table with granular service, provider, and financial data for individual charges within processed accounts.
      writes AccountChargeDetail · reads AccountAccountChargeAccountDeltaOrthosAccountEstimateCalculationAccountEstimateChargeDimensionMemberPatientTransformAccountInsuranceOrthos
      ↳ feeds from Augment AccountDelta ReprocessChangedConfigurations
    10. Update Modifier 22 Custom PropertyUpdateCustomPropertyFromQuery
      · reads AccountChargeAccountDeltaOrthos
      ↳ feeds from Augment AccountDelta ReprocessChangedConfigurations
      entity=Accountcustom property key=Modifier22custom query