Skip to main content

Table

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

  • Manage the lifecycle of new and existing tables
  • Manage the grants of a table
  • Manage the tags applied to tables

Usage​

note

We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. The major difference is in how you define Snowflake objects in the configuration file. Rather than having a grouped collection of objects, SOLE for Data Products goes for modular, self-describing, and explicit object definition.
Learn more about SOLE for Data Products, currently available as a private preview.

We have also introduced Data products as an extra layer on top of the data product platform capabilities making managing data products easier than ever. Learn more about Data Products, currently available as a private preview.

databases:
  <database-name>:
    schemas:
      <schema-name>:
        tables:
          <table-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>
            shares:
              <privilege>:
                - <share-name>
                - <share-name>
            with_tags:
              <tag-name>:
                value: <tag-value>
                schema: <source-schema>

Supported parameters​

The engine supports the parameters listed below.

Configuration KeyRequired/OptionalData Types and ValuesDescription
columnsRequiredObject: See here for definition of columnsDefinitions of a column to create in the table. Minimum one required
commentOptionalStringSpecifies a comment for the table
change_trackingOptionalBooleanSpecifies whether to enable change tracking on the table. Default false
cluster_byOptionalList of StringA list of one or more table columns/expressions to be used as clustering key(s) for the table
data_retention_daysIntegerInteger: For standard edition 0 or 1, and for enterprise edition 0 to 90 (permanent tables) and 0 or 1 (transient table)Specifies the retention period for the table so that Time Travel actions (SELECT, CLONE, UNDROP) can be performed on historical data in the table. The default value is inherited from the parent schema.
deletedOptionalBoolean: True enables deletion prevention, False does nothingSpecifies what objects are allowed to be deleted
grantsOptionalMap: See Supported Table Grants to RolesList of Privileges and Roles to which privileges are granted on the current table
manage_modeOptionalString: all (default), none, grantsConfigures what properties to manage for the table.
See Changing Manage Mode before changing the value.
primary_keyOptionalBlock Map (max 1): See here for definition of primary_keyDefinitions of primary key constraint to create on table
sharesOptionalMap: See Supported Table Grants to SharesList of Privileges and Roles to which privileges are granted on the current table
with_tagsOptionalObject: See here for a definition of with_tagsList of tag references to be applied to the current table

columns Parameter​

You can specify the columns of the table to be managed.

Multiples columns in an object format can be listed in the columns parameter with each having the following supported parameters:

Configuration KeyRequired/OptionalData Types and ValuesDescription
typeRequiredStringData type of the Column
commentOptionalStringComment on the column
defaultOptionalBlock List (max 1): See here for a definition of defaultDefines the column default value.
Note: Because of the limitations of Snowflake's ALTER TABLE ADD/MODIFY COLUMN, updates to default will not be applied.
identityOptionalBlock List (max 1): See here for a definition of identityDefines the identity start/step values for a column.
Note: identity and default are mutually exclusive.
nullableOptionalBooleanWhether this column can contain null values.
Note: Depending on your Snowflake version, the default value will not suffice if this column is used in a primary key constraint.
with_tagsOptionalObject: See here for the definition of with_tagsList of tag references to be applied to the current column

Example​

columns:
  NAME:
    type: VARCHAR(16777216)
  YEAR:
    type: VARCHAR(16777216)
    comment: "Test column YEAR"
    nullable: true

default parameter​

You should provide only one of the three keys.

Configuration KeyRequired/OptionalData Types and ValuesDescription
constantOptionalString/Boolean/Null/Number (Float or Integer)The default constant value for the column
expressionOptionalStringThe default expression value for the column
sequenceOptionalStringThe default sequence to use for the column

Example​

default:
  constant: "10"

identity parameter​

Configuration KeyRequired/OptionalData Types and ValuesDescription
start_numOptionalIntegerThe number to start incrementing at
step_numOptionalIntegerStep size to increment by

Example​

identity:
  start_num: 1
  step_num: 5

primary_key parameter​

Configuration KeyRequired/OptionalData Types and ValuesDescription
keysRequiredList of StringColumns to use in primary key
nameOptionalStringName of constraint

Example​

primary_key:
  keys:
    - NAME
    - YEAR
  name: "PK_NAME_YEAR"

with_tags parameter​

You can specify a list of tags to apply to the table/column.

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

Configuration KeyRequired/OptionalData Types and ValuesDescription
valueRequiredStringValue to assign to the tag
databaseOptionalStringDatabase of the Tag identifier. If omitted defaults to the name of the database the table/column is in.
schemaOptionalStringSchema of the Tag identifier. If omitted defaults to the name of the schema the table/column is in.

