Thanks Tibor. Few questions are coming in my mind. The volume of data in source (for actual tables) is too high. So we are planning for a staging layer in which only those records which got Inserted/updated/deleted will be present. From the staging we need to load the target by using the join query.
The challange is how to design the staging table. Will it be exactly like the source tables or staging will have only one table like the target. How to handle insert/update/delete for the incremental records.
Feel free to let me know if you need more details. Thanks.
If you can capture keys for add / change / deletes you are interested in with triggers on the source system, then use this to drive the staging, you should be able to handle about any volume.
To mirror the source for the most part will make it easier to understand and maintain.
I have a hard time understanding why you would keep saying the target has to be a single table, not a view.
Your description is just a logical representation of the 2 tables, not any real transformation logic.
Give more thought to the SDC suggestions.
Architecture can have huge impacts to performance, scalability, and ease of maintaining and adding changes.
It will have much more payback when modeling more complex situations.