We started using Snowflake data warehouse in our project recently and encountered an issue. The table and view creation scripts were uploaded to Git repo and executed through Jenkins. For any updation (columns addition or deletion) of the tables creation scripts in snowflake, We had to drop and recreate the tables in snowflake. This is causing data loss from tables which is undesirable.
Every time we drop and recreate the tables, data is getting lost and we had to manually load all the data to newly created tables. This is both time consuming and causing unwanted down time. Our table creation scripts are generic for all environments and were previously designed for HIVE. The same were extended to snowflake and we are facing issues only during any schema changes to the main snowflake tables.
The ALTER command is also not solving our problem when we add new columns to tables. I am looking for any tool or process which can deploy the changes to tables or can modify the tables without deleting the underlying data.
I considered Oracle SQL developer data modeler (SDDM) after going through Snowflake documentation but I couldn't find any proper method to change the already created tables. All I found was how to connect to snowflake form the tool and do an import from there. Couple other tools like Sqitch and Dataedo were mentioned in the chain of snowflake documentation but they donot seem to meet my requirement.
The link to snowflake documentation community is as below. https://snowflakecommunity.force.com/s/question/0D50Z00007v6qQbSAI/what-schema-management-tools-are-there-for-snowflake
Could anyone please suggest any tools or process to solve my issue. thanks in advance
