Comment on page

TABLE

Config path: /<database>/<schema>/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]
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)
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
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 to apply changes which cannot be applied via ALTER TABLE.
5.is_transient and retention_time are inherited from parent SCHEMA and DATABASE objects if omitted.
6.Currently only standard column names are supported for SEARCH OPTIMIZATION on specific columns. 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 , an additional CLI option must be specified:
 --apply-replace-table 
Links
​CREATE TABLE​
​ALTER TABLE​
​ALTER TABLE ... ALTER COLUMN​
​SHOW TABLE​
​DESC TABLE​
​Parser & JSON Schema (GitHub)​

STREAM

TASK

Last modified 7d ago