1. The Need for Project Background and Data Integration
In the modern corporate management environment, project management systems have evolved beyond simple task management tools to become the core platform for business operations.
In large organizations, various decision-making processes are based on project progress status, schedules, resources, and relationship data.
One of the most important factors in this process is the reliability and integrity of the data.
The biggest challenge at the start of the project was to integrate the project data that was distributed across multiple legacy systems into a single centralized working system.
The issue was not just a simple data transfer. Each legacy system operated with different purposes and structures, and the DBMS and data models used were all different.
Furthermore, data generation and modification were continuously occurring in each system on a daily basis.
Especially due to the nature of project management data, Activity and Relationship data were intricately connected.
For example, there was a problem like the following.
- Data structure mismatch by system
- Different activity relationship models
- Differences in key systems
- Deletion data handling issue
- Absence of change history tracking
- Data integrity assurance is required
In general, CDC (Change Data Capture) methods are often used in data integration projects.
In other words, it is a method of tracking only the changed data and delivering it to the target system.
However, in this project, a CDC-based approach was not suitable. The reason is that the source system itself was not perfectly managing the change history.
Additionally, there was a possibility of deleted data or missed changes occurring in some exceptional situations.
In particular, 100% data integrity was considered extremely important in business settings.
Ultimately, the project decided to adopt a Full-Load based strategy of 'recollecting all data every day.'
In other words, it involved collecting all the data from the Legacy system via ETL every day and reflecting new or changed data by comparing it with the existing Working System data. This was quite an audacious approach.
The actual volume of data that needs to be processed daily was as follows.
- Hundreds of thousands of Activity data
- Tens of millions of Relationship data
- Full-load processing in the tens of millions
Ultimately, the core of this project was not simply about data migration, but rather about how to process large-scale Full-Load data reliably and efficiently.
2. Full-Load based data integration strategy
One of the most important design philosophies in this project was to absolutely ensure data integrity.
While the general CDC approach has advantages in terms of efficiency, there is a possibility that data consistency can be compromised in case of missed changes or exceptional situations.
Especially, project management data has a complex relational structure, which means that even a slight omission of some data can disrupt the entire connection structure.
Therefore, in the project, we adopted a Full-Load strategy to collect all data again every day.
In other words, it was a method of retrieving all data from the Source Table of the source system through ETL every day. Of course, this method imposes a considerable burden in terms of data volume.
This was because we had to process hundreds of thousands of Activity data and over ten million Relationship data every day.
However, the advantages to be gained were also clear.
The first is the guarantee of data integrity.
This eliminated the possibility of missing changes as it reflects the final state of the source system as is.
The second was the response to deleted data.
In CDC-based architectures, it is often difficult to track deleted data, but in Full-Load architectures, it was easy to identify data that currently does not exist.
The third aspect was operational stability.
This is because it did not have to rely on the quality of change history management of the legacy system itself.
However, this structure brought along problems of handling large volumes of data that cannot be solved with simple DB processing.
In particular, there were significant issues like the following.
- Comparison operations for large amounts of data
- Increased cost of upsert processing
- Join-based normalization processing
- Batch time limit
- Increased DB load
In the actual project, the entire batch processing had to be completed within about 3 hours in the early morning.
Therefore, it was necessary to optimize the architecture itself for large data processing beyond the level of simple query optimization.
Ultimately, the project chose a strategy of designing a multi-stage pipeline-based structure to process data in a distributed manner at each stage.
3. Stage 1 ~ 2: Collection and Refinement Architecture
The first step was Ingestion.
The project required collecting data from various legacy systems, each operating in different server and DBMS environments.
For example, some systems were based on Oracle, some on MSSQL, and some on MySQL. Initially, we also considered developing adapters specifically for each system.
However, every time a new legacy system was added, a new adapter needed to be developed, and the maintenance costs could also become very high.
We ultimately decided to utilize Informatica, an ETL solution specialized in cross-domain data integration.
Informatica provides various DBMS connections and data collection capabilities by default, allowing us to structure connections across systems much more efficiently.
Minimizing the load on the legacy operating systems was also a very important requirement. As the legacy systems were all operational business systems, it was essential to avoid any performance degradation due to ETL tasks.
To solve this, the project designed a replica-based architecture.
In other words, it was a method where the Legacy operating DB data was directly replicated into the Working System's internal Replica DB, and the actual cleaning operation was performed on the Replica DB.
In this process, we used a Truncate & Insert based Full-Load strategy.
Thanks to this, we were able to secure the latest data reliably while minimizing the load on the operational DB.
The second stage was the Refinement stage.
Data from legacy systems had unique structures for each system.
For example, the structure of the Activity table and the Relationship structure varied across systems. In this state, it was impossible to perform integrated comparisons and Upsert operations, so standardization work was absolutely necessary.
The project designed a structure to refine Activity and Relationship data into a common standard model.
The issue was the volume of data. The amount of data that needed to be processed daily was in the tens of millions, and the standardization process required large-scale operations such as joins, string processing, and relationship calculations. Simply optimizing queries would not suffice to meet the processing time.
Ultimately, a parallel processing structure was introduced.
By noting that there was no data correlation between the legacy systems, data cleansing tasks were executed in parallel for each system. As a result, the overall processing time was reduced to about half.
4. Stage 3 ~ 4: Integration and Final Loading
The third stage was the Integration stage.
It was the task of merging refined Activity and Relationship data from legacy systems into a single unified model at Stage 2.
In this stage, there was complexity beyond a simple merge.
The biggest issue was the possibility of key duplication. This was due to the likelihood of different legacy systems using the same key value.
To address this, we established an identification system based on the origin system.
In other words, we designed it to not only use simple PKs but also to manage 'which Legacy system the data was generated from.'
This allowed us to eliminate the possibility of data collisions and secure traceability.
Additionally, all data has been appended with an Origin System Tag.
Thanks to this structure, it was easy to track which source system specific data was generated from during subsequent operational processes.
The fourth step was the Transformation & Loading stage.
The key to this stage was to efficiently compare the existing Working DB data with the new Full-Load data.
The problem was the amount of data. Comparing millions of records daily could take an excessively long time with conventional comparison methods.
To solve this, the project employed a hash-based comparison strategy.
A hash value was generated based on each row of data, and the hash was compared with the existing data that had the same PK.
In other words, rather than comparing the entire actual data, only the hash values were compared to quickly determine if there were any changes.
This structure allowed us to quickly identify the following data.
- New Insert Target
- Update target
- No change data
We also designed the actual Upsert process to have an asynchronous structure. The confirmed Insert and Update target data was delivered to the temporary tables of each service via Kafka.
Subsequently, each service is configured to perform the Upsert on its own to update the final Working DB.
This Kafka-based asynchronous architecture reduced coupling between systems and also provided fault isolation effects.
5. Performance Optimization and Operational Stability
One of the main goals of this project was not just to succeed in simple data processing, but to create a structure that can be processed reliably every day.
In particular, the performance and operational stability of the large Full-Load structure were very important.
We applied the following optimization strategies in the project.
- Parallel processing structure
- Hash-based comparison
- Step-by-step distributed processing
- Replica-based load balancing
- Kafka-based asynchronous processing
- Stage-based fault isolation
In particular, the Stage-based pipeline structure has been very effective in terms of operational stability.
I designed it to log at each step and to stop processing after that point if an error occurs.
This means we were able to prevent incorrect data from reaching the final Working DB.
We have also configured automatic email notifications to the administrator in case of a failure, allowing for quick responses.
Thanks to this structure, we were able to secure both operational stability and data reliability.
In actual projects, we have secured performance that can reliably process millions to tens of millions of data entries daily. Above all, it was very significant that we secured a processing speed that is operable even in a Full-Load based structure.
Additionally, thanks to the Kafka-based structure, we were able to prevent specific service failures from spreading to the entire data pipeline failure.
As a result, the project has secured reliability and scalability at the level of a 'large-scale data integration platform' beyond a simple ETL system.
6. Conclusion
This project was not just a simple data migration project.
It was a project to integrate large-scale project management data scattered across multiple legacy systems into a single centralized platform, and to build a data integration architecture to support reliable business decision-making based on it.
The most significant feature was that the Full-Load based structure was implemented to a level that is operationally viable.
Generally, large-scale Full-Load structures are often evaluated as inefficient, but the project optimized the architecture accordingly, prioritizing data integrity as the highest value.
As a result, we were able to achieve the following outcomes.
- Ensuring data integrity
- Delete data reliably reflected
- Improve operational stability
- Ensuring large data processing performance
- Building a fault isolation structure
- Securing scalable data pipelines
Above all, what was most meaningful was the attempt to guarantee 'business reliability' itself as a data structure, going beyond simple technical implementation.
While working on the project, I was reminded once again that data integration is not just about moving information, but is a crucial area that forms the foundation for corporate decision-making.
Additionally, through this experience, I also had a deep understanding that in a large data processing environment, it's necessary to design not only simple query optimization but also the overall data flow and architecture together.
I believe that even if we carry out similar large-scale data integration projects in the future, this experience will serve as a very important benchmark.
informalife