Overview

Key Features

Prerequisites

What You Will Learn

What You Will Need

What You Will Build

Note - DLSync is a community-developed tool, not an official Snowflake offering. It comes with no support or warranty.

GitHub Actions Overview

DLSync Overview

Project Structure

/db_scripts
├── /main
│   ├── /ACCOUNT
│   │   ├── /DATABASES
│   │   │   ├── DATABASE_NAME.SQL
│   │   ├── /SCHEMAS
│   │   │   ├── DATABASE_NAME.SCHEMA_NAME.SQL
│   │   ├── /ROLES
│   │   │   ├── ROLE_NAME.SQL
│   │   ├── /WAREHOUSES
│   │   │   ├── WAREHOUSE_NAME.SQL
│   ├── /DEMO_DB
│   │   ├── /DEMO_SCHEMA
│   │   │   ├── /VIEWS
│   │   │   │   ├── CUSTOMER_SUMMARY.SQL
│   │   │   ├── /TABLES
│   │   │   │   ├── CUSTOMERS.SQL
│   │   │   │   ├── ORDERS.SQL
│   │   │   ├── /FUNCTIONS
│   │   │   │   ├── CALCULATE_TAX.SQL
├── /test
│   ├── /DEMO_DB
│   │   ├── /DEMO_SCHEMA
│   │   │   ├── /VIEWS
│   │   │   │   ├── CUSTOMER_SUMMARY_TEST.SQL
│   │   │   ├── /FUNCTIONS
│   │   │   │   ├── CALCULATE_TAX_TEST.SQL
├── config.yml
├── parameter-dev.properties
├── parameter-prod.properties

Note: Schema object filenames in the ACCOUNT directory should include the database name in the format database_name.schema_name.sql (e.g., DEMO_DB.DEMO_SCHEMA.SQL).

Script Files

Declarative Scripts

Note: If the object name is parametrized (e.g., SAMPLE_${TENANT}_VIEW), the file name should use the same parameter syntax (e.g., SAMPLE_${TENANT}_VIEW.SQL).

CREATE OR REPLACE VIEW ${MY_DB}.${MY_SCHEMA}.CUSTOMER_SUMMARY AS
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) as total_orders,
    SUM(o.order_amount) as total_spent
FROM ${MY_DB}.${MY_SCHEMA}.CUSTOMERS c
LEFT JOIN ${MY_DB}.${MY_SCHEMA}.ORDERS o 
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

CREATE OR REPLACE FUNCTION ${MY_DB}.${MY_SCHEMA}.CALCULATE_TAX(amount NUMBER, country VARCHAR)
RETURNS NUMBER
LANGUAGE SQL
AS
$$
  CASE 
    WHEN country = 'US' THEN amount * 0.08
    WHEN country = 'CA' THEN amount * 0.12
    WHEN country = 'UK' THEN amount * 0.20
    ELSE amount * 0.05
  END
$$;

Migration Scripts

---version: VERSION_NUMBER, author: NAME
CONTENT;
---rollback: ROLLBACK_CONTENT;
---verify: VERIFY_CONTENT;

---version: 0, author: demo_user
CREATE OR REPLACE TABLE ${MY_DB}.${MY_SCHEMA}.CUSTOMERS(
    customer_id NUMBER,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);
---rollback: DROP TABLE IF EXISTS ${MY_DB}.${MY_SCHEMA}.CUSTOMERS;
---verify: SELECT * FROM ${MY_DB}.${MY_SCHEMA}.CUSTOMERS LIMIT 1;

---version: 1, author: demo_user
ALTER TABLE ${MY_DB}.${MY_SCHEMA}.CUSTOMERS ADD COLUMN created_date DATE;
---rollback: ALTER TABLE ${MY_DB}.${MY_SCHEMA}.CUSTOMERS DROP COLUMN created_date;
---verify: SELECT created_date FROM ${MY_DB}.${MY_SCHEMA}.CUSTOMERS LIMIT 1;

