Best practises on extending Interaction History table in Pega CDH
We have a requirement where Interaction History is to be extracted to a file and sent to Client's Data warehouse application. One specific piece of that requirement is to send MemberID and two other fields also in the extracted file, apart from the fields already present in interaction history. The MemberID and two other fields are already available in another custom table of the application.
The question i have is what is the best practice around this when it comes to extending Interaction History table. The above requirement can be achieved by either one of the following design
1. Extend Interaction History Table with Member ID and the two other fields (as required) and have that populated as per this PDN link
The advantage with this approach is ease of implementation and extraction is done of the Interaction history table as is.
The disadvantage with this approach is when we upgrade to the next version of Pega Core platform and CDH, we may need to retrofit these additional columns created into the upgraded environment. In short, upgrade will not be seamless and some customization will be required after upgrade.
2. The other design pattern is, not extending interaction history table. At the time of the extraction process, the stored process can read the interaction history table as well as MemberID and two other fields from that custom table and write it to the extraction file.
The advantage with this approach is OOTB Interaction History table is not modified and remains as it is and future upgrades to the next version of Pega Core platform and CDH will be smoother.
The disadvantage with this approach is the stored procedure implementation will have to be little more complex and do a join between two tables before doing an extraction to a file.
Please provide inputs, suggestions and feedback on what can be the best approach for this. Appreciate the inputs.