fannie_mae_quicktest_local_fs_one script and data

Input files

Payments

Current Actual UPBUPB at IssuanceOriginal Interest RateLoan IdentifierSeller NameMonthly Reporting PeriodRemaining Months To MaturityOrigination DateDeal NameBorrower Credit Score at OriginationZero Balance Effective DateScheduled Principal CurrentRemaining Months to Legal MaturityOriginal UPBOriginal Loan Term
485775.65485775.656.875136610574Flagstar Bank, National Association2023082035020221020CAS 2023 R08 G173500351490000360
248112.85248112.856.375136610575Planet Home Lending, LLC2023082035220221020CAS 2023 R08 G170900352250000360
468571.34468571.346.5136610576Rocket Mortgage, LLC2023082035120221020CAS 2023 R08 G178700351473000360
307534.77307534.775.25136610577Rocket Mortgage, LLC2023082035120220920CAS 2023 R08 G173200351311000360
348025.67348025.675.5136610578U.S. Bank N.A.2023082035020221020CAS 2023 R08 G176300351352000360
139258.92139258.926136610579Other2023082033920220920CAS 2023 R08 G172000351142000360
202982.62202982.625.875136610580U.S. Bank N.A.2023082035120221020CAS 2023 R08 G179900351205000360
172686.14172686.146.575136610581Planet Home Lending, LLC2023082035220221020CAS 2023 R08 G169000352174000360
110751.32110751.325.375136610582Other2023082032320221020CAS 2023 R08 G176200351116000360
231756.02231756.025.5136610583JPMorgan Chase Bank, National Association2023082035020220920CAS 2023 R08 G179500350234000360

Total 30200 rows

01_read_payments

Script node 01_read_payments:

{
  "01_read_payments": {
    "type": "file_table",
    "desc": "Read source files to a table",
    "r": {
      "urls": "{file_urls|stringlist}",
      "columns": {
        "col_loan_id": {
          "col_type": "int",
          "parquet": {
            "col_name": "Loan Identifier"
          }
        },
        "col_deal_name": {
          "col_type": "string",
          "parquet": {
            "col_name": "Deal Name"
          }
        },
        "col_seller_name": {
          "col_type": "string",
          "parquet": {
            "col_name": "Seller Name"
          }
        },
        "col_origination_date": {
          "col_type": "int",
          "parquet": {
            "col_name": "Origination Date"
          }
        },
        "col_original_interest_rate": {
          "col_type": "float",
          "parquet": {
            "col_name": "Original Interest Rate"
          }
        },
        "col_borrower_credit_score_at_origination": {
          "col_type": "int",
          "parquet": {
            "col_name": "Borrower Credit Score at Origination"
          }
        },
        "col_original_upb": {
          "col_type": "decimal2",
          "parquet": {
            "col_name": "Original UPB"
          }
        },
        "col_upb_at_issuance": {
          "col_type": "decimal2",
          "parquet": {
            "col_name": "UPB at Issuance"
          }
        },
        "col_original_loan_term": {
          "col_type": "int",
          "parquet": {
            "col_name": "Original Loan Term"
          }
        },
        "col_monthly_reporting_period": {
          "col_type": "int",
          "parquet": {
            "col_name": "Monthly Reporting Period"
          }
        },
        "col_current_actual_upb": {
          "col_type": "decimal2",
          "parquet": {
            "col_name": "Current Actual UPB"
          }
        },
        "col_remaining_months_to_legal_maturity": {
          "col_type": "int",
          "parquet": {
            "col_name": "Remaining Months to Legal Maturity"
          }
        },
        "col_remaining_months_to_maturity": {
          "col_type": "int",
          "parquet": {
            "col_name": "Remaining Months To Maturity"
          }
        },
        "col_zero_balance_effective_date": {
          "col_type": "int",
          "parquet": {
            "col_name": "Zero Balance Effective Date"
          }
        }
      }
    },
    "w": {
      "name": "payments",
      "having_tmp": "(w.zero_balance_effective_date == 0 || w.scheduled_principal_current > 0) && w.original_income > 0",
      "fields": {
        "loan_id": {
          "expression": "r.col_loan_id",
          "type": "int"
        },
        "deal_name": {
          "expression": "r.col_deal_name",
          "type": "string"
        },
        "origination_date": {
          "expression": "r.col_origination_date",
          "type": "int"
        },
        "seller_name": {
          "expression": "r.col_seller_name",
          "type": "string"
        },
        "original_interest_rate": {
          "expression": "r.col_original_interest_rate",
          "type": "float"
        },
        "borrower_credit_score_at_origination": {
          "expression": "r.col_borrower_credit_score_at_origination",
          "type": "int"
        },
        "original_upb": {
          "expression": "r.col_original_upb",
          "type": "decimal2"
        },
        "upb_at_issuance": {
          "expression": "r.col_upb_at_issuance",
          "type": "decimal2"
        },
        "original_loan_term": {
          "expression": "r.col_original_loan_term",
          "type": "int"
        },
        "payment_json": {
          "expression": "strings.ReplaceAll(fmt.Sprintf(`{'monthly_reporting_period':%d,'current_actual_upb':%s,'remaining_months_to_legal_maturity':%d,'remaining_months_to_maturity':%d,'zero_balance_effective_date':%d}`,r.col_monthly_reporting_period, r.col_current_actual_upb, r.col_remaining_months_to_legal_maturity, r.col_remaining_months_to_maturity,r.col_zero_balance_effective_date), `'`,`\"`)",
          "type": "string"
        }
      },
      "indexes": {
        "idx_payments_by_loan_id": "non_unique(loan_id)"
      }
    }
  }
}

Script node 01_read_payments produces Cassandra table payments:

