Data Pipeline for Improved Data Management
Customer Challenge
A local insurance company was struggling to create meaningful SQL extract reports in a timely manner due to the highly complex nature of working directly with free form XML data.
Innovative Solution
ILW data engineers developed a novel SQL-based data pipeline solution with an abstraction layer to reformat the XML data in a way that significantly made the data more manageable. ILW created a double layered data map of the XML content. The first layer was schema mapping with a second layer of business relationships in the data. The pairing of these data map layers enabled the creation of XML data navigation/extracts with little to no effort by the users. In addition, ILW migrated the XML data to a Snowflake platform for use in future data warehouse initiatives.
Benefits/Outcomes
- SQL platform from which in-house developers can easily select columns to quickly build extract reports
- Stored SQL procedures provide a repeatable method to streamline a pre-formatting process for the XML data
- Increased report creation time from months to hours
- Developed solution with future vision in mind, to include possible user interface and data warehouse
Toolbox
- XML, SQL, Snowflake
- Data Preparation
- Data Management
Business Value
- Eases in-house developer burden by pre-formatting XML data, making the data more manageable to work with and relevant for extract reporting requirements
- Significant time savings with speed to analytics resulting in new insights for improved business decision making
Domain Expertise
- Insurance
- Retail
