u/VillageDisastrous230

Hi everyone, I need some advice on implementing data pipeline for the analytics application in healthcare
Our current tech stack / architecture is as below

1. Microservices architecture
2. Backend services are written in .NET Core and most of the front end is in react
3. Part of the system is still legacy and it is in asp.net and MsSQL
4. Databases used are MySQL, MongoDB, MSSql
5. Kafka is used for pub/sub
6. Applications in production running on GKE

Now we need to implement data pipeline for analytics and I am mostly leaning towards medallion architecture and what I have thought so far is

1. A analytics worker service sitting in same GKE and listening to Kafka topic
2. Periodically push the data to GCS bucket (bronze layer)
3. Cloud scheduler triggers the cloud function at fixed interval and takes the not processed files and batch loads into BigQuery (silver layer)
4. Data farm takes from BigQuery silver layer and create one BigQuery dataset per tenant (gold layer)

Suggestions I need from community

1. Is this is right architecture or any better approach is there?
2. Worker service when it reads from Kafka should use a temporary database to store the data and on batch full send it to GCS or should I consider Kafka itself as a storage and do not commit offset until batch is full and uploaded to GCS
3. Some Kafka events may require enrichment by calling other service APIs, and bulk apis may not be available so how I can effectively handle enrichment + batch upload
4. In case if I also need to connect to legacy database to poll and get the changed data how I can make sure both processes creates the correct order batches (mostly this use case should not come since CDC is enabled in legacy DBs and it publishes the changes to Kafka using a tool similar to Debezium )