Hybrid Table

You can provide configuration to Snowflake Object Lifecycle Engine (SOLE) for the following operations with a hybrid table:

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

Usage

Default Configuration

databases:
  <database-name>:
    schemas:
      <schema-name>:
        hybrid_tables:
          <hybrid 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>

Data Products Configuration

- hybrid_table:
    name: <hybrid 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

SOLE supports the parameters listed below.

Configuration Key	Required/Optional	Data Types and Values	Description
columns	Required	Object: See here for a definition of columns	Defines a column to create in the hybrid table. At least one is required.
comment	Optional	String	Specifies a comment for the hybrid table
change_tracking	Optional	Boolean: false (default)	Specifies whether to enable change tracking on the hybrid table
cluster_by	Optional	List of String	A list of one or more hybrid table columns or expressions you can use as clustering key(s) for the hybrid table
data_retention_days	Integer	Integer: For standard edition 0 or 1, and for enterprise edition 0 to 90 (permanent hybrid tables) and 0 or 1 (transient hybrid table)	Specifies the retention period for the hybrid table so that Time Travel actions (SELECT, CLONE, UNDROP) can be performed on historical data in the hybrid table. The default value is inherited from the parent schema.
deleted	Optional	Boolean: true enables deletion prevention, false does nothing	Specifies what objects can be deleted
grants	Optional	Map: See Supported hybrid table grants to roles	List of Privileges and Roles to which privileges are granted on the current hybrid table
manage_mode	Optional	String: all (default), none, grants	Configures what properties to manage for the hybrid table. See Changing Manage Mode before changing the value.
primary_key	Optional	Block Map (max 1): See here for a definition of primary_key	Defines the primary key constraint to create on the hybrid table
shares	Optional	Map: See Supported hybrid table grants to shares	List of Privileges and Roles to which privileges are granted on the current hybrid table
with_tags	Optional	Object: See here for a definition of with_tags	List of tag references to apply to the current hybrid table

columns parameter

You can specify the columns of the hybrid table to manage.

List multiple columns in an object format in the columns parameter, each with 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	Defines 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 a definition of with_tags	List of tag references to be applied to the current column

Example

databases:
  <database-name>:
    schemas:
      <schema-name>:
        hybrid_tables:
          <table-name>:
            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

databases:
  <database-name>:
    schemas:
      <schema-name>:
        hybrid_tables:
          <table-name>:
            columns:
              column_1:
                Type: text
              COLUMN_2:
                type: "TIMESTAMP_NTZ"
                comment: "Test column 2"
                nullable: true
                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

databases:
  <database-name>:
    schemas:
      <schema-name>:
        hybrid_tables:
          <table-name>:
            columns:
              NAME:
                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

databases:
  <database-name>:
    schemas:
      <schema-name>:
        hybrid_tables:
          <table-name>:
            primary_key:
              keys:
                - NAME
                - YEAR
            name: "PK_NAME_YEAR"

with_tags parameter

You can specify a list of tags to apply to the hybrid 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 hybrid table/column is in.
schema	Optional	String	Schema of the Tag identifier. If omitted, it defaults to the name of the schema the hybrid table/column is in.

Example

Default Configuration

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

Data Products Configuration

- hybrid_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 hybrid 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 hybrid table grants to roles

Following are the privileges you can grant to roles in the hybrid 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 hybrid table grants to shares

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

• SELECT

Inherited grants

You can define grants for a hybrid table in one of three sections:

• Hybrid Table definition with parameter grants
• Schema definition with parameter hybrid table_grants
• Database definition with parameter hybrid table_grants

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

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

Example

Inheriting grants defined in a database

Default Configuration

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

Data Products Configuration

- database:
    name: SALES_RECORD
    hybrid_table_grants:
      UPDATE:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
- schema:
    name: SALES
    database: rel(database.SALES_RECORD)
- hybrid_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"

With this configuration, the hybrid table PRODUCT will have the following grants:

• UPDATE to:
  • HR_ROLE
  • ACCOUNTADMIN

Inheriting grants defined in a schema

Default Configuration

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

Data Products Configuration

- database:
    name: SALES_RECORD
    hybrid_table_grants:
      UPDATE:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
- schema:
    name: SALES
    database: rel(database.SALES_RECORD)
    hybrid_table_grants:
      INSERT:
        - DEV_ROLE
        - SYSADMIN
      TRUNCATE:
        - ACCOUNTADMIN
- hybrid_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"

With this configuration, the hybrid table PRODUCT will have the following grants:

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

Overriding grants in a hybrid table

Default Configuration

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

Data Products Configuration

- database:
    name: SALES_RECORD
    hybrid_table_grants:
      UPDATE:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
- schema:
    name: SALES
    database: rel(database.SALES_RECORD)
    hybrid_table_grants:
      INSERT:
        - DEV_ROLE
        - SYSADMIN
      TRUNCATE:
        - ACCOUNTADMIN
- hybrid_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 hybrid table PRODUCT will have the following grants:

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

Examples

Default Configuration

databases:
  SALES_RECORD:
    schemas:
      SALES:
        hybrid_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

Data Products Configuration

- hybrid_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)