Case Study: Datamart Design and Build

The Client

Government Sponsored Enterprise (GSE)

The Problem

A GSE needed a centralized data solution for its forecasting process which involved cross-functional teams from different business lines (Single Family, Multi Family, Capital Markets).

The client also needed a cloud-based data warehouse to host forecasting outputs for reporting purpose with a faster querying and processing speed.

The input and output files and datasets came from different sources and/or in different formats. Analysis and transformation were required prior to designing, developing and loading tables. The client was also migrating data from legacy data sources to new datamarts


The Solution

RiskSpan was engaged to build and maintain a new centralized datamart (in both Oracle and Amazon Web Services) for the client’s revenue and loss forecasting processes. This included data modeling, historical data upload as well as the monthly recurring data process.


The Deliverables

  • Analyzed the end-to-end data flow and data elements
  • Designed data models satisfying business requirements
  • Processed and mapped forecasting input and output files
  • Migrated data from legacy databases to the new sources 
  • Built an Oracle datamart and a cloud-based data warehouse (Amazon Web Services) 
  • Led development team to develop schemas, tables and views, process scripts to maintain data updates and table partitioning logic
  • Resolved data issues with the source and assisted in reconciliation of results

Talk Scope