design tables to consolidate the kafka messages

[Ankit Mani Tripathi]

Hi,
I have below scenario:

2 DBs: DB1 and DB2 which will later replace DB1. Both needs to be in sync. DB1 is de-normalized and 1 table in DB1 might have multiple tables in DB2. When we are loading data from DB2 to DB1 then we need to consolidate the messages to populate a table in DB1 in one shot.

Need to design a configuration table having below information Target_System (DB1 but later can be DB2 as well) Target_Table Source_table (this will be 1-many with Target Table) Mandatory Source Columns/Optional Source columns

For the entries in this table the messages will be consolidated. To consolidate need to store the messages in landing DB and need to have an indicator set when all the mandatory columns have arrived, combine the message and pass to Target.

The messages will be in Kafka and Landing DB and configuration table needed in SQL server.

Thanks.