I currently have a File System Pipeline which uses stored procedure to do upsert in a table. After the upsert is done , i am deleting the old entries in the table which is not available in the new file based on a timestamp set at the beginning of the SP. Also i am inserting few columns to another table.
What happens if the pipeline fails after the upsert is successful and before delete is executed. Would only the upsert be available in memsql.
If i insert values into another table after the first table upsert, what will happen if the pipeline fails between table 1 and table 2.
I guess my basic question is whether the whole SP is considered as single unit of work ? Or do we need to handle the failure scenarios.