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>' [ , ... ] ) ]
  [ IMPORTS = ( { '<source_location>' | <system_function> } [ AS '<alias>' ] [ , ... ] ) ]
  [ WRITEBACK = { TRUE | FALSE } ]

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.

IMPORTS = ( { 'source_location' | system_function } [ AS 'alias' ] [ , ... ] )

When executing a deployed dbt project object, makes files from one or more source locations available under the project’s ./imports directory. You can import from a stage, a deployed dbt project object, a workspace, or one of these system functions:

• SYSTEM$DBT_GET_LAST_SUCCESSFUL_RUN_TARGET
• SYSTEM$DBT_GET_LAST_FAILED_RUN_TARGET
• SYSTEM$DBT_GET_LAST_RUN_TARGET
• SYSTEM$LOCATE_DBT_ARTIFACTS
• SYSTEM$LOCATE_DBT_ARCHIVE

Other system functions aren’t supported in IMPORTS.

Use AS '<alias>' to name the subdirectory for an import. For example, AS 'state' mounts the imported files at ./imports/state. The --state option in ARGS must point to the mounted directory. IMPORTS alone doesn’t tell dbt to use the files as state. For a complete example, see Run changed models by importing state from a production project.

Without an alias, Snowflake derives a lowercase directory name in the form {lowercased object name}_{lowercased last folder name}. For example, @my_dbt_project/target/ mounts at ./imports/my_dbt_project_target, while a target directory from a dbt project object named DBT_PROD mounts at ./imports/dbt_prod_target. Use an alias when you want a shorter, more predictable path.

An IMPORTS list can include at most one ZIP file. To import it, use the SYSTEM$LOCATE_DBT_ARCHIVE system function, which returns the archive from a dbt project object’s results stage. ZIP files from other source locations aren’t supported. Snowflake extracts the archive automatically.

For dbt artifacts from the most recent successful execution of a dbt project object, use SYSTEM$DBT_GET_LAST_SUCCESSFUL_RUN_TARGET. To import dbt artifacts from a specific query, use SYSTEM$LOCATE_DBT_ARTIFACTS. This function mounts the query’s results directory, so the dbt artifacts are under ./imports/<alias>/target.

Default: No value

WRITEBACK = { TRUE | FALSE }

When executing a deployed dbt project object, overrides its DEFAULT_WRITEBACK property for this execution:

• TRUE: Writes generated target and log files back to the mutable live version.
• FALSE: Doesn’t write generated target and log files back to the live version.

For concurrent executions that don’t need to update the live version, Snowflake recommends WRITEBACK = FALSE. If writeback is required, use distinct, non-overlapping target and log directories for each execution. Snowflake stores the per-query result artifacts and archive regardless of this setting.

Default: The dbt project object’s DEFAULT_WRITEBACK value.

Output¶

Access control requirements¶

Examples¶

• Default run command with target and models specified
• Explicit test command with target and models specified
• Explicit run command with downstream models specified
• Run changed models by importing state from a production project
• Run changed models by importing state from a specific query
• Run a dbt project object concurrently
• 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';

Run changed models by importing state from a production project¶

Import dbt artifacts from the most recent successful execution of a production dbt project object. The alias state mounts the returned artifacts at ./imports/state. The dbt arguments compare the current project with those artifacts, select changed nodes and their downstream dependencies, and defer references for unbuilt nodes:

EXECUTE DBT PROJECT ci_database.dbt_projects.pr_test_project
  ARGS = 'run --state ./imports/state --defer --select state:modified+'
  IMPORTS = (
    SYSTEM$DBT_GET_LAST_SUCCESSFUL_RUN_TARGET(
      'prod_database.dbt_projects.production_project'
    ) AS 'state'
  );

The role that executes this statement must have the MONITOR privilege on prod_database.dbt_projects.production_project. That object must have a successful execution from the previous 7 days. For details, see SYSTEM$DBT_GET_LAST_SUCCESSFUL_RUN_TARGET.

Run changed models by importing state from a specific query¶

Use SYSTEM$LOCATE_DBT_ARTIFACTS to import the results directory from a specific dbt project execution. The state alias mounts the directory at ./imports/state, and the dbt artifacts are in its target subdirectory:

EXECUTE DBT PROJECT ci_database.dbt_projects.pr_test_project
  ARGS = 'run --state ./imports/state/target --defer --select state:modified+'
  IMPORTS = (
    SYSTEM$LOCATE_DBT_ARTIFACTS(
      '01c6a772-001e-db19-0000-5349650b6cfe'
    ) AS 'state'
  );

For requirements and query ID lookup examples, see SYSTEM$LOCATE_DBT_ARTIFACTS.

Import a ZIP archive from a results stage¶

SYSTEM$LOCATE_DBT_ARCHIVE is the supported way to import a ZIP file. It returns an archive from a dbt project object’s results stage. An IMPORTS list can include at most one ZIP file. ZIP files from other source locations aren’t supported. Snowflake extracts the archive automatically:

EXECUTE DBT PROJECT ci_database.dbt_projects.pr_test_project
  ARGS = 'run --state ./imports/archive --defer --select state:modified+'
  IMPORTS = (
    SYSTEM$LOCATE_DBT_ARCHIVE(
      '01c6a772-001e-db19-0000-5349650b6cfe'
    ) AS 'archive'
  );

To let Snowflake generate the mount directory name, omit AS '<alias>'. For example, if the query ID identifies an execution of a dbt project object named PROD_PROJECT, Snowflake extracts the archive under ./imports/prod_project_dbt_artifacts/:

EXECUTE DBT PROJECT ci_database.dbt_projects.pr_test_project
  ARGS = 'run --state ./imports/prod_project_dbt_artifacts/target --defer --select state:modified+'
  IMPORTS = (
    SYSTEM$LOCATE_DBT_ARCHIVE('<query_id>')
  );

For requirements, see SYSTEM$LOCATE_DBT_ARCHIVE.

Run a dbt project object concurrently¶

For concurrent executions of the same dbt project object, Snowflake recommends WRITEBACK = FALSE when the executions don’t need to persist target and log artifacts to the live version. This prevents the executions from writing artifacts to overlapping target and log directories. Snowflake continues to store per-query result artifacts.

Use WRITEBACK = FALSE for each concurrent execution:

EXECUTE DBT PROJECT my_database.my_schema.my_dbt_project
  ARGS = 'run --target prod'
  WRITEBACK = FALSE;

For an alternative that writes to distinct target and log directories, see Run a dbt project object concurrently.

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.