EXECUTE DBT PROJECT¶

Executes the specified dbt project object or the dbt project in a Snowflake workspace using the dbt command and command-line options specified.

See also:: CREATE DBT PROJECT, ALTER DBT PROJECT, DESCRIBE DBT PROJECT, DROP DBT PROJECT, SHOW DBT PROJECTS

Syntax¶

Executes the dbt project object with the specified name.

EXECUTE DBT PROJECT [ IF EXISTS ] <name>
  [ ARGS = '[ <dbt_command> ] [ --<dbt_cli_option> <option_value_1> [ ... ] ] [ ... ]' ]
  [ DBT_VERSION = 'version_number' ]
  [ EXTERNAL_ACCESS_INTEGRATIONS = ( <integration_name> [ , ... ] ) ]
  [ ENVIRONMENT = '<environment_name>' ]
  [ ENV_VARS = ( '<key>' = '<value>' [ , ... ] ) ]

Variant syntax¶

Executes the dbt project that is saved in a workspace with the specified workspace name. The user who owns the workspace must be the user who runs this command variant.

EXECUTE DBT PROJECT [ IF EXISTS ] [ FROM WORKSPACE <name> ]
  [ ARGS = '[ <dbt_command> ] [ --<dbt_cli_option> <option_value_1> [ ... ] [ ... ] ]' ]
  [ DBT_VERSION = 'version_number' ]
  [ EXTERNAL_ACCESS_INTEGRATIONS = ( <integration_name> [ , ... ] ) ]
  [ ENVIRONMENT = '<environment_name>' ]
  [ ENV_VARS = ( '<key>' = '<value>' [ , ... ] ) ]
  [ PROJECT_ROOT = '<subdirectory_path>' ]

Required parameters¶

name

When executing a dbt project object, specifies the name of the dbt project object to execute.

When executing a dbt project by using the FROM WORKSPACE option, specifies the name of the workspace for dbt Projects on Snowflake. The workspace name is always specified in reference to the public schema in the user’s personal database, which is indicated by user$.

We recommend enclosing the workspace name in double quotes because workspace names are case-sensitive and can contain special characters.

The following example shows a workspace name reference:

user$.public."My dbt Project Workspace"

Optional parameters¶

ARGS = '[ dbt_command ] [ --dbt_cli_option option_value_1 [ ... ] [ ... ] ]'

Specifies the dbt command and supported command-line options to use when the dbt project object executes. This is a literal string that must conform to the syntax and requirements of dbt CLI commands.

If no value is specified, the dbt project object executes with the dbt command and command-line options specified in the dbt project object definition. If you specify dbt CLI options without specifying a dbt command, the dbt run command executes by default.

Default: No value

DBT_VERSION = 'version_number'

Specifies a version for the dbt project object.

Default: When you execute a dbt project object, the system uses the default version you specified when creating the dbt project object. If none was specified, the system uses 1.9.4 by default.

For more information, see Supported dbt versions for dbt Projects on Snowflake.

PROJECT_ROOT = 'subdirectory_path'

Specifies the subdirectory path to the dbt_project.yml file within the dbt project object or workspace. This parameter is only supported when executing a dbt project by using the FROM WORKSPACE option.

If no value is specified, the dbt project object executes with the dbt_project.yml file in the root directory of the dbt project object.

If no dbt_project.yml file exists in the root directory or in the PROJECT_ROOT subdirectory, an error occurs.

Default: No value

EXTERNAL_ACCESS_INTEGRATIONS = ( integration_name [ , ... ] )

Specifies the external access integration that grants dbt outbound network access to external endpoints. Most commonly, this lets dbt pull remote packages from the dbt package hub or a Git provider such as GitHub when dbt deps runs during execution.

If a command needs external access during execution (for example, dbt deps), specify EXTERNAL_ACCESS_INTEGRATIONS on the EXECUTE DBT PROJECT command. This also applies when your project resolves a Snowflake secret from an env.yml file to authenticate private Git packages. For more information, see Using SQL environment variables and private Git packages for dbt Projects on Snowflake.

For more information, see Understand dependencies for dbt Projects on Snowflake.

ENVIRONMENT = 'environment_name'

Selects the named environment defined in the project’s env.yml file for this execution. The environment determines which set of environment variables and secrets is injected before dbt runs.

This argument takes the highest precedence for environment selection, overriding the DEFAULT_ENVIRONMENT set on the dbt project object and the default_environment in env.yml. To run without any environment, use the reserved name NO_ENV.

Default: The dbt project object’s DEFAULT_ENVIRONMENT, or the default_environment: in env.yml if DEFAULT_ENVIRONMENT isn’t set.

