This client wanted to bring together LOS (Loan Origination System) data, investment hedge data, lead information, and more into Google Big Query. In all, there were three data sources with three different data formats and access methods. One, the LOS data, was accessible from the MeridianLink XML API. We created a fault-tolerant connector for this that would run in a Cloud Function. Another was a csv file that was automatically emailed and the other was a csv file picked up from an ftp server. Both of those were handled easily and cheaply with Fivetran using their built-in connectors. Each source was landed into its own dataset (schema) in Big Query. Since the client was already data and sql savvy, we used dbt in dbt cloud to handle the Transform part of the pipeline.
This is where things started getting interesting…
You may not know this but, mortgage lenders use various financial securities to hedge their risk on loans. The client had numerous spreadsheets where they calculated hedge amounts for all the securities that they tracked and invested in. We ported these proprietary calculations into dbt model and macros that facilitated faster and more flexible exploration and discovery for the client. Ultimately, the modeled data which was built by dbt were displayed in a friendly UI built in Looker, initially. The build was very fast and frequent.
The dashboard was refreshed every couple of hours to show updated lead statuses, loan statuses, hedges, and over all portfolio information. This project presented management with comprehensive and actionable data in near real-time in one place – something they had not had before.
