apply cdc to existing data warehouse

Question

I'm building an education data warehouse. I have various dimensions that i would like to track changes to i.e. DimStudent, DimClass, DimCollege, DimExamPaper etc. etc. My solution currently drops keys, truncates tables and recreates keys before loading staging tables, which then go on to load Dimension and fact tables.

Therefore, my question is what are the minimum physical changes i need to put in place to --> truncate staging tables prior to load, track changes between staging tables, dim tables and fact tables, to incrementally load rows to dim tables and fact tables? Do i need to add a table/tables to track changes for EACH dimension or fact table? I have looked at examples but there seems to be a fair amount of work for just one table. I have 11 dimensions, 4 fact tables and 22 staging tables.

Answer 1

seeing as no one has helped me with this i'll answer myself to help anyone who is looking at the same thing. It looks as if to enable CDC on an existing warehouse you need to do this on a table by table basis, recommending capturing all the ETL in package per table. Also, it is recommended to enable CDC state per table to avoid conflicts. Recommended approach would be --> enable CDC on source DB. Transfer incremental loads to staging DB or schema inside DWH. Incrementally load records to Dimensions and Facts. Every example i can find on net is for one table, which is fairly useless... be much appreciated if someone can find a CDC example for multiple tables...