# 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) --- # 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/table.md?ask= ``` 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.