Data Transformation · Topic 11 of 14

Git Integration, dbt Projects and Deployment Pipelines

Connect Git to Snowflake, run dbt projects natively, and build deployment pipelines with EXECUTE IMMEDIATE FROM, Snowflake CLI and CI/CD for promotion.

  • 4 min
In a nutshell
  • Git setup: secret, API integration (git_https_api), CREATE GIT REPOSITORY; refresh with FETCH.
  • EXECUTE IMMEDIATE FROM @repo/branches/main/file.sql runs repository SQL.
  • dbt: CREATE DBT PROJECT or snow dbt deploy, run with EXECUTE DBT PROJECT, schedule with tasks.

What it is

Git integration puts a clone of a remote repository inside Snowflake so Snowflake can read, and run, the files. dbt Projects on Snowflake lets you build, deploy, schedule and monitor dbt transformations as Snowflake objects. A deployment pipeline moves reviewed code from Git to each environment automatically.

Remote repoGitHub, GitLab and othersFETCHGit repository objectclone in SnowflakeEXECUTE IMMEDIATE FROMDeployed objectstables, views, proceduresRemote reposnow dbt deployDBT PROJECT objectversionedEXECUTE DBT PROJECTModels and tests

How it works

Git integration and version control

Snowflake supports GitHub, GitLab, Bitbucket, Azure DevOps and AWS CodeCommit. Setup has three pieces:

  1. A secret that holds the credentials (needed for private repositories).
  2. An API integration with API_PROVIDER = git_https_api, listing API_ALLOWED_PREFIXES and the secrets it may use.
  3. A Git repository object created with CREATE GIT REPOSITORY, naming the API integration, the origin URL and the secret.
CREATE GIT REPOSITORY etl_repo
  API_INTEGRATION = github_int
  GIT_CREDENTIALS = github_secret
  ORIGIN = 'https://github.com/acme/etl.git';

ALTER GIT REPOSITORY etl_repo FETCH;

EXECUTE IMMEDIATE FROM @etl_repo/branches/main/deploy/setup.sql;
  • The clone holds all branches, tags and commits. Files are addressed as @repo/branches/<branch>/<path> (or tags/).
  • FETCH pulls the latest from the remote; the clone does not update on its own.
  • EXECUTE IMMEDIATE FROM runs a .sql file from the repository. It supports template variables, so one script can serve several environments.
  • Files in the clone can be used as handler code for functions, procedures and tasks.
  • You can commit and push from Workspaces, notebooks and Streamlit apps. Browse with SHOW GIT BRANCHES and SHOW GIT TAGS.
Exam tip

"Run the same SQL script from the main branch in every environment" is EXECUTE IMMEDIATE FROM @repo/branches/main/... with a variable for the environment.

Snowflake dbt Projects

dbt Core transformations run inside Snowflake. A project needs dbt_project.yml, a profile file and model files, stored in a workspace or a connected Git repository. Run dbt deps so packages are in the project.

ActionSQLSnowflake CLI
Deploy a project as a Snowflake objectCREATE DBT PROJECT ... FROM <source>snow dbt deploy
Run it (build, run, test)EXECUTE DBT PROJECTsnow dbt execute
  • Scheduling: put EXECUTE DBT PROJECT in a task, or call it from an external orchestrator such as Apache Airflow.
  • Compute: runs use a virtual warehouse and bill as normal compute; there is no separate licence fee.
  • Monitoring: run history, logs, artifacts and column level lineage appear in Snowsight.
  • CI/CD: use the Snowflake CLI from GitHub Actions, GitLab or Azure DevOps. Slim CI builds only changed models and their dependents, and defer to production resolves unchanged upstream references to the production objects.

Code deployment pipelines

A deployment pipeline is Git plus automation.

Pull requestmerged to maintriggersCI jobSnowflake CLIdeploys toDev databasetests passTest databaseapprovalProduction
ApproachHow
Script basedNumbered SQL scripts run by snow sql or EXECUTE IMMEDIATE FROM; migration tools such as schemachange record what has run
DeclarativeCREATE OR ALTER brings a supported object to the stated definition without dropping it
dbtsnow dbt deploy and EXECUTE DBT PROJECT
Infrastructure as codeTerraform provider for objects such as databases, roles and warehouses
Snowpark objectssnow snowpark deploy packages and creates functions and procedures

Good practices: run the pipeline under a dedicated service user with key pair authentication, keep secrets in the CI system, and use the same scripts in every environment with parameters for names and sizes.

Watch out

Do not deploy by running a worksheet by hand in production. A repeatable, reviewed pipeline is the exam answer for "reduce deployment errors".

Common mistakes

Expecting the Git repository object to update itself. Run ALTER GIT REPOSITORY ... FETCH first.

Forgetting the API integration allow list. The origin URL must match an entry in API_ALLOWED_PREFIXES.

Dropping and recreating tables in a pipeline. That loses data; use migrations or CREATE OR ALTER.

Treating a deployed dbt project as scheduled. It runs only when something executes it, such as a task.

What to remember for the exam

  • Git setup: secret, API integration (git_https_api), CREATE GIT REPOSITORY; refresh with FETCH.
  • EXECUTE IMMEDIATE FROM @repo/branches/main/file.sql runs repository SQL.
  • dbt: CREATE DBT PROJECT or snow dbt deploy, run with EXECUTE DBT PROJECT, schedule with tasks.
  • Pipelines: Git, CI, Snowflake CLI, per environment promotion, CREATE OR ALTER or migration scripts.

Check yourself

  • How do you refresh a Git repository object with the latest remote commits?

    Run ALTER GIT REPOSITORY with FETCH.

  • How do you run a dbt project every night inside Snowflake?

    Deploy it as a DBT PROJECT object and call EXECUTE DBT PROJECT from a scheduled task.

  • What is needed before CREATE GIT REPOSITORY works on a private repository?

    A secret with credentials and an API integration that allows the repository URL prefix.

Topic 11 of 14 in Data Transformation

Test yourself on Data Transformation

The full Snowflake DEA-C02 question bank has 709 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.