How to use transaction in Snowflake?

Question

How to use transaction with commit,rollback and try catch block in snowflake like sql server?

SQL Server:

BEGIN TRY
    BEGIN TRANSACTION 
        --Insert statement 1
        --Insert statement 2
    COMMIT
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

As per the snowflake documentation we can use transaction like this.

BEGIN TRANSACTION NAME T1;
-- Insert statement 1;
-- Insert statement 2;
COMMIT;

Answer 1

The TRANSACTION_ABORT_ON_ERROR session parameter allows statements that return an error to abort a non-autocommit transaction. All statements issued inside that transaction fail until a commit or rollback statement is executed to close the transaction.

https://docs.snowflake.net/manuals/sql-reference/parameters.html#transaction-abort-on-error

Answer 2

As of today Snowflake does not provide an equivalent construct. We're working on a feature (to be released very soon) allowing aborting a transaction if any statement in it fails, which should address this use case.