Case Study: Using Snowflake to Create Single Family Credit Risk Grids for a Federal Agency
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
