Batch / file feed · RevFind pipeline
OrthoAlliance · RevFind-SBO-Daily-Processing-Accounts
Process Athena Accounts
trigger
How it's scheduled
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 task | Cadence | Server | State | Coordination |
|---|---|---|---|---|
\MDC\Daily - Processing - OrthoAlliance | daily at 09:15 | prod-db-1 | enabled | standalone |
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.
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).
Payers in SQLMedicare
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-SBO-Daily-Processing-Accounts
▶RevFind-SBO-Source-LoadAthenaDatasub-job · 26↗Adding 'Orthos' suffix to all source tables
- Merge SourceClaimMergeSqlwrites
SourceClaimOrthos· readsCLAIMmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceClaimOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceClinicalEncounterMergeSqlwrites
SourceClinicalEncounterOrthos· readsCLINICALENCOUNTERmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceClinicalEncounterOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceAppointmentMergeSqlwrites
SourceAppointmentOrthos· readsAPPOINTMENTCLAIMmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceAppointmentOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceTransactionMergeSqlwrites
SourceTransactionOrthos· readsCLAIMTRANSACTIONmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceTransactionOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceTransactionChargeMergeSqldisabledwrites
SourceTransactionCharge· readsSOURCETRANSACTIONmerge data source type=ThisDatabasetable name=SourceTransactionChargemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock - Merge SourceTransationPaymentMergeSqldisabledwrites
SourceTransactionPayment· readsSOURCETRANSACTIONmerge data source type=ThisDatabasetable name=SourceTransactionPaymentmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock - Merge SourceTransactionAdjustmentMergeSqldisabledwrites
SourceTransactionAdjustment· readsSOURCETRANSACTIONmerge data source type=ThisDatabasetable name=SourceTransactionAdjustmentmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock - Merge SourceTransactionTransferMergeSqldisabledwrites
SourceTransactionTransfer· readsSOURCETRANSACTIONmerge data source type=ThisDatabasetable name=SourceTransactionTransfermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock - Merge SourceERABatchMergeSqlwrites
SourceERABatchOrthos· readsERABATCHmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceERABatchOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceERARecordMergeSqlwrites
SourceERARecordOrthos· readsERARECORDmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceERARecordOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceERADetailMergeSqlwrites
SourceERADetailOrthos· readsERADETAILERARECORDmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceERADetailOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceERAKickMergeSqlwrites
SourceERAKickOrthos· readsERAKICKmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceERAKickOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceClaimNoteMergeSqldisabledwrites
SourceClaimNote· readsCLAIMNOTEmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceClaimNotemerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceDenialClaimNoteMergeSqldisabledwrites
SourceDenialClaimNote· readsDENIALCLAIMNOTEmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceDenialClaimNotemerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourcePatientMergeSqlwrites
SourcePatientOrthos· readsCLAIMPATIENTmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourcePatientOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourcePayerMergeSqlwrites
SourcePayerOrthos· readsINSURANCEPACKAGEPAYERmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourcePayerOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourcePatientInsuranceMergeSqlwrites
SourcePatientInsuranceOrthos· readsCLAIMPATIENTINSURANCEmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourcePatientInsuranceOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceProviderMergeSqlwrites
SourceProviderOrthos· readsPROVIDERmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceProviderOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceDepartmentMergeSqlwrites
SourceDepartmentOrthos· readsDEPARTMENTmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceDepartmentOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceFeeScheduleProcedureMergeSqlwrites
SourceFeeScheduleProcedureOrthos· readsFEESCHEDULEPROCEDUREmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceFeeScheduleProcedureOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceProcedureCodeMergeSqlwrites
SourceProcedureCodeOrthos· readsPROCEDURECODEmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceProcedureCodeOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceFeeScheduleMergeSqlwrites
SourceFeeScheduleOrthos· readsFEESCHEDULEmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceFeeScheduleOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceProcedureCodeModifierMergeSqlwrites
SourceProcedureCodeModifierOrthos· readsPROCEDURECODEMODIFIERmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceProcedureCodeModifierOrthosmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceFeeScheduleDepartmentMergeSqlwrites
SourceFeeScheduleDepartment· readsFEESCHEDULEDEPARTMENTmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceFeeScheduleDepartmentmerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge FEESCHEDALLOWABLECATEGORYMergeSqlwrites
SourceFeeSchedAllowableCategory· readsFEESCHEDALLOWABLECATEGORYmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceFeeSchedAllowableCategorymerge mode=InsertUpdatebulk copy timeout seconds=300 - Merge SourceAppointmentTypeMergeSqlwrites
SourceAppointmentType· readsAPPOINTMENTTYPEmerge data source type=Snowflakeconnection string={AthenaConnectionString}table name=SourceAppointmentTypemerge mode=InsertUpdatebulk copy timeout seconds=300
▶RevFind-SBO-Transform-LoadInsuranceMapsub-job · 3↗
- Load-InsuranceMapLoad-InsuranceMapdisabledwrites
InsuranceMap· readsInsuranceMapSourcePayerentity type=InsuranceMapauto querykey prefix=IMDISABLED - Load-InsuranceMap-With-Import-TableLoad-InsuranceMap-With-Import-Tabledisabledwrites
InsuranceMap· readsInsuranceMapSourceInsuranceMapImportSourcePayerentity type=InsuranceMapcustom querykey prefix=IMDISABLED - Load-InsuranceMap (InsPackageID)LoadEntitieswrites
InsuranceMap· readsInsuranceMapSourcePayerOrthosentity type=InsuranceMapcustom querykey prefix=IM
▶RevFind-SBO-Transform-ChargeTypesub-job · 6↗Load the Charge Types from ChargeMaster + CPT Crosswalk
- Merge ChargeTypeMergeSqlwrites
ChargeType· readsChargeTypeSourceProcedureCodeOrthosSourceTransactionOrthosmerge data source type=ThisDatabasetable name=ChargeTypemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock - Load TransformAthenaFeeScheduleMapsql
revfind_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.writesTransformAthenaFeeScheduleMap· readsSourceFeeSchedAllowableCategorySourceFeeScheduleDepartmentSourceFeeScheduleOrthosSourcePayerOrthosTransformAthenaFeeScheduleMap - Update-ProcedureCode-DimensionMembersql
revfind_sbo_transform_chargetype__update_procedurecode_dimensionmember.sql✨ Updates the procedure code dimension with member IDs, descriptions, and category hierarchies to support clinical service reporting.writesDimensionMember· readsChargeTypeProcedureCode↳ feeds from Merge ChargeType - Update-ChargeType-DimensionMembersql
revfind_sbo_transform_chargetype__update_chargetype_dimensionmember.sql✨ Updates the charge type dimension with standardized codes and display names for consistent reporting across the pipeline.writesDimensionMember· readsChargeType↳ feeds from Merge ChargeType - Update ChargeMaster SchedulesUpdateFeePricingdisabledwrites
FeePricingRecord· readsSourceChargeMasterOrthosschedule insert type=Replaceschedule type=Chargessource table=SourceChargeMasterOrthosDISABLED - Generate time-based fee schedules from pricing records to establish effective date ranges for facility and insurance billing combinations.writes
ScheduleScheduleMap· readsFeePricingRecordDISABLED
▶RevFind-SBO-Transform-Accountssub-job · 11↗Athena Transforms
- Create AccountDeltasql
revfind_sbo_transform_accounts__create_accountdelta.sql✨ Identifies new or modified accounts, encounters, and claims to determine which records require incremental processing in the pipeline.writesAccountDeltaOrthos· readsAccountDeltaDateOrthosAccountDeltaOrthosSourceAppointmentOrthosSourceClaimOrthosSourceClinicalEncounterOrthosSourceERADetailOrthosSourcePatientInsuranceOrthosSourceTransactionOrthos↳ feeds from Update AccountDeltaDate - Load TransformTransaction - Updated for Claims sans Visitssql
revfind_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.writesTransformTransactionOrthos· readsAccountDeltaOrthosSourceClaimOrthosSourcePatientInsuranceOrthosSourceTransactionOrthos↳ feeds from Create AccountDelta - Load TransformRemitAdjustment - Updated for Claims sans Visitssql
revfind_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.writesTransformRemitAdjustmentOrthos· readsAccountDeltaOrthosAdjustmentCodeRollupSourceClaimOrthosSourceERABatchOrthosSourceERADetailOrthosSourceERAKickOrthosSourceERARecordOrthosSourcePatientInsuranceOrthos↳ feeds from Create AccountDelta - Load TransformCharge - Updated for Claims sans Visitssql
revfind_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.writesTransformChargeOrthos· readsAccountDeltaOrthosSourceClaimOrthosSourceDepartmentOrthosSourceTransactionOrthos↳ feeds from Create AccountDelta - Load TransformAccount - Updated for Claims sans Visitssql
revfind_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.writesTransformAccountOrthos· readsAccountDeltaorthosSourceClaimOrthosSourceClinicalEncounterOrthosSourceDepartmentOrthosSourcePatientInsuranceOrthosSourceProviderOrthos↳ feeds from Create AccountDelta - Load TransformRemitAdjustmentAggregatesql
revfind_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.writesTransformRemitAdjustmentAggregateOrthos· readsAccountDeltaOrthosTransformChargeOrthosTransformRemitAdjustmentOrthos↳ feeds from Create AccountDelta, Load TransformCharge - Updated for Claims sans Visits, Load TransformRemitAdjustment - Updated for Claims sans Visits - Load TransformChargeTransactionMeasuressql
revfind_sbo_transform_accounts__load_transformchargetransactionmeasures.sql✨ Aggregates transaction and remittance measures, including payments, adjustments, and denials, at the charge level for updated accounts.writesTransformChargeTransactionMeasureOrthos· readsAccountDeltaOrthosTransformChargeOrthosTransformRemitAdjustmentAggregateOrthosTransformTransactionOrthos↳ feeds from Create AccountDelta, Load TransformCharge - Updated for Claims sans Visits, Load TransformRemitAdjustmentAggregate, Load TransformTransaction - Updated for Claims sans Visits - Load TransformPatientsql
revfind_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.writesTransformPatientOrthos· readsSourcePatientOrthos - Load TransformPatientInsurancesql
revfind_sbo_transform_accounts__load_transformpatientinsurance.sql✨ Synchronizes patient insurance coverage details, including coordination of benefits and policy identifiers, while removing outdated or expired records.writesTransformPatientInsurance· readsInsuranceMapSourcePatientInsuranceOrthosSourcePayerOrthos - TransformAccountInsurance - ExecuteMergeSqlMergeSqlwrites
TransformAccountInsuranceOrthos· readsAccountDeltaOrthosInsuranceMapSourcePatientInsuranceOrthosSourcePayerOrthosTransformAccountOrthos↳ feeds from Create AccountDelta, Load TransformAccount - Updated for Claims sans Visitsmerge data source type=ThisDatabasetable name=TransformAccountInsuranceOrthosmerge mode=InsertUpdateHardDeletebulk copy timeout seconds=300bulk copy use table lock - Update AccountDeltaDatesql
revfind_sbo_transform_accounts__update_accountdeltadate.sql✨ Resets and updates the account delta tracking table with the current system date and time.writesAccountDeltaDateOrthos
▶RevFind-SBO-Process-PatientsEntitysub-job · 1↗
- Execute LoadPatientsLoadPatientsUpsert patient records into the patient dimension.· reads
PatientTransformPatientOrthoscustom patient query
▶RevFind-SBO-Process-LoadEntitiessub-job · 2↗Load Facility, Provider
- Load FacilityLoadEntitieswrites
Facility· readsFacilitySourceDepartmentOrthosentity type=Facilitycustom query - Load Providersql
revfind_sbo_process_loadentities__load_provider.sql✨ Loads and updates provider entity records including names, NPIs, and taxonomy codes from the source provider data.writesProvider· readsSourceProviderOrthos
▶RevFind-SBO-Process-DimensionMemberssub-job · 1↗
- Load Modifier Dimensionsql
revfind_sbo_process_dimensionmembers__load_modifier_dimension.sql✨ Loads and updates the modifier dimension table with procedure code modifier IDs and descriptions from source data.writesDIMENSIONMEMBER· readsSOURCEPROCEDURECODEMODIFIERORTHOS
▶RevFind-SBO-Process-LoadAccountsub-job · 10↗Load Account-Level Tables
- Augment AccountDeltaReprocessChangedConfigsreprocess accounts
- Augment AccountDelta ReprocessChangedConfigurationssql
revfind_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.writesAccountDeltaOrthos· readsAccountDeltaOrthosAccountsToReprocessTransformAccountOrthos - Load ChargeMeasuressql
revfind_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.writesChargeMeasuresOrthos· readsChargeMeasuresOrthos - LoadAccountDataLoadAccountDataImports 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 ChargeMeasurescustom account charge queryauto account querycustom account remit querycustom account insurance query ▶RevFind-SBO-Process-LoadCustomPropertiessub-job · 2↗
- Load Claim ID Custom PropertyUpdateCustomPropertyFromQuery· reads
AccountDeltaOrthosTransformAccountOrthosentity=Accountcustom property key=CustomProperty-[id]auto querybatch mode - SyncBatchSyncBatchSynchronizes processed data from batch staging tables to production tables, updating visits, quotes, and custom property mappings.writes
CustomPropertyMap· readsCustomPropertyMapBatchsync custom property map
▶RevFind-SBO-Process-LoadDimensionMemberssub-job · 10↗
- LoadInsuranceDimensionMembersLoadInsuranceDimensionMembersSynchronize insurance-specific dimension members from the insurance table to update contract and eligibility filtering options across the application.writes
DimensionMemberload insuranceload quote insuranceload eligibility insurance - Load Time Dimensionssql
revfind_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.writesDimensionMember· readsTransformAccountOrthos - Load Provider Dimensionsql
revfind_sbo_process_loaddimensionmembers__load_provider_dimension.sql✨ Updates provider and provider type dimensions with names and classifications to organize data by clinical staff.writesDimensionMember· readsProvider - Load Facility Dimensionssql
revfind_sbo_process_loaddimensionmembers__load_facility_dimensions.sql✨ Loads facility dimension members by extracting facility keys, groups, and names from the facility source table.writesDimensionMember· readsFacility - Load FacilityGroup Dimensionsql
revfind_sbo_process_loaddimensionmembers__load_facilitygroup_dimension.sqldisabled✨ Builds the facility group dimension by identifying unique parent groups from facility dimension records.writesDimensionMember· readsDimensionMember - Load FacilityType Dimensionsql
revfind_sbo_process_loaddimensionmembers__load_facilitytype_dimension.sql✨ Loads facility type dimension members from facility records to enable standardized categorization across the revenue cycle pipeline.writesDimensionMember· readsFacility - Load SourceInsurance Dimensionssql
revfind_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. - Load PlaceOfService Dimensionsql
revfind_sbo_process_loaddimensionmembers__load_placeofservice_dimension.sql✨ Builds or updates the place of service dimension by extracting unique location values from transformed charge data.writesDimensionMember· readsTransformChargeOrthos - Load DiagnosisCode Dimensionsql
revfind_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.writesDimensionMember· readsTransformChargeOrthos - Load State Dimensionssql
revfind_sbo_process_loaddimensionmembers__load_state_dimensions.sql✨ Updates state dimensions using facility location data to allow for geographic segmentation of healthcare services and revenue.writesDimensionMember· readsFacility
▶RevFind-SBO-Process-ScoreAccountssub-job · 1↗
- ProcessAccountDataFromQueryProcessAccountDataFromQuery· reads
AccountDeltaOrthosAccountDetailAccountInsuranceContractMapcustom query
- AccountDetailsql
revfind_sbo_process_loadaccount__accountdetail.sql✨ Updates the account detail table with comprehensive encounter, insurance, and financial summary data for accounts in the current delta.writesAccountDetail· readsAccountAccountChargeAccountDeltaOrthosAccountEstimateDimensionMemberPatientSourcePayerTransformAccountInsuranceOrthos↳ feeds from Augment AccountDelta ReprocessChangedConfigurations - AccountChargeDetailsql
revfind_sbo_process_loadaccount__accountchargedetail.sql✨ Populates the account charge detail table with granular service, provider, and financial data for individual charges within processed accounts.writesAccountChargeDetail· readsAccountAccountChargeAccountDeltaOrthosAccountEstimateCalculationAccountEstimateChargeDimensionMemberPatientTransformAccountInsuranceOrthos↳ feeds from Augment AccountDelta ReprocessChangedConfigurations - Update Modifier 22 Custom PropertyUpdateCustomPropertyFromQuery· reads
AccountChargeAccountDeltaOrthos↳ feeds from Augment AccountDelta ReprocessChangedConfigurationsentity=Accountcustom property key=Modifier22custom query