Zeev Shteiman
10/16/2024, 7:50 AMbegin transaction; and commit; parts are added by some utility function called transactionally.
The issue with snowflake is that the commits are session level and not transaction level. So in case the delete fails, the insert will still be committed. Airbyte then detects the failure and reruns the transaction. Now we have inserted the same records twice, and when the delete runs, the way it is written, it deletes both of the records. I believe this to be a major issue.
What should work for snowflake is to wrap the transaction in a script block and handle the exception. Something like:
BEGIN
begin transaction;
INSERT INTO table ...;
DELETE FROM table ...;
commit;
EXCEPTION
WHEN OTHER THEN
ROLLBACK;
END;
I believe the fix is small, but not sure where to do it as I am don't understand the architecture very well. WOuld like for your help, as this is something that affects everyone using snowflake.Zeev Shteiman
10/16/2024, 8:17 AM[DEPRECATED] Marcos Marx
Zeev Shteiman
10/17/2024, 3:36 PMZeev Shteiman
10/17/2024, 3:38 PM