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

Batch / file feed · RevFind pipeline

ISpine · RevFind-Daily-Processing

pipeline ISpine/RevFind-Daily-Processing · db ISpine_ProdMirror · updated 2026-05-01
classification: revfind · loads 1 visit table

Loads visit tables
Besides its back-office work, this RevFind pipeline also loads a visit dimension/fact table (Load VisitType Dimensions) — the shared rows Flow builds on. It runs no front-of-house work (no eligibility, estimate, or letters), so it stays RevFind.

trigger

How it's scheduled

Daily batch — once at 03:45

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 - iSpinedaily at 03:45prod-db-3enabledstandalone

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)
835
→
Contract model
→
Payer rules
→
Work queue & sync
Work queueSync

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).

Good-faith estimateExpected reimbursementDenial / underpaymentCPT / procedure logic

Payers in SQLMedicaidMedicare

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-Daily-Processing

7 tasks · ISpine
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-EDI-Processingsub-job · 5↗
    1. Download EDI & Archive 835sDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=ISpine_ProdMirror-mdcssh-sftpremote directory=/835/local directory={SourceDataFolder}
    2. Download EDI & Archive 837sDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=ISpine_ProdMirror-mdcssh-sftpremote directory=/local directory={SourceDataFolder}
    3. ▶RevFind-EDI-LoadFilessub-job · 2↗
      1. Bulk Insert into DatabaseExecuteProcess
        command=powershell.exe
      2. Archive EDI FilesExecuteProcess
        command=cmd.exe
    4. ▶RevFind-EDI-TransformAndParseEDIsub-job · 5↗
      1. EDITransformsqlrevfind_edi_transformandparseedi__editransform.sql
        ✨ Loads unique EDI messages from staging into the transformation table while preventing duplicate records.
        writes EDITransform · reads EDIFileStagingEDITransform
      2. EDIBatchsqlrevfind_edi_transformandparseedi__edibatch.sql
        ✨ Builds a batch of unprocessed EDI messages, excluding empty records, for subsequent parsing and transformation.
        writes EDIBatch
      3. Clean EDIBatch (for bug w EDIFabric)sqlrevfind_edi_transformandparseedi__clean_edibatch_for_bug_w_edifabric.sqldisabled
        ✨ Updates EDI message formatting by replacing specific delimiters to ensure compatibility with the parsing engine.
        · reads EDIBatch
      4. EDIParserEDIParser
        writes EDIBatch
        table name=EDIBatch
      5. ▶RevFind-EDI-LoadMetadatasub-job · 4↗
        1. Load EDIFacilityMapsqlrevfind_edi_loadmetadata__load_edifacilitymap.sql
          ✨ Loads the facility map with unique billing and service facility details extracted from electronic claim transactions.
          writes EDIFacilityMap · reads EDIFacilityMapTxn837P
        2. Load EDIProviderMapsqlrevfind_edi_loadmetadata__load_ediprovidermap.sqldisabled
          ✨ Builds the provider map by combining rendering provider information from claim transactions with national provider registry data.
          writes EDIProviderMap · reads EDIProviderMapNPIDataTxn837P
        3. Load EDIPayerMapsqlrevfind_edi_loadmetadata__load_edipayermap.sql
          ✨ Builds a mapping table of payer names and filing indicators from electronic remittance transactions to standardize payer metadata.
          writes EDIPayerMap · reads Txn835Txn835ClaimPayment
        4. ExecuteMergeSql-EDIProviderMapExecuteMergeSql-EDIProviderMap
          writes EDIProviderMap · reads EDIProviderMapTxn837ITxn837P
          merge data source type=ThisDatabasetable name=EDIProviderMapmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    5. ▶RevFind-Load-Transform-EDIDatasub-job · 18↗
      1. Load RemitDeltasqlrevfind_load_transform_edidata__load_remitdelta.sql
        ✨ Identifies recently processed or updated electronic remittance claims to determine which records require incremental transformation and loading.
        writes RemitDelta · reads EDIFilesProcessedRemitDeltaTransformRemitTxn835Txn835ClaimPayment
        ↳ feeds from Load TempRemitCOB, Load TransformRemit
      2. Load TransformRemitsqlrevfind_load_transform_edidata__load_transformremit.sql
        ✨ Extracts and transforms electronic remittance advice data from raw transaction files into a structured format for processing.
        writes TransformRemit · reads Txn835Txn835ClaimPaymentTxn835ClaimPaymentLineTxn835ClaimPaymentOtherClaimAmount
      3. Load TransformRemitEncounterMapsqlrevfind_load_transform_edidata__load_transformremitencountermap.sqldisabled
        ✨ Maps remittance claim identifiers to internal account records to link insurance payments with specific patient encounters.
        writes TransformRemitEncounterMap · reads TransformRemitEncounterMap
      4. Update TransformRemitEncounterMapsqlrevfind_load_transform_edidata__update_transformremitencountermap.sqldisabled
        ✨ Updates remittance mapping records with patient account identifiers by matching service dates and charge IDs at the line level.
        · reads TransformChargeTransformRemitEncounterMapTransformRemitLinerem
      5. Load TempRemitCOB (old)sqlrevfind_load_transform_edidata__load_tempremitcob_old.sqldisabled
        ✨ Builds a temporary table to determine claim status, member IDs, and coordination of benefits order for remittance data.
      6. Load TempRemitCOBsqlrevfind_load_transform_edidata__load_tempremitcob.sql
        ✨ Determines the coordination of benefits order and current claim status by analyzing historical remittance and adjustment data.
        writes TempRemitCOBTransformRemit · reads TempRemitCOBTransformRemitTransformRemitAdjustment
        ↳ feeds from Load TransformRemit, Load TransformRemitAdjustment
      7. Load TransformRemitAdjustmentsqlrevfind_load_transform_edidata__load_transformremitadjustment.sql
        ✨ Loads claim and line-level adjustments by unpivoting reason codes and amounts from electronic remittance advice transactions.
        writes TransformRemitAdjustment · reads TransformRemitTxn835ClaimAdjustment
        ↳ feeds from Load TempRemitCOB, Load TransformRemit
      8. Load TransformRemitLinesqlrevfind_load_transform_edidata__load_transformremitline.sql
        ✨ Loads detailed remittance line items, mapping procedure codes, service dates, and payment amounts from electronic remittance advice.
        writes TransformRemitLine · reads TransformRemitTxn835ClaimPaymentLine
        ↳ feeds from Load TempRemitCOB, Load TransformRemit
      9. Load TransformTxn837sqlrevfind_load_transform_edidata__load_transformtxn837.sql
        ✨ Consolidates and transforms professional and institutional claim submission data into a unified format for billing tracking.
        writes TransformTxn837 · reads Txn837ITxn837IServiceLineTxn837PTxn837PServiceLine
      10. Load TransformTxn837Linesqlrevfind_load_transform_edidata__load_transformtxn837line.sql
        ✨ Builds a unified table of professional and institutional claim service lines, extracting procedure codes, charges, and provider details.
        writes TransformTxn837Line · reads TransformTxn837Txn837ITxn837IServiceLineTxn837PServiceLine
        ↳ feeds from Load TransformTxn837
      11. Load TransformAccountsqlrevfind_load_transform_edidata__load_transformaccount.sql
        ✨ Loads account-level records by mapping claim data to patients, providers, and insurance payers using the latest claim versions.
        writes TransformAccount · reads EDIFacilityMapTransformTxn837
        ↳ feeds from Load TransformTxn837
      12. Load TransformChargesqlrevfind_load_transform_edidata__load_transformcharge.sql
        ✨ Transforms claim service lines into individual charge records linked to accounts, capturing procedure codes, units, and financial amounts.
        writes TransformCharge · reads TransformAccountTransformTxn837TransformTxn837Line
        ↳ feeds from Load TransformAccount, Load TransformTxn837, Load TransformTxn837Line
      13. Load TransformChargeFamilyPoolsqlrevfind_load_transform_edidata__load_transformchargefamilypool.sql
        ✨ Maps historical claim service lines to their latest charge versions to establish a consistent charge family for longitudinal tracking.
        writes TransformChargeFamilyPool · reads TransformChargeTransformChargeFamilyPoolTransformTxn837TransformTxn837Line
        ↳ feeds from Load TransformCharge, Load TransformTxn837, Load TransformTxn837Line
      14. Load TransformPatientMergeSql
        writes TransformPatient · reads TransformAccountTransformTxn837
        ↳ feeds from Load TransformAccount, Load TransformTxn837
        merge data source type=ThisDatabasetable name=TransformPatientmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      15. Load TransformRemitAccountMapsqlrevfind_load_transform_edidata__load_transformremitaccountmap.sql
        ✨ Matches remittance advice records to patient accounts using tiered logic based on identifiers like Tax ID and service dates.
        writes TransformRemitAccountMap · reads TransformAccountTransformRemit
        ↳ feeds from Load TempRemitCOB, Load TransformAccount, Load TransformRemit
      16. Load TransformRemitTransactionssqlrevfind_load_transform_edidata__load_transformremittransactions.sql
        ✨ Consolidates remittance payments and adjustments into a single transaction table, categorizing reason codes for financial reporting and analysis.
        writes TransformRemitTransactions · reads AdjustmentCodeRollupTransformRemitTransformRemitAdjustmentTransformRemitLine
        ↳ feeds from Load TempRemitCOB, Load TransformRemit, Load TransformRemitAdjustment, Load TransformRemitLine
      17. Load TransformChargeRemitMatchsqlrevfind_load_transform_edidata__load_transformchargeremitmatch.sql
        ✨ Matches normalized remittance service lines to specific charges within the charge family pool using tiered logic and procedure codes.
        writes TransformChargeRemitMatch · reads TransformChargeFamilyPoolTransformChargeRemitMatchTransformRemitTransformRemitAccountMapTransformRemitLine
        ↳ feeds from Load TempRemitCOB, Load TransformChargeFamilyPool, Load TransformRemit, Load TransformRemitAccountMap, Load TransformRemitLine
      18. Load TransformAccountRemitsqlrevfind_load_transform_edidata__load_transformaccountremit.sql
        ✨ Links remittance transactions to specific charges or accounts, providing a detailed view of payments and adjustments at the charge level.
        writes TransformAccountRemit · reads TransformAccountRemitTransformChargeRemitMatchTransformRemitAccountMapTransformRemitTransactions
        ↳ feeds from Load TransformChargeRemitMatch, Load TransformRemitAccountMap, Load TransformRemitTransactions
  2. ▶Load-Entitiessub-job · 2↗Load Facility, Provider
    1. Load FacilityLoadEntities
      writes Facility · reads EDIFacilityMapFacility
      entity type=Facilitycustom querykey prefix=F
    2. Load ProviderMergeSql
      writes Provider · reads EDIProviderMapProviderProviderTaxonomy
      sql file=sql/load_entities__load_provider.sqlmerge data source type=ThisDatabasetable name=Providermerge mode=InsertUpdatebulk copy timeout seconds=300
  3. ▶Load-Charge-Dimensionsub-job · 1↗
    1. ExecuteMergeSqlExecuteMergeSql
      Synchronizes data from external sources into target tables by performing batched insert, update, and delete operations based on keys.
      writes ChargeType · reads ProcedureCodeTransformTxn837TransformTxn837Line
      merge data source type=ThisDatabasetable name=ChargeTypemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
  4. ▶Load-InsuranceMapsub-job · 1↗
    1. LoadEntitiesFromQueryLoadEntitiesFromQuery
      Load or update entity records from custom SQL query results into their corresponding system tables using intelligent merge logic.
      writes InsuranceMap · reads InsuranceMapTransformTxn837
      entity type=InsuranceMapcustom querykey prefix=IM
  5. ▶Load-Dimension-Memberssub-job · 20↗
    1. LoadInsuranceDimensionMembersLoadInsuranceDimensionMembersdisabled
      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 insuranceDISABLED
    2. Load Time Dimensionssqlload_dimension_members__load_time_dimensions.sql
      ✨ Populates the time dimension table with day, month, and year members to support temporal reporting and analysis.
      writes DimensionMember · reads TransformAccount
    3. Load ChargeType Dimensionsqlload_dimension_members__load_chargetype_dimension.sqldisabled
      ✨ Loads appointment status values into the visit status dimension to track patient encounter progress.
      writes DimensionMember · reads TransformVisit
    4. 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
    5. Load Provider + ProviderType Dimensionssqlload_dimension_members__load_provider_providertype_dimensions.sql
      ✨ Synchronizes provider and provider type dimensions by mapping individual providers to their respective specialty or classification categories.
      writes DimensionMember · reads DimensionMemberProvider
      ↳ feeds from Load AllowedStatus Dimension, Load ChargeType Dim, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load Facility + FacilityGroup Dimensions, Load FacilityType Dimensions, Load PlaceOfService Dimensions, Load ProcedureCode Dim, Load ProcedureModifier Dim, Load RevenueCode Dim, Load State Dimension, Load Time Dimensions, Load-Source-Insurance-Dimension, LoadDimensionMembers
    6. Load Facility + FacilityGroup Dimensionssqlload_dimension_members__load_facility_facilitygroup_dimensions.sql
      ✨ Updates facility and facility group dimensions to organize locations within their parent organizational structures for reporting.
      writes DimensionMember · reads DimensionMemberFacility
      ↳ feeds from Load AllowedStatus Dimension, Load ChargeType Dim, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load FacilityType Dimensions, Load PlaceOfService Dimensions, Load ProcedureCode Dim, Load ProcedureModifier Dim, Load Provider + ProviderType Dimensions, Load RevenueCode Dim, Load State Dimension, Load Time Dimensions, Load-Source-Insurance-Dimension, LoadDimensionMembers
    7. Load FacilityType Dimensionssqlload_dimension_members__load_facilitytype_dimensions.sql
      ✨ Loads unique facility types into the dimension table to categorize locations by their specific service or operational model.
      writes DimensionMember · reads Facility
    8. 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
    9. 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
    10. Load VisitType Dimensionssqlload_dimension_members__load_visittype_dimensions.sqldisabled
      ✨ Loads and synchronizes visit type dimensions from event mapping data while removing entries no longer present in the source.
      writes DimensionMember · reads SourceEventTransformEventMap
    11. 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
    12. 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
    13. 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 ChargeType Dim, Load DenialCategory Dimension, Load Facility + FacilityGroup Dimensions, Load FacilityType Dimensions, Load PlaceOfService Dimensions, Load ProcedureCode Dim, Load ProcedureModifier Dim, Load Provider + ProviderType Dimensions, Load RevenueCode Dim, Load State Dimension, Load Time Dimensions, Load-Source-Insurance-Dimension, LoadDimensionMembers
    14. Load-Source-Insurance-Dimensionsqlload_dimension_members__load_source_insurance_dimension.sql
      ✨ Updates the insurance dimension table with source insurance names and identifiers from EDI data for reporting.
      writes DimensionMember · reads AccountInsuranceInsuranceMap
    15. 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
    16. LoadDimensionMembersLoadDimensionMembers
      Synchronize facility, provider, and insurance metadata into the dimension member table to refresh dashboard filters and reporting categories.
      writes DimensionMember
      load insuranceload quote insuranceload eligibility insuranceload facilityload provider
    17. Load ChargeType DimMergeSql
      writes DimensionMember · reads ChargeType
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    18. Load ProcedureCode DimMergeSql
      writes DimensionMember · reads ChargeType
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    19. Load ProcedureModifier DimMergeSql
      writes DimensionMember · reads AccountChargeProcedureModifier
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    20. Load RevenueCode DimMergeSql
      writes DimensionMember · reads TransformCharge
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
  6. ▶RevFind-Load-Account-Datasub-job · 9↗Load Account-Level Tables
    1. ReprocessChangedConfigurationsReprocessChangedConfigurations
      Identify and queue visits or accounts affected by configuration changes to ensure historical data reflects updated pricing or insurance rules.
      reprocess accounts
    2. LoadPatientsLoadPatients
      Upsert patient records into the patient dimension.
      · reads PatientTransformPatient
      custom patient query
    3. Load ChargeMeasuressqlrevfind_load_account_data__load_chargemeasures.sql
      ✨ Aggregates remittance activity by charge to calculate paid, denied, and allowed amounts for financial analysis.
      writes ChargeMeasures · reads TransformAccountRemitTransformCharge
    4. LoadAccountDataLoadAccountData
      Imports historical billing data from external systems into core account tables while preserving manual user modifications and application fields.
      · reads AccountChargeChargeMeasuresChargeTypeFacilityInsuranceMapPatientProviderTransformAccount
      ↳ feeds from Load ChargeMeasures
      custom account charge queryauto account querycustom account remit querycustom account insurance query
    5. Load-Custom-Propertiessub-jobdisabled
    6. SyncBatchSyncBatchdisabled
      Synchronizes processed data from batch staging tables to production tables, updating visits, quotes, and custom property mappings.
      writes CustomPropertyMap · reads CustomPropertyMapBatch
      sync custom property mapDISABLED
    7. ▶Score-Accountssub-job · 3↗
      1. Rescore Profee AccountsBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
      2. Rescore Accounts FacilityBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        incremental
      3. Medicare ScenarioBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        scenario insurance=Medicareincremental
    8. Load AccountDetailsqlrevfind_load_account_data__load_accountdetail.sqldisabled
      ✨ Builds a comprehensive account summary table by combining patient, insurance, and financial data for detailed encounter reporting.
      writes AccountDetail · reads AccountAccountChargeAccountDeltaAccountEstimateDimensionMemberPatientSourceEncounterInsuranceSourcePayer
    9. Load AccountChargeDetailsqlrevfind_load_account_data__load_accountchargedetail.sqldisabled
      ✨ Loads granular charge-level details including procedure codes, modifiers, and payment metrics for individual service line analysis.
      writes AccountChargeDetail · reads AccountAccountChargeAccountDeltaAccountEstimateCalculationAccountEstimateChargeDimensionMemberPatientSourceEncounterInsurance
  7. WorkqueueAssignmentWorkqueueAssignment
    Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.