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.

  • Only a subset of Snowflake object types are supported.

  • All objects must be defined with a fully qualified name (database.schema.object_name).

  • References to other objects must use fully qualified names.

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

Database

Limitations:

All CREATE OR ALTER DATABASE limitations apply, including:

  • Renaming the database

Schema

Limitations:

All CREATE OR ALTER SCHEMA limitations apply, including:

  • Renaming the schema

Table

Limitations:

All CREATE OR ALTER TABLE limitations apply, including:

  • Renaming tables

  • Renaming columns

  • Reordering columns

  • Changing column types to incompatible types

  • Adding search optimization to a table or columns

  • Adding tags and policies to a table or columns

View

Limitations:

All CREATE OR ALTER VIEW limitations apply, including:

  • Renaming views

  • Reordering columns

Sequence

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

Limitations:

Dynamic table

Supported changes:

Without a full refresh:

  • Warehouse

  • Target-lag

With re-initialization or a full refresh:

  • Refresh mode

  • Any changes of the body including:

    • Dropping columns

    • Adding columns at the end

Immutable arguments:

  • INITIALIZE

Limitations:

All CREATE OR ALTER DYNAMIC TABLE limitations apply, including:

  • Reordering columns

  • Renaming dynamic tables

Task

When definition changes are deployed for a task that is already started, Snowflake automatically suspends that task (or its root task) temporarily, applies the change, and then resumes it again.

Newly deployed tasks are suspended by default.

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
;

Limitations:

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.

Limitations:

File format

Limitations:

Stages

DCM Projects supports both internal and external stages.

Supported changes:

  • Directory table

  • Comment

Immutable attributes:

  • Encryption type

Limitations:

Internal stage

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 function

Note

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

Limitations:

Data metric function

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.

The user-defined names of expectations must be unique per project and attachment.

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.

Supported changes:

  • Defining UDMFs (user-defined data metric functions)

  • Attaching system DMFs and UDMFs to tables, views, or dynamic tables inside and outside a DCM project

  • Defining data expectations for table columns

Example:

An example of defining a 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
$$;

An example of attaching a system DMF with an expectation:

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

An example of attaching a UDMF with an expectation:

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.

Limitations:

Example:

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;

Warehouse

Immutable attributes:

  • INITIALLY_SUSPENDED

Limitations:

Role and Database Role

Unsupported types:

  • Application Role

Grant

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.

Unsupported GRANT types:

  • APPLICATION ROLE grants

  • CALLER grants

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.

Limitations:

  • 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.

Tag

Unsupported attributes:

  • Propagate

Limitations:

Authentication policy

Limitations:

Attaching tags, masking policies, and row access policies (unsupported)

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.