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
We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. Learn more about SOLE for Data Products which is currently available as a private preview.
- Table in Current Configuration
- Table in SOLE for Data Products
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 | Specifies whether to enable change tracking on the table. Default false |
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 | Integer | 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 |
manage_mode | Optional | String: all (default), none , grants | List of Privileges and Shares 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 |
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 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. |
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 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"
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"
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 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 defaults to the name of the database the table/column is in. |
schema | Optional | String | Schema of the Tag identifier. If omitted defaults to the name of the schema the table/column is in. |
Example
- Table in Current Configuration
- Table in SOLE for Data Products
databases:
DB1:
schemas:
SCHEMA1:
tables:
TABLE1:
with_tags:
DEPARTMENT:
value: Sales
schema: TAG_LIBRARY
CONFIDENTIALITY:
value: Sensitive
schema: TAG_LIBRARY
- 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
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
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.
- Table in Current Configuration
- Table in SOLE for Data Products
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
- 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"
- UPDATE to:
- HR_ROLE
- ACCOUNTADMIN
Inheriting Grants defined in Schema
- Table in Current Configuration
- Table in SOLE for Data Products
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
- 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"
- INSERT to:
- DEV_ROLE
- SYSADMIN
- TRUNCATE to:
- ACCOUNTADMIN
Overriding Grants in Table
- Table in Current Configuration
- Table in SOLE for Data Products
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
- 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"
With this configuration, the table PRODUCT
would have the following grants:
- SELECT to:
- DEV_ROLE
- UPDATE to:
- DEV_ROLE
Examples
- Table in Current Configuration
- Table in SOLE for Data Products
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
- table:
name: PRODUCT
database: rel(database.SALES_RECORD)
schema: rel(schema.SALES)
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:
- rel(role.DEV_ROLE)
UPDATE:
- rel(role.DEV_ROLE)