Rollbacks are not supported for DDL statements.

[jokineno]

We use the schemachange library to run database migrations, and I would like to highlight a limitation: rollbacks are not supported for DDL operations like CREATE TABLE or ALTER TABLE statements because Snowflake executes these operations in their own separate transactions.

A possible workaround is to take a backup of resources using Snowflake's built-in CLONE feature (e.g., CREATE TABLE CLONE ) before running any migrations (SQL files containing one or multiple statements). If schemachange deploy returns an exit code 1, a rollback can be triggered by SWAPping the resources, such as tables, and then removing the backups. This approach would effectively revert the changes in case of any issues during the migration.

Anyone who has multiple role grants in the database resources should pay attention how those are impacted after CLONE/SWAP.

I believe adding a similar rollback mechanism as a wrapper around the deploy command could make the schemachange library more flexible and attractive to users. It would provide a much-needed safety net for DDL migrations, aligning with best practices for robust database management.

Also, I am happy to hear how others have solved rollback for DDL operations.