Snowflake Architecture · Topic 9 of 18

Git Integration and Rollback Processes in Snowflake

Connect Git repositories to Snowflake with CREATE GIT REPOSITORY and plan rollback using Time Travel, UNDROP, clones, swap and Git revert for deployments.

  • 5 min
In a nutshell
  • CREATE GIT REPOSITORY needs an API integration (and a secret for private repositories); FETCH is manual; run files with EXECUTE IMMEDIATE FROM @repo/branches/<name>/<file>.
  • Supported hosts: GitHub, GitLab, Bitbucket, Azure DevOps, AWS CodeCommit.
  • Rollback code through Git (previous tag); data through Time Travel, UNDROP, clones with AT or BEFORE, and SWAP WITH.

What it is

Two lifecycle questions finish the chapter's first half. How does code held in Git reach Snowflake, and how do you undo a release that went wrong?

Git integration lets Snowflake hold a synced copy of a remote repository, so files can be browsed and run from inside Snowflake. Rollback is the plan for returning code and data to a known good state. Code rolls back through Git; data rolls back through Time Travel, clones, swap and replication.

Remote repositoryGitHub, GitLab and othersfetchGit repository objectclone in SnowflakeEXECUTE IMMEDIATE FROMSQL scriptsdeployed objects

How it works

Git integration

A Git repository object in Snowflake is a clone of a remote repository, including all branches, tags and commits. Supported providers are GitHub, GitLab, Bitbucket, Azure DevOps and AWS CodeCommit. Snowflake does not require a Git repository for CI/CD; create one when Snowflake features such as Workspaces need the repository files.

Setup uses three objects: a secret with credentials (for a private repository), an API integration that allows the repository host, and the repository itself.

CREATE SECRET git_secret TYPE = PASSWORD USERNAME = '<user>' PASSWORD = '<token>';

CREATE API INTEGRATION git_api
  API_PROVIDER = git_https_api
  API_ALLOWED_PREFIXES = ('https://github.com/my-org')
  ALLOWED_AUTHENTICATION_SECRETS = (git_secret)
  ENABLED = TRUE;

CREATE GIT REPOSITORY app_repo
  API_INTEGRATION = git_api
  GIT_CREDENTIALS = git_secret
  ORIGIN = 'https://github.com/my-org/app.git';

ALTER GIT REPOSITORY app_repo FETCH;
EXECUTE IMMEDIATE FROM @app_repo/branches/main/deploy/setup.sql;

What the repository object lets you do:

  • FETCH pulls the latest branches, tags and commits from the remote. It is not automatic; fetch before you deploy.
  • Files are addressed under the repository as a stage-like path: @repo/branches/<branch>/... or @repo/tags/<tag>/....
  • EXECUTE IMMEDIATE FROM runs a .sql file from the repository, so a script in Git becomes a deployment.
  • SHOW GIT BRANCHES and SHOW GIT TAGS list what was fetched.
  • Handler code for functions, procedures and tasks can be imported from repository files.
  • Workspaces, Streamlit apps and notebooks can commit and push changes back.
Exam tip

Deploy from a tag or a commit, not a moving branch. A tag names exactly what went to production, which makes rollback a redeploy of the previous tag.

Because the repository is read through Snowflake, the account's network access to the Git host is controlled by the API integration's allowed prefixes, and credentials stay in a secret and not in scripts.

Rollback process

Plan rollback before you deploy. The right tool depends on what went wrong.

ProblemRollback tool
Bad code or DDLRedeploy the previous Git tag, or git revert and redeploy
Table dropped by mistakeUNDROP TABLE (also schemas and databases)
Bad DML on a tableClone or query the table BEFORE the statement, then restore
Whole release must be reversedSwap back to the retained previous version
Region or account failureReplication and failover groups

Time Travel keeps changed and deleted data for the retention period. The default is 1 day; on Enterprise Edition and above permanent tables can be set up to 90 days, while Standard Edition is limited to 1 day. Transient and temporary tables allow 0 or 1 day.

-- Restore a table to its state before a bad statement
CREATE TABLE orders_restored CLONE orders
  BEFORE (STATEMENT => '<query_id>');

-- Bring back a dropped table
UNDROP TABLE orders;

Clone before release. Take a zero copy clone of the database or schema just before deployment. If the release fails, you have an instant restore point that costs little storage.

Swap for blue green releases. Build the new version in a clone, validate it, then exchange names with ALTER SCHEMA prod SWAP WITH prod_new (or the same for tables and databases). The swap is a metadata operation, so rolling back is another swap.

Fail-safe is not a rollback tool. It is a further 7 days for permanent tables after Time Travel ends, and only Snowflake can recover from it. Transient tables have no Fail-safe.

Releaseclone taken firstvalidateSwapnew and old names exchangedproblem foundSwap backor UNDROP or clone BEFORE

Rolling back code and data are separate steps. Reverting DDL does not restore data that a faulty script already changed, so a release plan includes both the Git revert and the data restore.

Common mistakes

Assuming the repository updates itself. Run ALTER GIT REPOSITORY ... FETCH first, or you deploy stale files.

Deploying from a branch name. The branch moves; use a tag or commit.

Relying on Fail-safe. Only Snowflake can recover it, it is not instant, and it only follows Time Travel for permanent tables.

Letting retention expire before checking. On Standard Edition with 1 day Time Travel, a problem found after two days cannot be rolled back with Time Travel.

Rolling back code only. Data changed by the release also needs restoring.

What to remember for the exam

  • CREATE GIT REPOSITORY needs an API integration (and a secret for private repositories); FETCH is manual; run files with EXECUTE IMMEDIATE FROM @repo/branches/<name>/<file>.
  • Supported hosts: GitHub, GitLab, Bitbucket, Azure DevOps, AWS CodeCommit.
  • Rollback code through Git (previous tag); data through Time Travel, UNDROP, clones with AT or BEFORE, and SWAP WITH.
  • Time Travel default 1 day; up to 90 days on Enterprise or higher for permanent tables.
  • Fail-safe: 7 days for permanent tables, Snowflake only, not a rollback plan.

Check yourself

  • What command makes new commits from GitHub visible in a Snowflake Git repository?

    ALTER GIT REPOSITORY ... FETCH. The repository does not sync on its own.

  • A release dropped a table yesterday. What is the fastest restore?

    UNDROP TABLE, as long as Time Travel retention has not expired.

  • Why is a swap a good rollback design?

    It is a metadata only exchange of names, so switching back to the previous version is instant and repeatable.

Topic 9 of 18 in Snowflake Architecture

Test yourself on Snowflake Architecture

The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.