rowidbatch_idxborrower_credit_score_at_originationdeal_nameloan_idoriginal_interest_rateoriginal_loan_termoriginal_upborigination_datepayment_jsonseller_nameupb_at_issuance
26926988280234700540763CAS 2023 R08 G11366256906.25360291000.0020221020{"monthly_reporting_period":20230820,"current_actual_upb":288660.84,"remaining_months_to_legal_matur ... total length 176Rocket Mortgage, LLC288660.84
39971314454144567081689CAS 2023 R08 G11366427976.75360407000.0020221220{"monthly_reporting_period":20230820,"current_actual_upb":404597.56,"remaining_months_to_legal_matur ... total length 176Other404597.56
29287706625284604621768CAS 2023 R08 G11366627026.12536084000.0020220920{"monthly_reporting_period":20230820,"current_actual_upb":83164.58,"remaining_months_to_legal_maturi ... total length 175Other83164.58
44337959617906582421736CAS 2023 R08 G11366504515.625360276000.0020220920{"monthly_reporting_period":20230820,"current_actual_upb":272590.71,"remaining_months_to_legal_matur ... total length 176Rocket Mortgage, LLC272590.71
12664578181419246470793CAS 2023 R08 G11366168395360363000.0020220820{"monthly_reporting_period":20230820,"current_actual_upb":358495.61,"remaining_months_to_legal_matur ... total length 176Other358495.61
45601713430552582080734CAS 2023 R08 G11366196747.625360191000.0020221120{"monthly_reporting_period":20230820,"current_actual_upb":190043.18,"remaining_months_to_legal_matur ... total length 176Other190043.18
76245279838411557311777CAS 2023 R08 G11366435477.12536079000.0020221120{"monthly_reporting_period":20230820,"current_actual_upb":78235.63,"remaining_months_to_legal_maturi ... total length 175United Wholesale Mortgage, LLC78235.63
77917301233618168230793CAS 2023 R08 G11366327925.625360480000.0020221020{"monthly_reporting_period":20230820,"current_actual_upb":474480.85,"remaining_months_to_legal_matur ... total length 176Other474480.85
36496283878328939191809CAS 2023 R08 G11366707815.25360559000.0020221020{"monthly_reporting_period":20230820,"current_actual_upb":546107.15,"remaining_months_to_legal_matur ... total length 176Other546107.15
87076796879337282751808CAS 2023 R08 G11366626185.99360600000.0020221020{"monthly_reporting_period":20230820,"current_actual_upb":589153.26,"remaining_months_to_legal_matur ... total length 176Guaranteed Rate, Inc.589153.26

Total 60345 rows

02_loan_ids

Script node 02_loan_ids:

{
  "02_loan_ids": {
    "type": "distinct_table",
    "desc": "Select distinct loan ids",
    "start_policy": "{manual_if_multi}",
    "rerun_policy": "fail",
    "r": {
      "table": "payments",
      "rowset_size": 10000,
      "expected_batches_total": "{expected_batches|number}"
    },
    "w": {
      "name": "loan_ids",
      "fields": {
        "loan_id": {
          "expression": "r.loan_id",
          "type": "int"
        },
        "deal_name": {
          "expression": "r.deal_name",
          "type": "string"
        },
        "origination_date": {
          "expression": "r.origination_date",
          "type": "int"
        },
        "seller_name": {
          "expression": "r.seller_name",
          "type": "string"
        },
        "original_interest_rate": {
          "expression": "r.original_interest_rate",
          "type": "float"
        },
        "borrower_credit_score_at_origination": {
          "expression": "r.borrower_credit_score_at_origination",
          "type": "int"
        },
        "original_upb": {
          "expression": "r.original_upb",
          "type": "decimal2"
        },
        "upb_at_issuance": {
          "expression": "r.upb_at_issuance",
          "type": "decimal2"
        },
        "original_loan_term": {
          "expression": "r.original_loan_term",
          "type": "int"
        }
      },
      "indexes": {
        "idx_loan_ids_loan_id": "unique(loan_id)",
        "idx_loan_ids_deal_name": "non_unique(deal_name)"
      }
    }
  }
}

Script node 02_loan_ids produces Cassandra table loan_ids:

rowidbatch_idxborrower_credit_score_at_originationdeal_nameloan_idoriginal_interest_rateoriginal_loan_termoriginal_upborigination_dateseller_nameupb_at_issuance
64578910392596491049767CAS 2023 R08 G11366609295.125360114000.0020220920Other112240.33
85951167222812862740755CAS 2023 R08 G11366252534.875360379000.0020221020Truist Bank374737.06
6423126487632714915777CAS 2023 R08 G11366447736.125360336000.0020221120Guaranteed Rate, Inc.282979.09
64313178213363403737804CAS 2023 R08 G11366222476.25360284000.0020221020Wells Fargo Bank, N.A.281241.23
5385705463310603357713CAS 2023 R08 G11366610776.125360213000.0020220820Other210465.86
91253675912813528551804CAS 2023 R08 G11366363606.7360175000.0020221120Truist Bank173758.69
38911496171989865148698CAS 2023 R08 G11366248355.875360505000.0020221120Other500746.75
90036929996089119161787CAS 2023 R08 G11366497095.625360288000.0020220920Other284532.60
72294151945105473576745CAS 2023 R08 G11366255965.75360880000.0020221020Planet Home Lending, LLC871570.76
62767623382596846857774CAS 2023 R08 G11366528594.125360376000.0020220920Other370708.19

Total 60345 rows

02_deal_names

Script node 02_deal_names:

{
  "02_deal_names": {
    "type": "distinct_table",
    "desc": "Select distinct deal names",
    "start_policy": "{manual_if_multi}",
    "rerun_policy": "fail",
    "r": {
      "table": "loan_ids",
      "rowset_size": 10000,
      "expected_batches_total": "{expected_batches|number}"
    },
    "w": {
      "name": "deal_names",
      "fields": {
        "deal_name": {
          "expression": "r.deal_name",
          "type": "string"
        }
      },
      "indexes": {
        "idx_deal_names_deal_name": "unique(deal_name)"
      }
    }
  }
}

Script node 02_deal_names produces Cassandra table deal_names:

rowidbatch_idxdeal_name
5319896468569258900CAS 2023 R08 G1

Total 1 rows

03_deal_total_upbs

Script node 03_deal_total_upbs:

{
  "03_deal_total_upbs": {
    "type": "table_lookup_table",
    "desc": "For each deal, calculate total UPBs",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "deal_names",
      "expected_batches_total": "{expected_batches|number}"
    },
    "l": {
      "index_name": "idx_loan_ids_deal_name",
      "join_on": "r.deal_name",
      "idx_read_batch_size": 10000,
      "right_lookup_read_batch_size": 10000,
      "group": true,
      "join_type": "left"
    },
    "w": {
      "name": "deal_total_upbs",
      "fields": {
        "deal_name": {
          "expression": "r.deal_name",
          "type": "string"
        },
        "total_original_upb": {
          "expression": "sum(l.original_upb)",
          "type": "decimal2"
        },
        "total_upb_at_issuance": {
          "expression": "sum(l.upb_at_issuance)",
          "type": "decimal2"
        },
        "total_original_upb_for_nonzero_rates": {
          "expression": "sum_if(l.original_upb, l.original_interest_rate > 0)",
          "type": "decimal2"
        },
        "total_original_upb_for_nonzero_credit_scores": {
          "expression": "sum_if(l.original_upb, l.borrower_credit_score_at_origination > 0)",
          "type": "decimal2"
        }
      }
    }
  }
}

Script node 03_deal_total_upbs produces Cassandra table deal_total_upbs:

rowidbatch_idxdeal_nametotal_original_upbtotal_original_upb_for_nonzero_credit_scorestotal_original_upb_for_nonzero_ratestotal_upb_at_issuance
86302964345107833525CAS 2023 R08 G119356281000.0019339292000.0019356281000.0018880190518.06

Total 1 rows

04_loan_payment_summaries

Script node 04_loan_payment_summaries:

{
  "04_loan_payment_summaries": {
    "type": "table_lookup_table",
    "desc": "For each loan, merge all payments into single json string",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "loan_ids",
      "expected_batches_total": "{expected_batches|number}"
    },
    "l": {
      "index_name": "idx_payments_by_loan_id",
      "join_on": "r.loan_id",
      "group": true,
      "join_type": "left"
    },
    "w": {
      "name": "loan_payment_summaries",
      "fields": {
        "loan_id": {
          "expression": "r.loan_id",
          "type": "int"
        },
        "deal_name": {
          "expression": "r.deal_name",
          "type": "string"
        },
        "origination_date": {
          "expression": "r.origination_date",
          "type": "int"
        },
        "seller_name": {
          "expression": "r.seller_name",
          "type": "string"
        },
        "original_interest_rate": {
          "expression": "r.original_interest_rate",
          "type": "float"
        },
        "borrower_credit_score_at_origination": {
          "expression": "r.borrower_credit_score_at_origination",
          "type": "int"
        },
        "original_upb": {
          "expression": "r.original_upb",
          "type": "decimal2"
        },
        "upb_at_issuance": {
          "expression": "r.upb_at_issuance",
          "type": "decimal2"
        },
        "original_loan_term": {
          "expression": "r.original_loan_term",
          "type": "int"
        },
        "payments_json": {
          "expression": "string_agg(l.payment_json,\",\")",
          "type": "string"
        }
      }
    }
  }
}

Script node 04_loan_payment_summaries produces Cassandra table loan_payment_summaries:

rowidbatch_idxborrower_credit_score_at_originationdeal_nameloan_idoriginal_interest_rateoriginal_loan_termoriginal_upborigination_datepayments_jsonseller_nameupb_at_issuance
16152317381862878303752CAS 2023 R08 G11366615696.125360134000.0020220820{"monthly_reporting_period":20230820,"current_actual_upb":132925.83,"remaining_months_to_legal_matur ... total length 176CitiMortgage, Inc.132925.83
73889349757961159098784CAS 2023 R08 G11366121186.5360370000.0020221020{"monthly_reporting_period":20230820,"current_actual_upb":366923.6,"remaining_months_to_legal_maturi ... total length 175Other366923.60
73451261073332341340735CAS 2023 R08 G11366497747.125360284000.0020220820{"monthly_reporting_period":20230820,"current_actual_upb":280901.84,"remaining_months_to_legal_matur ... total length 176Wells Fargo Bank, N.A.280901.84
20957974909388988006780CAS 2023 R08 G11366532206.625360253000.0020221020{"monthly_reporting_period":20230820,"current_actual_upb":249839.53,"remaining_months_to_legal_matur ... total length 176Other249839.53
43816821788067005870739CAS 2023 R08 G11366377037.6253601013000.0020221120{"monthly_reporting_period":20230820,"current_actual_upb":1006505.42,"remaining_months_to_legal_matu ... total length 177NewRez LLC1006505.42
84720240478438723950781CAS 2023 R08 G11366115465.125360412000.0020221120{"monthly_reporting_period":20230820,"current_actual_upb":385685.84,"remaining_months_to_legal_matur ... total length 176Guaranteed Rate, Inc.385685.84
20145153445373048655818CAS 2023 R08 G11366189385.375360362000.0020221020{"monthly_reporting_period":20230820,"current_actual_upb":158739.13,"remaining_months_to_legal_matur ... total length 176Guaranteed Rate, Inc.158739.13
52787865960057087928795CAS 2023 R08 G11366457604.99360295000.0020221020{"monthly_reporting_period":20230820,"current_actual_upb":291740.39,"remaining_months_to_legal_matur ... total length 176PennyMac Loan Services, LLC291740.39
32825194019894316633711CAS 2023 R08 G11366626787.125360528000.0020221120{"monthly_reporting_period":20230820,"current_actual_upb":524551.12,"remaining_months_to_legal_matur ... total length 176Guild Mortgage Company LLC524551.12
37153026056734281859772CAS 2023 R08 G11366471246.375360400000.0020220920{"monthly_reporting_period":20230820,"current_actual_upb":396329.19,"remaining_months_to_legal_matur ... total length 176Rocket Mortgage, LLC396329.19

Total 60345 rows

04_loan_smrs_clcltd

Script node 04_loan_smrs_clcltd:

{
  "04_loan_smrs_clcltd": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "py_calc",
    "desc": "Apply Python calculations to loan summaries",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "loan_payment_summaries",
      "rowset_size": 1000,
      "expected_batches_total": "{expected_batches|number}"
    },
    "p": {
      "python_code_urls": [
        "{dir_cfg}/py/payment_calc.py"
      ],
      "calculated_fields": {
        "sorted_payments_json": {
          "expression": "sorted_payments_json(r.payments_json)",
          "type": "string"
        },
        "payments_behind_ratio": {
          "expression": "payments_behind_ratio(r.payments_json)",
          "type": "float"
        },
        "paid_off_amount": {
          "expression": "paid_off_amount(r.original_upb,r.upb_at_issuance,r.payments_json)",
          "type": "decimal2"
        },
        "paid_off_ratio": {
          "expression": "paid_off_ratio(r.original_upb,r.upb_at_issuance,r.payments_json)",
          "type": "float"
        }
      }
    },
    "w": {
      "name": "loan_smrs_clcltd",
      "fields": {
        "loan_id": {
          "expression": "r.loan_id",
          "type": "int"
        },
        "deal_name": {
          "expression": "r.deal_name",
          "type": "string"
        },
        "origination_date": {
          "expression": "r.origination_date",
          "type": "int"
        },
        "seller_name": {
          "expression": "r.seller_name",
          "type": "string"
        },
        "original_interest_rate": {
          "expression": "r.original_interest_rate",
          "type": "float"
        },
        "borrower_credit_score_at_origination": {
          "expression": "r.borrower_credit_score_at_origination",
          "type": "int"
        },
        "original_upb": {
          "expression": "r.original_upb",
          "type": "decimal2"
        },
        "upb_at_issuance": {
          "expression": "r.upb_at_issuance",
          "type": "decimal2"
        },
        "original_loan_term": {
          "expression": "r.original_loan_term",
          "type": "int"
        },
        "is_original_loan_term_30y": {
          "expression": "int.iif(r.original_loan_term == 360, 1, 0)",
          "type": "int"
        },
        "payments_json": {
          "expression": "p.sorted_payments_json",
          "type": "string"
        },
        "payments_behind_ratio": {
          "expression": "p.payments_behind_ratio",
          "type": "float"
        },
        "paid_off_amount": {
          "expression": "decimal2(p.paid_off_amount)",
          "type": "decimal2"
        },
        "paid_off_ratio": {
          "expression": "p.paid_off_ratio",
          "type": "float"
        }
      },
      "indexes": {
        "idx_loan_smrs_clcltd_deal_name": "non_unique(deal_name)",
        "idx_loan_smrs_clcltd_deal_name_seller_name": "non_unique(deal_name,seller_name)"
      }
    }
  }
}

Script node 04_loan_smrs_clcltd produces Cassandra table loan_smrs_clcltd:

rowidbatch_idxborrower_credit_score_at_originationdeal_nameis_original_loan_term_30yloan_idoriginal_interest_rateoriginal_loan_termoriginal_upborigination_datepaid_off_amountpaid_off_ratiopayments_behind_ratiopayments_jsonseller_nameupb_at_issuance
6032043041754069011743CAS 2023 R08 G111366608544.75360100000.00202210201150.440.01150440[{"monthly_reporting_period": 20230820, "current_actual_upb": 98849.56, "remaining_months_to_legal_m ... total length 186Other98849.56
8312088688870769876791CAS 2023 R08 G111366355007.125360252000.00202211201857.360.00737047619050[{"monthly_reporting_period": 20230820, "current_actual_upb": 250142.64, "remaining_months_to_legal_ ... total length 187Flagstar Bank, National Association250142.64
70536646865155733309806CAS 2023 R08 G111366707786.625360296000.00202210202677.360.00904513513510[{"monthly_reporting_period": 20230820, "current_actual_upb": 293322.64, "remaining_months_to_legal_ ... total length 187Other293322.64
17592358504941908949702CAS 2023 R08 G111366182786.375360403000.00202210203927.380.00974535980150[{"monthly_reporting_period": 20230820, "current_actual_upb": 399072.62, "remaining_months_to_legal_ ... total length 187Rocket Mortgage, LLC399072.62
45768869641967659390804CAS 2023 R08 G111366185096.875360320000.00202210202475.740.00773668750[{"monthly_reporting_period": 20230820, "current_actual_upb": 317524.26, "remaining_months_to_legal_ ... total length 187Other317524.26
80589733520694716303754CAS 2023 R08 G111366509305.875360578000.00202209207137.560.01234871972320[{"monthly_reporting_period": 20230820, "current_actual_upb": 570862.44, "remaining_months_to_legal_ ... total length 187Other570862.44
2342379019394140115774CAS 2023 R08 G111366300136.875360278000.00202211202547.660.00916424460430[{"monthly_reporting_period": 20230820, "current_actual_upb": 275452.34, "remaining_months_to_legal_ ... total length 187United Wholesale Mortgage, LLC275452.34
28842555360109155685739CAS 2023 R08 G111366248375.125360364000.00202209204356.550.0119685439560[{"monthly_reporting_period": 20230820, "current_actual_upb": 359643.45, "remaining_months_to_legal_ ... total length 187Other359643.45
31112105253723726318680CAS 2023 R08 G111366149717.25360188000.002022112012196.510.06487505319150[{"monthly_reporting_period": 20230820, "current_actual_upb": 175803.49, "remaining_months_to_legal_ ... total length 187United Wholesale Mortgage, LLC175803.49
87138784761401626687703CAS 2023 R08 G111366521276.5360113000.0020221020542.930.00480469026550[{"monthly_reporting_period": 20230820, "current_actual_upb": 112457.07, "remaining_months_to_legal_ ... total length 187Other112457.07

Total 60345 rows

05_deal_seller_summaries

Script node 05_deal_seller_summaries:

{
  "05_deal_seller_summaries": {
    "type": "table_lookup_table",
    "desc": "For each deal/seller, calculate aggregates from calculated loan summaries",
    "start_policy": "{manual_if_multi}",
    "rerun_policy": "fail",
    "r": {
      "table": "deal_sellers",
      "expected_batches_total": "{expected_batches|number}"
    },
    "l": {
      "index_name": "idx_loan_smrs_clcltd_deal_name_seller_name",
      "join_on": "r.deal_name,r.seller_name",
      "group": true,
      "join_type": "left"
    },
    "w": {
      "name": "deal_seller_summaries",
      "fields": {
        "deal_name": {
          "expression": "r.deal_name",
          "type": "string"
        },
        "seller_name": {
          "expression": "r.seller_name",
          "type": "string"
        },
        "avg_original_interest_rate": {
          "expression": "avg(l.original_interest_rate)",
          "type": "float"
        },
        "min_original_interest_rate": {
          "expression": "min(l.original_interest_rate)",
          "type": "float"
        },
        "max_original_interest_rate": {
          "expression": "max(l.original_interest_rate)",
          "type": "float"
        },
        "avg_borrower_credit_score_at_origination": {
          "expression": "avg(float(l.borrower_credit_score_at_origination))",
          "type": "float"
        },
        "min_borrower_credit_score_at_origination": {
          "expression": "min(l.borrower_credit_score_at_origination)",
          "type": "int"
        },
        "max_borrower_credit_score_at_origination": {
          "expression": "max(l.borrower_credit_score_at_origination)",
          "type": "int"
        },
        "total_original_upb": {
          "expression": "sum(l.original_upb)",
          "type": "decimal2"
        },
        "total_upb_at_issuance": {
          "expression": "sum(l.upb_at_issuance)",
          "type": "decimal2"
        },
        "total_loans": {
          "expression": "count()",
          "type": "int"
        },
        "total_original_loan_term_30y": {
          "expression": "sum(l.is_original_loan_term_30y)",
          "type": "int"
        },
        "avg_payments_behind_ratio": {
          "expression": "avg(l.payments_behind_ratio)",
          "type": "float"
        },
        "total_paid_off_amount": {
          "expression": "sum(l.paid_off_amount)",
          "type": "decimal2"
        },
        "avg_paid_off_ratio": {
          "expression": "avg(l.paid_off_ratio)",
          "type": "float"
        }
      }
    }
  }
}

Script node 05_deal_seller_summaries produces Cassandra table deal_seller_summaries:

rowidavg_borrower_credit_score_at_originationavg_original_interest_rateavg_paid_off_ratioavg_payments_behind_ratiobatch_idxdeal_namemax_borrower_credit_score_at_originationmax_original_interest_ratemin_borrower_credit_score_at_originationmin_original_interest_rateseller_nametotal_loanstotal_original_loan_term_30ytotal_original_upbtotal_paid_off_amounttotal_upb_at_issuance
2605030512062607645761.103000811035.9382400648820.025362034669607CAS 2023 R08 G18237.62502.99Guaranteed Rate, Inc.12331233443720000.0012278921.05431441078.95
7681190877932109215749.6575121163176.0084798061390.018049534705102CAS 2023 R08 G18217.6256204.25Lakeview Loan Servicing, LLC619612231574000.004177208.66227396791.34
3666906138091569880748.0602032838156.4518256450350.020118542519507CAS 2023 R08 G18247.9902.75Planet Home Lending, LLC12791277432167000.008169068.41423997931.59
2717906083198624711756.4925690021235.72034925690.027638807748202CAS 2023 R08 G18237.62503.99Fifth Third Bank, National Association942941285578000.008575728.03277002271.97
744404163417716791764.3809523809525.7637082939140.026760062322304CAS 2023 R08 G18287.62502.75Wells Fargo Bank, N.A.317131661115305000.0032102763.261083202236.74
4082150750686757249751.0086880973075.9414031277150.018653755304601CAS 2023 R08 G18218.1256203loanDepot.com, LLC11511151388120000.007360117.11380759882.89
8132355452051647433736.6859323882226.3111908396950.017917753992505CAS 2023 R08 G18208.1256014.25Nationstar Mortgage LLC917913266952000.005065464.57261886535.43
292831738858778117753.408756.693318750.021668004548203CAS 2023 R08 G18198.12503.5CrossCountry Mortgage, LLC800798237075000.005715230.05231359769.95
6183503794039805140753.5878260869576.5344765217390.01936502094909CAS 2023 R08 G18248.1256233.75Flagstar Bank, National Association575575194992000.004062149.92190929850.08
3592591457388772981754.7327162279436.510392826150.01857825000302CAS 2023 R08 G18298.1256204.125United Wholesale Mortgage, LLC345734521148073000.0022176379.261125896620.74

Total 25 rows

05_deal_summaries

Script node 05_deal_summaries:

{
  "05_deal_summaries": {
    "type": "table_lookup_table",
    "desc": "For each deal, calculate aggregates from calculated loan summaries",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "deal_total_upbs",
      "expected_batches_total": "{expected_batches|number}"
    },
    "l": {
      "index_name": "idx_loan_smrs_clcltd_deal_name",
      "join_on": "r.deal_name",
      "group": true,
      "join_type": "left"
    },
    "w": {
      "name": "deal_summaries",
      "fields": {
        "deal_name": {
          "expression": "r.deal_name",
          "type": "string"
        },
        "wa_original_interest_rate_by_original_upb": {
          "expression": "sum(l.original_interest_rate * float(l.original_upb))",
          "type": "float"
        },
        "min_original_interest_rate": {
          "expression": "min_if(l.original_interest_rate, l.original_interest_rate > 0)",
          "type": "float"
        },
        "max_original_interest_rate": {
          "expression": "max(l.original_interest_rate)",
          "type": "float"
        },
        "wa_borrower_credit_score_at_origination_by_original_upb": {
          "expression": "sum(float(l.borrower_credit_score_at_origination) * float(l.original_upb))",
          "type": "float"
        },
        "min_borrower_credit_score_at_origination": {
          "expression": "min_if(l.borrower_credit_score_at_origination, l.borrower_credit_score_at_origination > 0)",
          "type": "int"
        },
        "max_borrower_credit_score_at_origination": {
          "expression": "max(l.borrower_credit_score_at_origination)",
          "type": "int"
        },
        "total_original_upb": {
          "expression": "r.total_original_upb",
          "type": "decimal2"
        },
        "total_upb_at_issuance": {
          "expression": "r.total_upb_at_issuance",
          "type": "decimal2"
        },
        "total_original_upb_for_nonzero_rates": {
          "expression": "r.total_original_upb_for_nonzero_rates",
          "type": "decimal2"
        },
        "total_original_upb_for_nonzero_credit_scores": {
          "expression": "r.total_original_upb_for_nonzero_credit_scores",
          "type": "decimal2"
        },
        "total_loans": {
          "expression": "count()",
          "type": "int"
        },
        "total_original_loan_term_30y": {
          "expression": "sum(l.is_original_loan_term_30y)",
          "type": "int"
        },
        "avg_payments_behind_ratio": {
          "expression": "avg(l.payments_behind_ratio)",
          "type": "float"
        },
        "total_paid_off_amount": {
          "expression": "sum(l.paid_off_amount)",
          "type": "decimal2"
        },
        "avg_paid_off_ratio": {
          "expression": "avg(l.paid_off_ratio)",
          "type": "float"
        }
      }
    }
  }
}

Script node 05_deal_summaries produces Cassandra table deal_summaries:

rowidavg_paid_off_ratioavg_payments_behind_ratiobatch_idxdeal_namemax_borrower_credit_score_at_originationmax_original_interest_ratemin_borrower_credit_score_at_originationmin_original_interest_ratetotal_loanstotal_original_loan_term_30ytotal_original_upbtotal_original_upb_for_nonzero_credit_scorestotal_original_upb_for_nonzero_ratestotal_paid_off_amounttotal_upb_at_issuancewa_borrower_credit_score_at_origination_by_original_upbwa_original_interest_rate_by_original_upb
61216524276065455590.023111203774804CAS 2023 R08 G18328.1256002.5603456011419356281000.0019339292000.0019356281000.00476091393.3718880190518.061.4670429761e+13118134224964

Total 1 rows

04_write_file_loan_smrs_clcltd

Script node 04_write_file_loan_smrs_clcltd:

{
  "04_write_file_loan_smrs_clcltd": {
    "type": "table_file",
    "desc": "Write from table to file loan_summaries.parquet",
    "r": {
      "table": "loan_smrs_clcltd"
    },
    "w": {
      "top": {
        "order": "loan_id(asc)"
      },
      "url_template": "{dir_out}/loan_smrs_clcltd.parquet",
      "columns": [
        {
          "parquet": {
            "column_name": "loan_id"
          },
          "name": "loan_id",
          "expression": "r.loan_id",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "deal_name"
          },
          "name": "deal_name",
          "expression": "r.deal_name",
          "type": "string"
        },
        {
          "parquet": {
            "column_name": "seller_name"
          },
          "name": "seller_name",
          "expression": "r.seller_name",
          "type": "string"
        },
        {
          "parquet": {
            "column_name": "original_interest_rate"
          },
          "name": "original_interest_rate",
          "expression": "r.original_interest_rate",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "borrower_credit_score_at_origination"
          },
          "name": "borrower_credit_score_at_origination",
          "expression": "r.borrower_credit_score_at_origination",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "origination_date"
          },
          "name": "origination_date",
          "expression": "r.origination_date",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "original_upb"
          },
          "name": "original_upb",
          "expression": "r.original_upb",
          "type": "decimal2"
        },
        {
          "parquet": {
            "column_name": "upb_at_issuance"
          },
          "name": "upb_at_issuance",
          "expression": "r.upb_at_issuance",
          "type": "decimal2"
        },
        {
          "parquet": {
            "column_name": "original_loan_term"
          },
          "name": "original_loan_term",
          "expression": "r.original_loan_term",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "payments_json"
          },
          "name": "payments_json",
          "expression": "r.payments_json",
          "type": "string"
        },
        {
          "parquet": {
            "column_name": "payments_behind_ratio"
          },
          "name": "payments_behind_ratio",
          "expression": "math.Round(r.payments_behind_ratio*100000)/100000",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "paid_off_amount"
          },
          "name": "paid_off_amount",
          "expression": "r.paid_off_amount",
          "type": "decimal2"
        },
        {
          "parquet": {
            "column_name": "paid_off_ratio"
          },
          "name": "paid_off_ratio",
          "expression": "math.Round(r.paid_off_ratio*100000)/100000",
          "type": "float"
        }
      ]
    }
  }
}

Script node 04_write_file_loan_smrs_clcltd produces data file:

loan_iddeal_nameseller_nameoriginal_interest_rateborrower_credit_score_at_originationorigination_dateoriginal_upbupb_at_issuanceoriginal_loan_termpayments_jsonpayments_behind_ratiopaid_off_amountpaid_off_ratio
136610574CAS 2023 R08 G1Flagstar Bank, National Association6.87573520221020490000485775.65360[{monthly_reporting_period": 20230820 "current_actual_upb": 485775.65 "remaining_months_to_legal_maturity": 351 "remaining_months_to_maturity": 350 "zero_balance_effective_date": 0}]"04224.350.00862
136610575CAS 2023 R08 G1Planet Home Lending, LLC6.37570920221020250000248112.85360[{monthly_reporting_period": 20230820 "current_actual_upb": 248112.85 "remaining_months_to_legal_maturity": 352 "remaining_months_to_maturity": 352 "zero_balance_effective_date": 0}]"01887.150.00755
136610576CAS 2023 R08 G1Rocket Mortgage, LLC6.578720221020473000468571.34360[{monthly_reporting_period": 20230820 "current_actual_upb": 468571.34 "remaining_months_to_legal_maturity": 351 "remaining_months_to_maturity": 351 "zero_balance_effective_date": 0}]"04428.660.00936
136610577CAS 2023 R08 G1Rocket Mortgage, LLC5.2573220220920311000307534.77360[{monthly_reporting_period": 20230820 "current_actual_upb": 307534.77 "remaining_months_to_legal_maturity": 351 "remaining_months_to_maturity": 351 "zero_balance_effective_date": 0}]"03465.230.01114
136610578CAS 2023 R08 G1U.S. Bank N.A.5.576320221020352000348025.67360[{monthly_reporting_period": 20230820 "current_actual_upb": 348025.67 "remaining_months_to_legal_maturity": 351 "remaining_months_to_maturity": 350 "zero_balance_effective_date": 0}]"03974.330.01129
136610579CAS 2023 R08 G1Other672020220920142000139258.92360[{monthly_reporting_period": 20230820 "current_actual_upb": 139258.92 "remaining_months_to_legal_maturity": 351 "remaining_months_to_maturity": 339 "zero_balance_effective_date": 0}]"02741.080.0193
136610580CAS 2023 R08 G1U.S. Bank N.A.5.87579920221020205000202982.62360[{monthly_reporting_period": 20230820 "current_actual_upb": 202982.62 "remaining_months_to_legal_maturity": 351 "remaining_months_to_maturity": 351 "zero_balance_effective_date": 0}]"02017.380.00984
136610581CAS 2023 R08 G1Planet Home Lending, LLC6.57569020221020174000172686.14360[{monthly_reporting_period": 20230820 "current_actual_upb": 172686.14 "remaining_months_to_legal_maturity": 352 "remaining_months_to_maturity": 352 "zero_balance_effective_date": 0}]"01313.860.00755
136610582CAS 2023 R08 G1Other5.37576220221020116000110751.32360[{monthly_reporting_period": 20230820 "current_actual_upb": 110751.32 "remaining_months_to_legal_maturity": 351 "remaining_months_to_maturity": 323 "zero_balance_effective_date": 0}]"05248.680.04525
136610583CAS 2023 R08 G1JPMorgan Chase Bank, National Association5.579520220920234000231756.02360[{monthly_reporting_period": 20230820 "current_actual_upb": 231756.02 "remaining_months_to_legal_maturity": 350 "remaining_months_to_maturity": 350 "zero_balance_effective_date": 0}]"02243.980.00959

Total 60345 rows

05_write_file_deal_seller_summaries

Script node 05_write_file_deal_seller_summaries:

{
  "05_write_file_deal_seller_summaries": {
    "type": "table_file",
    "desc": "Write from table to file deal_seller_summaries.parquet",
    "r": {
      "table": "deal_seller_summaries"
    },
    "w": {
      "top": {
        "order": "deal_name(asc),seller_name(asc)"
      },
      "url_template": "{dir_out}/deal_seller_summaries.parquet",
      "columns": [
        {
          "parquet": {
            "column_name": "deal_name"
          },
          "name": "deal_name",
          "expression": "r.deal_name",
          "type": "string"
        },
        {
          "parquet": {
            "column_name": "seller_name"
          },
          "name": "seller_name",
          "expression": "r.seller_name",
          "type": "string"
        },
        {
          "parquet": {
            "column_name": "avg_original_interest_rate"
          },
          "name": "avg_original_interest_rate",
          "expression": "math.Round(r.avg_original_interest_rate*1000)/1000",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "min_original_interest_rate"
          },
          "name": "min_original_interest_rate",
          "expression": "r.min_original_interest_rate",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "max_original_interest_rate"
          },
          "name": "max_original_interest_rate",
          "expression": "r.max_original_interest_rate",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "avg_borrower_credit_score_at_origination"
          },
          "name": "avg_borrower_credit_score_at_origination",
          "expression": "math.Round(r.avg_borrower_credit_score_at_origination*100)/100",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "min_borrower_credit_score_at_origination"
          },
          "name": "min_borrower_credit_score_at_origination",
          "expression": "r.min_borrower_credit_score_at_origination",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "max_borrower_credit_score_at_origination"
          },
          "name": "max_borrower_credit_score_at_origination",
          "expression": "r.max_borrower_credit_score_at_origination",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "total_original_upb"
          },
          "name": "total_original_upb",
          "expression": "r.total_original_upb",
          "type": "decimal2"
        },
        {
          "parquet": {
            "column_name": "total_original_upb_paid_off_ratio"
          },
          "name": "total_original_upb_paid_off_ratio",
          "expression": "math.Round(float(r.total_paid_off_amount)/float(r.total_original_upb)*100000)/100000",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "total_upb_at_issuance"
          },
          "name": "total_upb_at_issuance",
          "expression": "r.total_upb_at_issuance",
          "type": "decimal2"
        },
        {
          "parquet": {
            "column_name": "total_loans"
          },
          "name": "total_loans",
          "expression": "r.total_loans",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "total_original_loan_term_30y"
          },
          "name": "total_original_loan_term_30y",
          "expression": "r.total_original_loan_term_30y",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "avg_payments_behind_ratio"
          },
          "name": "avg_payments_behind_ratio",
          "expression": "math.Round(r.avg_payments_behind_ratio*100000)/100000",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "total_paid_off_amount"
          },
          "name": "total_paid_off_amount",
          "expression": "r.total_paid_off_amount",
          "type": "decimal2"
        },
        {
          "parquet": {
            "column_name": "avg_paid_off_ratio"
          },
          "name": "avg_paid_off_ratio",
          "expression": "math.Round(r.avg_paid_off_ratio*100000)/100000",
          "type": "float"
        }
      ]
    }
  }
}

Script node 05_write_file_deal_seller_summaries produces data file:

deal_nameseller_nameavg_original_interest_ratemin_original_interest_ratemax_original_interest_rateavg_borrower_credit_score_at_originationmin_borrower_credit_score_at_originationmax_borrower_credit_score_at_originationtotal_original_upbtotal_original_upb_paid_off_ratiototal_upb_at_issuancetotal_loanstotal_original_loan_term_30yavg_payments_behind_ratiototal_paid_off_amountavg_paid_off_ratio
CAS 2023 R08 G1CitiMortgage, Inc.5.5523.257.125765.146248202764120000.02568269314771.1674674507097228.840.02411
CAS 2023 R08 G1CrossCountry Mortgage, LLC6.6933.58.125753.4108192370750000.02411231359769.9580079805715230.050.02167
CAS 2023 R08 G1DHI Mortgage Company, Ltd.4.8853.257.375758.6508272684690000.02285262333547.5685785706135452.440.02289
CAS 2023 R08 G1Fairway Independent Mortgage Corporation6.3443.757.625757.3508294579050000.02773445205133.0913571355012699866.910.02525
CAS 2023 R08 G1Fifth Third Bank, National Association5.723.997.625756.4908232855780000.03003277002271.9794294108575728.030.02764
CAS 2023 R08 G1Flagstar Bank, National Association6.5343.758.125753.596238241949920000.02083190929850.0857557504062149.920.01937
CAS 2023 R08 G1Guaranteed Rate, Inc.5.9382.997.625761.108234437200000.02767431441078.9512331233012278921.050.02536
CAS 2023 R08 G1Guild Mortgage Company LLC6.624.258760.8208212339250000.02983226947065.5368568506977934.470.02757
CAS 2023 R08 G1JPMorgan Chase Bank, National Association6.2173.58.125759.276208232535240000.0362244345711.6689488409178398.20.03168
CAS 2023 R08 G1Lakeview Loan Servicing, LLC6.0084.257.625749.666208212315740000.01804227396791.3461961204177208.660.01805

Total 25 rows

05_write_file_deal_summaries

Script node 05_write_file_deal_summaries:

{
  "05_write_file_deal_summaries": {
    "type": "table_file",
    "desc": "Write from table to file deal_summaries.parquet",
    "r": {
      "table": "deal_summaries"
    },
    "w": {
      "top": {
        "order": "deal_name(asc)"
      },
      "url_template": "{dir_out}/deal_summaries.parquet",
      "columns": [
        {
          "parquet": {
            "column_name": "deal_name"
          },
          "name": "deal_name",
          "expression": "r.deal_name",
          "type": "string"
        },
        {
          "parquet": {
            "column_name": "wa_original_interest_rate_by_original_upb"
          },
          "name": "wa_original_interest_rate_by_original_upb",
          "expression": "math.Round(r.wa_original_interest_rate_by_original_upb/r.total_original_upb_for_nonzero_rates*1000)/1000",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "min_original_interest_rate"
          },
          "name": "min_original_interest_rate",
          "expression": "r.min_original_interest_rate",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "max_original_interest_rate"
          },
          "name": "max_original_interest_rate",
          "expression": "r.max_original_interest_rate",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "wa_borrower_credit_score_at_origination_by_original_upb"
          },
          "name": "wa_borrower_credit_score_at_origination_by_original_upb",
          "expression": "math.Round(r.wa_borrower_credit_score_at_origination_by_original_upb/r.total_original_upb_for_nonzero_credit_scores*100)/100",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "min_borrower_credit_score_at_origination"
          },
          "name": "min_borrower_credit_score_at_origination",
          "expression": "r.min_borrower_credit_score_at_origination",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "max_borrower_credit_score_at_origination"
          },
          "name": "max_borrower_credit_score_at_origination",
          "expression": "r.max_borrower_credit_score_at_origination",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "total_original_upb"
          },
          "name": "total_original_upb",
          "expression": "r.total_original_upb",
          "type": "decimal2"
        },
        {
          "parquet": {
            "column_name": "total_original_upb_paid_off_ratio"
          },
          "name": "total_original_upb_paid_off_ratio",
          "expression": "math.Round(float(r.total_paid_off_amount)/float(r.total_original_upb)*100000)/100000",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "total_upb_at_issuance"
          },
          "name": "total_upb_at_issuance",
          "expression": "r.total_upb_at_issuance",
          "type": "decimal2"
        },
        {
          "parquet": {
            "column_name": "total_loans"
          },
          "name": "total_loans",
          "expression": "r.total_loans",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "total_original_loan_term_30y"
          },
          "name": "total_original_loan_term_30y",
          "expression": "r.total_original_loan_term_30y",
          "type": "int"
        },
        {
          "parquet": {
            "column_name": "avg_payments_behind_ratio"
          },
          "name": "avg_payments_behind_ratio",
          "expression": "math.Round(r.avg_payments_behind_ratio*100000)/100000",
          "type": "float"
        },
        {
          "parquet": {
            "column_name": "total_paid_off_amount"
          },
          "name": "total_paid_off_amount",
          "expression": "r.total_paid_off_amount",
          "type": "decimal2"
        },
        {
          "parquet": {
            "column_name": "avg_paid_off_ratio"
          },
          "name": "avg_paid_off_ratio",
          "expression": "math.Round(r.avg_paid_off_ratio*100000)/100000",
          "type": "float"
        }
      ]
    }
  }
}

Script node 05_write_file_deal_summaries produces data file:

deal_namewa_original_interest_rate_by_original_upbmin_original_interest_ratemax_original_interest_ratewa_borrower_credit_score_at_origination_by_original_upbmin_borrower_credit_score_at_originationmax_borrower_credit_score_at_originationtotal_original_upbtotal_original_upb_paid_off_ratiototal_upb_at_issuancetotal_loanstotal_original_loan_term_30yavg_payments_behind_ratiototal_paid_off_amountavg_paid_off_ratio
CAS 2023 R08 G16.1032.58.125758.58600832193562810000.024618880190518.0660345601140476091393.370.02311

Total 1 rows