My two cents:
1. What is the best approach in this scenario, so that we dont have to maintain 'n' versions of DB objects? --- I don't think so. You can use the SAME physical table in one schema. For your "client specific schemas", you can create those schemas and create database views mapped to the physical table in the earlier schema. Again, this is very generic and simplified answer. There are some more possibilities and options depending upon the detailed requirements.
2. Several blogs mentions that it is recommended to maintain separate schema for SLT replicated tables and BODS replicated tables. Why is this necessary? What is the downside if we maintain all tables in one schema? --- To my knowledge the SLT schema is created as part of the SLT implementation. Those objects are exact copy of the source data. BODS may be able to create tables in the same SLT schema, if you grant BODS user access to create objects in the same schema. But from the governance perspective, it is a good practice to keep those schemas separated. Again, it is your choice.
Add comment