Flyway is an open-source database migration tool and Snowflake is one of the database it supports. With the help of Flyway every time the need to evolve the database arises, whether structure (DDL) or reference data (DML), create new scripts with a version number higher than the current one, trigger the pipeline and the database upgrade completes automatically with the latest scripts.
Flyway Database Tool Setup
Navigate to Tools Registry.
Select ‘+ New Tool’ button.
Locate the ‘Flyway Database’ tool and click ‘Select Tool’ button.
Provide values for required fields and any additional info and click ‘Create’ button.
With the new tool, navigate to the Connections tab to configure.
Provide proper credentials to required fields and any additional information, then click ‘Save’ button.
databaseType: Select from Snowflake, MySQL, Redshift or SQL Server.
Database URL:
Port:
Username:
Password:
Once saved, click ‘Test Connection’ button to verify connection is successful.
If connection is successful as indicated by button and log, proceed to pipeline setup.
Flyway Pipeline Setup
...
Navigate to pipelines.
Select ‘+ New Pipeline’ button to create a new pipeline.
Select a template then proceed to Edit Workflow.
In initial Step Setup, set tool as ‘Flyway Database’. Save and click settings cog to reconfigure in Step Configuration:
Step Type:
View pending migrations: View the difference between database vs Git and a list of pending migrations.
Deploy pending migrations: Once difference in pending migrations are found, migrate the above difference found to the database.
Flyway Database: Select the Snowflake account configured in the Tool Registry.
SCM Type: Select a Source Control Management type. Choose from Bitbucket, Gitlab and Github.
SCM Account: Select the Source Control Management tool configured in the Tool Registry.
Workspace/Project: Select the Workspace or Project within your SCM that hosts the Repository corresponding to the Snowflake Database.
Repository: Select the Repository within your selected SCM containing the scripts to be migrated.
Branch: Chose the branch that will be migrated.
Schema: Select the schemas that will be managed by Flyway.
Base Schema: The default schema that will host the Flyway schema history table.
Scripts Location: The location within SCM containing the SQL scripts.
Database: The Snowflake database where the migration will occur. Hidden for MySQL.
Warehouse: The Snowflake Compute warehouse to be utilized for this operation. Hidden for Redshift, SQL Server & MySQL.
Allow Out of Order Deployment:
...
If this toggle is enabled it allows the user to deploy migrations in any order.
Info Step output
This will provide a summary of the available history in the schema history table along with identified migrations from the filesystem marked as ‘Pending’. This feature is currently WIP and will be available by mid February 2022.
...
Deploy Step output
This will provide the details of the migrations applied and the status of the migrations
...