---version: 0, author: demo_user
CREATE OR REPLACE TABLE ${MY_DB}.${MY_SCHEMA}.ORDERS(
    order_id NUMBER,
    customer_id NUMBER,
    order_amount NUMBER(10,2),
    order_date DATE DEFAULT CURRENT_DATE
);
---rollback: DROP TABLE IF EXISTS ${MY_DB}.${MY_SCHEMA}.ORDERS;
---verify: SELECT * FROM ${MY_DB}.${MY_SCHEMA}.ORDERS LIMIT 1;

---version: 0, author: admin
CREATE OR REPLACE ROLE ANALYST;
---rollback: DROP ROLE IF EXISTS ANALYST;

---version: 1, author: admin
GRANT ROLE ANALYST TO ROLE ACCOUNTADMIN;
---rollback: REVOKE ROLE ANALYST FROM ROLE ACCOUNTADMIN;

Test Scripts

WITH CUSTOMERS AS (
    SELECT * FROM VALUES
        (1, 'John Doe', '[email protected]'),
        (2, 'Jane Smith', '[email protected]')
    AS T(customer_id, customer_name, email)
),
ORDERS AS (
    SELECT * FROM VALUES
        (101, 1, 250.00),
        (102, 1, 100.00),
        (103, 2, 300.00)
    AS T(order_id, customer_id, order_amount)
),
EXPECTED_DATA AS (
    SELECT 
        1 as customer_id, 
        'John Doe' as customer_name, 
        2 as total_orders, 
        350.00 as total_spent
    UNION ALL
    SELECT 
        2 as customer_id, 
        'Jane Smith' as customer_name, 
        1 as total_orders, 
        300.00 as total_spent
)
SELECT * FROM ${MY_DB}.${MY_SCHEMA}.CUSTOMER_SUMMARY;

WITH MOCK_DATA AS (
    SELECT * FROM VALUES
        (100.00, 'US'),
        (200.00, 'CA'),
        (300.00, 'UK'),
        (400.00, 'DE')
    AS T(amount, country)
),
EXPECTED_DATA AS (
    SELECT 
        8.00 as expected_tax
    UNION ALL
    SELECT  
        24.00 as expected_tax
    UNION ALL
    SELECT 
        60.00 as expected_tax
    UNION ALL
    SELECT 
        20.00 as expected_tax
)
SELECT 
    ${MY_DB}.${MY_SCHEMA}.CALCULATE_TAX(m.amount, m.country) as calculated_tax
FROM MOCK_DATA m;

Configuration

Parameter Profiles

parameter-[profile].properties

Config File

version: 1.0
configTables:
scriptExclusion:
continueOnFailure: "false"
dependencyOverride:
  - script:
    dependencies:
connection:
    account:
    user:
    password:
    role:
    warehouse:
    db:
    schema:
    authenticator:
    private_key_file:
    private_key_pwd:

Warning: Avoid adding connection information to config.yml since it is checked into your Git repository. Use environment variables instead for connection details.

version: 1.0
continueOnFailure: "false"

MY_DB=DEMO_DB_DEV
MY_SCHEMA=DEMO_SCHEMA

MY_DB=DEMO_DB_PROD
MY_SCHEMA=DEMO_SCHEMA_PROD

DLSync Commands

Environment Variables

account=my_account
db=database
schema=dl_sync
user=user_name
password=password
authenticator=externalbrowser
warehouse=my_warehouse
role=my_role
private_key_file=my_private_key_file.p8
private_key_pwd=my_private_key_password

Deploy

dlsync deploy --script-root path/to/db_scripts --profile dev

dlsync deploy --only-hashes --script-root path/to/db_scripts --profile dev

Test

dlsync test --script-root path/to/db_scripts --profile dev

Plan

dlsync plan --script-root path/to/db_scripts --profile dev

