Snowflake Archives

How RiskSpan Helped a Credit-Focused Investment Management Firm Transition to Snowflake

A leading investment management firm and recognized leader in structured credit, including asset-backed securities (ABS), mortgage-backed securities (MBS), and other fixed-income sectors, sought RiskSpan’s help transitioning key data processing functions from the data management platform 1010data to Snowflake.

The ability to share data with partners using the same system in which the analytics are performed made the combination of RiskSpan and Snowflake especially attractive. The shift provided significant operational and financial benefits to the client, marking another successful milestone in RiskSpan’s history of helping clients optimize their data management.

Converting Key Functionalities from 1010data to Snowflake

The company had been relying on 1010data for several critical timeseries-based calculations. However, the limitations of the platform—both in terms of speed and cost—prompted them to seek a more modern solution. RiskSpan worked closely with them to replicate and enhance key functionalities using Snowflake. Converted functionalities included:

Timeseries-Based Calculations: We re-engineered these to operate efficiently within Snowflake’s cloud-native architecture, maintaining accuracy while enhancing processing speeds.
fill_nearest: This function retrieves the nearest non-N/A value within a group. It was implemented seamlessly using Snowflake’s window functions, preserving data integrity while boosting performance.
rolling_sum: Snowflake’s SQL capabilities were leveraged to implement the moving sum of valid (non-N/A) values within a window. This provided the company with more responsive and scalable time-series analysis capabilities.
cumulative_run_length: The cumulative run length within a group was translated into Snowflake’s environment using efficient SQL queries, making the entire process faster and more robust.

Integration Capabilities

In addition to replicating 1010data’s core functionalities, the company sought to expand its data capabilities by integrating additional datasets such as Market Data and Home Price Indices (HPI). We showed them how to incorporate and analyze these datasets within Snowflake’s environment, further enhancing their decision-making capabilities.

This cross-functional integration was pivotal in showcasing Snowflake’s ability to streamline complex data workflows. By integrating third-party data directly into their ecosystem, our client can now generate more insightful reports and conduct deeper analysis across multiple datasets without leaving the Snowflake platform.

The Benefits of Transitioning to Snowflake

Our client experienced several immediate and impactful benefits by transitioning from 1010data to Snowflake were immediate and impactful. These included:

Complete Replacement of 1010data: With all critical functionalities successfully converted, the company now can fully discontinue their reliance on 1010data. This eliminates the need for maintaining multiple platforms and simplifies their technology stack.
Significant Cost Savings: Discontinuing 1010data relieved our client of the high costs associated with the platform’s licensing and maintenance fees. Snowflake’s cost-efficient pricing model has already resulted in substantial savings for the company.
Improved Processing Speeds: One of the most noticeable changes has been the drastic improvement in the company’s processing times. Snowflake’s optimized cloud infrastructure provides faster data processing and querying capabilities, significantly reducing time-to-insight.
Access to Full Snowflake Feature Set: Moving to Snowflake has enabled the company to take advantage of features such as data sharing, enhanced security, and elasticity. Snowflake’s built-in scalability ensures our client’s data infrastructure will continue to grow effortlessly as its data needs expand.
Speed and Cost Efficiency: The company has expressed particular satisfaction with both the speed and cost-efficiency of the Snowflake platform. The reduction in data processing time and cost per query has positively impacted its business operations.

Partnering with RiskSpan not only enabled the company to replace 1010data with a more modern and efficient platform, but it has also empowered them to take advantage of Snowflake’s newest, advanced features, including AI.

Contact us to learn how RiskSpan can help you unlock the full potential of your data by guiding you through complex transitions and helping you implement scalable, secure, and cost-effective solutions.

How RiskSpan and Snowflake Helped a Large Insurance Company Revolutionize Its Data Management

Background

Asset managers are increasingly turning to Snowflake’s cloud infrastructure to address the limitations of outdated databases. Migrating to Snowflake grants them access to a sustainable and secure platform that enables efficient data storage, processing, and analytics. This transition empowers asset managers to streamline operations, improve data accessibility, and reduce costs associated with maintaining on-premises infrastructure.

Client Challenge

A large insurance company’s asset management team was seeking to improve its approach to data management in response to its increasingly complex investment portfolio. The company recognized that transitioning to Snowflake would serve as a foundation for sustainable data analysis for years to come.

Desiring a partner to assist with the transition, the life insurer turned to RiskSpan – a preferred Snowflake partner with substantial experience in database architecture and management.

Specifically, the insurance company sought to achieve the following:

Systems Consolidation: Data stored across multiple transactional systems had contributed to data fragmentation and inefficiencies in data retrieval and analysis. The client sought to establish and maintain a consistent source of asset data for enterprise consumption and reporting.

