1. Introduction
In the existing service currently in operation, one piece of data is generated and stored in the database every minute. The data arrives sequentially over time, so over the course of an hour, 60 pieces of data are stored at their respective points in time.
While developing a new feature, a requirement arose to process data that is stored in the same table but generated in a different way from the existing data.
The newly added data is the result of a separate analysis of the previous hour's data performed by an external service. Previously, one piece of data arrived every minute, whereas with the new feature, approximately 60 pieces of data corresponding to the previous hour are delivered in a single API request after the analysis is complete.
Is it appropriate to process tasks initiated within the service, whose processing time can be controlled, in the same way as requests received from an external source?
This work began with that question.
2. Problem Definition
At first, I thought we could use the existing data processing method as-is. We had already implemented functionality that generated more than 10,000 records in a single operation and divided them into batches of 500 for Batch Insert.
Since we were already processing more than 10,000 records at a time, we determined that storing approximately 60 records per request could be handled sufficiently using the same approach. In fact, the volume of 60 records itself was not a problem.
However, while comparing the existing operation with this feature, I discovered an important difference. The existing bulk data processing is initiated within the service. We can anticipate, to some extent, when the operation will start and how much data will be generated at once, and the data processing flow can also be controlled within the service.
In contrast, with the new feature, an API is called after the external service completes its analysis. Our service cannot determine when the request will occur, and if multiple analysis tasks finish at around the same time, multiple requests may arrive simultaneously. If this were implemented using the same synchronous approach as before, each request would go through the following process.
Request → Validation → Transform → Batch Insert → ResponseThere is no problem when considering only a single request, but when requests arrive in bursts, data transformation and database operations are performed simultaneously for each request. Ultimately, the difference between the two operations was not the amount of data processed at once. The existing operation allowed our service to control the processing time and flow, whereas the new feature is initiated by external requests.
Therefore, rather than performing all the way through the actual data processing every time an external request arrives, we considered separating the process of receiving requests from the process of handling the actual data.
3. Solution
Ultimately, we decided to separate the Receive stage, which receives external requests, from the Process stage, which stores the actual data. For external requests, the received data is first stored in the form of a Payload, and the actual data processing is performed in a separate Process.
Storing Data per Request Using a Payload
To separate Receive and Process, we needed a way to retain the data received from outside until the time of actual processing. The approximately 60 pieces of data delivered in a single API request represent one analysis result for the previous hour.
Therefore, rather than storing each piece of data as a separate processing target, we determined that it would be more appropriate to manage one request as one unit of work. To do this, we serialize all the data received through the API as a JSON String and store it in one Row of the Payload table.
60 pieces of data → JSON Serialize → 1 Payload Row
At this stage, we do not create 60 Rows in the final table. We only store the externally received request as one Payload, and the actual data processing is performed later during the Process stage. This allows us to perform only a relatively simple storage operation when an external request arrives, while separating data transformation and final storage into a separate processing flow.
Data Processing Through a Timer Event
The data stored in the Payload is processed through a Timer Event that runs every minute. Because the data being delivered this time is the result of an external service analyzing the previous hour's data, it was not real-time data that had to be reflected in the final table immediately upon receipt of the request.
Therefore, we could allow a certain degree of processing delay. When the Timer Event runs, it retrieves unprocessed data from the Payload table. It then Deserializes the data stored as a JSON String, performs the necessary data transformations, and stores the result in the final table.
Timer Event → 처리 대상 Payload 조회 → JSON Deserialize → 데이터 변환 → Batch Insert / UpsertThe method of storing the final data itself continued to use the existing Batch Insert approach. What changed was when Batch Insert was executed. Instead of storing the final data immediately when an external API request arrived, we first received the request as a Payload, and the internally executed Timer Event handled the actual database operation. This allowed us to separate the time an external request arrives from the time the actual data is processed.
Request ID and Data Integrity
After separating Receive and Process, we also needed a way to identify requests and track their processing status. With a synchronous approach, a successful API response can be considered an indication that data processing has also been completed. However, in the changed structure, a successful API request does not mean that storage of the final data has been completed.
Therefore, when receiving a request, we generate a UUID-style requestId and store it together with the Payload, while the API returns 202 Accepted and the requestId. requestId is not a value for identifying individual records; it is a value for tracking the work associated with one request, that is, one Payload unit.
We also had to consider cases where the same data might be delivered again due to network issues or other characteristics of the external API. Even if the same request is delivered again, a new requestId may be generated, so requestId alone cannot be used to determine whether the actual data is duplicated. Therefore, we separated the role of requestId, which tracks the work, from that of the Unique Key, which determines whether the actual data is duplicated.
requestId → 요청 및 작업의 추적
Unique Key → 실제 데이터의 유일성 보장When storing the final data, we perform an Upsert based on a separate Unique Key so that duplicate Rows are not created even if the same data is delivered again. This allows us to track each request separated into an asynchronous process while maintaining the integrity of the final data.
4. Conclusion
Based on the points above, the synchronous processing method initially considered and the processing method ultimately implemented can be summarized as follows.
We were already using a method that generated more than 10,000 records in a single operation and divided them into batches of 500 for Batch Insert. Therefore, at first, we thought that this feature, which processes approximately 60 records per request, could also use the existing approach as-is. In fact, considering only the amount of data, it was a sufficiently viable method.
However, while the existing operation starts within the service, allowing us to control its execution time and processing flow, this feature is initiated by a request from an external service.
Accordingly, rather than changing Batch Insert itself, we separated the receipt of external requests from the processing of the actual data. External requests are first stored as Payloads, while the actual data transformation and storage are performed in an internal Process.
Even when storing the same data, the appropriate processing method may vary depending on how the data arrives, whether the start time of the operation can be controlled, and by what point processing must be guaranteed. This work once again confirmed the importance of considering these differences when designing a system.
yang