Case study · Web application · ETL / ELT
Data migration platform
A web platform that generates, runs and schedules data integration jobs across five database engines. My end-of-studies engineering project.
- Role
- Sole developer
- Company
- Datarox
- Period
- Jan 2024 - Aug 2024
- Status
- Delivered and demonstrated
The product
Interface redesigned for this portfolio
The problem
Moving data from one database to another usually means writing a script by hand for each pair of source and target. The scripts look alike, but every engine has its own SQL dialect, so each one is rewritten and debugged again.
Once the scripts run, it is hard to say what ran, when, and where a given table came from. The goal was one tool that writes the jobs, runs them on a schedule and shows what happened.
My role
This was my end-of-studies engineering project at Datarox, and I built it alone: the React application and the Python engine behind it.
It was delivered and demonstrated to the company at the end of the internship.
What I built
01
Connections
Save a connection to each database once, test it, and browse its schemas and tables from the application.
02
SQL workspace
Write and run SQL against any saved connection and read the results in the same screen.
03
Jobs generated from patterns
Pick an integration pattern, a source and a target: the Python engine writes the ETL or ELT job, in the right dialect for each engine.
04
Scheduler and tracking
Schedule a job, follow each run, and see at a glance which ones succeeded, failed or are still running.
05
Data lineage
For each table, see where its data comes from and where it goes, across the jobs that touch it.
06
Five engines
MySQL, PostgreSQL, Oracle, SQL Server and Snowflake, as a source or as a target.
How it works
The path of one migration, from the first connection to the lineage of the result.
Connect
Source and target databases
Explore
Schemas, tables and SQL workspace
Choose a pattern
ETL or ELT integration pattern
Generate
The Python engine writes the job
Schedule and run
Runs tracked one by one
Trace
Lineage of every table
What was hard
One pattern, five dialects
The same load does not read the same in Oracle and in Snowflake: types, functions and the way to merge rows all differ. Each pattern is described once and rendered for the engine it targets.
ETL and ELT in the same tool
Transforming data before loading it and transforming it inside the target are two different jobs. The engine generates both, and the user chooses which one fits the migration.
Making runs visible
A job that fails silently is worse than no job. Every run is recorded with its state, so a failure is seen when it happens and not discovered days later.
Stack
Other case studies
Have data to move, or a tool to build around it?
I can build the application and the data engine behind it.