EXECUTE DCM PROJECT¶

Executes one of the following actions on a DCM project:

EXECUTE DCM PROJECT <name> PLAN performs a dry run of the DCM project to analyze the changes that would be applied to the target during a deployment, but doesn’t apply any changes.
EXECUTE DCM PROJECT <name> DEPLOY deploys the changes defined in the project’s definition files to the account.
EXECUTE DCM PROJECT <name> REFRESH ALL refreshes dynamic tables managed by the DCM project.
EXECUTE DCM PROJECT <name> TEST ALL tests all expectations from attached data metric functions managed by the DCM project.
EXECUTE DCM PROJECT <name> PREVIEW returns a data sample of the current definitions specified in the source path for the specified table, view, or dynamic table.
EXECUTE DCM PROJECT <name> PURGE drops all entities, revokes all grants, and removes all attachments currently managed by the DCM project.

See also:: CREATE DCM PROJECT, ALTER DCM PROJECT, DESCRIBE DCM PROJECT, DROP DCM PROJECT, SHOW DCM PROJECTS, SHOW DEPLOYMENTS IN DCM PROJECT

语法

EXECUTE DCM PROJECT <name>
  PLAN
  [ USING [ CONFIGURATION <config_name> ] [ (<expr>, [, <expr>, ...]) ] ]
  FROM '<source-files_path>'

EXECUTE DCM PROJECT <name>
  DEPLOY [ AS "<deployment_name_alias>" ]
  [ USING [ CONFIGURATION <name> ] [ (<expr>, [, <expr>, ...]) ] ]
  FROM '<source-files_path>'

EXECUTE DCM PROJECT <name>
  REFRESH ALL

EXECUTE DCM PROJECT <name>
  TEST ALL

EXECUTE DCM PROJECT <name>
  PREVIEW <fully_qualified_table_object_name>
  USING CONFIGURATION <config_name>
  FROM '<source_files_path>'

EXECUTE DCM PROJECT <name>
  PURGE [ AS "<deployment_name_alias>" ]

必填参数

name

指定要执行的 DCM 项目的标识符。

If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

PLAN

Instructs Snowflake to perform a dry run of the DCM project. For a dry run, Snowflake analyzes the changes that would be applied to the target during a deployment, but doesn’t apply any changes.

DEPLOY [ AS "deployment_name_alias" ]

将项目定义文件中定义的变更部署到账户中；可选择为部署指定别名。

FROM 'source_files_path'

Specifies the directory that contains the source files for the DCM project. The directory must contain a manifest file and at least one definition file in /sources/definitions/. The manifest file provides the templating values in case a configuration was specified.

REFRESH ALL

Refreshes all dynamic tables that are currently managed by the DCM project.

TEST ALL

Tests all data quality expectations attached to tables, dynamic tables, or views which are currently managed by the DCM project.

PREVIEW fully_qualified_table_object_name

返回为指定表、视图或动态表在源路径中定义的当前定义的数据样本 – 与任何已部署状态无关。

PURGE [ AS "deployment_name_alias" ]

Drops all entities, revokes all grants, and removes all attachments currently managed by the DCM project, by running a deployment with no definitions. Optionally, specify an alias for the deployment.

Warning

PURGE is destructive by design. Use it with caution and only for non-production projects, such as development sandboxes or demos.

PURGE doesn’t drop the DCM project itself. To remove the project object after purging, run DROP DCM PROJECT. The purge execution appears in the deployment history of the DCM project, where you can confirm that the command ran successfully.

可选参数

USING CONFIGURATION config_name

Specifies the configuration to use. This lets you customize deployments for different environments, such as development, staging, or production, without using different project definition files.

如果配置名称不是全大写，请将其用双引号引起来。

USING ( expr [, expr , ... ] )

Optionally specifies template variable values. Using this option overrides any default or configuration values for this specific variable. The single expression must have the following form: <variable_name> => <variable_value>. For lists, use the following form: <variable_name> => [<value1>, <value2>, ...]. For example: wh_size => 'MEDIUM' or teams => ['TEAM_A', 'TEAM_B'].

这使您能够为不同环境（如开发、预发布或生产环境）自定义部署，而无需使用不同的项目定义文件。

访问控制要求

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

权限	对象	备注
OWNERSHIP	DCM 项目	OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the [GRANT OWNERSHIP](/sql-reference/sql/grant-ownership) command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

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.

