Best Practices: Liquibase with Snowflake

Applies to: 

• Liquibase Pro

Conditions:

• Snowflake

Driver and Extension for Snowflake

To use Liquibase and Snowflake, you need the JDBC driver JAR file (Maven download). If you use Liquibase 4.11.0 or earlier, you also need the Liquibase extension for Snowflake. In newer versions of Liquibase, the Snowflake driver is shipped with Liquibase. You can check the internal/lib folder in your root Liquibase directory or run 'liquibase --version', which will showcase all the drivers that are currently installed.

User and Role

• Create a unique user for each environment.
  • For example: If you have DEV, QA, and PROD environments, then you'll need a user for DEV, another user for QA, and another user for PROD. This is important so that you have a clear separation of who has the ability to deploy to each environment.
• Make sure to assign proper roles to each user.
• You may need to set up some defaults for the user, e.g, default schema or default warehouse for the user. 

Snowflake URL

• Ensure that URL contains: db=<database&schema=></database&schema=>
• Optionally, may also need role= in the URL, e.g.,

jdbc:snowflake://xxxxx.snowflakecomputing.com/?role=SYSADMIN&db=DDB_DEV&schema=public

• Liquibase only supports normal logins via username and password or a key pair authentication (private_key_file and private_key_file_pwd) for Snowflake.
  • The example below shows a URL utilizing the key pair authentication. You will need to have a public and private key created. Please follow Key Pair Authentication & Key Pair Rotation to generate your key.

jdbc:snowflake://xxxxx.snowflakecomputing.com/?db=DB_NAME&private_key_file=rsa_key.p8&private_key_file_pwd=${file_pwd}

Stored Logic Scripts

For stored logic scripts, use 'endDelimiter' for formatted SQL changesets to indicate the end of stored logic code. Here's an example of the Snowflake procedure, which uses '$$' in the body of the procedure. This interferes with Liquibase, and the 'endDelimiter' tag indicates a specific string to indicate the end of code. In the sample below, note the use of endDelimiter: '$$$$'  on the last line to indicate the end of the procedure. 

--liquibase formatted sql

--changeset author:stproc1 endDelimiter:$$$$
create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
    var sql_command = 
     "INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";
    try {
        snowflake.execute (
            {sqlText: sql_command}
            );
        return "Succeeded.";   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
    $$
    ;
$$$$

Related Article(s):

• Using Liquibase with Snowflake