Skip to content

Table

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

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

Usage

- table:
    name: <table-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-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>:
        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 Key Required/Optional Data Types and Values Description
columns Required Object: See here for definition of columns Definitions of a column to create in the table. Minimum one required
comment Optional String Specifies a comment for the table
change_tracking Optional Boolean: false (default) Specifies whether to enable change tracking on the table
cluster_by Optional List of String A list of one or more table columns/expressions to be used as clustering key(s) for the table
data_retention_days Optional Integer: 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.
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 table depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
grants Optional Map: See Supported Table Grants to Roles List of Privileges and Roles to which privileges are granted on the current table
manage_mode Optional String: all (default), none, grants Configures what properties to manage for the table.
See Changing Manage Mode before changing the value.
primary_key Optional Block Map (max 1): See here for definition of primary_key Definitions of primary key constraint to create on table
unique Optional List of Block Maps: See here for definition of unique Definitions of unique constraints to create on table
foreign_keys Optional List of Block Maps: See here for definition of foreign_keys Definitions of foreign key constraints to create on table
shares Optional Map: See Supported Table Grants to Shares List of Privileges and Roles to which privileges are granted on the current table
with_tags Optional Object: See here for a definition of with_tags List of tag references to be applied to the current table
always_drop_and_create Optional Boolean: false (default)
See here for a definition of always_drop_and_create
Specifies to drop and recreate the table
row_timestamp Optional Boolean: false (default)
See here for a definition of row_timestamp
Enables automatic row timestamp tracking for the table.

columns Parameter

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

List multiple columns in an object format in the columns parameter with each having the following supported parameters:

Configuration Key Required/Optional Data Types and Values Description
type Required String Data type of the Column
comment Optional String Comment on the column
default Optional Block List (max 1): See here for a definition of default Defines the column default value.
Note: Because of the limitations of Snowflake's ALTER TABLE ADD/MODIFY COLUMN, updates to default will not be applied.
as Optional Block List (max 1): See here for a definition of as Defines the virtual column value.
Note: Because of the limitations of Snowflake's ALTER TABLE ADD/MODIFY COLUMN, updates to virtual column will not be applied.
identity Optional Block List (max 1): See here for a definition of identity Defines the identity start/step values for a column.
Note: identity and default are mutually exclusive.
nullable Optional Boolean 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.
with_tags Optional Object: See here for the definition of with_tags List of tag references to be applied to the current column
masking_policy Optional Object: See here for a definition of masking_policy Apply masking policy on a column

Example

columns:
  NAME:
    type: VARCHAR(16777216)
  YEAR:
    type: VARCHAR(16777216)
    comment: "Test column YEAR"
    nullable: true
  ADDRESS:
    type: VARCHAR(40)
    comment: "Customer address — masked for non-HR roles"
    masking_policy: rel(masking_policy.ADDRESS_MASK)

default parameter

You should provide only one of the three keys.

Configuration Key Required/Optional Data Types and Values Description
constant Optional String/Boolean/Null/Number (Float or Integer) The default constant value for the column
expression Optional String The default expression value for the column
sequence Optional String The default sequence to use for the column

Example

default:
  constant: "10"

as parameter

Info

The as parameter is only supported in SOLE for data products.

Configuration Key Required/Optional Data Types and Values Description
expression Optional String The expression value for the column

Example

as:
  expression: "MD5('hash-me')"
Data product configuration
- table:
    name: STG_CUSTOMER
    database: rel(database.CRM_STAGING)
    schema: rel(schema.CORE)
    manage_mode: all
    grants:
      OWNERSHIP:
        - CRM
    columns:
      CUSTOMER_NAME:
        type: VARCHAR(32)
        nullable: false
      EFFECTIVE_DATE_FROM:
        type: TIMESTAMP_LTZ(9)
        as:
          expression: CURRENT_TIMESTAMP()
        nullable: false
        comment: "Test Virtual Column"
