HYBRID TABLE

Config path: /<database>/<schema>/hybrid_table/<name>.yaml

Example:

columns:
  actor_id: NUMBER(10,0) NOT NULL
  first_name: VARCHAR(45) NOT NULL
  last_name: VARCHAR(45) NOT NULL
  last_update: TIMESTAMP_NTZ(3)

primary_key: [actor_id]

columns:
  ticket_no:
    type: VARCHAR(13) NOT NULL
    comment: "Ticket number"

  flight_id:
    type: NUMBER(10,0) NOT NULL
    comment: "Flight ID"

  boarding_no:
    type: NUMBER(10,0) NOT NULL
    comment: "Boarding pass number"

  seat_no:
    type: VARCHAR(4) NOT NULL
    comment: "Seat number"

primary_key: [ticket_no, flight_id]

unique_keys:
  - [flight_id, boarding_no]
  - [flight_id, seat_no]

foreign_keys:
  - columns: [ticket_no, flight_id]
    ref_table: ticket_flights
    ref_columns: [ticket_no, flight_id]

indexes:
  - columns: [flight_id]

Schema

• columns (dict)

  • {key} (ident) - column name

  • {value} (str) - full data type with optional "NOT NULL" constraint --- OR ---
  • {value} (dict)

    • type (str) - full data type with optional "NOT NULL" constraint
    • default (str) - default SQL expression

    • default_sequence (ident) - sequence used for "auto increment"

    • collate (str) - column collation for string comparison
    • comment (str)

• comment (str)

• primary_key (list)

  • {items} (ident) - column names for PRIMARY KEY constraint

• unique_keys (list)

  • {items} (list)

    • {items} (ident) - column names for UNIQUE KEY constraint

• foreign_keys (list)

  • {items} (dict) - FOREIGN KEY definitions

    • columns (list)

      • {items} (ident) - column names from current table

    • ref_table (ident) - reference table

    • ref_columns (list)

      • {items} (ident) - column names from reference table

• indexes (list)

  • {items} (dict) - INDEX definition

    • columns (list)

      • {items} (ident) - column names to be indexed

    • include (list)

      • {items} (ident) - column names to additionally include for covering index

Usage notes

1. Columns definition has two possible syntax options:

   a) Short syntax (str) with column type definition only. b) Full syntax (dict) with type definition as well as other properties.

2. Column type is a full native Snowflake data type definition, exactly how it appears in output of DESC TABLE command. Aliases and short forms are not allowed.
3. Column default is an SQL expression, not value. VARCHAR values should be enclosed in quotes. TIMESTAMP_* values should be casted explicitly.

4. Anonymous auto-increment is not supported. All sequences must be created explicitly and assigned to default_sequence of relevant table columns. It helps to preserve sequence value when table is re-created.

Safe & unsafe operations

At this moment only the following operations are "safe" for hybrid tables:

• Create a new hybrid table

• Add or remove unique key from hybrid table

• Add or remove foreign key from hybrid table

All other changes would be resolved as "unsafe" CREATE OR REPLACE TABLE ... AS SELECT command. In order to apply this command, you also need to specify the following CLI option: --apply-replace-table

Hybrid tables are designed to be relatively small (less than 100Gb), so replacement should be a relatively cheap operation. We may consider implementing basic table and secondary index transformations in future once Hybrid table feature is available in all regions and leaves "Public preview".

Links

• CREATE HYBRID TABLE
• SHOW HYBRID TABLES
• DESC TABLE
• Parser & JSON Schema (GitHub)