"Legacy Layers"
Case Study in Data Transformation
Case Background:
Vantage Vehicle Insurance has grown significantly over the years, both organically and through a series of mergers. As a result of the mergers, they've inherited a variety of legacy systems, each housing critical claims data. The claims department has been experiencing inefficiencies when analyzing data across these disparate systems, leading to lost time and potential errors. A team has been tasked with addressing these challenges.
Company Interviews:
Through these detailed interviews, we get a clearer picture of the challenges faced by different stakeholders within Vantage Vehicle Insurance. Their frustrations and concerns underscore the need for a more efficient and centralized system, while also highlighting the intricacies involved in managing legacy data systems.
Student Tasks:
Complete the tasks below using the data (provided later) and by writing SQL statements. Through these tasks, you will gain hands-on experience with the challenges of working with legacy systems and the complexities of data integration and transformation.
Data Exploration:
- Fetch basic claim details from both systems.
- Count the in progress claims within each system.
- Calculate the average claim amount in each system.
- Identify the most common incident type in each system.
Data Transformation:
- Identify inconsistencies in naming conventions, formats, and missing values.
- Transform date formats to a consistent format across systems.
- Standardize naming conventions for columns to be merged.
Data Integration:
- Merge data from both systems into a single consolidated table.
Data Analysis:
- Analyze patterns in claims, such as frequency of incident types, claim amounts, etc.
Recommendation:
- Recommend whether Vantage should centralize claims data in one system or continue operating with legacy systems. Consider factors like cost, time, potential errors, and the impact on end-users, like claims adjusters and analysts.
Claims Data:
The following data, which are in CSV files, was pulled from the company's two legacy systems. You will need this data to complete the tasks mentioned previously. You will notice plenty of differences between data and formats across systems.