Example​

databases:
  DB1:
    schemas:
      SCHEMA1:
        tables:
          TABLE1:
            with_tags:
              DEPARTMENT:
                value: Sales
                schema: TAG_LIBRARY
              CONFIDENTIALITY:
                value: Sensitive
                schema: TAG_LIBRARY

In-Place changes​

Using data type aliases leads to in-place changes when running a PLAN or APPLY for tables.

Aliases such as TEXT are converted to their actual value VARCHAR(16777216) when an object is created in Snowflake. Further, the actual value is saved in the local state as well. Thus, when subsequent pipelines run, the engine assumes that a change has to be made to convert VARCHAR(16777216) to specified data-type alias e.g. TEXT.

To reduce the number of in-place changes, use the actual data type, such as VARCHAR(16777216) or NUMBER(38,0), instead of data-type aliases such as TEXT or INTEGER.

For a complete list of data types and their aliases, refer to the Snowflake data type summary.

Supported table grants to roles​

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

  • ALL PRIVILEGES
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • REFERENCES
  • 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.

Supported table grants to shares​

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

  • SELECT

Inherited grants​

Grants for a table can be defined in one of three sections:

  • Table efinition with parameter grants
  • Schema definition with parameter table_grants
  • Database definition with parameter table_grants

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

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

Example​

Inheriting grants defined in a database.

databases:
  SALES_RECORD:
    schemas:
      SALES:
        tables:
          PRODUCT:
            columns:
              PRODUCT_ID:
                type: NUMBER(38,0)
              PRODUCT_NAME:
                type: VARCHAR(16777216)
            comment: "Product Info"
    table_grants:
      UPDATE:
        - HR_ROLE
        - ACCOUNTADMIN

With this configuration, the table PRODUCT would have the following grants:

  • UPDATE to:
    • HR_ROLE
    • ACCOUNTADMIN

Inheriting Grants defined in Schema

databases:
  SALES_RECORD:
    schemas:
      SALES:
        table_grants:
          INSERT:
            - DEV_ROLE
            - SYSADMIN
          TRUNCATE:
            - ACCOUNTADMIN
        tables:
          PRODUCT:
            columns:
              PRODUCT_ID:
                type: NUMBER(38,0)
              PRODUCT_NAME:
                type: VARCHAR(16777216)
            comment: "Product Info"
    table_grants:
      UPDATE:
        - HR_ROLE
        - ACCOUNTADMIN

With this configuration, the table PRODUCT would have the following grants:

  • INSERT to:
    • DEV_ROLE
    • SYSADMIN
  • TRUNCATE to:
    • ACCOUNTADMIN

Overriding Grants in Table

databases:
  SALES_RECORD:
    schemas:
      SALES:
        table_grants:
          INSERT:
            - DEV_ROLE
            - SYSADMIN
          TRUNCATE:
            - ACCOUNTADMIN
        tables:
          PRODUCT:
            grants:
              SELECT:
                - DEV_ROLE
              UPDATE:
                - DEV_ROLE
            columns:
              PRODUCT_ID:
                type: NUMBER(38,0)
              PRODUCT_NAME:
                type: VARCHAR(16777216)
            comment: "Product Info"
    table_grants:
      UPDATE:
        - HR_ROLE
        - ACCOUNTADMIN

With this configuration, the table PRODUCT would have the following grants:

  • SELECT to:
    • DEV_ROLE
  • UPDATE to:
    • DEV_ROLE

Examples​

databases:
  SALES_RECORD:
    schemas:
      SALES:
        tables:
          PRODUCT:
            columns:
              PRODUCT_ID:
                type: NUMBER(38,0)
              PRODUCT_NAME:
                type: VARCHAR(16777216)
              PURCHASE_DATE:
                type: TIMESTAMP_NTZ(9)
                comment: "Product purchase date"
                nullable: true
                default:
                  expression: "CURRENT_TIMESTAMP()"
              SERIAL:
                type: NUMBER(38,0)
                identity:
                  start_num: 1
                  step_num: 2
            comment: "Product Info"
            data_retention_days: 10
            change_tracking: false
            primary_key:
              keys:
                - PRODUCT_ID
              name: PK_PRODUCT_ID_SERIAL
            cluster_by:
              - "to_date(PURCHASE_DATE)"
              - "PRODUCT_NAME"
            grants:
              SELECT:
                - DEV_ROLE
              UPDATE:
                - DEV_ROLE
Last updated on