EDITION: ENTERPRISE

Flyway's advanced capabilities for Snowflake is now generally available, they let you compare, govern, and deploy schema changes for Snowflake, using the same state and migrations workflows you already use for other database types. Flyway connects to Snowflake over JDBC and works with Snowflake Data Warehouse and Snowflake Postgres.

Advanced capabilities include

• Understanding changes made to a development schema
• Scripting those changes out so each object can be version controlled, giving you an object-level history to see who changed what, when and why 
• Optionally generating versioned and undo migration scripts, which account for dependencies
• Reporting on changes as part of your CI/CD pipeline so you can understand how many objects will be touched at a glance and drill into more details with a side-by-side diff of the changes
• Check for drift to ensure environments reflect what is in version control to catch issues sooner before releasing to production

Supported Objects

Flyway supports the following object types:

• Schemas, including comments. Schemas can be created and dropped
• Tables, including their columns and comments
• Columns, including basic data types, NOT NULL and comments. Columns can be added, dropped, retyped, and switched between nullable and NOT NULL
• Views, including the view body and comment, with dependency-aware creation order
• Functions, including SQL-language bodies, scalar return types, and parameters with default values. Overloads are treated as distinct objects, so two functions sharing a name but differing in parameter types are compared and deployed separately
• Stored procedures, including SQL-language bodies, scalar return types, and parameters with default values. Overloads are treated as distinct objects, as for functions
• Dynamic tables, including the query, target lag, warehouse and refresh mode, with dependency-aware creation order. Dynamic tables are compared and deployed as their own object type, separately from ordinary tables
• Tasks, including the warehouse, schedule, WHEN condition and task body. Serverless tasks (those without a warehouse) are supported

Views, functions, stored procedures and dynamic tables are updated with CREATE OR REPLACE, so a change to a body, return type or parameter default doesn't require dropping and recreating the object. A change to only a view's comment, or a dynamic table's target lag or warehouse, is made with ALTER instead.

Changing a dynamic table's query, refresh mode or columns replaces it, which triggers a full refresh on its warehouse. The deployment waits for that refresh to finish.

Any change to a task drops and recreates it. Snowflake creates tasks suspended, so after deploying, resume the task with ALTER TASK ... RESUME.

Where Snowflake cannot alter a column's type in place, Flyway drops and recreates the column instead of generating an ALTER that Snowflake would reject. This applies to narrowing a VARCHAR (Snowflake only allows widening, even when every existing value would fit) and to changing a NUMBER's scale in either direction. The data in that column is lost. The rest of the table is left untouched.

Deployments don't run in a transaction

Snowflake does not support running DDL inside an explicit transaction. Snowflake implicitly commits any open transaction before a DDL statement runs, and the DDL statement itself cannot be rolled back.

Because of this, Flyway Desktop doesn't offer the run in a transaction option when your target is Snowflake, in its place you'll see an indicator explaining that this database doesn't support running DDL inside a transaction. Snowflake deployment scripts always run without one. This applies everywhere you review a script before applying it: deploying a migration, reviewing a schema model deployment script, and reviewing an update-development script. 

Known limitations

Snowflake support covers common workflows, but not every Snowflake feature yet. Known limitations include:

• Object coverage is limited to the types listed above. Constraints (primary keys, foreign keys, check constraints), streams, sequences, materialized views, and Iceberg, Hybrid, External, temporary and transient tables are not yet supported.
• SQL-language routines only. Functions and stored procedures written in JavaScript, Python, Java or Scala, and stored procedures returning a table, are not supported. Flyway may still pick these up but can't script them correctly, so exclude them using filters.
• No data comparison, permissions or governance objects. Static and row data, grants, masking policies and row access policies are not modelled and CREATE OR REPLACE VIEW does not preserve grants.
• Tags, owners, CLUSTER BY, column defaults and computed columns are not supported, nor are comments on dynamic tables or tasks. Renames are not detected, and are deployed as a drop and a create.
• Unsupported properties of a supported object are not scripted. Where an object we support has a feature we don't yet model, that feature is ignored rather than captured or deployed. If the object is recreated, the unmodelled property is lost — for example, a CLUSTER BY on a table.
• Task graphs aren't supported. A child task's AFTER predecessor isn't captured, so it's deployed as a standalone task. Task error and retry settings aren't captured either, and nor is whether a task is suspended or resumed.

NOTE: If any of these are blocking you or you feel something is missing, please let us know so we can prioritize these as we work through this list.

Connecting to a Snowflake database

 In the Flyway Desktop connection dialog, enter:

• Server hostname – your Snowflake account hostname, for example myorganization-myaccount.snowflakecomputing.com 
• Warehouse and Database – both are required. Flyway takes its compute and database context from these.
• Role – optional

Choose how to authenticate from the Authentication list.

• Username and password or PAT
  • Enter your Snowflake username, and either your password or a programmatic access token (PAT) in the Password or PAT field.
  • Use a PAT if your account enforces MFA and you're connecting from a pipeline or other unattended process.
• MFA
  • Enter your username and password. Snowflake sends a push notification to your authenticator app to approve the sign-in.
  • Only push notification authenticator apps are supported
  • Time-based One-Time Passcodes (TOTP) or passkeys are not supported
  • To reduce the number of prompts during authentication, enable MFA caching by setting the ALLOW_CLIENT_MFA_CACHING parameter on your Snowflake account. See Connecting to Snowflake with MFA. 
• Other (configure in JDBC URL)
  • For other authentication methods the Snowflake JDBC driver supports, such as key-pair authentication, set them in the JDBC URL directly. See the Snowflake driver page for details.
• Using a JDBC URL
  • You can paste a JDBC URL into the connection dialog, and Flyway Desktop fills in the fields from it.
  • The URL must include the database (db) and warehouse (warehouse), for example:

jdbc:snowflake://myOrganization-myAccount.snowflakecomputing.com/?db=myDatabase&warehouse=myWarehouse

Feedback

Snowflake Postgres

Snowflake Postgres aligns with the advanced capabilities we already support for the PostgreSQL database.