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

sub-job

ECP · Process-EDI

called by Daily-Accounts-Processing-RevFind

sub-job ECP/Process-EDI · db ECP_ProdMirror · updated 2025-12-04

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

Process-EDI

6 tasks · ECP
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. Download Files from SFTPDownload
    Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
    server=ECP_ProdMirror-mdcssh-sftpremote directory=/local directory={SourceDataFolderEDI}
  2. Download Files from SFTP (AMD)Download
    Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
    server=ECP_ProdMirror-mdcssh-sftpremote directory=/AMD/local directory={SourceDataFolderEDI}
  3. Download Files from SFTP (Trizetto)Download
    Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
    server=ECP_ProdMirror-mdcssh-sftpremote directory=/Trizetto/local directory={SourceDataFolderEDI}
  4. ▶Load-Edi-Filessub-job · 1↗
    1. ▶Load-Edi-Filesub-job · 3↗
      1. Bulk Insert File Into Databasesqlload_edi_file__bulk_insert_file_into_database.sqldisabled
        ✨ Loads raw electronic data interchange files into a staging table while identifying file types and subtypes for further processing.
        writes EDIFileStaging · reads SINGLE_CLOB
      2. Bulk Insert File Into Database via PowershellExecuteProcess
        command=powershell.exe
      3. Archive FilesExecuteProcess
        command=cmd.exe
  5. ▶TransformAndParseEDIsub-job · 5↗
    1. EDITransformsqltransformandparseedi__editransform.sql
      ✨ Loads unique EDI messages from staging into the transformation table while preventing duplicate records.
      writes EDITransform · reads EDIFileStagingEDITransform
    2. EDIBatchsqltransformandparseedi__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)sqltransformandparseedi__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. ▶Load-EDI-Metadatasub-job · 3↗
      1. Load EDIFacilityMapsqlload_edi_metadata__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 EDIProviderMapsqlload_edi_metadata__load_ediprovidermap.sql
        ✨ Builds the provider map by combining rendering provider information from claim transactions with national provider registry data.
        writes EDIProviderMap · reads EDIProviderMapNPIDataTxn837P
      3. Load EDIPayerMapsqlload_edi_metadata__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
  6. ▶Load-Transform-RemitDatasub-job · 24↗
    1. Load RemitDeltasqlload_transform_remitdata__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 TransformRemitsqlload_transform_remitdata__load_transformremit.sql
      ✨ Transforms raw remittance transaction data into a structured format by parsing payer details, payment amounts, and insurance filing indicators.
      writes TransformRemit · reads Txn835Txn835ClaimPaymentTxn835ClaimPaymentLineTxn835ClaimPaymentOtherClaimAmount
    3. Load TransformRemitEncounterMapsqlload_transform_remitdata__load_transformremitencountermap.sql
      ✨ Maps remittance claims to their respective data sources and patient accounts using provider identifiers and complex matching logic.
      writes TransformRemitEncounterMap · reads TransformRemitEncounterMap
    4. Update TransformRemitEncounterMapsqlload_transform_remitdata__update_transformremitencountermap.sql
      ✨ Updates remittance mapping records with patient account identifiers by matching service dates and charge IDs at the line level.
      · reads TransformChargeTransformRemitEncounterMapTransformRemitLinerem
      ↳ feeds from Load TransformRemitEncounterMap, Load TransformRemitLine
    5. Load TempRemitCOB (old)sqlload_transform_remitdata__load_tempremitcob_old.sqldisabled
      ✨ Builds a temporary table to determine claim status, member IDs, and coordination of benefits order for remittance data.
    6. Load TempRemitCOBsqlload_transform_remitdata__load_tempremitcob.sql
      ✨ Builds a temporary table mapping remittance data to encounters while calculating coordination of benefits and current claim status.
      writes TempRemitCOBTransformRemit · reads TempRemitCOBTransformRemitTransformRemitAdjustmentTransformRemitEncounterMap
      ↳ feeds from Load TransformRemit, Load TransformRemitAdjustment, Load TransformRemitEncounterMap
    7. Load TransformRemitAdjustmentsqlload_transform_remitdata__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 TransformRemitLinesqlload_transform_remitdata__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 TransformRemitAdjustmentAggregatesqlload_transform_remitdata__load_transformremitadjustmentaggregate.sqldisabled
      ✨ Aggregates claim adjustments to identify and categorize initial and current denials based on standardized reason code rollups.
      writes TransformRemitAdjustmentAggregate · reads AdjustmentCodeRollupTransformRemitTransformRemitAdjustmentTransformRemitLine
    10. Load TransformChargeTransactionMeasureRemitsqlload_transform_remitdata__load_transformchargetransactionmeasureremit.sqldisabled
      ✨ Summarizes financial metrics including payments, charges, and denials at the line level for remittance reporting and analysis.
      writes TransformChargeTransactionMeasureRemit · reads TransformRemitTransformRemitAdjustmentAggregateTransformRemitLine
    11. Load TransformTxn837sqlload_transform_remitdata__load_transformtxn837.sqldisabled
      ✨ Loads and transforms outbound claim header data, determining bill types and mapping patient, subscriber, and provider information.
      writes TransformTxn837 · reads Txn837PTxn837PServiceLine
    12. Load TransformTxn837Linesqlload_transform_remitdata__load_transformtxn837line.sqldisabled
      ✨ Loads transformed service line details for outbound claims, including procedure codes, modifiers, and rendering provider information.
      writes TransformTxn837Line · reads TransformTxn837Txn837PServiceLine
    13. Load TransformAccountsqlload_transform_remitdata__load_transformaccount.sqldisabled
      ✨ Builds the account transformation table by consolidating primary and secondary claim data, provider details, and insurance information from EDI sources.
      writes TransformAccount · reads EDIFacilityMapTransformTxn837
    14. Load TransformChargesqlload_transform_remitdata__load_transformcharge.sqldisabled
      ✨ Loads detailed service line charges by mapping procedure codes, modifiers, and units while generating unique keys for charge matching.
      writes TransformCharge · reads TransformAccountTransformTxn837TransformTxn837Line
    15. Merge TransformPatientsqlload_transform_remitdata__merge_transformpatient.sqldisabled
      ✨ Updates or inserts patient demographic records using the most recent contact and identity information found in claim transaction data.
      writes TransformPatient · reads TransformTxn837
    16. Load TempTransactions (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactions_for_revfind_recovery.sql
      ✨ Builds a temporary transaction table by combining payment and adjustment data from remittance records and categorizing them for recovery analysis.
      writes TempTransactions · reads TempTransactions
    17. Load TempTransactionsAgg (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactionsagg_for_revfind_recovery.sql
      ✨ Aggregates transaction data by claim and line item to calculate total payments, adjustments, and denials for recovery reporting.
      writes TempTransactionsAgg
    18. Load TempTransactionsAggTotal (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactionsaggtotal_for_revfind_recovery.sql
      ✨ Summarizes aggregated transactions to compare initial and current payment, denial, and adjustment totals across different coordination of benefits levels.
      writes TempTransactionsAggTotal · reads TempTransactionsAggTotal
    19. Load TempTransactionsAggTotalFinal (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactionsaggtotalfinal_for_revfind_recovery.sqldisabled
      ✨ Produces the final consolidated summary of financial metrics, including total paid, denied, and adjusted amounts for each claim line.
      writes TempTransactionsAggTotalFinal
    20. Update TransformChargeTransactionMeasure (for RevFind Recovery)sqlload_transform_remitdata__update_transformchargetransactionmeasure_for_revfind_recovery.sqldisabled
      ✨ Updates the charge transaction measurement table with final calculated payment, patient responsibility, and contract adjustment totals for recovery tracking.
      · reads TempTransactionsAggTotalFinalTransformChargeTransactionMeasuret
    21. Append PatientID on TransformAccountsqlload_transform_remitdata__append_patientid_on_transformaccount.sqldisabled
      ✨ Updates account records with patient identifiers by matching claim transaction details against existing patient mapping data.
      · reads TransformAccountTransformPatientTransformTxn837ta
    22. Append TransformPatient, Second Append of PatientID to TransformAccountsqlload_transform_remitdata__append_transformpatient_second_append_of_patientid_to_transformaccount.sqldisabled
      ✨ Updates the patient master list and assigns patient identifiers to accounts using encounter and practice information.
      writes TempTransformPatientTransformPatient · reads TempTransformPatientTransformAccountta
    23. TempChargeLinkRemit (moved to Load Account Transform)sqlload_transform_remitdata__tempchargelinkremit_moved_to_load_account_transform.sqldisabled
      ✨ Builds a temporary mapping table to link charge records with remittance data based on procedure codes and service dates.
      writes TempChargeLinkRemit · reads ChargeTypeSourceChargeSourceEncounterTempChargeLinkRemitTransformCharge
    24. TempTransactionsAggTotalLink (moved to Load Account Transform)sqlload_transform_remitdata__temptransactionsaggtotallink_moved_to_load_account_transform.sqldisabled
      ✨ Builds an aggregated transaction table that links financial totals to specific accounts and charges for remittance reporting.
      writes TempTransactionsAggTotalLink · reads TempChargeLinkRemitTempTransactionsAggTotalTempTransactionsAggTotalLink