# HYBRID TABLE

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

Example:

```yaml
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]
```

```yaml
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

* <mark style="background-color:red;">**columns**</mark> (dict)
  * *{key}* (ident) - column name
  * *{value}* (str) - full [data type](https://docs.snowddl.com/guides/data-types) with optional "NOT NULL" constraint\
    \--- OR ---
  * *{value}* (dict)
    * <mark style="background-color:red;">**type**</mark> (str) - full [data type](https://docs.snowddl.com/guides/data-types) with optional "NOT NULL" constraint
    * **default** (str) - default SQL expression
    * **default\_sequence** (ident) - sequence used for "auto increment"
    * **collate** (str) - column [collation](https://docs.snowflake.com/en/sql-reference/collation.html#label-collation-specification) for string comparison
    * **comment** (str)
* **comment** (str)
* <mark style="background-color:red;">**primary\_key**</mark> (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](https://docs.snowflake.com/en/sql-reference/sql/desc-table.html) command. Aliases and short forms [are not allowed](https://docs.snowddl.com/guides/data-types).
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](https://docs.snowddl.com/basic/yaml-configs/sequence) 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`&#x20;

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](https://docs.snowflake.com/en/sql-reference/sql/create-hybrid-table)
* [SHOW HYBRID TABLES](https://docs.snowflake.com/en/sql-reference/sql/show-hybrid-tables)
* [DESC TABLE](https://docs.snowflake.com/en/sql-reference/sql/desc-table.html)
* [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/hybrid_table.py)