Skip to content

Schema

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with schemas:

  • Manage the lifecycle of new and existing schemas
  • Manage grants of schemas
  • Manage tags applied to schemas

Usage

- schema:
    name: <schema-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)
    shares:
      <privilege>:
        - rel(share.<share-name>)
        - rel(share.<share-name>)
    with_tags:
      <tag-name>:
        value: <tag-value>
        schema: <source-schema>
databases:
  <database-name>:
    schemas:
      <schema-name>:
        <configuration-key>: <value>
        grants:
          <privilege>:
            - <role-name>
            - <role-name>
        shares:
          <privilege>:
            - <share-name>
        with_tags:
          <tag-name>:
            value: <tag-value>
            schema: <source-schema>

Supported parameters

The engine supports the parameters listed below.

Configuration Key Required / Optional Data Types and Values Description
comment Optional String Specifies a comment for the schema
data_retention_days Optional Integer: For standard edition 0 or 1, and for enterprise edition 0 to 90 (permanent databases) and 0 or 1 (transient databases) Number of days for which Time Travel actions can be performed on the schema
deleted Optional Boolean: True enables deletion prevention, False does nothing Specifies what objects are allowed to be deleted
depends_on Optional List of String: See Object Dependencies for a definition of depends_on List of SOLE-managed objects that this schema depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
is_managed Optional Boolean Managed access schemas centralize privilege management with the schema owner
is_transient Optional Boolean Transient schemas do not have a fail-safe period, so they do not incur additional storage costs once they leave Time Travel.
For more information about schema recreation behavior, see Schema storing masking policies or tags.
future_grants Optional Map: See Future grants on objects List of future grants on the schema objects managed outside SOLE
grants Optional Map: See Supported Schema Grants to Roles List of Privileges and Roles to which privileges are granted on the current schema
manage_mode Optional String: all (default), none, grants Configures what properties to manage for the Schema.
See Changing Manage Mode before changing the value.
shares Optional Map: See Supported schema grants to shares List of Privileges and Shares to which privileges are granted on the current schema
table_grants Optional Map List of Privileges and Roles to which privileges are granted for all tables with manage_mode set to all or grants in the current schema.
For more information, see Manage Mode.
with_tags Optional Object: See here for a definition of with_tags List of tag references to be applied to the current schema

with_tags parameter

You can specify a list of tags to apply to the schema.

Multiple tags are listed in the with_tags parameter in an object format with the following parameters:

Parameter Required/Optional Data Type and Values Description
value Required String Value to assign to tag
schema Optional String Schema of the tag identifier. If omitted, it defaults to the name of this schema object.
database Optional String Database of the tag identifier. If omitted, it defaults to the name of the database this schema is in.

Example

- schema:
    name: SCHEMA1
    database: rel(database.DB1)
    with_tags:
      DEPARTMENT:
        value: Sales
      CONFIDENTIALITY:
        value: Sensitive
        schema: TAG_LIBRARY
databases:
  DB1:
    schema:
      SCHEMA1:
        with_tags:
          DEPARTMENT:
            value: Sales
          CONFIDENTIALITY:
            value: Sensitive
            schema: TAG_LIBRARY

Supported schema grants to roles

Following are the privileges you can grant to roles in the schema definition:

  • ALL PRIVILEGES
  • MODIFY
  • MONITOR
  • USAGE
  • CREATE TABLE
  • CREATE HYBRID TABLE
  • CREATE EXTERNAL TABLE
  • CREATE VIEW
  • CREATE MATERIALIZED VIEW
  • CREATE MASKING POLICY
  • CREATE FILE FORMAT
  • CREATE STAGE
  • CREATE PIPE
  • CREATE STREAM
  • CREATE TASK
  • CREATE SEQUENCE
  • CREATE FUNCTION
  • CREATE PROCEDURE
  • OWNERSHIP

ALL PRIVILEGES handling

