We have a requirement to archive the Interaction History table (PR_DATA_IH_FACT) based on a condition on date. Interaction records older than a particular date will be sent to an archive table and delete the records from the active Interaction History table.
Considering the volume of records in the table, we are considering a DataFlow based approach for the same.
While we can send the records to an archive table using a DataFlow, can we use the same to delete records from the main IH table? If yes, how?
Also, please let me know if you have any other suggestion/approach to achieve the above requirement.
The data-set integration in data flows give you the ability to only insert and update OOTB (considering the sensitive nature of data loss and you don't want to do that acidentally anywhere) But, the lower level data-set implementation also supports delete records by key.
If you want a real data flow integration to this - in one of the target shapes of your DF, you can call an activity and that can internally do a "Data-Set" - Delete by Keys operation with the incoming primary key.