Following the comment of @klyse in my post. I decided to explain in a few words what is CQRS pattern and how we implemented it in our ETL system.
Command and Query Responsibility Segregation pattern separates reads and updates operations for data storage. I.e. the complete separation between insert/update and select operations.
If you want to understand the pattern better I can recommend the following article https://docs.microsoft.com/en-us/azure/architecture/patterns/cqrs
In the heart of our system located 2 microservices, 1. The processing service that is responsible for processing and calculations of the data consumed from the Kafka topic has only select queries for data that helps to accomplish the mission, at the end processed data produced to another Kafka topic and 2. Sync service responsible for synchronization of processed data to our time-series ETL database TimescaleDB and the multi-tenant data storage SQL Server. This service updates and inserts processed data into the databases.
- The main reason is flexibility in scalability. Our reads and writes operations are not balanced, it means that when we scale-out the number of processing and synchronization services is not the same.
- Second but very important reason is data consistency, all the updates and inserts operations done by the same service and the same code.
- Ability to navigate the data to the different systems by developing new synchronization services.
- Great flexibility in the development process.
Below you can see the container diagram of this part of the system.