Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Snowflake - Delta Deployment with Liquibase

Applies to
Platform: Snowflake

Deploy the changes of a Snowflake DEV database to a PROD database that holds data, with Liquibase Community in Docker. Liquibase writes the differences of the tables as SQL files that you review and run in PROD, and a Python script exports the views and stored procedures of DEV. Afterwards PROD has the structure of DEV.

biGENIUS-X itself always deploys the complete solution, see Do a delta deployment.

Example environment

The article uses two databases of one Snowflake account, deployed from the same project:

  • DEV, <DEV database>: holds the new version, deployed and loaded. Compared with PROD, it adds the Stage EMPLOYEE, renames the term LINETOTAL of the satellite ORDERDETAIL to TOTAL, and no longer has the Stage and Reference Table CURRENCY.
  • PROD, <PROD database>: holds the previous version with its loaded data.

Both are reached with key-pair authentication; the private key file is <key file>, for example C:\keys\snowflake_key.p8. The Snowflake - Target environment describes how to prepare the databases.

Prerequisites

Liquibase folder structure

Create a folder, here liquibase_demo, with the subfolder liquibase and its subfolders changelog, conf and lib:

Liquibase folder with the changelog, conf and lib subfolders

Drivers

Download these JAR files from Maven Central into lib: snowflake-jdbc, slf4j-api, slf4j-simple, bcprov-jdk18on and bcpkix-jdk18on. The commands below use snowflake-jdbc-4.0.0.jar, slf4j-api-2.0.9.jar, slf4j-simple-2.0.9.jar, bcprov-jdk18on-1.83.jar and bcpkix-jdk18on-1.83.jar. With other versions, adapt the file names in liquibase.properties and in the commands.

Properties

Create conf\snowflake-session.properties:

SESSION_PARAMETER_TIMESTAMP_TYPE_MAPPING=TIMESTAMP_TZ
TIMEZONE=UTC

Download liquibase_snowflake_v3.properties, save it as conf\liquibase.properties, and set username to the PROD user and referenceUsername to the DEV user. The file contains no connection URLs and no passphrase: Liquibase reads the URLs from environment variables. It writes its tracking tables DATABASECHANGELOG and DATABASECHANGELOGLOCK to the schema LIQUIBASE of PROD.

Liquibase schema in PROD

Create the schema for the tracking tables once in PROD:

CREATE SCHEMA IF NOT EXISTS <PROD database>.LIQUIBASE;

Connection variables

In the PowerShell 7 session that runs Docker, set the passphrase of the private key and the two connection URLs. The target URL is PROD, the reference URL is DEV:

$env:SNOWFLAKE_PRIVATE_KEY_PASSPHRASE = '<key passphrase>'
$env:LIQUIBASE_COMMAND_URL =
  "jdbc:snowflake://<account host>.snowflakecomputing.com/" +
  "?account=<account identifier>&db=<PROD database>" +
  "&warehouse=<PROD warehouse>&role=<PROD role>" +
  "&private_key_file=/liquibase/keys/snowflake_key.p8" +
  "&private_key_file_pwd=$env:SNOWFLAKE_PRIVATE_KEY_PASSPHRASE"
$env:LIQUIBASE_COMMAND_REFERENCE_URL =
  "jdbc:snowflake://<account host>.snowflakecomputing.com/" +
  "?account=<account identifier>&db=<DEV database>" +
  "&warehouse=<DEV warehouse>&role=<DEV role>" +
  "&private_key_file=/liquibase/keys/snowflake_key.p8" +
  "&private_key_file_pwd=$env:SNOWFLAKE_PRIVATE_KEY_PASSPHRASE"

Here <account host> is the host name part of your account URL and <account identifier> is <organization>-<account>. Each docker run below passes the two URLs to the container with -e LIQUIBASE_COMMAND_URL -e LIQUIBASE_COMMAND_REFERENCE_URL. Liquibase 5.0.1 masks the passphrase in its log.

Python

Install Python and add it to PATH during the installation.

Generate the delta deployment scripts

Generate the scripts in three parts with the official Liquibase Docker image: all database objects except the views, then the views, then the stored procedures. Liquibase Community does not compare stored procedures and cannot read the original definition of views, so a Python script exports both.