Improved Reporting Capabilities: Quantifying full risk exposures in fast-moving situations proved challenging, leaving the institution vulnerable to unforeseen market fluctuations. Consequently, the client sought to improve its asset evaluation and risk assessment process by incorporating comprehensive look-through data and classification information. The need for various hierarchical classifications further complicated data access and reporting processes which required streamlining the process of producing ad-hoc exposure reports, which often required several weeks and involved teams of people.

Reduction of Manual Processes: The client needed more automated data extraction processes in order to create exposure reports across different asset classes in a more time-efficient manner with less risk of human error.

Reduction of Infrastructure Constraints: On-premise infrastructure had defined capacity limitations, hindering scalability and agility in data processing and analysis.

RiskSpan’s Approach and Solutions Implemented

Collaborative Partnership: RiskSpan worked closely with the client’s IT, risk management, and analytics teams throughout the project lifecycle, fostering collaboration and ensuring alignment with organizational goals and objectives.

Comprehensive Assessment: Together, we conducted a thorough assessment of the client’s existing data infrastructure, analytics capabilities, and business requirements to identify pain points and opportunities for improvement.

Strategic Planning: Based on the assessment findings, the collective team developed a strategic roadmap outlining the migration plan to the unified data platform, encompassing asset data consolidation, portfolio analytics enhancement, and reporting automation.

Unified Data Platform: Leveraging modern technologies, including cloud-based solutions and advanced analytics tools, RiskSpan orchestrated the integration of various data sources and analytics capabilities. Together, we consolidated asset data from various transactional systems into a unified data platform, providing a single source of truth for comprehensive asset evaluation and risk assessment.

Data Lineage Tracking: The team employed dbt Labs tools to build, validate, and deploy flexible reporting solutions from the Snowflake cloud infrastructure. This enabled the tracking of data lineage, adjustments, and ownership.

Daily Exposure Reporting: Leveraging automated analytic pipelines, we enabled real-time generation of exposure reports across different asset classes, enhancing the client’s ability to make timely and informed decisions.

Automated Data Extraction: We automated the data extraction processes, reducing manual intervention and streamlining data retrieval, cleansing, and transformation workflows.

Hierarchical Classification Framework: We implemented a hierarchical classification framework, providing standardized and consistent data hierarchies for improved data access and reporting capabilities.

Transformative Outcomes

Enhanced Decision-making: Implementing advanced analytics capabilities and exposure reporting empowered our client to make informed decisions more quickly, mitigating risks and capitalizing on market opportunities.

Operational Efficiency: Automation of data extraction, analytics modeling, and reporting processes resulted in significant operational efficiencies, reducing time-to-insight and enabling resource reallocation to strategic initiatives.

Scalability and Agility: The migration to a cloud-based infrastructure provides scalability and agility, allowing our client to adapt quickly to changing business needs and accommodate future growth without infrastructure constraints.

Data Governance and Compliance: The implementation of standardized hierarchical classifications strengthened data governance and compliance, ensuring data consistency, integrity, and regulatory adherence. By leveraging Snowflake’s scalable architecture and advanced features, this large asset manager is now positioned to maneuver both its current and future data landscapes. The implementation of Snowflake not only streamlined data management processes but also empowered the organization to extract valuable insights with unprecedented efficiency. As a result, the asset manager can make data-driven decisions confidently, enhance operational agility, and drive sustainable growth in a rapidly evolving market landscape.

The newest, fastest and easiest way to access and analyze Agency MBS data

TL;DR Summary of Benefits

Data normalization and enhancement: RiskSpan’s MBS data on Snowflake normalizes Fannie, Freddie, and Ginnie loan-level data, consolidating everything into one set of field names. It also offers enhanced loan level-data fields, including current coupon, spec pool category, and mark-to-market LTV, which are not available in the raw data from the agencies. The data also includes pool-level factors like pool prefix and pool age, as well as full loan histories not available from the GSEs directly.
Data access and querying: Users access the data in Snowflake using SQL or Python connectors. Snowflake functions essentially as a cloud SQL server that allows for instantaneous data sharing across entities. In just a few clicks, users can start analyzing MBS data using their preferred coding language—no data, ETL, or IT Teams required.
Data merging and analytics: Users can merge the data in Snowflake with other available loan level or macroeconomic data, including interest rates, home prices, and unemployment, for advanced analytics. Users can also project performance, monitor portfolios, and create spec pools, among other features.

CHECK OUT RISKSPAN’S OFFERING ON SNOWFLAKE MARKETPLACE

The Problem

Even though Fannie, Freddie and Ginnie have been making MBS performance data publicly available for years, working with the raw data can be challenging for traders and back-office analysts.