Data product configuration
- table:
    name: STG_EMPLOYEE
    database: rel(database.TEST)
    schema: rel(schema.CORE)
    manage_mode: all
    columns:
      EmployeeID:
        type: INT
        nullable: true
      Name:
        type: VARCHAR(100)
        nullable: true
      Salary:
        type: DECIMAL(10,2)
        nullable: true
      Bonus:
        type: DECIMAL(10,2)
        nullable: true
      TotalCompensation:
        type: DECIMAL(10,2)
        as:
          expression: "(Salary + Bonus)"
        nullable: true

identity parameter

Configuration Key Required/Optional Data Types and Values Description
start_num Optional Integer The number to start incrementing at
step_num Optional Integer Step size to increment by

Example

identity:
  start_num: 1
  step_num: 5

primary_key parameter

Configuration Key Required/Optional Data Types and Values Description
keys Required List of String Columns to use in primary key
name Optional String Name of constraint

Example

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

unique parameter

Info

The unique parameter is only supported in SOLE for data products.

You can define multiple unique constraints as a list of objects with the following parameters:

Configuration Key Required/Optional Data Types and Values Description
name Optional String Name of the unique constraint
keys Required List of String Keys to use in unique constraint

Example

unique:
  - name: "UNIQUE_EMAIL_NAME"
    keys:
      - EMAIL
      - NAME
  - name: "UNIQUE_AGE"
    keys:
      - AGE

foreign_keys parameter

Info

The foreign_keys parameter is only supported in SOLE for data products.

You can define multiple foreign key constraints as a list of objects with the following parameters:

Configuration Key Required/Optional Data Types and Values Description
name Optional String Name of the foreign key constraint
keys Required List of String Columns in the current table for the foreign key
referenced_table Required String Table that contains the referenced columns
referenced_keys Required List of String Columns in the referenced table

Example

foreign_keys:
  - name: "FK_EMPLOYEE_DEPARTMENT"
    keys:
      - DEPARTMENT_ID
    referenced_table: rel(table.DEPARTMENT)
    referenced_keys:
      - DEPARTMENT_ID

with_tags parameter

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

List multiple tags in the with_tags parameter in an object format with the following parameters:

Configuration Key Required/Optional Data Types and Values Description
value Required String Value to assign to the tag
database Optional String Database of the Tag identifier. If omitted, it defaults to the name of the database the table/column is in.
schema Optional String Schema of the Tag identifier. If omitted, it defaults to the name of the schema the table/column is in.

Example

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

row_timestamp parameter

Use the row_timestamp parameter in the table definition to enable row timestamp tracking for a table.

When this parameter is set to true, Snowflake enables row timestamp tracking on the table. This can be useful for features and workflows that rely on identifying when rows were modified.

Example

- table:
    name: STG_CUSTOMER
    database: rel(database.CRM_STAGING)
    schema: rel(schema.CORE)
    row_timestamp: true
    columns:
      CUSTOMER_NAME:
        type: VARCHAR(32)
        nullable: false
databases:
  CRM_STAGING:
    schemas:
      CORE:
        tables:
          STG_CUSTOMER:
            row_timestamp: true
            columns:
              CUSTOMER_NAME:
                type: VARCHAR(32)
                nullable: false

always_drop_and_create parameter

Use the always_drop_and_create parameter in the table definition only for ingestion tables, where the entire content is populated every time a data pipeline is run.

Warning

Avoid using it for other tables where existing data needs to be preserved, as the always_drop_and_create parameter can result in data loss.

The advantage of using this parameter is that all columns in the table are arranged inside Snowflake in the same order as they are defined in the YAML file. Without this parameter, data is preserved, but new columns (with NULL content) are appended at the end.

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
  • APPLYBUDGET
  • EVOLVE SCHEMA
  • REBUILD

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 definition 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

- database:
    name: SALES_RECORD
    table_grants:
      UPDATE:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
- schema:
    name: SALES
    database: rel(database.SALES_RECORD)
- table:
    name: PRODUCT
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    columns:
      PRODUCT_ID:
        type: NUMBER(38,0)
      PRODUCT_NAME:
        type: VARCHAR(16777216)
    comment: "Product Info"
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 a schema

- database:
    name: SALES_RECORD
    table_grants:
      UPDATE:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
