Managing dbt Projects on Snowflake using Snowflake CLI

Install Snowflake CLI with dbt Projects on Snowflake features

To install the Public Preview version of Snowflake CLI that contains the snow dbt commands, you must first install Snowflake CLI version 3.9.0 or later. For more information, see Installing Snowflake CLI.

Enable the dbt Projects on Snowflake feature in Snowflake CLI

During the preview, you must enable the new snow dbt commands using either of the following methods:

  • Set the SNOWFLAKE_CLI_FEATURES_ENABLE_DBT environment variable:

    export SNOWFLAKE_CLI_FEATURES_ENABLE_DBT=true
    Copy

  • Enable the commands in your config.toml configuration files:

    [cli.features]
enable_dbt = true
    Copy

  • To verify that the commands are available, run the snow --help command:

     snow --help

dbt should appear in the list of commands, as shown:
    Copy
    Usage: snow [OPTIONS] COMMAND [ARGS]...

Snowflake CLI tool for developers [v3.9.0]

...

╭─ Commands ──────────────────────────────────────────
│ app          Manages a Snowflake Native App        │
│ connection   Manages connections to Snowflake.     │
│ cortex       Provides access to Snowflake Cortex   │
│ dbt          Manages dbt on Snowflake projects     │
│ ...                                                │
╰─────────────────────────────────────────────────────

Connection management

Snowflake CLI requires a working connection to interact with Snowflake. For information about managing connections, see Managing Snowflake connections.

Snowflake CLI commands that support dbt projects

Snowflake CLI provides the following commands for dbt projects:

  • snow dbt deploy

  • snow dbt list

  • snow dbt execute

Deploy a dbt project object

The snow dbt deploy command uploads local files to a temporary stage and creates a new dbt project object, updates it by making a new version, or completely recreates it. A valid dbt project must contain two files:

  • dbt_project.yml: Standard dbt configuration file, which must specify the profile to use.

  • profiles.yml: A dbt connection profile definition referenced in dbt_project.yml. profiles.yaml must define the database, role, warehouse, and schema. You can leave the remaining values empty, as in the following example:

    <profile_name>:
outputs:
  dev:
    account: ''
    database: <database_name>
    role: <role_name>
    schema: <schema_name>
    type: snowflake
    user: ''
    warehouse: <warehouse_name>
target: dev
    Copy

Usage

snow dbt deploy [--source <path>] [--profiles-dir <path>] [--force] NAME
Copy

where:

  • NAME is the name of the dbt project object.

  • [--source <path]> is the path to the project’s dbt_project.yml:file:`` file. Defaults to the current working directory.

  • [--profiles-dir <path]> is the path of the profiles.yml file. If not specified, defaults to the path resolved as --source

  • [--force] Creates a new dbt project object or updates and adds a new version to an existing one.

Examples

  • Deploy a dbt project object named jaffle_shop:

    snow dbt deploy jaffle_shop
    Copy

  • Deploy a project named jaffle_shop from a specified directory and overwrite the dbt project object if it already exists:

    snow dbt deploy jaffle_shop --force --source /path/to/dbt/directory --profiles-dir ~/.dbt/
    Copy

List all available dbt project objects

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

Usage

snow dbt list [--like <string>] [--in <string>]
Copy

where:

  • [--like string] is a SQL LIKE pattern for filtering by object name.

  • [--in string] specifies the scope of this command.

Examples

  • List all available dbt project objects:

    snow dbt list
    Copy

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

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

Execute a dbt project object command

The snow dbt execute command executes one of the following dbt commands <https://docs.getdbt.com/reference/dbt-commands>`_ on a Snowflake dbt project object:

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

Usage

snow dbt execute [snowflake-cli-options] NAME [dbt-command-and-options]
Copy

where:

  • NAME is the name of the dbt project object.

  • [snowflake-cli-options] is a list of Snowflake CLI options, including the following:

    • --run-async, which runs the dbt command asynchronously and immediately returns a query you can use to track its progress

    • Any global Snowflake CLI option, such as --help or --connection

  • [dbt-command-and-options] is the name of a dbt command and its options, such as run --target dev.

Examples

  • Execute the dbt test command:

    snow dbt execute jaffle_shop test
    Copy

  • Execute the run dbt command asynchronously:

    snow dbt execute --run-async jaffle_shop run --select @source:snowplow,tag:nightly models/export
    Copy

Using 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:

    #. Download your dbt project or start a new one. - Ensure that the main project directory contains the dbt_project.yml and profiles.yml files. - Verify that the profile name referenced in dbt_project.yml is defined in profiles.yml.

    Note

    Snowflake’s dbt project objects don’t need passwords, so if profiles.yml contains any, deployment stops until they are removed.

  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
    Copy
Language: English