We want to move data from one DB to another, and first step is to delete all the target data and then do insert. However in case of pipeline failure, we want to rollback to the original data in Target. Currently we are ending up with no data in target in case of pipeline failure
First Oracle execute is doing delete from <table_name>, but I want to rollback the commit, if pipeline fails
Use a database transaction to make the update atomic. Create a temporary table, with same schema as the target table. Use the bulk load snap to write the input records into the temp table. Then use a stored procedure to delete records from the target table and insert into target reading from temp table. For databases which have a Multi Execute snap, that can be used instead of a stored procedure to do the update in one transaction. Any error will result in the whole update being rolled back.
If the target was not a database (and did not support transactions), an Error Pipeline could be used to define custom error handling.
