Snowflake Git Integration with Azure DevOps

Git repository for Business Intelligence solutions is a hot topic, especially for SaaS (Software-as-a-Service) platforms like Microsoft Fabric, Snowflake or Databricks.

While in Fabric setup for Git repository in Azure DevOps is relatively simple and it gives you connection in both ways (you can commit changes from Fabric UI), in Snowflake it requires some additional steps.

Snowflake Git Integration

It is a new Preview Feature for Snowflake and it allows you to access Git repository from Snowflake and execute code from that repository using command:

EXECUTE IMMEDIATE FROM snowflake_repository/branches/main/SQL_command_to_execute.sql;

Snowflake allows you to connect to different platform like GitHub, BitBucket, Azure DevOps or AWS CodeCommit.

Ok let's go and set it up for Azure DevOps and private repository. We need to do few steps for that.

1. Generate Git Credentials for repository

In the Azure DevOps repository generate Git credentials which Snowflake will use to connect to it. You will get a username and a password

2. Store credentials in Snowflake secret

Next step is to store those credentials in Snowflake secret

CREATE OR REPLACE SECRET git_secret
  TYPE = password
  USERNAME = 'git_username'
  PASSWORD = 'git_password';

3. Create API Integration

API Integration stores information about our connection. In that step you need to provide link for your Azure DevOps organization and allow to use previously created secret

CREATE OR REPLACE API INTEGRATION git_api_integration
  API_PROVIDER = git_https_api
  API_ALLOWED_PREFIXES = ('https://dev.azure.com/your_devops_organization')
  allowed_authentication_secrets=('git_secret' )
  ENABLED = TRUE;

4. Create GIT repository in Snowflake

Finally using our integration and secret we can connect our repository in the Snowflake.

CREATE OR REPLACE GIT REPOSITORY database_name.schema.repository_name
  API_INTEGRATION = git_api_integration
  GIT_CREDENTIALS = git_secret
  ORIGIN =
  'https://dev.azure.com/your_devops_organization/project_name/_git/repository_name;

Repository is created in a given database (as a Git repositories object) and it is not automatically in sync. You need to always update repository before executing any scripts from it.

Summary

This solution is not perfect yet, but for sure Snowflake is working on new features. Currently, integration is only in one direction - you cannot change objects in database and commit those changes back into repository. However, with this solution you can create your CI/CD pipeline and move all the commits between different environments. This will require preparing all descriptive scripts for database objects, so if you are starting your Snowflake project, think about version control from the beginning.

References

Using a Git repository in Snowflake | Snowflake Documentation

Snowflake Documentation