Supported object types in DCM Projects

The DEFINE statement is a special command used exclusively in DCM project definition files. Its syntax is similar to the CREATE OR ALTER command, but with the following key differences:

  • The order and location of DEFINE statements don’t matter. Snowflake collects and sorts all statements from all definition files during project execution.
  • If you remove a DEFINE statement that was previously deployed, Snowflake drops the corresponding object the next time you deploy the project. The same applies to GRANT and ATTACH statements that are removed after being previously deployed.
  • 仅支持 Snowflake 对象类型的子集。
  • All objects must be defined with a fully qualified name (database.schema.object_name).
  • 对其他对象的引用必须使用完全限定名称。

The following object types are natively supported in DCM Projects definition files with the DEFINE, GRANT, or ATTACH statements.

数据库

限制：

All CREATE OR ALTER DATABASE limitations apply, including:

  • 重命名数据库

架构

限制：

All CREATE OR ALTER SCHEMA limitations apply, including:

  • 重命名架构

表

限制：

All CREATE OR ALTER TABLE limitations apply, including:

  • 重命名表
  • 重命名列
  • 对列重新排序
  • 将列类型更改为不兼容的类型
  • 向表或列添加搜索优化
  • 向表或列添加标签和策略

视图

限制：

All CREATE OR ALTER VIEW limitations apply, including:

  • 重命名视图
  • 对列重新排序

Sequence

DCM Projects supports defining sequences that generate unique numbers across sessions and statements. For more information, see Using Sequences.

限制：

动态表

支持的变更：

在没有完全刷新的情况下：

  • 仓库
  • 目标滞后

包含重新初始化或完全刷新：

  • 刷新模式
  • 主体的任何变化，包括：
    • 弃用列
    • 在末尾添加列

不可变实参：

  • INITIALIZE

限制：

All CREATE OR ALTER DYNAMIC TABLE limitations apply, including:

  • 对列重新排序
  • 重命名动态表

任务

当为已启动的任务部署定义更改时，Snowflake 会自动暂时暂停该任务（或其根任务），应用更改，然后再次恢复。

新部署的任务默认处于暂停状态。

Target state:

You can specify a target state of STARTED or SUSPENDED for each task in your definitions. Place the target state keyword immediately before the AS keyword in the DEFINE TASK statement. If you define a task as STARTED, Snowflake resumes the task after deployment. This property is independent of other changes to the task definition. If you define a task as STARTED and then suspend it outside of DCM Projects, the next deployment of that same definition starts the task again.

DCM Projects handles the dependency resolution between root-task and child-task states.

Note

The target state is a DCM Projects-specific property. It won’t be visible in the DDL of the deployed task.

DEFINE TASK MY_DB.MY_SCHEMA.TSK_INGEST_DAILY_ORDERS
    WAREHOUSE = 'MY_WH'
    SCHEDULE = 'USING CRON 0 5 * * * UTC'
    STARTED
AS
   SELECT 1
;

限制：

Alert

DCM Projects supports defining alerts that run a SQL statement on a schedule and notify you when a condition is met. For more information, see Setting up alerts based on data in Snowflake.

限制：

文件格式

限制：

Stages

DCM Projects supports both internal and external stages.

支持的变更：

  • 目录表
  • 注释

不可变属性：

  • 加密类型

限制：

内部暂存区

An internal stage stores data files within Snowflake.

External stage

An external stage references data files stored in a location outside of Snowflake, such as Amazon S3, Google Cloud Storage, or Microsoft Azure.

Warning

Don’t include sensitive information, such as API keys or credentials, in external stage definitions. DCM Projects doesn’t currently identify and obfuscate this data, so it would be stored in plain text in your rendered DCM project files and deployment history.

Functions

DCM Projects supports defining the following types of user-defined functions:

SQL 函数

Note

Only SQL-language functions are supported in DCM Projects definitions. Other languages (Python, Java, Scala, JavaScript) and external functions aren’t yet supported.

限制：

数据指标函数

Data metric functions (DMFs) let you define data quality expectations and attach those expectations to tables. You can select from existing system DMFs or write your own user-defined data metric functions (UDMFs). You can then attach them to tables, views, and dynamic tables with a many-to-many relationship. For more information, see Use SQL to set up data metric functions.

To attach data metric functions, you first need to add a DATA_METRIC_SCHEDULE to each table, dynamic table, or view definition. For example: DATA_METRIC_SCHEDULE = TRIGGER_ON_CHANGES. The TRIGGER_ON_CHANGES schedule is not available for views.

每个项目和附件的用户定义的期望名称必须是唯一的。