Traders and analysts already have many of the tools they need to write powerful queries that can reveal hidden patterns and insights across different markets – patterns that can reveal lucrative trading opportunities based on prepayment analysis. But one big obstacle often stands in the way of getting the most out of these tools: the data from the agencies is large and unwieldy and is not formatted in a consistent way, making it hard to compare and combine.

What’s more, the Agencies do not maintain full history of published data on the websites for download. Only recent history is available.

The Solution: RiskSpan’s new MBS loan-level historical offering on Snowflake Marketplace

Using RiskSpan’s new MBS Loan-Level Historical Data Offering, MBS traders and analysts can now leverage the power of Snowflake, the leading cloud data platform, to perform complex queries and merge data from multiple sources like never before.

This comprehensive data offering provides a fully normalized view of the entire history of loan-level performance data across Agencies – allowing users to interact with the full $9T Agency MBS market in unprecedented ways.

A list of normalized Fannie and Freddie fields can be found at the end of this post.

In addition to being able to easily compare different segments of the market using a single set of standardized data fields, MBS traders and analysts also benefit from derived and enhanced data, such as current coupon, refinance incentive, current loan-to-value ratio, original specified pool designation, and normalized seller and servicer names.

The use cases are practically limitless.

MBS traders and analystscan track historical prepayment speeds, find trading opportunities that offer relative value, and build, improve, or calibrate prepayment models. They can see how prepayment rates vary by loan size, credit score, geographic location, or other factors. They can also identify pools that have faster or slower prepayments than expected and exploit the differences in price.

Loan originators can see how their loans perform compared to similar loans issued by other originators, servicers, or agencies, allowing them to showcase their ability to originate high-quality loans that command premium pricing.

Enhanced fields provide users with more comprehensive insights and analysis capabilities. They include a range of derived and enhanced data attributes beyond the standard dataset: derived fields useful for calculations, additional macroeconomic data, and normalized field names and enumerations. These fields give users the flexibility to customize their analyses by incorporating additional data elements tailored to their specific needs or research objectives.

Enhanced loan-level fields include:

Refi Incentive: The extent to which a borrower’s interest rate exceeds current prevailing market rates
Spread at Origination (SATO): a representation of the total opportunities for refinancing within a mortgage servicing portfolio. SATO encompasses all potential refinance candidates based on prevailing market conditions, borrower eligibility, and loan characteristics
Servicer Normalization: A standardization of servicer names to ensure consistency and accuracy in reporting and analysis
Scheduled Balance: A helper field necessary to easily calculate CPR and other performance metrics
Spec Pool Type: A designation of the type of spec story on the loan’s pool at origination
Current LTV: a walked forward LTV based on FHFA’s HPI and the current balance of the loan

Not available in the raw data from the agencies, these fields allow MBS traders and analysts to seamlessly project loan and pool performance, monitor portfolios, create and evaluate spec pools, and more.

Access the Data on Your Terms

Traders and analysts can access the data in Snowflake using SQL or Python connectors. Alternatively, they can also access the data through the Edge UI, our well-established product for ad hoc querying and visualization. RiskSpan’s Snowflake listing provides sample queries and a data dictionary for reference. Data can be merged with macroeconomic data from other sources – rates, HPI data, unemployment – for deeper insights and analytics.

The listing is available for a 15-day free trial and can be purchased on a monthly or annual basis. Users don’t need to have a Snowflake account to try it out. Learn more and get started at the Snowflake Marketplace or contact us to schedule a demo or discussion.

CHECK OUT RISKSPAN’S OFFERING ON SNOWFLAKE MARKETPLACE

Fannie/Freddie Normalized Fields