When you define ALL PRIVILEGES in the SOLE configuration file, you grant all the privileges listed above to roles on this object except OWNERSHIP. However, the management of ALL PRIVILEGES in SOLE differs from its handling in Snowflake. See Handling ALL PRIVILEGES in SOLE for more information.

Info

For supported table_grants, see the grants section for tables.

Supported schema grants to shares

Following is the privilege you can grant to shares in the schema definition:

  • USAGE

Inherited grants

You can define grants for a schema in one of three sections:

  • Schema Definition with parameter grants
  • Database Definition with parameter schema_grants

Local grants override grants defined in the parent's section. That means that schema_grants defined in the database would be overridden by grants defined in the schema.

If no overriding grants are defined, then grants from parents are inherited for all schemas. If schema_grants is defined in the database, then all schema in the database would inherit the grants defined.

Future grants on objects in a schema

You can assign future grants on objects in a database or a schema when objects aren't managed by SOLE.

The DataOps SOLE and MATE engines manage all grants on Snowflake objects. The grants key in the Snowflake object configuration and MATE project configuration let you decide what grants to allow on objects before applying them to the target platform via automated DataOps pipelines.

But there are cases when Snowflake objects, typically schema-level objects like tables or hybrid tables, are managed outside the two DataOps engines, namely:

  • When objects are created during DataOps pipelines by an external tool
  • When objects are created outside the DataOps pipelines

The future_grants key in the SOLE configuration lets you define future grants on the Snowflake objects managed outside DataOps and thus have control over their lifecycle.

It is worth mentioning that future grants support all grants supported by child objects in databases and schemas. For example, check out Supported table grants to roles for a list of the privileges you can grant to roles in the table definition with future_grants.

Example

Define future grants in any existing or new YAML file parsed by SOLE, typically in the directory specified in the parameter CONFIGURATION_DIR.

database:
  MY_DATABASE:
    ...
    schemas:
      MY_SCHEMA:
        ...
        future_grants:
          hybrid_tables:
            SELECT:
              - MY_ROLE
            INSERT:
              - MY_ROLE
          views:
            SELECT:
              - MY_ROLE
database:
  MY_DATABASE:
    ...
    schemas:
      MY_SCHEMA:
        ...
        future_grants:
          tables:
            SELECT:
              - MY_ROLE
            INSERT:
              - MY_ROLE
          views:
            SELECT:
              - MY_ROLE

Note

If the schema level is present, the database level is ignored. For more information, see Future Grants on Database or Schema Objects.

Schema storing masking policies or tags

If a schema is used to store masking policies or tags, a recreation of the schema will lead to an error. Read more about this behavior here.

The recreation can occur due to the following factors:

Switching the transient nature of the schema

Snowflake doesn't allow altering the schema to change the transient nature. Due to this if the value is_transient is changed, it leads to recreation.

Database recreated

If a database is recreated, the schemas present in the database are also recreated. To read more about database recreation, see database storing masking policies or tags.

Examples

Schemas with configured parameters

- schema:
    name: SALES
    database: rel(database.SALES_RECORD)
    comment: "Test Schema"
    data_retention_days: 0
    is_managed: false
    is_transient: false
    grants:
      MODIFY:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
      MONITOR:
        - rel(role.DEV_ROLE)
        - ACCOUNTADMIN
    shares:
      USAGE:
        - rel(share.DEV_SHARE)
    table_grants:
      UPDATE:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
databases:
  SALES_RECORD:
    schemas:
      SALES:
        comment: "Test Schema"
        data_retention_days: 0
        is_managed: false
        is_transient: false
        grants:
          MODIFY:
            - HR_ROLE
            - ACCOUNTADMIN
          MONITOR:
            - DEV_ROLE
            - ACCOUNTADMIN
        shares:
          USAGE:
            - DEV_SHARE
        table_grants:
          UPDATE:
            - HR_ROLE
            - ACCOUNTADMIN