Skip to main content

Table

Configuration can be provided to Snowflake Object Lifecycle Engine for the following operation with Table:

  • Manage Lifecycle of new and existing Table
  • Manage Grants of Table

Supported Parameters#

The engine supports the parameters listed below.

  • COLUMNS: Definitions of a column to create in the table. Minimum one required.
    • REQUIRED
    • Configuration key: columns
    • Data Type: Object. See here for definition of Column
  • COMMENT: Specifies a comment for the table.
    • Configuration Key: comment
    • Data Type: String
  • CHANGE_TRACKING: Specifies whether to enable change tracking on the table. Default false.
    • Configuration Key: change_tracking
    • Data Type: Boolean
  • CLUSTER_BY: A list of one or more table columns/expressions to be used as clustering key(s) for the table.
    • Configuration Key: cluster_by
    • Data Type: List of String
  • DATA_RETENTION_DAYS: Specifies the retention period for the table so that Time Travel actions (SELECT, CLONE, UNDROP) can be performed on historical data in the table. Default value is 1, if you wish to inherit the parent schema setting then pass in the schema attribute to this argument.
    • Configuration Key: data_retention_days
    • Data Type: Integer
  • PRIMARY_KEY: Definitions of primary key constraint to create on table. See here for definition of Primary Key.
    • Configuration Key: primary_key
    • Data Type: Block Map, Max: 1
  • GRANTS: List of Privileges and Roles to which privileges are granted to on the current Table.
    • Configuration key: grants
    • Data Type: Map
  • SHARES: List of Privileges and Shares to which privileges are granted to on the current Table.
    • Configuration key: shares
    • Data Type: Map
  • MANAGE_MODE: Configures what properties to manage for the Table.
    • Configuration key: manage_mode
    • Data Type: String
    • Possible Values:
      • none
      • grants
      • all(Default)

Column#

In the column parameter, users 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:

  • TYPE: Data-type of the Column
    • REQUIRED
    • Configuration key: type
    • Data Type: String
  • COMMENT: Column comment.
    • Configuration key: comment
    • Data Type: String
  • DEFAULT: Defines the column default value; note due to limitations of Snowflake's ALTER TABLE ADD/MODIFY COLUMN updates to default will not be applied. See here for definition of Default Parameter.
    • Configuration key: default
    • Data Type: Block List, Max: 1
  • IDENTITY: Defines the identity start/step values for a column. Note Identity/default are mutually exclusive. See here for definition of Identity parameter.
    • Configuration key: identity
    • Data Type: Block List, Max: 1
  • NULLABLE: Whether 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.
    • Configuration key: nullable
    • Data Type: Boolean
Example#
columns:  NAME:    type: VARCHAR(16777216)  YEAR:    type: VARCHAR(16777216)    comment: "Test column YEAR"    nullable: true

Default Parameter#

Only one of the three should be provided.

  • CONSTANT: The default constant value for the column.
    • Configuration key: constant
    • Data Type: String
  • EXPRESSION: The default expression value for the column.
    • Configuration key: expression
    • Data Type: String
  • SEQUENCE: The default sequence to use for the column.
    • Configuration key: sequence
    • Data Type: String
Example#
default:  constant: "10"

Identity Parameter#

  • START_NUM: The number to start incrementing at.
    • Configuration key: start_num
    • Data Type: Integer
  • STEP_NUM: Step size to increment by.
    • Configuration key: step_num
    • Data Type: Integer
Example#
identity:  start_num: 1  step_num: 5

Primary Key#

  • KEYS: Columns to use in primary key.
    • REQUIRED
    • Configuration key: keys
    • Data Type: List of String
  • NAME: Name of constraint.
    • Configuration key: name
    • Data Type: String
Example#
primary_key:  keys:    - NAME    - YEAR  name: "PK_NAME_YEAR"

In-Place Changes#

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

Aliases such as TEXT are converted to its true value(VARCHAR(16777216)) when an object is created in Snowflake and the true value is saved in local state as well.
Due to this, when subsequent pipelines are run, the engine assumes that a change has to made to convert VARCHAR(16777216) to specified data-type alias eg. TEXT.

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

Basic syntax#

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

Supported Table Grants to Roles#

Following is the list of Privileges Grant to Roles that can be specified in the table definition

  • ALL PRIVILEGES
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • REFERENCES
  • OWNERSHIP

Supported Table Grants to Shares#

Following is the list of Privileges Grant to Shares that can be specified in the table definition

  • SELECT

Inherited Grants#

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

  • Table Definition with parameter grants
  • Schema Definition with parameter table_grants
  • Database Definition with parameter table_grants

Local grants overrides grants defined in parent's section.
That means that table_grants defined in the database would be overridden by table_grants defined in 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 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 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