========== DEPLOYMENT PLAN ==========
Total changes to deploy: 2
========== DEPLOYMENT ORDER ==========
1 of 2: MY_DB.MY_SCHEMA.SAMPLE_TABLE
   Type: Migration
   Object: MY_DB.MY_SCHEMA.SAMPLE_TABLE (Version: 1)
   Author: [email protected]
   Hash: a1b2c3d4e5f6g7h8
   Content:
   CREATE TABLE MY_DB.MY_SCHEMA.SAMPLE_TABLE(id VARCHAR, my_column VARCHAR);
========== END CONTENT ==========
2 of 2: MY_DB.MY_SCHEMA.SAMPLE_VIEW
   Type: Declarative
   Object: MY_DB.MY_SCHEMA.SAMPLE_VIEW
   Object Type: VIEW
   Hash: b2c3d4e5f6g7h8i9
   Content:
   CREATE VIEW MY_DB.MY_SCHEMA.SAMPLE_VIEW AS SELECT * FROM MY_DB.MY_SCHEMA.SAMPLE_TABLE;
========== END CONTENT ==========
========== END PLAN ==========

Rollback

dlsync rollback --script-root path/to/db_scripts --profile dev

Verify

dlsync verify --script-root path/to/db_scripts --profile dev

Create Script

dlsync create_script --script-root path/to/db_scripts --profile dev

dlsync create_script --script-root path/to/db_scripts --profile dev --target-schemas schema1,schema2

DLSync Metadata Tables

Important: Do not delete or modify these tables. If DLSync cannot find them, it assumes it is running for the first time.

DL_SYNC_SCRIPT_HISTORY

DL_SYNC_CHANGE_SYNC

DL_SYNC_SCRIPT_EVENT

Setup Snowflake

-- Create the demo role
CREATE ROLE IF NOT EXISTS demo_role;

-- Create the development database and schemas
CREATE DATABASE IF NOT EXISTS demo_db_dev;
CREATE SCHEMA IF NOT EXISTS demo_db_dev.demo_schema;

-- Create DLSync metadata schema
CREATE SCHEMA IF NOT EXISTS demo_db_dev.dlsync;

-- Grant necessary privileges
GRANT ALL PRIVILEGES ON DATABASE demo_db_dev TO ROLE demo_role;
GRANT ALL PRIVILEGES ON SCHEMA demo_db_dev.demo_schema TO ROLE demo_role;
GRANT ALL PRIVILEGES ON SCHEMA demo_db_dev.dlsync TO ROLE demo_role;

-- Grant warehouse usage
GRANT USAGE ON WAREHOUSE DEMO_WH TO ROLE demo_role;

-- Grant the role to your user
GRANT ROLE demo_role TO USER DEMO_USER;

Required Privileges

You may also want to create similar objects for production environments (e.g., demo_db_prod).

Create Action Secrets

Tip - For more details on Snowflake connection properties, see the JDBC Driver connection parameter reference.

Create Actions Workflows

Validate Workflow (Pull Request)

name: validate-db-changes

on:
  pull_request:
    branches:
      - develop
      - main
    paths:
      - 'db_scripts/**'

permissions:
  contents: read
  pull-requests: write

