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

Batch / file feed · RevFind pipeline

TheOregonClinic · RevFind-EDI-Daily-Processing

pipeline TheOregonClinic/RevFind-EDI-Daily-Processing · db TheOregonClinic_ProdMirror · updated 2026-06-12
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 - TheOregonClinicdaily at 03:45prod-db-3disabledstandalone

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

8 tasks · TheOregonClinic
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-Source-Processingsub-job · 4↗
    1. Download EDI & Archive 835sDownloaddisabled
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      remote directory=/local directory={SourceDataFolder}DISABLED
    2. Clean up unsupported filesExecuteProcess
    3. ▶RevFind-EDI-Source-LoadFilessub-job · 2↗
      1. Bulk Insert into DatabaseExecuteProcess
        command=powershell.exe
      2. Archive EDI FilesExecuteProcess
        command=cmd.exe
    4. ▶RevFind-EDI-Source-ParseEDIsub-job · 5↗
      1. EDITransformsqlrevfind_edi_source_parseedi__editransform.sql
        ✨ Loads unique EDI messages from staging into the transformation table while preventing duplicate records.
        writes EDITransform · reads EDIFileStagingEDITransform
      2. EDIBatchsqlrevfind_edi_source_parseedi__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_source_parseedi__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-Source-LoadMetadatasub-job · 4↗
        1. Load EDIFacilityMapsqlrevfind_edi_source_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_source_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_source_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
  2. ▶RevFind-EDI-Transform-EDIDatasub-job · 18↗
    1. Load RemitDeltasqlrevfind_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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_edi_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
  3. ▶RevFind-EDI-Process-LoadEntitiessub-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
      merge data source type=ThisDatabasetable name=Providermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
  4. ▶RevFind-EDI-Process-ChargeDimensionsub-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
  5. ▶RevFind-EDI-Process-LoadInsuranceMapsub-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
  6. ▶RevFind-EDI-Process-LoadDimensionMemberssub-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 Dimensionssqlrevfind_edi_process_loaddimensionmembers__load_time_dimensions.sql
      ✨ Updates the time dimension table with day, month, and year members to support temporal reporting across the pipeline.
      writes DimensionMember · reads TransformAccount
    3. Load ChargeType Dimensionsqlrevfind_edi_process_loaddimensionmembers__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 Dimensionsqlrevfind_edi_process_loaddimensionmembers__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 Dimensionssqlrevfind_edi_process_loaddimensionmembers__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 Dimensionssqlrevfind_edi_process_loaddimensionmembers__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 Dimensionssqlrevfind_edi_process_loaddimensionmembers__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 Dimensionssqlrevfind_edi_process_loaddimensionmembers__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 Dimensionssqlrevfind_edi_process_loaddimensionmembers__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 Dimensionssqlrevfind_edi_process_loaddimensionmembers__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 Dimensionsqlrevfind_edi_process_loaddimensionmembers__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 Dimensionsqlrevfind_edi_process_loaddimensionmembers__load_allowedstatus_dimension.sql
      ✨ Builds allowed status dimensions to categorize how actual payments compare to expected insurance contract rates.
      writes DimensionMember
    13. Load DiagnosisCode Dimensionsqlrevfind_edi_process_loaddimensionmembers__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-Dimensionsqlrevfind_edi_process_loaddimensionmembers__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 Dimensionsqlrevfind_edi_process_loaddimensionmembers__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
  7. ▶RevFind-EDI-Process-LoadAccountDatasub-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_edi_process_loadaccountdata__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. ▶RevFind-EDI-Score-Accountssub-job · 3↗
      1. Score Profee AccountsBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        incremental
      2. Score Facility AccountsBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        incremental
      3. Medicare ScenarioBatchProcessAccountsdisabled
        writes AccountEstimate · reads QuoteBatchVisitBatch
        incrementalDISABLED
    8. Load AccountDetailsqlrevfind_edi_process_loadaccountdata__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_edi_process_loadaccountdata__load_accountchargedetail.sqldisabled
      ✨ Loads granular charge-level details including procedure codes, modifiers, and payment metrics for individual service line analysis.
      writes AccountChargeDetail · reads AccountAccountChargeAccountDeltaAccountEstimateCalculationAccountEstimateChargeDimensionMemberPatientSourceEncounterInsurance
  8. WorkqueueAssignmentWorkqueueAssignment
    Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.