transactions are not properly rolled back (autocommit issues)

[kd2718]

Here is my schemachange-config.yml file:

config-version: 1

root-folder: dcm/migrations/
modules-folder: dcm/modules/

snowflake-account: ....
snowflake-user: "{{ env_var('DBT_USER', '') }}"
snowflake-role: "{{ env_var('DBT_ROLE', '') }}"
snowflake-warehouse: "{{ env_var('DBT_WAREHOUSE', '') }}"

snowflake-database: my_database

change-history-table: my_database.test_dcm.schemachange
autocommit: false

here is my migration file:

begin transaction;
CREATE SCHEMA if not exists DEMO;
use schema demo;

CREATE TABLE HELLO_WORLD
(
   FIRST_NAME VARCHAR
  ,LAST_NAME VARCHAR
);

select 1/0;
commit;

Expected behavior

When running this migration I would expect that once the last command select 1/0 is reached, the entire migration is rolled back. Running a second time would give the same error as the first:

snowflake.connector.errors.ProgrammingError: 100051 (22012): Division by zero

Actual behavior

The table is created and persists. If I run the second time, the error is now that the table already exists:

snowflake.connector.errors.ProgrammingError: 002002 (42710): SQL compilation error:
Object 'HELLO_WORLD' already exists.

I know this is a contrived example, but I discovered this when testing out the tool. I had a mistake in a migration file and it left the migration in a partial state, even though autocommit is set to false.

[kd2718]

Just found this in the docs: https://docs.snowflake.com/en/sql-reference/transactions.html#ddl

Basically, DDL statements are autocommit no matter what in snowflake.