jobs:
  validate-snowflake-changes-job:
    environment: dev
    env:
      PROFILE: ${{ vars.PROFILE }}
      DLSYNC_VERSION: "3.2.0"
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Set up JDK 11
        uses: actions/setup-java@v3
        with:
          java-version: '11'
          distribution: 'temurin'

      - name: Download DLSync JAR
        run: |
          curl -sL -o "$GITHUB_WORKSPACE/dlsync.jar" \
            "https://github.com/Snowflake-Labs/dlsync/releases/download/v${DLSYNC_VERSION}/dlsync-${DLSYNC_VERSION}.jar"

      - name: Run DLSync Plan
        id: plan
        env:
          account: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          user: ${{ secrets.SNOWFLAKE_USERNAME }}
          password: ${{ secrets.SNOWFLAKE_PASSWORD }}
          role: ${{ secrets.SNOWFLAKE_ROLE }}
          warehouse: ${{ secrets.SNOWFLAKE_WAREHOUSE }}
          db: ${{ vars.SNOWFLAKE_DATABASE }}
          schema: ${{ vars.SNOWFLAKE_SCHEMA }}
        run: |
          set -o pipefail
          java -jar "$GITHUB_WORKSPACE/dlsync.jar" \
            plan \
            --script-root "$GITHUB_WORKSPACE/db_scripts" \
            --profile "$PROFILE" 2>&1 | tee /tmp/dlsync-plan.log
          PLAN_OUTPUT=$(sed -n '/DEPLOYMENT PLAN/,/END PLAN/p' /tmp/dlsync-plan.log)
          if [ -z "$PLAN_OUTPUT" ]; then
            PLAN_OUTPUT="No deployment plan changes detected."
          fi
          DELIM=$(uuidgen)
          echo "PLAN_OUTPUT<<$DELIM" >> $GITHUB_ENV
          echo "$PLAN_OUTPUT" >> $GITHUB_ENV
          echo "$DELIM" >> $GITHUB_ENV

      - name: Post Plan Output to PR
        uses: actions/github-script@v7
        with:
          script: |
            const planOutput = process.env.PLAN_OUTPUT;
            const body = `### DLSync Deployment Plan\n\n\`\`\`\n${planOutput}\n\`\`\``;
            const { data: comments } = await github.rest.issues.listComments({
              owner: context.repo.owner,
              repo: context.repo.repo,
              issue_number: context.issue.number,
            });
            const existing = comments.find(c => c.body.startsWith('### DLSync Deployment Plan'));
            if (existing) {
              await github.rest.issues.updateComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                comment_id: existing.id,
                body,
              });
            } else {
              await github.rest.issues.createComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                issue_number: context.issue.number,
                body,
              });
            }

      - name: Run DLSync Test
        env:
          account: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          user: ${{ secrets.SNOWFLAKE_USERNAME }}
          password: ${{ secrets.SNOWFLAKE_PASSWORD }}
          role: ${{ secrets.SNOWFLAKE_ROLE }}
          warehouse: ${{ secrets.SNOWFLAKE_WAREHOUSE }}
          db: ${{ vars.SNOWFLAKE_DATABASE }}
          schema: ${{ vars.SNOWFLAKE_SCHEMA }}
        run: |
          set -o pipefail
          java -jar "$GITHUB_WORKSPACE/dlsync.jar" \
            test \
            --script-root "$GITHUB_WORKSPACE/db_scripts" \
            --profile "$PROFILE" 2>&1 | tee /tmp/dlsync-test.log
          DELIM=$(uuidgen)
          echo "TEST_OUTPUT<<$DELIM" >> $GITHUB_ENV
          sed -n '/Started Test module/,$p' /tmp/dlsync-test.log >> $GITHUB_ENV
          echo "$DELIM" >> $GITHUB_ENV

      - name: Post Test Results to PR
        if: always()
        uses: actions/github-script@v7
        with:
          script: |
            const testOutput = process.env.TEST_OUTPUT || 'No test output';
            const body = `### DLSync Test Results\n\n\`\`\`\n${testOutput}\n\`\`\``;
            const { data: comments } = await github.rest.issues.listComments({
              owner: context.repo.owner,
              repo: context.repo.repo,
              issue_number: context.issue.number,
            });
            const existing = comments.find(c => c.body.startsWith('### DLSync Test Results'));
            if (existing) {
              await github.rest.issues.updateComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                comment_id: existing.id,
                body,
              });
            } else {
              await github.rest.issues.createComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                issue_number: context.issue.number,
                body,
              });
            }

Deploy Workflow (After Merge)

name: deploy-db-changes

on:
  push:
    branches:
      - develop
      - main
    paths:
      - 'db_scripts/**'
  workflow_dispatch:

permissions:
  contents: read

