Hybrid table support in Snowflake

Target database: Snowflake

Hybrid table support in Snowflake

See also:

• https://docs.snowflake.com/en/user-guide/tables-hybrid

• STORAGE.COLUMN_STORE

• PRIMARY_KEY

Usage

• Create entity with physical type TABLE

• Add physical option STORAGE.COLUMN_STORE with value FALSE

• Add primary key

Notes

To define a Hybrid Table in Snowflake it requires the entity to be physical type of TABLE and a primary key constraint to be set and also the physical option STORAGE.COLUMN_STORE to be set to false.

When the Hybrid Table is defined these features won’t be applied:

• Clustering key (SORT_KEY , INTERLEAVED_SORT_KEY )

• Transient table (STORAGE.TRANSIENT_TABLE )

• Change tracking (STORAGE.CHANGE_TRACKING )

• Time travel retention time in days (STORAGE.TIME_TRAVEL_RETENTION_TIME_IN_DAYS)

Hint

Snowflake Hybrid Tables support Indexes and this is enabled in Agile Data Engine by defining a key with a type SECONDARY_INDEX.

Examples

Hybrid Table

This example presents how a hybrid table would be created

Feature is enabled by:

Set Physical Option

STORAGE.COLUMN_STORE: false

Set Primary key

PK_CUSTOMER_ORDER: customer_id

Resulting DML generated by Agile Data Engine:

Snowflake SQL:

CREATE HYBRID TABLE publish.CUSTOMER_ORDER (
    order_id VARCHAR (40) NULL
    , customer_id VARCHAR (40) NULL
    , product_id VARCHAR (40) NULL
    , payment_amount NUMBER (10,2) NULL
    , CONSTRAINT PK_CUSTOMER_ORDER PRIMARY KEY ( customer_id )
);