Hi there, I believe there is a critical bug in sno...
# connector-contributions
z
Hi there, I believe there is a critical bug in snowflake destination. I am no Kotlin expert, but I think that what happens is the following. When you run a snowflake destination with append+dedup, it generates the following structure (more or less): 1. begin transaction; 2. insert into table ...; 3. delete from table ...; 4. commit; The
begin 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:
Copy code
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.
Anyway, I have submitted a ticket for now: https://github.com/airbytehq/airbyte/issues/46936
u
Thanks for open the issue @Zeev Shteiman I’m going to ask the destination team to take a look
z
Thanks @[DEPRECATED] Marcos Marx, I think I have a fix for it as well...I started following the directions on how to contribute. It says that triage needs to be remove before I can link my branch to the ticket.
I see you have removed it...I will link my branch a bit later, thanks!