输出

After the DCM project executes, this command returns the following output depending on the variation:

PLAN, DEPLOY, and PURGE: A single row containing a JSON object with the change log. Because PURGE runs as an empty deployment, its output is identical to DEPLOY, with all managed entities reported as DROP.
PREVIEW：选择使用时默认使用的角色和仓库。结果集。
REFRESH ALL：包含完整响应 JSON 对象的单行数据。
TEST ALL：包含完整响应 JSON 对象的单行数据。

PLAN 和 DEPLOY 输出¶

Note

在预览阶段，确切的输出格式可能会发生相应变化。

标准计划输出包含以下有关计划执行的 JSON 格式信息：

{
  "version": 2,
  "metadata": {
    "timestamp": <timestamp>,
    "query_id": <query_id>,
    "project_name": <project_name>,
    "user": <user>,
    "role_name": <role_name>,
    "command": <command>
  },
  "changeset": [
    {
      "type": <type>,
      "object_id": {
        "domain": <domain>,
        "name": <name>,
        "fqn": <fqn>,
        "database": <database>,
        "schema": <schema>
      },
      "changes": [
        {
          "kind": <kind>,
          "attribute_name": <attribute_name>,
          "value": <value>,
          "changes": [
            {
              "kind": <kind>,
              "attribute_name": <attribute_name>,
              "value": <value>
            }
          ]
        }
      ]
    }
  ]
}

属性	描述
`version`	输出格式的架构版本。版本 2 是最新且唯一受支持的版本。
`metadata`	有关执行的上下文信息。
`metadata.timestamp`	执行命令时的 ISO 8601 时间戳。
`metadata.query_id`	生成此计划的查询的唯一标识符。
`metadata.project_name`	DCM 项目对象的完全限定名称。
`metadata.user`	执行命令的用户的名称。
`metadata.role_name`	用于执行命令的活动角色。
`metadata.command`	The command that was executed. `PLAN` or `DEPLOY`.
`changeset`	变更条目数组。每个条目代表一个将要创建、更改或删除的对象。空数组表示项目定义已与账户同步。
`changeset[].type`	The planned action for the object. Possible values: `CREATE`, `ALTER`, `DROP`.
`changeset[].object_id`	标识目标对象。
`changeset[].object_id.domain`	Snowflake 对象类型。
`changeset[].object_id.name`	对象名称。
`changeset[].object_id.fqn`	对象的完全限定名称。
`changeset[].object_id.database`	包含对象的数据库。为账户级对象省略。
`changeset[].object_id.schema`	包含对象的架构。对于数据库级和账户级对象省略。
`changeset[].changes`	详细说明特定属性修改的变更描述符数组。
`changeset[].changes[].kind`	The type of change. Possible values: `set`, `changed`, `unset`, `nested`, `collection`. The value of `kind` determines the remaining keys in the object.
`changeset[].changes[].attribute_name`	Name of the attribute being set or changed. Present when `kind` is `set`, `changed`, or `unset`.
`changeset[].changes[].value`	The new value for the attribute. Present when `kind` is `set` or `changed`.
`changeset[].changes[].prev_value`	The previous value of the attribute before the change. Present only when `kind` is `changed`.
`changeset[].changes[].collection_name`	Name of the collection being modified (for example, `columns`, `constraints`, `privileges`, `expectations`). Present only when `kind` is `collection`.
`changeset[].changes[].id_label`	Label used to identify items within the collection (for example, `name`). Present only on certain collections.
`changeset[].changes[].changes`	A nested array of collection item descriptors. Present only when `kind` is `collection`.
`changeset[].changes[].changes[].kind`	The type of change to the collection item. Possible values: `added`, `removed`, `modified`.
`changeset[].changes[].changes[].item_id`	标识集合中的项目。可以是字符串或对象，具体取决于集合类型。
`changeset[].changes[].changes[].changes`	An array of further change descriptors for this item. Present for `added` and `modified` items. Always absent for `removed` items.

计划输出的示例：

