Snowflake Transactions Handling

By default, Snowflake operates in autocommit mode, where each statement is automatically committed after execution.

When your client changed it to manual, they switched to multi-statement transactions, where you need to explicitly commit your DML operations.

It does provide mechanisms to maintain consistency when loading data in parallel. Snowflake supports ACID Transactions but with some important differences from PostgreSQL:

1. Statement-level Atomicity: Each DML statement (INSERT, UPDATE, DELETE) is atomic. It either completely succeeds or completely fails.
2. Multi-statement Transactions: Snowflake allows explicit transaction control using BEGIN, COMMIT, and ROLLBACK.
3. Automatic Commits: By default, each statement auto-commits unless wrapped in a transaction block.

It can be set at a account level (globally), user or session level:

• Account (global) -> alter account set autocommit = false;
• User -> alter user <username> set autocommit = false;
• Session -> alter session set autocommit = false;

Whereas session user and account settings overrule. See https://docs.snowflake.com/en/sql-reference/parameters#label-autocommit.

To change between these modes:

1. To disable autocommit (enable manual mode):

1

BEGIN;

or

1

BEGIN TRANSACTION;

2. To manually commit changes after executing DML statements:

1

COMMIT;

3. To roll back changes:

1

ROLLBACK;

4. To return to autocommit mode from a transaction: Simply execute a COMMIT or ROLLBACK command to end the current transaction, and autocommit will be re-enabled.

The term for this manual transaction mode is “multi-statement transaction” in Snowflake terminology. More on docs here Transactions | Snowflake Documentation.

Origin: Snowflake