CREATE DBT PROJECT¶

Creates a new dbt project object or replaces an existing dbt project. Running CREATE DBT PROJECT with the OR REPLACE option resets the version identifier to version$1 and removes all version name aliases. For more information, see Versioning for dbt project objects and files.

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

Syntax¶

CREATE [ OR REPLACE ] DBT PROJECT [ IF NOT EXISTS ] <name>
  [ FROM '<source_location>' ]
  [ DEFAULT_ARGS = '<string_literal>']
  [ DEFAULT_VERSION = { FIRST | LAST | VERSION$<num> | '<version_name_alias>' } ]
  [ COMMENT = '<string_literal>' ]

Copy

Required parameters¶

name

String that specifies the identifier (that is, the name) for the dbt project object within Snowflake; must be unique for the schema in which the dbt project is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example, "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

Optional parameters¶

FROM 'source_location'

A string that specifies the location in Snowflake of the source files for the dbt project object. This can be a parent directory that contains multiple dbt projects, or a specific subdirectory that contains a dbt project and dbt_project.yml file.

If the specified location doesn’t contain a dbt_project.yml file, the EXECUTE DBT PROJECT command must use the PROJECT_ROOT parameter to specify the subdirectory path to a dbt_project.yml file.

If no value is specified, Snowflake creates an empty dbt project.

The dbt project source files can be in any one of the following locations:

A Git repository stage, for example:

'@my_db.my_schema.my_git_repository_stage/branches/my_branch/path/to/dbt_project_or_projects_parent'

For more information about creating a Git repository object in Snowflake that connects a Git repository to a workspace for dbt Projects on Snowflake, see Create a workspace connected to your Git repository. For more information about creating and managing a Git repository object and stage without using a workspace, see Using a Git repository in Snowflake and CREATE GIT REPOSITORY.

An existing dbt project stage, for example:

'snow://dbt/my_db.my_schema.my_existing_dbt_project_object/versions/last'

The version specifier is required and can be last (as shown in the previous example), first, or the specifier for any existing version in the form version$<num>. For more information, see Versioning for dbt project objects and files.

An internal named stage, for example:

'@my_db.my_schema.my_internal_named_stage/path/to/dbt_projects_or_projects_parent'

Internal user stages and table stages aren’t supported.

A workspace for dbt on Snowflake, for example:

'snow://workspace/user$.public."my_workspace_name"/versions/live/path/to/dbt_projects_or_projects_parent'

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

The version specifier is required and can be last, first, live, or the specifier for any existing version in the form version$<num>. For more information, see Versioning for dbt project objects and files.

Default: No value

DEFAULT_ARGS = 'string_literal'

A string that specifies the default dbt command (https://docs.getdbt.com/reference/dbt-commands) and command line options (https://docs.getdbt.com/reference/global-configs/about-global-configs#available-flags) to use if EXECUTE DBT PROJECT specifies no command.

Important

/INCLUDE/text/dbt-sql-arguments-overwrite.txt

Default: No value

DEFAULT_VERSION = { FIRST | LAST | VERSION$num | 'version_name_alias' }

Specifies the default version of the dbt project object to use if EXECUTE DBT PROJECT doesn’t specify a version. For more information, see Versioning for dbt project objects and files.

Default: No value

COMMENT = 'string_literal'

Specifies a comment for the dbt project object.

Default: No value

Access control requirements¶

A role used to execute this operation must have the following privileges at a minimum:

Privilege	Object
CREATE DBT PROJECT	Schema

The USAGE privilege on the parent database and schema are required to perform operations on any object in a 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.

Usage notes¶

The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.
CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

Regarding metadata:

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

Examples¶

Create a dbt project object from a Git repository stage in Snowflake
Create a dbt project object from a subdirectory within a Git repository stage in Snowflake
Create a dbt project object from a specific version of an existing dbt project object
Create a dbt project object from a workspace that contains multiple dbt projects

Create a dbt project object from a Git repository stage in Snowflake¶

Create a dbt project object named sales_dbt_model from dbt project files in a Git repository stage. The command specifies the main branch of a Git repository stage named sales_dbt_git_stage in Snowflake, where the dbt_project.yml is saved in the repository root.

CREATE DBT PROJECT sales_db.dbt_projects_schema.sales_model
  FROM '@sales_db.integrations_schema.sales_dbt_git_stage/branches/main'
  COMMENT = 'generates sales data models';

Copy

Create a dbt project object from a subdirectory within a Git repository stage in Snowflake¶

Create a dbt project object named sw_region_dbt_project from a Git repository stage that contains multiple dbt projects. The command specifies the main branch of a Git repository stage named sales_dbt_git_stage in Snowflake, where the dbt_project.yml is saved in the sw_region_dbt_project sub-directory of the sales_dbt_projects_parent directory.

CREATE DBT PROJECT sales_db.dbt_projects_schema.sw_region_sales_model
  FROM '@sales_db.integrations_schema.sales_dbt_git_stage/branches/main/sales_dbt_projects_parent/sw_region_dbt_project'
  COMMENT = 'generates data models for sw sales region';

Copy

Create a dbt project object from a specific version of an existing dbt project object¶

Create a new dbt project object named sales_nw_dbt_model_combined_bookings from version$2 of the sales_dbt_projects dbt object stage. The DEFAULT_ARGS parameter specifies the --select command line option so that only models with the tag nw_region will run with EXECUTE DBT PROJECT.

CREATE DBT PROJECT sales_db.dbt_projects_schema.sales_model_nw_region
  FROM 'snow://dbt/sales_db.dbt_projects_schema.sales_model/versions/version$2'
  DEFAULT_ARGS = '--select "tag:nw_region"'
  COMMENT = 'generates data models for the NW sales region';

Copy

Create a dbt project object from a workspace that contains multiple dbt projects¶

Create a new dbt project object named sales_model_from_workspace from the live version of a workspace for dbt Projects on Snowflake. This is particularly useful for creating a dbt project object from a parent workspace that contains multiple dbt project subdirectories. We recommend enclosing the workspace name in double quotes because workspace names are case-sensitive and can contain special characters.

-- Create a dbt project object from a workspace named "My dbt Project Workspace" in the user's personal database.

CREATE DBT PROJECT sales_db.dbt_projects_schema.sales_model_from_workspace
  FROM 'snow://workspace/user$.public."My dbt Project Workspace"/versions/live'

-- Execute the dbt project, specifying a subdirectory path for a dbt project within the workspace

EXECUTE DBT PROJECT sales_db.dbt_projects_schema.sales_model_from_workspace
  PROJECT_ROOT = 'project2'
  ARGS = 'run --target prod';

Copy