Executive summary
Vellum Insurance is an early-stage startup building an analytics platform for reinsurers. Getting a new client’s data into the platform took a data engineer an entire week of manually mapping Excel columns to the fields the platform expected.
Awaken automated column mapping and other ingestion steps on Databricks, using Apache Spark DataFrames to distribute processing across the large datasets. New-client data onboarding fell from seven days to one.
The situation
Vellum receives client data in bordereaux, the spreadsheets that feed its reinsurance analytics platform. Each client’s columns had to be mapped into a consistent structure before the data could be used. For an early-stage startup, that manual Excel work took up substantial engineering time with every new client.
The platform also needed enough computing capacity to process the data and a consistent way to prepare it for analytics.
The challenge
A data engineer worked through each new client’s spreadsheets by hand, even when column names had appeared in earlier files. We needed to make those existing mappings available to the ingestion process so the engineer could reuse that work.
The data volume called for distributed processing. The pipeline also needed separate stages for raw files, standardized data, and data ready for analytics.
What we did
Awaken built the ingestion workflow on Databricks, with reusable column mappings and Spark DataFrames for distributed processing. We organized it using a medallion architecture.
Reusing column mappings with reverse dictionary lookups
Reverse dictionary lookups connect previously observed column names to their destination fields. When a spreadsheet contains a known column name, the pipeline applies the existing mapping automatically. This reduces the manual preparation needed for each new client.
Processing large datasets with Spark DataFrames
We used Spark DataFrames to express transformations in terms of named columns and distribute the processing across a cluster. This let the pipeline handle the data volume once the column mappings were established. Apache Spark’s DataFrame documentation describes this distributed, column-based model.
Implementing bronze, silver, and gold layers
The workflow follows the Databricks medallion architecture pattern:
- Bronze holds incoming source data before standardization.
- Silver cleans and standardizes the data to match the fields the platform expects.
- Gold holds data ready for use in the reinsurance analytics platform.
Spark DataFrames provide the distributed processing within this workflow.
Results
New-client data onboarding dropped from seven days to one day. The baseline was the week a data engineer previously spent manually mapping a new client’s spreadsheet columns. The improved workflow automated much of that preparation.
What changed for the business
Vellum could make new clients’ data available for analytics sooner and spend less engineering time on repetitive spreadsheet preparation. The Databricks workflow gave the startup reusable mappings and a consistent process for handling large datasets as it continued to build its platform.
What could we build for you?
Tell us about your project. Let’s talk through what it would take.
Let’s talk