# 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](/guides/data-types.md) with optional "NOT NULL" constraint\
    \--- OR ---
  * *{value}* (dict)
    * <mark style="background-color:red;">**type**</mark> (str) - full [data type](/guides/data-types.md) 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](/guides/data-types.md).
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](/basic/yaml-configs/sequence.md) 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)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.snowddl.com/basic/yaml-configs/hybrid-table.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.