SnowDDL
Search…
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)
  • cluster_by (list)
    • {items} (str) - SQL expressions for CLUSTER BY
  • change_tracking (bool) - enable CHANGE TRACKING
  • search_optimization (bool) - enable SEARCH OPTIMIZATION
  • 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. 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. 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. 3.
    Column default is an SQL expression, not value. VARCHAR values should be enclosed in quotes. TIMESTAMP_* values should be casted explicitly.
  4. 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.

Links

Last modified 3mo ago
Copy link
On this page
Schema
Usage notes
Links