Managing dbt projects in Snowflake CLI¶

Install Snowflake CLI with dbt on Snowflake features¶

To install the Private Preview version of Snowflake CLI that contains the snow dbt commands, you need to install Snowflake CLI version 3.9.0 or newer. For more information, see Installing Snowflake CLI.

Enable the dbt on Snowflake feature in Snowflake CLI¶

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

  • Set the SNOWFLAKE_CLI_FEATURES_ENABLE_DBT environment variable, as shown:

    export SNOWFLAKE_CLI_FEATURES_ENABLE_DBT=true
    Copy

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

    [cli.features]
enable_dbt = true
    Copy

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

    snow --help
    Copy

    You should see dbt in the list of commands, as shown:

    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

snow dbt deploy command¶

The snow dbt deploy command uploads local files to a temporary stage and creates, updates by making a new version, or completely recreates a dbt object. 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 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 object.

  • [--source <path]> is the path where the dbt project is stored. 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] overwrites if a dbt project with the same name already exists,

Examples¶

  • Deploy a dbt project named jaffle_shop:

    snow dbt deploy jaffle_shop
    Copy

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

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

snow dbt list command¶

The snow dbt list command lists all available dbt 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 objects:

    snow dbt list
    Copy

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

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

snow dbt execute command¶

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

  • build

  • compile

  • deps

  • list

  • parse

  • run

  • run-operation

  • seed

  • show

  • snapshot

  • test

Usage¶

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

where:

  • NAME is the name of the dbt 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 or

    • Any global 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. SnowGit 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 mainline.

To build a CI/CD workflow with snow dbt commands, do the following:

  1. Prepare your dbt project

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

    2. Ensure the main project directory contains the dbt_project.yml and profiles.yml files.

    3. Check that the profile name referenced in dbt_project.yml is defined in profiles.yml.

      Note

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

  2. Setup 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 dbt object 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