Snowflake-Labs/dlsync

Snowflake-Labs/dlsync

Description: A declarative and migration based Database Change Management tool for Snowflake

Language: Java

License: Apache-2.0

Stars: 42

Forks: 11

Open issues: 6

Created: 2024-11-14T10:13:54Z

Pushed: 2026-04-06T07:54:06Z

Default branch: main

Fork: no

Archived: no

README:

DLSync

 ---

Overview

DLSync is a database change management tool designed to streamline the development and deployment of Snowflake changes. By associating each database object (view, table, UDF, etc.) with a corresponding SQL script file, DLSync tracks every modification, ensuring efficient and accurate updates. Each script can also have a corresponding test script that can be used to write unit tests for the database object. DLSync keeps track of what changes have been deployed to the database by using a hash. Hence, DLSync is capable of identifying what scripts have changed in the current deployment. Because of this, DLSync only deploys changed scripts to database objects. DLSync also understands interdependencies between different scripts, thus applying these changes according to their dependencies.

Table of Contents

1. [Overview](#overview) 1. [Key Features](#key-features) 1. [Project structure](#project-structure) 1. [Script content](#script-content) 1. [Declarative Script](#1-declarative-script) 1. [Migration Script](#2-migration-script) 1. [Test Script](#3-test-script) 1. [Configurations](#configurations) 1. [Parameter profile](#parameter-profile) 1. [config file](#config-file) 1. [How to use this tool](#how-to-use-this-tool) 1. [Deploy](#deploy) 1. [Test](#test) 1. [Plan](#plan) 1. [Rollback](#rollback) 1. [Verify](#verify) 1. [Create script](#create-script) 1. [Required Privileges](#required-privileges) 1. [Warehouse & Database Access](#warehouse--database-access) 1. [DLSync Schema (Metadata Tracking)](#dlsync-schema-metadata-tracking) 1. [Database-Level Objects](#database-level-objects) 1. [Account-Level Objects](#account-level-objects) 1. [DLSync Metadata Tables](#dlsync-metadata-tables) 1. [dl_sync_script_history](#dl_sync_script_history) 1. [dl_sync_change_sync](#dl_sync_change_sync) 1. [dl_sync_script_event](#dl_sync_script_event) 1. [Example scripts](#example-scripts) 1. [Contributing](#contributing)

Key Features

• Hybrid Change Management: It combines declarative and migration based change management to manage database changes
• Account-Level and Database-Level Objects: DLSync supports both account-level objects (databases, schemas, roles, warehouses, security integrations, etc.) and database-level objects (views, tables, functions, procedures, etc.)
• Unique Script per Object: Each object has a corresponding unique script file where you can define changes to the object
• Unit Testing: It supports unit testing where you can write test scripts for each database object
• Change Detection: It can detect changes between the previous deployment and the current script state
• Dependency Resolution: It can reorder scripts based on their dependencies before deploying to the database
• Parametrization: It supports parametrization of scripts where you can define variables that change between different database instances. Each instance is associated with a parameter config file that lists the variables and their values for that instance
• Rollback: It supports rollback to the previous deployment state. Rollback is simple and intuitive. Only one needs to rollback the git repository of the scripts and trigger the rollback module
• Verification: It supports a verify module where each database object is checked against the current script to verify deployment or track out-of-sync database changes
• Script Creation: It supports creating script files for existing database objects

Project structure

To use this tool, first create your script root directory. This directory will contain all scripts and configurations. Inside this directory, create a directory structure like this:

/script-root # Root directory for the scripts
├── /main # Main scripts for deployment
│ ├── /ACCOUNT # Account-level objects (databases, schemas, roles, warehouses)
│ │ ├── /DATABASES # Database definitions
│ │ │ ├── database_name_1.sql # Database creation script
│ │ │ ├── database_name_2.sql # Database creation script
│ │ ├── /SCHEMAS # Schema definitions
│ │ │ ├── database_name_1.schema_name_1.sql # Schema creation script (database.schema format)
│ │ │ ├── database_name_2.schema_name_2.sql # Schema creation script (database.schema format)
│ │ ├── /ROLES # Role definitions
│ │ │ ├── role_name_1.sql # Role creation script
│ │ │ ├── role_name_2.sql # Role creation script
│ │ ├── /WAREHOUSES # Warehouse definitions
│ │ │ ├── warehouse_name.sql # Warehouse creation script
│ ├── /database_name_1 # Database name
│ │ ├── /schema_name_1 # database Schema name
│ │ │ ├── /[object_type]_1 # Database Object type like (VIEWS, FUNCTIONS, TABLES ...)
│ │ │ │ ├── object_name_1.sql # The database object name(table name, view name, function name ...)
│ │ │ │ ├── object_name_2.sql # The database object name(table name, view name, function name ...)
│ │ │ ├── /[object_type]_2 # Database Object type like (VIEWS, FUNCTIONS, TABLES ...)
│ │ │ │ ├── object_name_3.sql # The database object name(table name, view name, function name ...)
│ │ │ │ ├── object_name_4.sql # The database object name(table name, view name, function name ...)
│ │ ├── /schema_name_2 # database Schema name
│ │ │ ├── /[object_type]_1 # Database Object type like (VIEWS, FUNCTIONS, TABLES ...)
│ │ │ │ ├── object_name_5.sql # The database object name(table name, view name, function name ...)
│ │ │ │ ├── object_name_6.sql # The database object name(table name, view name, function name ...)
│ │ │ ├── /[object_type]_2 # Database Object type like (VIEWS, FUNCTIONS, TABLES ...)
│ │ │ │ ├── object_name_7.sql # The database object name(table name, view name, function name ...)
│ │ │ │ ├── object_name_8.sql # The database object name(table name, view name, function name ...)
├── /test # SQL unit test scripts
│ ├── /database_name_1
│ │ ├── /schema_name_1
│ │ │ ├── /[object_type]_1
│ │ │ │ ├── object_name_1_test.sql # unit test file for object object_name_1_test
│ │ │ │ ├── object_name_2_test.sql # unit test file for object object_name_2_test
│ │ ├── /schema_name_2
│ │ │ ├──…