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

sub-job

ECP · Load-Transform-RemitData

called by Process-EDI

sub-job ECP/Load-Transform-RemitData · db ECP_ProdMirror · updated 2025-12-09

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

Load-Transform-RemitData

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