Developing greenfield DWH with 2000+ separate daily loads. Many ODI (not SSIS) ETL loads are TRUNCATING a staging tbl (don't know tbl names) before the INSERT. Problem is many tbl have Clustered Column Store indexes applied which suit APPEND only data modification patterns and feel the TRUNCATE then load on CSS is hurting performance. (my testing implied inserts only do not carry too much overhead above a HEAP, but the TRUNCATE is bad)
Currently have no access to the ODI source code and unless I trawl through PlanCache/sp_WhoIstactive ect I cannot identify which tables are flushed and filled nightly. Is there a method to capture all the TRUNCATE within a fixed time?
Seen articles around capturing DLL's, audit feature and so on but did not have confidence to test. Look forward to your thoughts.