For more information, see Using SQL environment variables and private Git packages for dbt Projects on Snowflake.

ENV_VARS = ( 'key' = 'value' [ , ... ] )

Overrides individual environment variables for a single execution. These overrides merge into the selected environment and take final precedence over values in env.yml.

Keys must be prefixed with DBT_ and uppercase. Values can be SQL that resolves to a single VARCHAR value, string literals, session variables ($var), or bind placeholders (?). Snowflake secrets can’t be referenced directly in ENV_VARS; manage secrets through the env.yml file instead.

Default: No value

For more information, see Using SQL environment variables and private Git packages for dbt Projects on Snowflake.

Output¶

Column	Description
`0\|1 Success`	`TRUE` if the dbt project object executed successfully; otherwise, `FALSE`. If the dbt project object fails to execute, an exception message is returned.
`EXCEPTION`	Any exception message returned by the dbt project execution. If the dbt project object executes successfully, the string `None` is returned.
`STDOUT`	The standard output returned by the dbt project execution.
`OUTPUT_ARCHIVE_URL`	The URL of the output archive that contains output files of the dbt project execution. This includes log files and artifacts that dbt writes to the `/target` directory. For more information, see About dbt artifacts in dbt documentation. Selecting this link directly results in an error; however, you can use this URL to retrieve dbt project files and output. For more information, see Access dbt artifacts and logs programmatically.

Access control requirements¶

A role used to execute this SQL command must have at least one of the following privileges at a minimum:

Privilege	Object
USAGE	dbt project

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Note

The dbt command specified in EXECUTE DBT PROJECT runs with the privileges of the role specified in the outputs block of the projects profiles.yml file. Operations are further restricted to only those privileges granted to the Snowflake user calling EXECUTE DBT PROJECT. Both the user and the role specified must have the required privileges to use the warehouse, perform operations on the database and schema specified in the project’s profiles.yml file, and perform operations on any other Snowflake objects that the dbt model specifies.

Examples¶

Default run command with target and models specified
Explicit test command with target and models specified
Explicit run command with downstream models specified
Execute and test dbt project objects using production tasks

Default run command with target and models specified¶

Execute a dbt run targeting the dev profile in the dbt_project.yml file in the root directory of the dbt project object and selecting three models from the project DAG. No run command is explicitly specified and is executed by default.

EXECUTE DBT PROJECT my_database.my_schema.my_dbt_project
  ARGS = '--select simple_customers combined_bookings prepped_data --target dev';

Explicit test command with target and models specified¶

Execute a dbt test command targeting the prod profile in the dbt_project.yml file in the root directory of the dbt project object and selecting three models from the project DAG.

EXECUTE DBT PROJECT my_database.my_schema.my_dbt_project
  ARGS = '--select simple_customers combined_bookings prepped_data --target prod';

Explicit run command with downstream models specified¶

Execute a dbt run command targeting the dev profile in the dbt_project.yml file and selecting all models downstream of the simple_customers model using the dbt + notation.

EXECUTE DBT PROJECT my_database.my_schema.my_dbt_project
  ARGS = 'run --select simple_customers+ --target dev';

Execute and test dbt project objects using production tasks¶

Create a task for a production dbt target that executes a dbt run command on a six-hour interval. Then create a task that executes the dbt test command after each dbt run task completes. The EXECUTE DBT PROJECT command for each task targets the prod profile in the dbt_project.yml file in the root directory of the dbt project object.

CREATE OR ALTER TASK my_database.my_schema.run_dbt_project
  WAREHOUSE = my_warehouse
  SCHEDULE = '6 hours'
AS
  EXECUTE DBT PROJECT my_database.my_schema.my_dbt_project args='run --target prod';


CREATE OR ALTER TASK change_this.public.test_dbt_project
        WAREHOUSE = my_warehouse
        AFTER run_dbt_project
AS
  EXECUTE DBT PROJECT my_database.my_schema.my_dbt_project args='test --target prod';

Override the project’s pinned version at execution time for testing or temporary needs¶

my_dbt_project is pinned to 1.9.4. This execution overrides the dbt project object’s default 1.9.4 version:

EXECUTE DBT PROJECT finance_analytics
  DBT_VERSION = '1.11.11'

Select an environment and override variables at execution time¶

Run against the prod environment defined in env.yml, overriding a single variable for this execution:

EXECUTE DBT PROJECT my_database.my_schema.my_dbt_project
  ARGS = 'run --target prod'
  ENVIRONMENT = 'prod'
  ENV_VARS = ( 'DBT_DATABASE' = 'tasty_bytes_staging_db' );

For more information, see Using SQL environment variables and private Git packages for dbt Projects on Snowflake.