{
  "version": 2,
  "metadata": {
    "timestamp": <timestamp>,
    "query_id": <query_id>,
    "project_name": <project_name>,
    "user": <user>,
    "role_name": <role_name>,
    "command": <command>
  },
  "changeset": [
    {
      "type": "CREATE",
      "object_id": {
        "domain": "TABLE",
        "name": "CUSTOMER_SUMMARY",
        "fqn": "MY_DB.ANALYTICS.CUSTOMER_SUMMARY",
        "database": "MY_DB",
        "schema": "ANALYTICS"
      },
      "changes": [
        {
          "kind": "set",
          "attribute_name": "warehouse_size",
          "value": "XSMALL"
        },
        {
          "kind": "set",
          "attribute_name": "query",
          "value": "SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id"
        }
      ]
    },
    {
      "type": "ALTER",
      "object_id": {
        "domain": "DYNAMIC_TABLE",
        "name": "ORDER_DETAILS",
        "fqn": "MY_DB.ANALYTICS.ORDER_DETAILS",
        "database": "MY_DB",
        "schema": "ANALYTICS"
      },
      "changes": [
        {
          "kind": "changed",
          "attribute_name": "warehouse_size",
          "value": "SMALL",
          "prev_value": "XSMALL"
        },
        {
          "kind": "collection",
          "collection_name": "columns",
          "id_label": "name",
          "changes": [
            {
              "kind": "added",
              "item_id": "DISCOUNT_AMOUNT",
              "changes": [
                {
                  "kind": "set",
                  "attribute_name": "data_type",
                  "value": "NUMBER(10,2)"
                }
              ]
            },
            {
              "kind": "modified",
              "item_id": "ORDER_STATUS",
              "changes": [
                {
                  "kind": "changed",
                  "attribute_name": "data_type",
                  "value": "VARCHAR(50)",
                  "prev_value": "VARCHAR(20)"
                }
              ]
            },
            {
              "kind": "removed",
              "item_id": "LEGACY_FLAG"
            }
          ]
        }
      ]
    },
    {
      "type": "DROP",
      "object_id": {
        "domain": "VIEW",
        "name": "OLD_REPORT_VIEW",
        "fqn": "MY_DB.ANALYTICS.OLD_REPORT_VIEW",
        "database": "MY_DB",
        "schema": "ANALYTICS"
      },
      "changes": []
    }
  ]
}

REFRESH ALL 输出¶

JSON 输出包含动态表刷新操作的结果，格式如下：

{
  "dts_refresh_result": {
    "refreshed_tables": [
      {
        "table_name": <table_name>,
        "statistics": {
          "inserted_rows": <inserted_rows>,
          "deleted_rows": <deleted_rows>
        },
        "data_timestamp": <data_timestamp>
      }
    ]
  }
}

属性	描述
`dts_refresh_result`	包含动态表刷新操作的结果。
`refreshed_tables[]`	一个条目数组，每个条目对应一个刷新的动态表。
`table_name`	刷新的动态表的完全限定名称。
`statistics`	刷新表的统计信息。
`inserted_rows`	刷新期间插入的行数。
`deleted_rows`	刷新期间删除的行数。
`data_timestamp`	ISO 8601 时间戳，表示刷新后数据的时间点新鲜度。

动态表刷新的 JSON 输出的示例：

{
  "dts_refresh_result": {
    "refreshed_tables": [
      {
        "table_name": "db.schema.my_dynamic_table",
        "statistics": {
          "inserted_rows": 150,
          "deleted_rows": 30
        },
        "data_timestamp": "2026-03-16T12:00:00.000Z"
      }
    ]
  }
}

TEST ALL 输出¶

TEST 输出包含总体状态和期望及其值，格式如下：

Note

在预览阶段，确切的输出格式可能会发生相应变化。

{
  "status": <status>,
  "expectations": [
    {
      "table_name": <table_name>,
      "metric_database": <metric_database>,
      "metric_schema": <metric_schema>,
      "metric_name": <metric_name>,
      "expectation_name": <expectation_name>,
      "expectation_expression": <expectation_expression>,
      "value": <value>,
      "expectation_violated": <expectation_violated>,
      "column_names": <column_names>
    }
  ]
}

