Can sqitch works in terms of deploying final version of objects?

[bhawnatyagi]

Hi All,

My team has decided to go with Sqitch to deploy snowflake objects. I have two questions. Please provide guidance if someone has the similar use case or has any solution to this problem.

Use case: As we expect for a table to have few modifications in terms of adding/dropping columns, we want to move only the final version of the object in stage and prod environments without taking all the changes that have been applied to a particular change in dev environment. For example,

Change 1: Create a new table A with two columns c1 and c2.
Deployed the change to dev database.

Change 2: Add two new columns c3 and c4 to table A.
Change deployed to dev database.

Change 3: Modify the data type of c3 and also drop c1.
Change deployed to dev database.

Currently, in order to add or drop columns from an existing table, we are following process to add another change. This looks fine to me. However, while deploying changes to stage database, I noticed that the table is going through all of the previous changes and then showing the final version of the table. I also ran test to change the script in place with sqitch rework but the outcome is still same (I also read in the documentation - sqitch rework is similar to sqitch add). That being said, is it possible to take only final version of the table in stage and prod database without taking previous changes to the table?

If above thing is not possible, can we somehow take the changes based on the tag names after doing sqitch bundle while deploying to any of the environments? We want to achieve something like below in the plan file present under bundle folder:

[v1.0.0-dev1]
appusers
users

[v1.0.1-dev1]
alter_users (This is modified version of users table where we are adding one column to the users table.)

I tried this at my end but can see that even after bundling, it is showing me the complete entry in the sqitch.plan file. For example:

tables/users 2022-03-08T02:13:50Z # Add user table.
@t1_0009 2022-03-08T02:14:20Z # Tag @ANA_0009_dev

Any help would be appreciated!

Thanks in advance!

[theory]

This is the purpose of tagging. The idea is that you make changes however you like in development, adding or removing columns in a table, for example, in the change that creates the table. In line. Don't add more changes to add or remove columns. Just change your CREATE TABLE statement.

Until, that Is, you're ready to make a release. Then you tag the plan and never modify the changes prior to the tag again. After that if you need to modify the table --- which, as you point out, is far less common --- you would add new changes for your ALTER TABLE statements. Or, if your change can be idempotent (as in CREATE OR REPLACE FUNCTION), you can rework the change.

Similar SO answer.

[theory]

(BTW, I deleted your comment on #607, which appeared to be a dupe of this question.)