Defining expectations is optional, but recommended, when attaching DMFs to table columns. Attached DMFs without set expectations aren’t considered when running EXECUTE DCM PROJECT <my_project> TEST ALL.

支持的变更：

  • 定义 UDMFs（用户定义的数据指标函数）
  • Attaching system DMFs and UDMFs to tables, views, or dynamic tables inside and outside a DCM project
  • 定义表列的数据期望

示例：

定义 UDMF 的示例：

DEFINE DATA METRIC FUNCTION DCM_DEMO.TESTS.INVENTORY_SPREAD(
  TABLE_NAME TABLE(
    COLUMN_VALUE number
  )
)
  RETURNS number
AS
$$
  SELECT
    MAX(COLUMN_VALUE) - MIN(COLUMN_VALUE)
  FROM
    TABLE_NAME
  WHERE
    COLUMN_VALUE IS NOT NULL
$$;

添加附带期望的系统 DMF 的示例：

ATTACH DATA METRIC FUNCTION SNOWFLAKE.CORE.MIN
  TO TABLE DCM_PROJECT_{{db}}.RAW.INVENTORY
  ON (IN_STOCK)
  EXPECTATION MIN_10_ITEMS_INVENTORY (value > 10);

添加附带期望的 UDMF 的示例：

ATTACH DATA METRIC FUNCTION DCM_DEMO.TESTS.INVENTORY_SPREAD
  TO TABLE DCM_PROJECT_{{db}}.RAW.INVENTORY
  ON (IN_STOCK)
  EXPECTATION EVEN_ITEM_INVENTORY (VALUE < 50);

To see all available system DMFs, query SHOW DATA METRIC FUNCTIONS IN DATABASE SNOWFLAKE.

Procedures

DCM Projects supports defining the following types of stored procedures:

SQL procedure

Note

Only SQL-language procedures (LANGUAGE SQL) are supported in DCM Projects definitions. Other languages (Python, Java, Scala, JavaScript) aren’t yet supported.

The DEFINE PROCEDURE statement works the same as a standard Snowflake CREATE OR ALTER PROCEDURE statement, but uses the DEFINE keyword to deploy it through DCM Projects.

限制：

示例：

DEFINE PROCEDURE MY_DB.MY_SCHEMA.REFRESH_DAILY_SUMMARY()
  RETURNS VARCHAR
  LANGUAGE SQL
AS
BEGIN
  TRUNCATE TABLE MY_DB.MY_SCHEMA.DAILY_SUMMARY;

  INSERT INTO MY_DB.MY_SCHEMA.DAILY_SUMMARY
    SELECT
      CURRENT_DATE() AS REPORT_DATE,
      COUNT(*) AS TOTAL_ORDERS,
      SUM(AMOUNT) AS TOTAL_REVENUE
    FROM MY_DB.MY_SCHEMA.ORDERS
    WHERE ORDER_DATE = CURRENT_DATE();

  RETURN 'Daily summary refreshed';
END;

仓库

不可变属性：

  • INITIALLY_SUSPENDED

限制：

角色和数据库角色

不支持的类型

  • 应用程序角色

授权

Just like each object can be defined only once in DCM Projects, each privilege-grantee relationship can only be defined once across all DCM Projects.

DCM Projects is only aware of grants that were defined and deployed through DCM Projects. Any grants that were added outside of DCM Projects coexist, and DCM Projects doesn’t remove them.

不支持的 GRANT 类型：

  • APPLICATION ROLE 权限
  • CALLER 权限

GRANT OWNERSHIP

When removing a GRANT OWNERSHIP statement that was previously deployed, DCM Projects attempts to use the current owner role to grant ownership back to the DCM project owner. If the project owner role doesn’t hold the object’s owner role, ownership needs to be transferred back manually outside of DCM Projects.

限制：

  • The COPY CURRENT GRANTS and REVOKE CURRENT GRANTS clauses aren’t available in DCM Projects. Define all other privilege grants on the target object within the same DCM project as the OWNERSHIP grant. If the object has pre-existing grants, PLAN or DEPLOY of the GRANT OWNERSHIP statement fails.

    To work around this, do one of the following:

    • Define all desired grants on the target object in the DCM project, including any pre-existing grants.
    • Revoke the pre-existing grants manually outside of DCM Projects.

标签

不支持的属性：

  • 传播

限制：

身份验证策略

限制：

附加标签、掩码策略和行访问策略（不支持）

Tags, masking policies, and row access policies can’t be added to DCM Projects table column definitions.

You can attach masking and row access policies manually outside of DCM Projects. DCM Projects definitions for table objects ignore any attached masking or row access policies. They are not revoked by redeploying table definitions, even when those definitions do not contain the policies.