Case study
Bidirectional ETL between two MySQL databases with Kafka, Debezium and Apache NiFi
schedule3 min
calendar_today26 July 2025
syncCaricamento...
How we kept two databases with different structures in sync until the final migration
In every digital transformation project there is a moment when the past and the future have to coexist.
A legacy database, full of history but no longer sustainable. A new schema, clean and normalized, ready to become the core of the system. And in between there is the present, with all its requirements for consistency, integrity and operational continuity.
This is the scenario in which we at Shellonback were called in.
The challenge
The client had two MySQL databases:
- “Source” database: a non-normalized legacy system, still used in production.
- “Destination” database: a new normalized database, intended to replace the first one.
The goal: keep both databases in sync for a transition period, until the migration was complete.
In practice: every change made in the legacy database had to propagate to the new database in near real time, and in some specific cases the other way round.
Our technical solution
We built a modern, reactive ETL (Extract, Transform, Load) flow using open source tools designed for real-time architectures and scalable data integration:
- Kafka as the central event bus
- Debezium for change data capture on the legacy database
- Apache NiFi to orchestrate, transform and route the flows between the various sources and destinations
The flow, step by step
- Capturing changes
With Debezium, every insert, update or delete in the legacy database is intercepted in real time through the MySQL binlog.
These events are published automatically to Kafka, in topics organized by entity (e.g.db_source.users,db_source.orders). - Transformation and normalization
In Apache NiFi we configured a pipeline that can:- Read the messages from Kafka
- Transform the data according to the required normalization rules
- Handle the mappings between entities (e.g. table splits, logical joins, JSON restructuring)
- Writing to the destination database
After the transformation, NiFi performs the upsert (insert/update) on the new normalized database.
This ensures that the most recent data is always aligned between the two structures. - (Optional) Reverse flows and selective logic
For specific cases (e.g. changes to administrative tables in the new DB), we set up the reverse flow: from destination to source, again with Kafka and NiFi.
The benefits for the client
- Operational continuity: no downtime during the migration
- Consistent data between the old and the new schema, even with diverging structures
- Gradual normalization: the transformation happens in real time, without having to wait for the end of the project
- Audit and traceability: every change can be monitored and reconstructed
When synchronization becomes a competitive advantage
Most companies dread the moment of data migration. At Shellonback we turn this challenge into an opportunity to evolve.
Using tools such as Kafka, Debezium and NiFi, we build resilient, flexible architectures that can adapt to every need.
If you are facing a data migration, a restructuring of your systems, or simply want to modernize your infrastructure, let's talk.
Contact us today for a free assessment call.