Airbyte is an open-source data integration engine that helps you consolidate your data in your data warehouses, lakes and databases.

Airbyte

Is there an optimal way of using Snowflake as a destination for Airbyte loads? We periodically run into long running queries where the snowflake connector has set up a transaction to copy multiple temporary tables into target tables as a single transaction; and they block progress unless they are killed.  I believe this happens when queries start to queue if the warehouse is overloaded, but it seems like even sizing up the warehouse after the queuing happens doesn't resolve the issue.

The queries look something like this:
```BEGIN;
INSERT INTO MYSCHEMA._airbyte_raw_report_test_a SELECT * FROM MYSCHEMA._airbyte_tmp_ccz_report_test_a;
INSERT INTO MYSCHEMA._airbyte_raw_report_test_b SELECT * FROM MYSCHEMA._airbyte_tmp_cxz_report_test_b;
INSERT INTO MYSCHEMA._airbyte_raw_report_test_c SELECT * FROM MYSCHEMA._airbyte_tmp_tjd_report_test_c;
INSERT INTO MYSCHEMA._airbyte_raw_report_test_d SELECT * FROM MYSCHEMA._airbyte_tmp_upi_report_test_d;
COMMIT;```

usually these queries complete in a few seconds, but when the warehouse queue gets backed up they don't seem to make any progress even after hours. It feels like a deadlock but I'm not sure.

:octavia-wave: Hi Justen, I don't have a direct answer to your question although our team is working on a new feature that will remove the long-running queries (namely the `INSERT INTO from temp_table`)

This is currently being worked on as part of destination checkpointing but the main premise is that currently the data gets written to temporary tables to then be copied all at once to the finalized raw table. What will happen with the checkpointing changes is that throughout the flushing of the buffer (where records are stored in memory/files), it will commit that data directly into the finalized raw table

This gives us a few benefits, but in your case, it will reduce singular long-running transactions in favor of the smaller frequent transactions. Hope that helps but if not Snowflake does provide a way to see which <https://community.snowflake.com/s/article/how-to-resolve-blocked-queries|queries are holding a lock on a table>. This should give your more insights into what is causing the locking and hopefully other users can share their knowledge on optimizing Snowflake

Hey Justen! Thanks for your patience - I was out last week for the holidays and am catching up with messages now.

Let me know if Ryan's message helps you understand this better!

I'm encouraged to see that there is some optimization coming down the pipe for this; however it doesn't help the current state much. Is there no good way to avoid this deadlock situation before then? I'll keep an eye out for locks if this happens again, but I'm curious why it doesn't seem to make any progress after we size up the warehouse to give it more capacity.

I do not believe there is, unfortunately! Did you get a chance to look into which queries are causing this?

I do not believe there is, unfortunately.

so far we haven't experienced this; but it happens often enough that I expect to see it some time this month at least once. I'll see if `SHOW LOCKS IN ACCOUNT;` returns anything when this happens.  If it does; aside from aborting them, should I report the queries here?