NAME	TYPE	DESCRIPTION
AGE	Number	Loan Age in Months
AGENCY	Varchar	FN [Fannie Mae], FH [Freddie Mac]
ALTDQRESOLUTION	Varchar	Payment deferral type: CovidPaymentDeferral,DisasterPaymentDeferral,PaymentDeferral,Other/NA
BORROWERASSISTPLAN	Varchar	Type of Assistance: Forbearance, Repayment, TrialPeriod, OtherWorkOut, NoWorkOut, NotApplicable, NotAvailable
BUSINESSDAYS	Number	Business Day in Factor Period
COMBINEDLTV	Float	Original Combined LTV
CONTRIBUTION	Float	Contribution of Loan to the Pool, to be used to correctly attribution Freddie Mirror Pools
COUPON	Float	Net Coupon or NWAC in %
CURRBALANCE	Float	Current Balance Amount
CURRENTCOUPON	Float	Primary rate in the market (PMMS)
CURRENTLTV	Float	Current Loan to Value Ratio based on rolled-forward home value calculated by RiskSpan based on FHFA All-Transaction data
CURTAILAMOUNT	Float	Dollar amount curtailed in the period
DEFERRALAMOUNT	Float	Dollar amount deferred
DQSTRING	Varchar	Delinquency History String, left most field in the current period
DTI	Float	Debt to Income Ratio %
FACTORDATE	Date	Performance Period
FICO	Number	Borrower FICO Score [300,850]
FIRSTTIMEBUYER	Varchar	First time home buyer flag Y,N,NA
ISSUEDATE	Date	Loan Origination Date
LOANPURPOSE	Varchar	Loan Purpose: REFI,PURCHASE,NA
LTV	Float	Original Loan to Value Ratio in %
MATURITYDATE	Date	Loan Maturity Date
MICOVERAGE	Float	Mortgage Insurance Coverage %
MOSDELINQ	Varchar	Delinquency Status: Current, DQ_30_Day, DQ_60_Day, DQ_90_Day, DQ_120_Day, DQ_150_Day, DQ_180_Day, DQ_210_Day, DQ_240_Day, DQ_270_Day, DQ_300_Day, DQ_330_Day, DQ_360_Day, DQ_390_Day, DQ_420_Day, DQ_450_Day, DQ_480_Day, DQ_510_Day, DQ_540_Day, DQ_570_Day, DQ_600_Day, DQ_630_Day, DQ_660_Day, DQ_690_Day, DQ_720pls_Day
MSA	Varchar	Metropolitian Statistical Area
NUMBEROFBORROWERS	Number	Number of Borrowers
NUMBEROFUNITS	Varchar	Number of Units
OCCUPANCYTYPE	Varchar	Occupancy Type: NA,INVESTOR,OWNER,SECOND
ORIGBALANCE	Float	Original Loan Balance
ORIGSPECPOOLTYPE	Varchar	Spec Story of the pool that the loan is a part of. Please see Spec Pool Logic in our linked documentation
PERCENTDEFERRAL	Float	Percentage of the loan balance that is deferred
PIW	Varchar	Property Inspection Waiver Type: Appraisal,Waiver,OnsiteDataCollection, GSETargetedRefi, Other,NotAvailable
POOLAGE	Number	Age of the Pool
POOLID	Varchar	Pool ID

Transforming Loan Data Management Using Snowflake Secure Data Sharing

Presenters

Paul Gross

Senior Quantitative Analyst, Rithm Capital

Michael Cowley

Principal, Data Cloud Products, Snowflake

Bernadette Kogler

CEO, RiskSpan

Suhrud Dagli

CTO, RiskSpan

Wednesday, May 29th, 2024

1:00 ET

Hear from a distinguished panel including RiskSpan and Snowflake customers as they describe how Data Share has transformed their approach to mortgage investment. Specific topics to include:

High-speed data processing using Snowflake for easy delivery of risk analytics and diligence data
How Snowflake’s Data Sharing facilitates data access across and between organizations while maximizing computational performance and flexibility
How Snowflake protects client data
The unique value of a central hub for all mortgage industry data and never having to FTP a file again

watch recording

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

Case Study: Hadoop to Snowflake Migration

The Client

Government Sponsored Enterprise (GSE)

The Problem

The client sought to improve the performance and forecasting capabilities of its loan valuation and forecast engine. As part of this strategic initiative, the client planned to migrate the underlying platform from Hadoop to the Snowflake Data Cloud to achieve an increase in data loading and querying speeds and an overall optimization of system performance.

RiskSpan identified a need for project management and implementation planning, as well as data pipeline and ETL migration analysis to ensure a successful integration of the Snowflake data cloud into the loan valuation and forecast engine.

The Solution

RiskSpan led the data migration effort for the loan valuation engine and integrated its pipelines from multiple data sources. The RiskSpan team also executed planning, testing, and overall project management of the implementation effort to ensure a high quality, on-schedule delivery.

The Deliverables

An integrated project plan with transition from current state to target state and production parallel
A system and data flow comparing existing state to target state
SQL code to efficiently compare 13 million records and more than 100 attributes loaded to Snowflake with legacy data in just 2 minutes.
Review of target state database ETL patterns
Review of loan valuation engine output using data in Snowflake
Comprehensive report presented to Senior Management

Converting Key Functionalities from 1010data to Snowflake

Integration Capabilities

The Benefits of Transitioning to Snowflake

Background

Client Challenge

RiskSpan’s Approach and Solutions Implemented

Transformative Outcomes

The Problem

The Solution: RiskSpan’s new MBS loan-level historical offering on Snowflake Marketplace

Access the Data on Your Terms

Fannie/Freddie Normalized Fields

Wednesday, May 29th, 2024

1:00 ET

The Client

Government Sponsored Enterprise (GSE)

The Problem

The Solution

The Deliverables

The Client

Government Sponsored Enterprise (GSE)

The Problem

The Solution

The Deliverables

Company

Products

Security & Compliance