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
- Default Configuration
- Data Products Configuration
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>
- 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>
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 |
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 |
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 |
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 |
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 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 |