属性	描述
`status`	Overall result of the test run. Possible values: `SUCCESSFUL` (all expectations met), `FAILED` (one or more expectations violated).
`expectations[]`	预期结果数组，每个评估的数据质量预期对应一个结果。
`table_name`	评估该期望所对应的表或视图的完整限定名称。
`metric_database`	包含数据指标函数的数据库。
`metric_schema`	包含数据指标函数的架构。
`metric_name`	Name of the data metric function (for example, `NULL_COUNT`, `MIN`, `UNIQUE_COUNT`).
`expectation_name`	项目中定义的期望的名称。
`expectation_expression`	Boolean expression that the metric value is evaluated against (for example, `value = 0`, `value >= 0`).
`value`	The result of the data metric function evaluation. Present only when `expectation_violated` is `false`.
`expectation_violated`	Whether the expectation was violated. `true` if the metric value did not satisfy the expectation expression; `false` otherwise.
`column_names`	用于评估数据指标函数的列名数组。

数据质量测试的 JSON 输出示例：

{
  "status": "FAILED",
  "expectations": [
    {
      "table_name": "db.schema.my_table",
      "metric_database": "SNOWFLAKE",
      "metric_schema": "CORE",
      "metric_name": "NULL_COUNT",
      "expectation_name": "no_nulls_in_id",
      "expectation_expression": "value = 0",
      "value": 0,
      "expectation_violated": false,
      "column_names": ["ID"]
    },
    {
      "table_name": "db.schema.my_table",
      "metric_database": "SNOWFLAKE",
      "metric_schema": "CORE",
      "metric_name": "UNIQUE_COUNT",
      "expectation_name": "unique_id_check",
      "expectation_expression": "value >= 100",
      "value": null,
      "expectation_violated": true,
      "column_names": ["ID"]
    }
  ]
}

使用说明

When executing a DCM project with EXECUTE DCM PROJECT PLAN, the output of the command is the same as for the actual deployment. The difference is that no changes to the affected account are applied. This feature allows you to verify whether the rendered definition files have a valid syntax, what changes would be applied to the account, and whether the project owner role has the required privileges to apply these changes.

Because PLAN closely mirrors DEPLOY, it requires the same OWNERSHIP privilege on the DCM project as DEPLOY, even though no changes are applied. This way, a dry run surfaces privilege errors before deployment.

To avoid unintended changes and catch errors, always run EXECUTE DCM PROJECT PLAN before you deploy a DCM project.

对模板变量的支持

Template variables let you dynamically choose the content of the parameterized definitions files during the DCM project execution. You can use template variables in the following ways:

See the Template variable examples section for examples.

示例

基本示例

Execute a DCM project in PLAN mode to validate changes to a project without applying them:

EXECUTE DCM PROJECT my_project
  PLAN
  FROM '@my_database.my_schema.my_stage/my_project';

以 DEPLOY 模式执行 DCM（以应用更改），并指定部署别名和名为 PROD 的配置：

EXECUTE DCM PROJECT my_project
  DEPLOY AS "my_update"
  USING CONFIGURATION PROD
  FROM '@my_database.my_schema.my_stage/my_project';

Purge a non-production DCM project to drop all entities, revoke all grants, and remove all attachments it currently manages, with an alias to label the entry in the deployment history:

EXECUTE DCM PROJECT my_project PURGE AS "sandbox cleanup";

模板变量示例

以下示例演示了如何在 EXECUTE DCM PROJECT 语句中为模板变量指定值。

覆盖 DCM 项目清单文件中定义的模板变量

Define a template variable named desc in the manifest file:

manifest_version: 2
type: DCM_PROJECT
default_target: DCM_DEV
targets:
  DCM_DEV:
 desc: "created by hello world project"

创建使用模板变量的定义文件：
```
DEFINE DATABASE NEW_DB;
DEFINE TABLE NEW_DB.PUBLIC.TBL (ID INT) COMMENT = '{{desc}}';
```
Call the EXECUTE DCM PROJECT command in DEPLOY mode, and specify a value for the desc variable to override its default value in the manifest:
```
EXECUTE DCM PROJECT MY_PROJECT DEPLOY
  USING CONFIGURATION FIRST_CONFIG (desc => 'This object is mine')
  FROM '/my/project/source';
```

为清单文件中未定义的模板变量提供值

创建一个包含所需命令的定义文件：
```
DEFINE DATABASE NEW_DB;
DEFINE TABLE NEW_DB.PUBLIC.TBL (ID INT) COMMENT = '{{desc_new}}';
```
Call the EXECUTE DCM PROJECT command, and specify a value for the desc_new variable:
```
EXECUTE DCM PROJECT MY_PROJECT (desc_new => 'This object is mine');
```