CREATE TABLE
Grammar syntax
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] TABLE [ IF NOT EXISTS ]
table_name
[(
column | constraint_definition[, ...]
)]
[DEFAULT COLLATE collate_specification]
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]Sample Source Patterns
DEFAULT COLLATE
Input Code:
CREATE TABLE table1 (
col1 STRING
)
DEFAULT COLLATE 'und:ci';Output Code:
CREATE TABLE table1 (
col1 STRING
)
DEFAULT_DDL_COLLATION='und-ci';Labels table option
Input Code:
CREATE TABLE table1
(
col1 INT,
col2 DATE
)
OPTIONS(
labels=[("org_unit", "development")]
);Output Code:
CREATE TAG IF NOT EXISTS "org_unit";
CREATE TABLE table1
(
col1 INT,
col2 DATE
)
WITH TAG( "org_unit" = "development" )
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "bigquery", "convertedOn": "04/09/2025", "domain": "test" }}'
;Description table option
Input Code:
CREATE TABLE table1
(
col1 INT,
col2 DATE
)
OPTIONS(
description = 'My table comment'
);Output Code:
CREATE TABLE table1
(
col1 INT,
col2 DATE
)
COMMENT = '{ "description": "My table comment", "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "bigquery", "convertedOn": "04/09/2025", "domain": "test" }}'
;Description table option
Input Code:
CREATE TABLE table1
(
col1 INT,
col2 DATE
)
OPTIONS(
friendly_name = 'Some_table'
);Output Code:
CREATE TABLE table1
(
col1 INT,
col2 DATE
)
COMMENT = '{ "friendly_name": "Some_table", "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "bigquery", "convertedOn": "04/09/2025", "domain": "test" }}'
;Known Issues
1. Unsupported table options
Not all table options are supported in Snowflake, when an unsupported table option is encountered in the OPTIONS clause, an EWI will be generated to warn about this.
Input Code:
CREATE TABLE table1
(
col1 INT,
col2 DATE
)
OPTIONS(
expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
partition_expiration_days=1,
description="a table that expires in 2025, with each partition living for 24 hours",
labels=[("org_unit", "development")]
);Output Code:
CREATE TAG IF NOT EXISTS "org_unit";
CREATE TABLE table1
(
col1 INT,
col2 DATE
)
WITH TAG( "org_unit" = "development" )
!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0001 - SNOWFLAKE DOES NOT SUPPORT THE OPTIONS: EXPIRATION_TIMESTAMP, PARTITION_EXPIRATION_DAYS. ***/!!!
OPTIONS(
expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
partition_expiration_days=1
)
COMMENT = '{ "description": "a table that expires in 2025, with each partition living for 24 hours", "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "bigquery", "convertedOn": "04/09/2025", "domain": "test" }}'
;2. Micro-partitioning is automatically managed by Snowflake
Snowflake performs automatic partitioning of data. User defined partitioning is not supported.
Input Code:
CREATE TABLE table1(
transaction_id INT,
transaction_date DATE
)
PARTITION BY transaction_date;Output Code:
CREATE TABLE table1 (
transaction_id INT,
transaction_date DATE
)
!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0002 - MICRO-PARTITIONING IS AUTOMATICALLY PERFORMED ON ALL SNOWFLAKE TABLES. ***/!!!
PARTITION BY transaction_date;Related EWIs
SSC-EWI-BQ0001: Snowflake does not support the options clause.
SSC-EWI-BQ0002: Micro-partitioning is automatically performed on all Snowflake tables.
Last updated