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

sub-job

USDH · Load-Transform-RemitData

called by Daily-Processing

sub-job USDH/Load-Transform-RemitData · db USDH_ProdMirror · updated 2025-09-17

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

18 tasks · USDH
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 TransformRemitsqlload_transform_remitdata__load_transformremit.sql
    ✨ Standardizes remittance claim data by mapping filing indicators and calculating sequence ordinals for payment tracking.
    writes TransformRemit · reads Txn835Txn835ClaimPaymentTxn835ClaimPaymentLineTxn835ClaimPaymentOtherClaimAmount
  2. Load TempRemitCOBsqlload_transform_remitdata__load_tempremitcob.sql
    ✨ Calculates and updates the coordination of benefits sequence and claim ranking for remittance records.
    writes TempRemitCOBTransformRemit · reads TempRemitCOB
  3. 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
  4. Load TransformRemitLinesqlload_transform_remitdata__load_transformremitline.sql
    ✨ Builds standardized remittance line items by processing service dates, procedure codes, and unique charge matching identifiers.
    writes TransformRemitLine · reads TransformRemitTxn835ClaimPaymentLine
    ↳ feeds from Load TempRemitCOB, Load TransformRemit
  5. Load TransformRemitAdjustmentAggregatesqlload_transform_remitdata__load_transformremitadjustmentaggregate.sql
    ✨ Aggregates claim adjustments to identify and categorize initial and current denials based on standardized reason code rollups.
    writes TransformRemitAdjustmentAggregate · reads AdjustmentCodeRollupTransformRemitTransformRemitAdjustmentTransformRemitLine
    ↳ feeds from Load TempRemitCOB, Load TransformRemit, Load TransformRemitAdjustment, Load TransformRemitLine
  6. Load TransformChargeTransactionMeasuresqlload_transform_remitdata__load_transformchargetransactionmeasure.sql
    ✨ Aggregates financial metrics and denial categories at the charge level to support transaction measurement and reporting.
    writes TransformChargeTransactionMeasure · reads TransformRemitTransformRemitAdjustmentAggregateTransformRemitLine
    ↳ feeds from Load TempRemitCOB, Load TransformRemit, Load TransformRemitAdjustmentAggregate, Load TransformRemitLine
  7. Load TransformTxn837sqlload_transform_remitdata__load_transformtxn837.sql
    ✨ Loads and transforms outbound claim header data, determining bill types and mapping patient, subscriber, and provider information.
    writes TransformTxn837 · reads Txn837PTxn837PServiceLine
  8. Load TransformTxn837Linesqlload_transform_remitdata__load_transformtxn837line.sql
    ✨ Loads transformed service line details for outbound claims, including procedure codes, modifiers, and rendering provider information.
    writes TransformTxn837Line · reads TransformTxn837Txn837PServiceLine
    ↳ feeds from Load TransformTxn837
  9. Load TransformAccountsqlload_transform_remitdata__load_transformaccount.sql
    ✨ Builds a unified account view by consolidating encounter, provider, and multi-payer insurance information from claim transactions.
    writes TransformAccount · reads EDIFacilityMapTransformTxn837
    ↳ feeds from Load TransformTxn837
  10. Load TransformChargesqlload_transform_remitdata__load_transformcharge.sql
    ✨ Loads detailed service line charges by mapping procedure codes, modifiers, and units while generating unique keys for charge matching.
    writes TransformCharge · reads TransformAccountTransformTxn837TransformTxn837Line
    ↳ feeds from Load TransformAccount, Load TransformTxn837, Load TransformTxn837Line
  11. Merge TransformPatientsqlload_transform_remitdata__merge_transformpatient.sql
    ✨ Updates or inserts patient demographic records using the most recent contact and identity information found in claim transaction data.
    writes TransformPatient · reads TransformTxn837
    ↳ feeds from Load TransformTxn837
  12. Load TempTransactions (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactions_for_revfind_recovery.sql
    ✨ Builds a temporary table of payment and adjustment transactions by combining remittance line and adjustment data for recovery analysis.
    writes TempTransactions · reads TempTransactions
  13. Load TempTransactionsAgg (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactionsagg_for_revfind_recovery.sql
    ✨ Aggregates transaction data into a temporary table, calculating sums for payments, contracts, patient responsibility, and denials at the claim level.
    writes TempTransactionsAgg
  14. Load TempTransactionsAggTotal (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactionsaggtotal_for_revfind_recovery.sql
    ✨ Summarizes aggregated transaction data to provide total amounts and initial versus current denial categories for each claim and charge match.
    writes TempTransactionsAggTotal
  15. 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
  16. 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
  17. Append PatientID on TransformAccountsqlload_transform_remitdata__append_patientid_on_transformaccount.sql
    ✨ Updates account records with patient identifiers by matching claim transaction details against existing patient mapping data.
    · reads TransformAccountTransformPatientTransformTxn837ta
    ↳ feeds from Append TransformPatient, Second Append of PatientID to TransformAccount, Load TransformAccount, Load TransformTxn837, Merge TransformPatient
  18. Append TransformPatient, Second Append of PatientID to TransformAccountsqlload_transform_remitdata__append_transformpatient_second_append_of_patientid_to_transformaccount.sql
    ✨ Updates the patient master list and assigns patient identifiers to accounts using encounter and practice information.
    writes TempTransformPatientTransformPatient · reads TempTransformPatientTransformAccountta
    ↳ feeds from Load TransformAccount