Platform over Scripts: Data Migration Without a Python-First Mindset

Data transformation projects are among the most complex IT projects, especially when the source systems are embedded in a system landscape that has already been in productive use for 30 years. The term "historically grown" often comes up here, usually carrying a negative connotation.

In reality, "historically grown" mostly means that IT systems have been adapted to the business processes over a long time, with all their special cases and exceptions, until the complexity of the technical implementation comes to mirror the often high complexity of a company's operations.

So IT transformation projects are a welcome opportunity to shed old baggage and streamline processes. But they usually come with long negotiations with various stakeholders who pursue different interests. That calls for an especially high degree of agility, because the scope is particularly volatile.

Letting go is hard

Replacing a legacy system is a big challenge on several levels. Shutting such a system down and giving up access to it for good is a far-reaching decision that carries immense risk, especially when financial or other business transactions run through it. So the obvious move is to keep the system running (at least in a kind of shadow mode) and replace it only step by step.

For the data migration part, though, this is an extreme challenge, because suddenly you need bidirectional synchronization. To pull that off without burning resources, you also need a strategy for delta detection (keyword Change Data Capture). Out-of-the-box solutions like Oracle GoldenGate or Fivetran work reliably, but can get extremely expensive when a large volume of data has to be checked for changes. That is why they are often recommended only for selective use, not for keeping an entire ecosystem bidirectionally in sync.

So for our use case we needed a solution that was similarly flexible but considerably cheaper to implement.

Delta instead of full comparison

One of the most important non-functional requirements in the project was that data reconciliations happen before the existing data processing (computing aggregates, distributing data to downstream systems, and so on), and that our ETL processes become part of that sequence.

So it was clear that we needed delta detection to handle the daily processing within the tight time slots we were given. During a discovery phase we looked at several possible solutions. As so often, the simplest was the best: we used a combination of various internal timestamps to determine which data had to be transferred.

That information was only consistently available in one system, though, so for the other direction we had to fall back on other calculation methods. Apache Spark (in our case with Java) offers numerous performance optimizations for delta detection that developers can draw on. Since only data that actually changed gets processed, runtime and cost stayed manageable even with larger data volumes.

The foundation and the platform

The many moving parts in data migration projects invite building a self-service platform. Here we could draw on our experience building developer platforms, which rest on partly different technologies but the same paradigms. Domain experts should be able to submit migration rules as autonomously as possible and integrate them into workflows. At the same time, with generic solutions it is especially important to have broad compatibility with different database systems and a proven test framework, to stay flexible and robust.

Because of these requirements around abstraction, compatibility and testability, we went with a modern, Java-based stack. While Python remains the language of data science (and will stay so for a good while), Java is the language of enterprises, abstractions and platforms. Close to 400 ETL pipelines were built this way and orchestrated via Apache Airflow in a serverless Dataproc environment.

We put a lot of weight on our software architecture, using best practices like composition over inheritance and domain-driven design of the modules to make creating new pipelines as easy as possible, and so to counter a volatile project scope.

In times of AI-assisted development, the economics of clean interfaces and abstractions often get less attention. In this project we still made a deliberate choice to spend enough time intelligently encapsulating the complexity of all the repeating parts of our ETL pipelines. That let us spend as much time as possible illuminating the domains of the data migration, working out the differences in how the two systems behave together with the client, and developing domain-specific solutions for them.

The result was pipeline configurations that contained only domain-specific validation and transformation logic. DB connection optimization, caching, AuthZ/AuthN, mounting cloud storage as a Hadoop file system, batching and many other functions came included automatically for every developer as part of our platform.

Complex parts of our solution, like keeping dynamically allocated sequence values in sync across systems and the delta detection, we could test in isolation with proven tools like JUnit and Testcontainers, and simulate migration scenarios realistically before actually running them.

Operations

Since the two systems involved modeled processes differently and had different validation rules, errors could not be ruled out entirely. That made it all the more important that anomalies were detected and reported quickly, so problems could be fixed before operations were affected.

Alongside a sophisticated CI/CD setup that allowed fast deployment of hotfixes, notifications via Microsoft Teams and other channels were implemented within a few hours, from requirement to integration. Although the implemented solution was based on modern cloud technologies, embedding the ETL pipelines and their artifacts into a legacy enterprise environment worked well. In the end a whole range of protocols had to be supported: pipeline triggers via email, FTP(S) integrations, operations with cloud storage output, and triggers via database tables that the legacy systems demanded.

For orchestration, by the way, we used Python, since Airflow requires Python DAGs. Complex object-oriented structures would have been a hindrance there anyway, which is why Python was a very good fit.

Was it worth it?

Absolutely. For this project, using Java as the programming language was essential to handle a large number of pipelines with shared functionality (implemented as middlewares) through a high degree of abstraction. In the end a simple pipeline could be taken from requirement to production in about 5 minutes, and extended easily for more complex processing. The paradigm behind the platform's development always leaned heavily on classic object-oriented development and was predestined for the use of Java.

For more interactive workflows, especially when exploratory data analysis with Data Insights, as described here, is the focus, for instance in combination with Jupyter or Jupyter-like notebooks, Python remains the first choice.