Enhancing Data Operations with Snowflake Git Integration

Streamlining Your Data Workflow

In today's fast-paced data landscape, effective version control and seamless integration are paramount. Snowflake's Git integration offers a secure and streamlined way to connect your Snowflake environment with a remote Git repository. By leveraging this integration, you can synchronize files from your Git repository to a specialized stage within Snowflake, known as a repository stage. This repository stage functions like a local Git repository, containing a full clone of your remote repository complete with branches, tags, and commits.

With this setup, Snowflake provides a single source of truth for all your SQL scripts, Snowpark functions, procedures, Native Apps, and Streamlit Apps, ensuring that your codebase is consistent and up-to-date across your entire team. By having the repository contents synchronized to your Snowflake account, you can reference and utilize these files directly within Snowflake, just as you would with any other staged file.

Currently, Snowflake supports Git integration with several major platforms, including GitHub, GitLab, BitBucket, and Azure DevOps. This broad compatibility ensures that no matter where your code is hosted, you can seamlessly integrate it into your Snowflake workflows, enhancing collaboration, efficiency, and version control in your data operations.

Steps to set up

Step 1: Create a secret, to contain credentials for authenticating with the repository.

CREATE OR REPLACE SECRET my_secret
TYPE = PASSWORD
USERNAME = 'test'
PASSWORD = 'github_sjhsdjkfdsdcbcuilbjdsskahcncmjkdlhfsjdcnmdlaskjkcxm'
COMMENT = 'my git login' ;

Step 2: Create an API integration to specify details about Snowflake interaction with the Git repository API.

CREATE OR REPLACE API INTEGRATION git_api_integration
API_PROVIDER = git_https_api
API_ALLOWED_PREFIXES = ('https://github.com/test-repo')
ALLOWED_AUTHENTICATION_SECRETS = (my_secret)
ENABLED = TRUE;

Step 3: Create a Snowflake Git repository stage to which you can synchronize files from the repository.

CREATE OR REPLACE GIT REPOSITORY git_repo
API_INTEGRATION = git_api_integration
ORIGIN = 'https://github.com/test-repo/test-main.git'
GIT_CREDENTIALS = 'my_secret'
COMMENT = 'my git repo' ;

Git Operations

Refresh a repository stage from the repository

alter git repository git_repo fetch;

View a list of repository branches or tags

show git branches in git_repo;

View a list of repository files

By branch name:

ls @git_repo/branches/adding-schema-call-block-to-ci-cd-main;

By tag name:

ls @repository_stage_name/tags/tag_name;

By commit hash:

ls @git_repo/commits/ac9aaa8a09755ffbf7543deb7656fb06cf604703;

View repository stage properties

describe git repository git_repo;

Execute code from a repository

execute immediate from '@git_repo/branches/main/Code Base/SPFUNCTIONCHANGES.sql';

View available repositories

show git repositories;

View available tags in the repository

show git tags in git_repo;

Drop an existing repository

drop git repository git_repo;

Use Cases

SQL Scripts

With Snowflake's git integration, you can maintain all SQL scripts like stored procedures, tasks, and table creation statements in a git repository and use them directly in Snowflake. With execute immediate statements, you can execute SQL scripts from your git repository in Snowflake:

EXECUTE IMMEDIATE FROM @snowflake_extensions/branches/main/sql/create-database.sql;

Snowpark Python

You can create Snowflake stored procedures that import files from a git repository and use them as sources. As you update files in the git repository and refresh the git stage, the stored procedure automatically updates:

create or replace procedure filter_by_role(tablename varchar, role varchar)
returns table(id number, name varchar, role varchar)
language python
runtime_version = '3.8'
packages = ('snowflake-snowpark-python')
imports = ('@example_db.example_schema.snowflake_extensions/branches/main/python-handlers/filter.py')
handler = 'filter.filter_by_role';

Use a Git repository file to configure new accounts

With EXECUTE IMMEDIATE FROM, you can execute scripts managed in your Git repository from any Snowflake session. For example, you might have a script that sets up every new Snowflake account in your organization with users, roles, objects, and privileges.

1. Create the file setup.sql with the following contents:
   • CREATE ROLE analyst;
   • CREATE USER gladys;
   • GRANT ROLE analyst TO USER gladys;
   • SHOW GRANTS TO USER gladys;
2. Commit your SQL file to your Git repository using the git command line tool:
   • git add scripts/setup.sql
   • git commit -m "Adding code to set up new accounts"
   • git push
3. In Snowflake, refresh the repository stage:
   • alter git repository configuration_repo fetch;
4. In Snowflake, execute the file in your repository stage:
   • execute immediate from @configuration_repo/branches/main/scripts/setup.sql;

Streamlit

You can maintain Streamlit code in a git repository and use it in your Snowflake account:

create streamlit git_sample
root_location='@snowflake_extensions/branches/main/streamlit'
main_file='streamlitapp.py';

Current limitations

• Interaction between Snowflake and a Git repository is currently read-only; repository files may be read from the repository, but not written to it.
• Access to Git repositories from Snowflake is supported only over the public internet. Access to repositories behind a private network is not supported.
• Sharing repository stages is not supported through data sharing or apps built on the Snowflake Native App Framework.
• Creating repository stages inside application packages is not supported and might be blocked in the future.
• Creating repository stages inside native applications on the consumer side is not supported.

Cost Implication

Storage cost: Incurs stage storage cost

Compute cost: All git operations utilize compute of custom warehouse and compute cost depends on the time it takes for each git operation

Conclusion

This overview covers key scenarios, though it's not exhaustive—there are additional use cases, such as integrating git with Native app packages. This guide provides a solid foundation to start leveraging Snowflake's git integration. This blog represents the initial phase of a broader integration between git and Snowflake. Stay tuned for future updates and enhancements that will further streamline the management of your Snowflake objects through source control.