What is the alternate solution for using force commit when saving data in to an external DB which is highly normalized
We are using an external Database as source of truth which is highly normalized. In a single flow, we have a requirement to save the data in average 25-20 tables. Since this DB is highly normalized, we are using Write-Now option to force commit the changes in order to handle the foreign key violations.
But, in case of an error in the middle of the transaction we don't have a way to rollback as we don't use the Pega OOTB commit. This is causing data integrity issues in exception scenarios.
Please let me know if you have encountered this issue and resolved it in your projects.