All database objects except the views

  1. Open PowerShell 7 in liquibase_demo.
  2. Generate the changelog XML file for one schema, here <layer schema>. Run the command once per schema; each run adds to the same file. With all schemas in one run, the comparison takes very long. 
    docker run --rm `
  -e LIQUIBASE_COMMAND_URL -e LIQUIBASE_COMMAND_REFERENCE_URL `
  -v "<key file>:/liquibase/keys/snowflake_key.p8:ro" `
  -v "$PWD\liquibase\conf:/liquibase/conf:ro" `
  -v "$PWD\liquibase\changelog:/liquibase/changelog" `
  -v "$PWD\liquibase\lib\snowflake-jdbc-4.0.0.jar:/liquibase/internal/lib/snowflake-jdbc-4.0.0.jar:ro" `
  -v "$PWD\liquibase\lib\slf4j-api-2.0.9.jar:/liquibase/internal/lib/slf4j-api-2.0.9.jar:ro" `
  -v "$PWD\liquibase\lib\slf4j-simple-2.0.9.jar:/liquibase/internal/lib/slf4j-simple-2.0.9.jar:ro" `
  -v "$PWD\liquibase\lib\bcprov-jdk18on-1.83.jar:/liquibase/internal/lib/bcprov-jdk18on-1.83.jar:ro" `
  -v "$PWD\liquibase\lib\bcpkix-jdk18on-1.83.jar:/liquibase/internal/lib/bcpkix-jdk18on-1.83.jar:ro" `
  -e JAVA_OPTS='-Dnet.snowflake.jdbc.enableBouncyCastle=true' `
  liquibase/liquibase:5.0.1 `
  --defaultsFile=/liquibase/conf/liquibase.properties `
  --changeLogFile=/liquibase/changelog/dev_to_prod_all_except_views.xml `
  --schemas=<layer schema> --referenceSchemas=<layer schema> `
  --includeSchema=true --outputDefaultSchema=true --log-level=info `
  --diffTypes=tables,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,sequences `
  diffChangeLog
    The run ends with Liquibase command 'diffChangelog' was executed successfully.
    Liquibase console output ending with diffChangelog executed successfully
  3. Check that changelog\dev_to_prod_all_except_views.xml lists the changes, for example a createTable for the new Stage:
    Changelog folder with the file dev_to_prod_all_except_views.xml
    Changelog XML with a createTable change for STG_ST_EMPLOYEE in schema SA
  4. Download fix_timestamp_tz_v3.ps1 and run it on the file: .\fix_timestamp_tz_v3.ps1 -changelogFile "$PWD\liquibase\changelog\dev_to_prod_all_except_views.xml". The script changes every timestamp column type in the file to TIMESTAMP_TZ, because the Liquibase Snowflake driver writes no TIMESTAMP_TZ types.
  5. Generate the SQL file. Use the same docker run options up to the image name, followed by: 
      --defaultsFile=/liquibase/conf/liquibase.properties `
  --changeLogFile=dev_to_prod_all_except_views.xml `
  --schemas=<schemas> --outputDefaultSchema=true `
  updateSQL > liquibase\changelog\dev_to_prod_all_except_views.sql
    Here <schemas> lists the schemas of the first step, for example SA,RDV,BV. The run ends with Liquibase command 'updateSql' was executed successfully.
    Liquibase console output ending with updateSql executed successfully
  6. Open dev_to_prod_all_except_views.sql and delete the warning lines at its beginning. The file contains, for example, CREATE TABLE <PROD database>.SA.STG_ST_EMPLOYEE for the new Stage.
    Changelog folder with dev_to_prod_all_except_views.xml and dev_to_prod_all_except_views.sql
  7. Review each statement.

    Liquibase compares structures only. It writes a renamed column as a dropped and an added column, and it drops tables and columns that exist only in PROD; their data is lost. Review the generated SQL files and back up the PROD database before you run them.

Views and stored procedures

  1. Download export_snowflake_views_or_procedures_v3.py into liquibase_demo.
    liquibase_demo folder with the Python export script
  2. Set the values in CONFIG for your Snowflake environment, including private_key_file (<key file>), prod_database, schemas and output_dir. The script reads the passphrase from SNOWFLAKE_PRIVATE_KEY_PASSPHRASE, see Connection variables.
  3. Install the Snowflake connector: pip install snowflake-connector-python.
  4. Run python .\export_snowflake_views_or_procedures_v3.py views. The script writes views_to_deploy_prod.sql with every view of the DEV schemas, with the DEV database name replaced by prod_database.
    liquibase_demo folder with the created file views_to_deploy_prod.sql
    View definition for BV.BDV_HUB_CREDITCARD_SUPERIORCARD_DELTA in views_to_deploy_prod.sql
  5. Run python .\export_snowflake_views_or_procedures_v3.py procedures. The script writes procedures_to_deploy_prod.sql with every stored procedure, as CREATE OR REPLACE PROCEDURE <PROD database>.<layer schema>.<procedure>.
    liquibase_demo folder with the created file procedures_to_deploy_prod.sql

Run the delta deployment

Run the files in PROD in this order:

  1. Run procedures_to_deploy_prod.sql.
  2. Drop the tasks of the project's load control schema. List them with SHOW TASKS IN SCHEMA <PROD database>.<load control schema>;, then generate the drop statements and run them: 
    SELECT 'DROP TASK IF EXISTS ' || "database_name" || '.'
  || "schema_name" || '."' || "name" || '";'
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
  3. Generate the project, replace the placeholders with the PROD values, and run LoadControl\snowflake_depending_tasks.sql in PROD, or the task script of your load control option. With Generate Drop Load Control Schema selected, the script first drops the load control schema with all objects in it.
  4. Run dev_to_prod_all_except_views.sql.
  5. Run views_to_deploy_prod.sql. Views that use other views fail when those come later in the file, so run it again until no error remains.
  6. Drop the views and stored procedures of deleted model objects by name, for example the loader of CURRENCY. The export creates or replaces objects; it does not drop any.

Check the result

Load PROD, for example as described in Snowflake - Load data - Native Load Control, and check that every task succeeds and that the tables keep their earlier rows.

Related articles