Private ETL Platform

Data Migration & ETL Automation

The Challenges

Client depended on third-party ETL tools, which meant paying for them and being limited by what they would do.
That dependency hurt most on projects with complex data mapping rules, which is exactly where the tools were needed and exactly where they fell short.
Every project approached migration its own way, so nothing carried over from one to the next and estimates were guesses.
Source data sits in both relational and NoSQL databases, and connecting to each one was its own piece of work.
Nested and irregular structures broke straightforward field-to-field mapping.
Designing a migration required people who could write the pipeline, which put a small group of specialists on the critical path of every project.
A designed workflow is worthless if it does not become something that actually runs.
Jobs depend on each other and have to be sequenced, scheduled and run in parallel where possible.
When a migration goes wrong, someone has to be able to see exactly what happened. Without that, the failure cannot be explained to a client.
Client wanted the result to be a product it could sell later, not only an internal tool, so it had to be built to a standard beyond internal use.

Partnered with Our client to build a proprietary platform rather than another wrapper around bought tooling, so the capability stays with Client.
Built a Visual ETL Designer with a drag-and-drop canvas, so a migration is designed by laying it out rather than by writing it.
Added versioning and validation to the designer, so changes are tracked and problems are caught before a run starts rather than during one.
Translated designed workflows automatically into Airflow DAGs, closing the gap between the design and the thing that executes.
Built a unified connection manager covering both RDBMS and NoSQL sources, so connecting a new source is configuration rather than development.
Implemented flexible schema mapping and JSON flattening to handle the nested and complex structures that broke the previous approach.
Built orchestration with dependency management, parallel execution and Quartz/Cron scheduling.
Built monitoring on real-time dashboards with execution traces, log rotation and audit logs, so every run can be inspected during and after execution.
Built on Java Spring Boot and ReactJS.

Client owns its migration capability instead of renting it. The third-party licence cost and the constraints that came with it are removed from every future project.
The cases that used to be the hardest - complex mapping rules, nested structures, mixed source types - are now handled in-house, so Client can take on work it previously had to price defensively or turn down.
Migrations are designed on a canvas, so the work is no longer bottlenecked on the few people who could write pipelines by hand.
Every project runs the same way on the same platform, which makes estimating and delivery predictable across the portfolio rather than project by project.
Runs are auditable end to end, so Client can show a client exactly what moved, when, and what happened if something failed - which is what protects the relationship when a migration goes wrong.
The platform is an asset Client can commercialise, turning an internal cost centre into something it can sell.

JavaSpring BootReactJSApache AirflowQuartzCronRDBMSNoSQL