- schema:
    name: SALES
    database: rel(database.SALES_RECORD)
    table_grants:
      INSERT:
        - DEV_ROLE
        - SYSADMIN
      TRUNCATE:
        - ACCOUNTADMIN
- table:
    name: PRODUCT
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    columns:
      PRODUCT_ID:
        type: NUMBER(38,0)
      PRODUCT_NAME:
        type: VARCHAR(16777216)
    comment: "Product Info"
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 a table

- database:
    name: SALES_RECORD
    table_grants:
      UPDATE:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
- schema:
    name: SALES
    database: rel(database.SALES_RECORD)
    table_grants:
      INSERT:
        - DEV_ROLE
        - SYSADMIN
      TRUNCATE:
        - ACCOUNTADMIN
- table:
    name: PRODUCT
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    grants:
      SELECT:
        - rel(role.DEV_ROLE)
      UPDATE:
        - rel(role.DEV_ROLE)
    columns:
      PRODUCT_ID:
        type: NUMBER(38,0)
      PRODUCT_NAME:
        type: VARCHAR(16777216)
    comment: "Product Info"
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

- table:
    name: PRODUCT
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    always_drop_and_create: true
    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()"
      PURCHASE_SEQUENCE:
        type: NUMBER(38,0)
        default:
          sequence:
            name: rel(sequence.PURCHASE_SEQUENCE)
            schema: rel(schema.SALES)
            database: rel(database.SALES_RECORD)
      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:
        - rel(role.DEV_ROLE)
      UPDATE:
        - rel(role.DEV_ROLE)
      APPLYBUDGET:
        - rel(role.DEV_ROLE)
      EVOLVE SCHEMA:
        - rel(role.DEV_ROLE)
      REBUILD:
        - rel(role.DEV_ROLE)
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()"
              PURCHASE_SEQUENCE:
                type: NUMBER(38,0)
                default:
                  sequence:
                    name: PURCHASE_SEQUENCE
                    schema: SALES
                    database: SALES_RECORD
              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
              APPLYBUDGET:
                - DEV_ROLE
              EVOLVE SCHEMA:
                - DEV_ROLE
              REBUILD:
                - DEV_ROLE

Examples with Constraints

- table:
    name: EMPLOYEE
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    columns:
      EMPLOYEE_ID:
        type: NUMBER(38,0)
        nullable: false
      FIRST_NAME:
        type: VARCHAR(50)
        nullable: false
      LAST_NAME:
        type: VARCHAR(50)
        nullable: true
      EMAIL:
        type: VARCHAR(100)
        nullable: false
      DEPARTMENT_ID:
        type: NUMBER(38,0)
        nullable: false
    comment: "Employee information"
    primary_key:
      keys:
        - EMPLOYEE_ID
      name: PK_EMPLOYEE
    unique:
      name: UK_EMPLOYEE_EMAIL
      keys:
        - EMAIL
    foreign_keys:
      - name: FK_EMPLOYEE_DEPARTMENT
        keys:
          - DEPARTMENT_ID
        referenced_table: rel(table.DEPARTMENT)
        referenced_keys:
          - DEPARTMENT_ID
databases:
  SALES_RECORD:
    schemas:
      SALES:
        tables:
          EMPLOYEE:
            columns:
              EMPLOYEE_ID:
                type: NUMBER(38,0)
                nullable: false
              FIRST_NAME:
                type: VARCHAR(50)
                nullable: false
              LAST_NAME:
                type: VARCHAR(50)
                nullable: true
              EMAIL:
                type: VARCHAR(100)
                nullable: false
              DEPARTMENT_ID:
                type: NUMBER(38,0)
                nullable: false
            comment: "Employee information"
            primary_key:
              keys:
                - EMPLOYEE_ID
              name: PK_EMPLOYEE
            unique:
              name: UK_EMPLOYEE_EMAIL
              keys:
                - EMAIL
            foreign_keys:
              - name: FK_EMPLOYEE_DEPARTMENT
                keys:
                  - DEPARTMENT_ID
                referenced_table: DEPARTMENT
                referenced_keys:
                  - DEPARTMENT_ID