The Client

Government Sponsored Enterprise (GSE)

The Problem

The client sought to transition its ERCF spot capital reporting process from legacy systems and processes to a new, fully integrated system with automated processes. 

This required the re-creation and automation in Snowflake of a legacy report for FHFA consisting of 30 credit risk and risk factor grids rolled up from the loan level.

The Solution

RiskSpan led a cross-functional effort including the data and reporting teams to implement a fully automated report using data and SQL in Snowflake.

The Deliverables

  • Loan attributes re-mapped from legacy data to Snowflake data
  • Reverse-engineered logic mapping attribute values to grid cohorts​
  • Complex and efficient SQL developed in Snowflake to transform loan-level spot capital data into cohorts for credit risk grids​
  • Conversion of 13 million loan records into more than 2,200 grid cells in less than 3 minutes​
  • Design and execution UAT​ in cooperation with the business team
  • Fully automated FHFA credit risk report populated by calling SQL