Managing dbt Projects on Snowflake using Snowflake CLI

Note

The dbt Projects on Snowflake features in Snowflake CLI are available only in version 3.13.0 or later.

You can use Snowflake CLI to manage dbt project objects with the following operations:

Deploying a dbt project object

The snow dbt deploy command uploads local files to a temporary stage and creates a new dbt project object or updates it by making a new version. A valid dbt project must contain dbt_project.yml and one of the supported profile files:

  • dbt_project.yml: A standard dbt configuration file that specifies the profile to use.

  • dbt_projects_profiles.yml or profiles.yml: A dbt connection profile definition referenced in dbt_project.yml. The selected profile file must define the database, role, schema, and type. If both files are present, Snowflake uses dbt_projects_profiles.yml and ignores profiles.yml during deployment, compilation, and subsequent commands.

    • By default, dbt Projects on Snowflake uses your target schema (target.schema) specified from your dbt environment or profile. When you execute a dbt project object, dbt attempts to create the target schema specified in dbt_projects_profiles.yml or profiles.yml if it doesn’t already exist. For more information, see Understand schema generation and customization.
    <profile_name>:
  target: dev
  outputs:
    dev:
      database: <database_name>
      role: <role_name>
      schema: <schema_name>
      warehouse: <warehouse_name>
      type: snowflake

The following examples illustrate how to use the snow dbt deploy command:

Warning

Don’t use --force unless you intentionally want to recreate the dbt project object. In snow dbt deploy, --force runs CREATE OR REPLACE DBT PROJECT, which removes all existing versions and run history.

  • Deploy a dbt project object named jaffle_shop:

    snow dbt deploy jaffle_shop

  • Deploy a project named jaffle_shop from a specified directory, using a profile file from a separate directory. The CLI looks for dbt_projects_profiles.yml first and uses profiles.yml only if dbt_projects_profiles.yml isn’t present. The CLI copies the file into the root of the deployed project object with the same filename, overwriting a file with the same name in this location:

    snow dbt deploy jaffle_shop --source /path/to/dbt/directory --profiles-dir ~/my_profiles/

  • Deploy a project named jaffle_shop from a specified directory, supplying a profile file from outside the project, setting a default target, pinning a dbt version, and enabling external access integrations:

    snow dbt deploy jaffle_shop --source /path/to/dbt/directory \
  --profiles-dir ~/my_profiles/ \
  --default-target prod \
  --dbt-version 1.11.11 \
  --external-access-integration dbthub-integration \
  --external-access-integration github-integration

  • Deploy a project named jaffle_shop and set a specific version for the dbt project object:

    snow dbt deploy jaffle_shop --dbt-version '1.11.11'

  • Deploy a project named jaffle_shop, pull in an env.yml file from a separate directory, and set the default environment for compilation and later executions:

    snow dbt deploy jaffle_shop --source /path/to/dbt/directory \
  --env-file-dir /path/to/env/directory \
  --default-env prod

    The --env-file-dir flag points the CLI at an env.yml file elsewhere in your repo (similar to --profiles-dir) and pulls it into the deployed object, overwriting the object’s root env.yml if one already exists. The --default-env flag sets the environment used for compilation and subsequent executions. These flags require Snowflake CLI 3.21 or later. For more information, see Using SQL environment variables and private Git packages for dbt Projects on Snowflake.

Listing all available dbt project objects

The snow dbt list command lists all available dbt project objects on Snowflake.

The following examples illustrate how to use the snow dbt list command:

  • List all available dbt project objects:

    snow dbt list

  • List dbt project objects in the product database whose names begin with JAFFLE:

    snow dbt list --like JAFFLE% --in database product

Executing a dbt project object command

The snow dbt execute command executes one of the following dbt commands on a Snowflake dbt project object:

For more information about using dbt commands, see the dbt Command reference.

The following examples illustrate how to use the snow dbt execute command:

  • Execute the dbt test command:

    snow dbt execute jaffle_shop test

  • Execute the run dbt command asynchronously:

    snow dbt execute --run-async jaffle_shop run

  • Execute the run dbt command with a specific dbt version:

    snow dbt execute --dbt-version '1.9.4' jaffle_shop run

  • Execute the run dbt command against a selected environment, overriding individual variables for this run:

    snow dbt execute --env staging \
  --env-vars '{"DBT_DATABASE": "tasty_bytes_staging_db"}' jaffle_shop run

    The --env flag selects the environment defined in the project’s env.yml file, and --env-vars applies inline key/value overrides for this execution. Use --use-shell-env-vars to pull DBT_-prefixed shell variables (excluding DBT_ENV_SECRET_* variables) into the run. These flags require Snowflake CLI 3.21 or later. For more information, see Use the Snowflake CLI.

Describing a dbt project object

The snow dbt describe command describes a dbt project object on Snowflake.

The following example describes the dbt project object named my_dbt_project on Snowflake:

snow dbt describe my_dbt_project

Dropping a dbt project object

The snow dbt drop command deletes a dbt project object on Snowflake.

The following example deletes the dbt project object named my_dbt_project on Snowflake:

snow dbt drop my_dbt_project

Use snow dbt commands in a CI/CD workflow

Note

When building CI/CD workflows, you only need your Git server, such as Github, and Snowflake CLI. A Git repository object is not required.

You can run dbt commands with Snowflake CLI to build CI/CD pipelines. These pipelines are commonly used to test new code, such as new pull requests, or to update production applications whenever something is merged to the main branch.

To build a CI/CD workflow with snow dbt commands, follow these steps:

  1. Prepare your dbt project:

    1. Download your dbt project or start a new one.

      • Ensure that the main project directory contains dbt_project.yml and either dbt_projects_profiles.yml or profiles.yml.

      • Verify that the profile name referenced in dbt_project.yml is defined in dbt_projects_profiles.yml or profiles.yml. If both files are present, Snowflake uses dbt_projects_profiles.yml.

        Note

        Ensure that credentials are excluded from the profile file. Leave account and user as placeholder strings and let your CI/CD platform supply the connection through secrets or environment variables.

  2. Set up Snowflake CLI GitHub Action.

    Follow the guidelines for setting up GitHub Action for Snowflake CLI and verify your connection to Snowflake.

  3. Define your workflow.

    Determine which commands your workflow needs to run based on your organization’s needs. The following example illustrates a CI workflow that updates the version of the dbt project object named product_pipeline with new files, runs the transformations, and finally runs tests:

    - name: Execute Snowflake CLI command
  run: |
 snow dbt deploy product_pipeline
 snow dbt execute product_pipeline run
 snow dbt execute product_pipeline test