Implement transactional Table Upload / Update / Delete Rows for Snowflake backend

[radeusgd]

• (For August release) Possibly disable this for Snowflake (If inside a transaction)
• Warnings for this case (if this can be done non-destructively)
• Implement alternate transaction method

Snowflake is a first Database we support that has dataDefinitionCausesTransactionCommit() return true, meaning that we cannot make a DDL statement be a part of a bigger transaction.

Our Upload / Update rows mechanisms were relying on using DDL statements in transactions and on transaction roll-back also deleting such tables created within a transaction. In Snowflake that does not work.

We need to implement another strategy for creating / updating tables that can work in such environment.
It probably needs to split the action into 3 phases:

1. create all needed tables,
2. run the actual UPDATE transaction,
3. and if the transaction failed and was rolled-back - delete the tables created in step (1).

This is still less robust than combining the whole operation in a single transaction - but we've got to deal with what we can use. However, because of that - we do not want the new strategy to replace the 'old' fully transactional one. Instead, we want to abstract away the common parts (creating something like DDL_Transaction_Strategy) and provide to the Upload_Table code two possible strategies. If the transactional one is available for a given database it should be preferred, falling back to the one described above. Ideally the main table update logic should be the same and only delegate to the DDL strategy.

[radeusgd]

Assigning high priority as without it Snowflake update_rows may behave in unexpected ways and leave garbage behind.

If we want to ship proper Snowflake support before this is implemented, we should probably disable update_rows for Snowflake, or at least attach a warning.

[enso-bot]

Radosław Waśko reports a new STANDUP for yesterday (2024-07-29):

Progress: Investigating the IDE problems on my machine. Created PR #10708 that fixes propagation of some FS errors that used to be swallowed by accident. Found out the root cause - I had Controlled Folder Access feature of Windows Defender enabled and it was preventing writes to Documents folder. Started libs/engine discussion on what we can do about it. Started work on Snowflake transactional upload - refactored current upload logic to make it easier to work with. It should be finished by 2024-08-07.

Next Day: Next day I will be working on the same task. Continue - analyze the 'shape' of each transaction and see what kind of abstraction could work to make it work for Snowflake's constraints.

[enso-bot]

Radosław Waśko reports a new STANDUP for yesterday (2024-07-30):

Progress: Designing how to approach the Snowflake transaction constraints. It should be finished by 2024-08-07.

Next Day: Next day I will be working on the same task. Continue - implement the 'transaction with tables' and DDL check.

[enso-bot]

Radosław Waśko reports a new STANDUP for yesterday (2024-07-31):

Progress: Implemented the 'transaction with tables'. Added a check for DDL in transactions that prevents undesirable behaviour (accidental commit). Lost quite some time on #7117 bug when testing it. It should be finished by 2024-08-07.

Next Day: Next day I will be working on the same task. Integrate 'transaction with tables' with current operations.

[enso-bot]

Radosław Waśko reports a new STANDUP for yesterday (2024-08-01):

Progress: Integrated Create and Select_Into, basic tests passing. It should be finished by 2024-08-07.

Next Day: Next day I will be working on the same task. Continue - Update and Delete.

[enso-bot]

Radosław Waśko reports a new STANDUP for the provided date (2024-08-02):

Progress: Prepared the Snowflake transactions PR for review - finished remaining operations and fixed tests. It should be finished by 2024-08-07.

Next Day: Next day I will be working on the #10716 task. Next task - investigate suggestions issue