rollback feature

[qqcvd]

Hello

Thank you for your very usefull tool. Do you plan to add some rollback feature ?

When we install patches it happens that we need to rollback it. Here is my idea : Let's say that for all Vxxx script, it exists a RB_Vxxx script. Let's add a "Intallation_tag" field in the CHANGE_HISTORY table which would be filled by a new variable "Intallation_tag" if provided in the command line. So, if I ask snowchange to do a rollback on a specific "Intallation_tag", snowchange knows the list of all sql scripts installed during this installation_tag. Thus it can execute the rollback scripts in the inverse order.

[sfc-gh-jhansen]

Thanks for the suggestion @qqcvd! Undo (or rollback) migrations are tricky. And while tools like Flyway to do support them, they usually come with a lot of cautions (see Undo Migrations in the Flyway docs). Honestly I haven't spent much time thinking about the best approach with Snowflake.

What specific use cases do you have in mind? And how would you propose we deal with the issues raised in the Flyway docs?

[qqcvd]

I have 2 use case :

• When, the SQL delivery is part of an bigger delivery including many other application, it happen that , becasue of a problem on another application, you have to rollback all the patches. Yhus you will have to rollback your SQL patch (which was in a successful install state)

• If your installation is in a error state, you have to rollback it. In the pas, while working on SQL server, I could write the rollback command for each installation command. For exemple, in Snowflake, you can use the "IF EXIST" condition in some case... On SQL server, it was possible to test the existence of a fiel before create or delete it.... in Snowflake, it is not possible... that's the point.... :-(

[sfc-gh-jhansen]

Sorry for the super slow follow-up here @qqcvd. The rollback feature is one that I hear about somewhat frequently. If you'd be willing to code it up and submit a PR I'd be happy to review. And if you do, please copy the naming convention and other high level semantics from Flyway.

[iroche]

does anybody have examples of how the do rollback when using schemachange?

[danipisca07]

The way I did it is to add another folder (for example ./rollback instead of ./migrations) and I've put a file A__rollback.sql in it with a rollback instruction, example: create or replace schema demo clone demo at(offset => -{{ ROLLBACK_SECONDS }}); Then when I want to rollback a database I use schemachange with this folder providing the amount of seconds I want to go back.

If you want to do something more advanced you can use a python script to save "checkpoints" in time and when you want to restore you use this checkpoints to calculate the amount of "ROLLBACK_SECONDS". Anyway keep in mind that ROLLBACK_SECONDS must always be lower than the retention period that is set on snowflake. Not exactly a solution but can be a workaround in some cases...