> For the complete documentation index, see [llms.txt](https://docs.snowddl.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.snowddl.com/basic/yaml-configs/table.md). # TABLE Config path: `///table/.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] ``` ## Schema * **columns** (dict) * *{key}* (ident) - column name * *{value}* (str) - full [data type](/guides/data-types.md) with optional "NOT NULL" constraint\ \--- OR --- * *{value}* (dict) * **type** (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) * **is\_transient** (bool) - make table TRANSIENT * **retention\_time** (int) - data retention time in days * **cluster\_by** (list) * *{items}* (str) - SQL expressions for CLUSTER BY * **change\_tracking** (bool) - enable CHANGE TRACKING * **search\_optimization** (bool) - enable SEARCH OPTIMIZATION on the whole table\ \--- OR --- * **search\_optimization** (dict) - enable SEARCH OPTIMIZATION on specific columns * *{key}* (str) - search optimization method (e.g. EQUALITY, SUBSTRING, GEO); * *{value}* (list) * *{items}* (str) - search optimization targets (column names and VARIANT column paths) * **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 ## Policy reference parameters * **aggregation\_policy** (dict) * **policy\_name** (ident) - name of [AGGREGATION POLICY](/basic/yaml-configs/aggregation-policy.md) * **columns** (list) * *{items}* (ident) - optional reference column names defining "entity" * **join\_policy** (dict) * **policy\_name** (ident) - name of [JOIN POLICY](/basic/yaml-configs/join-policy.md) * **columns** (list) * *{items}* (ident) - optional allowed join keys * **masking\_policies** (list) * *{items}* (dict) * **policy\_name** (ident) - name of [MASKING POLICY](/basic/yaml-configs/masking-policy.md) * **columns** (list) * *{items}* (ident) - reference column names * **projection\_policies** (list) * *{items}* (dict) * **policy\_name** (ident) - name of [PROJECTION POLICY](/basic/yaml-configs/projection-policy.md) * **column** (ident) - reference column name * **row\_access\_policy** (dict) * **policy\_name** (ident) - name of [ROW ACCESS POLICY](/basic/yaml-configs/row-access-policy.md) * **columns** (list) * *{items}* (ident) - reference column names ## 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 to apply changes which cannot be applied via `ALTER TABLE`. 5. **is\_transient** and **retention\_time** are inherited from parent [SCHEMA](/basic/yaml-configs/schema.md) and [DATABASE](/basic/yaml-configs/database.md) objects if omitted. 6. Currently only **standard column names are supported** for [SEARCH OPTIMIZATION on specific columns](https://docs.snowflake.com/en/sql-reference/sql/alter-table.html#search-optimization-actions-searchoptimizationaction). Paths for VARIANT columns are not supported due to high complexity of parsing the output of `DESC SEARCH OPTIMIZATION ON ...` command. We expect it to be improved in future, once this features leaves the "Public preview" status. ## Safe & unsafe operations The following operations on tables are considered as "safe": * Create a new table from scratch; * Add a new column to the end of existing table; * Change comment on table; * Change comment on specific column; All other operations are "unsafe". Additionally, in order to execute `CREATE OR REPLACE TABLE ... AS SELECT` , the following CLI option must be specified: `--apply-replace-table` ## Links * [CREATE TABLE](https://docs.snowflake.com/en/sql-reference/sql/create-table.html) * [ALTER TABLE](https://docs.snowflake.com/en/sql-reference/sql/alter-table.html) * [ALTER TABLE ... ALTER COLUMN](https://docs.snowflake.com/en/sql-reference/sql/alter-table-column.html) * [SHOW TABLE](https://docs.snowflake.com/en/sql-reference/sql/show-tables.html) * [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/table.py)