jobs:
  deploy-snowflake-changes-job:
    environment: dev
    env:
      PROFILE: ${{ vars.PROFILE }}
      DLSYNC_VERSION: "3.2.0"
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Set up JDK 11
        uses: actions/setup-java@v3
        with:
          java-version: '11'
          distribution: 'temurin'

      - name: Download DLSync JAR
        run: |
          curl -sL -o "$GITHUB_WORKSPACE/dlsync.jar" \
            "https://github.com/Snowflake-Labs/dlsync/releases/download/v${DLSYNC_VERSION}/dlsync-${DLSYNC_VERSION}.jar"

      - name: Run DLSync Deploy
        env:
          account: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          user: ${{ secrets.SNOWFLAKE_USERNAME }}
          password: ${{ secrets.SNOWFLAKE_PASSWORD }}
          role: ${{ secrets.SNOWFLAKE_ROLE }}
          warehouse: ${{ secrets.SNOWFLAKE_WAREHOUSE }}
          db: ${{ vars.SNOWFLAKE_DATABASE }}
          schema: ${{ vars.SNOWFLAKE_SCHEMA }}
        run: |
          java -jar "$GITHUB_WORKSPACE/dlsync.jar" \
            deploy \
            --script-root "$GITHUB_WORKSPACE/db_scripts" \
            --profile "$PROFILE"

Enable Required Status Checks

Commit and Push Changes

Run Actions Workflows

On Pull Request

On Merge to Main

Confirm Deployment

SELECT * FROM DEMO_DB_DEV.DLSYNC.DL_SYNC_CHANGE_SYNC ORDER BY START_TIME DESC;
SELECT * FROM DEMO_DB_DEV.DLSYNC.DL_SYNC_SCRIPT_HISTORY;
SELECT * FROM DEMO_DB_DEV.DLSYNC.DL_SYNC_SCRIPT_EVENT ORDER BY CREATED_TS DESC;

Add More Changes

CREATE OR REPLACE VIEW ${MY_DB}.${MY_SCHEMA}.CUSTOMER_SUMMARY AS
SELECT 
    c.customer_id,
    c.customer_name,
    c.email,
    COUNT(o.order_id) as total_orders,
    SUM(o.order_amount) as total_spent,
    AVG(o.order_amount) as avg_order_value
FROM ${MY_DB}.${MY_SCHEMA}.CUSTOMERS c
LEFT JOIN ${MY_DB}.${MY_SCHEMA}.ORDERS o 
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.email;

CREATE OR REPLACE VIEW ${MY_DB}.${MY_SCHEMA}.SALES_REPORT AS
SELECT 
    DATE_TRUNC('month', o.order_date) as sales_month,
    COUNT(o.order_id) as total_orders,
    SUM(o.order_amount) as total_sales,
    SUM(${MY_DB}.${MY_SCHEMA}.CALCULATE_TAX(o.order_amount, 'US')) as total_tax,
    AVG(o.order_amount) as avg_order_value
FROM ${MY_DB}.${MY_SCHEMA}.ORDERS o
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY sales_month DESC;

---version: 0, author: demo_user
CREATE OR REPLACE TABLE ${MY_DB}.${MY_SCHEMA}.ORDERS(
    order_id NUMBER,
    customer_id NUMBER,
    order_amount NUMBER(10,2),
    order_date DATE DEFAULT CURRENT_DATE
);
---rollback: DROP TABLE IF EXISTS ${MY_DB}.${MY_SCHEMA}.ORDERS;
---verify: SELECT * FROM ${MY_DB}.${MY_SCHEMA}.ORDERS LIMIT 1;

---version: 1, author: demo_user
ALTER TABLE ${MY_DB}.${MY_SCHEMA}.ORDERS ADD COLUMN status VARCHAR(20) DEFAULT 'PENDING';
---rollback: ALTER TABLE ${MY_DB}.${MY_SCHEMA}.ORDERS DROP COLUMN status;
---verify: SELECT status FROM ${MY_DB}.${MY_SCHEMA}.ORDERS LIMIT 1;

Commit and Push Changes

Conclusion And Resources

What You Learned

Related Resources