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:

BigQuery

IN -> BigQuery_01.sql

CREATE TABLE table1 (
    col1 STRING 
) 
DEFAULT COLLATE 'und:ci';

Output Code:

Snowflake

OUT -> BigQuery_01.sql

CREATE TABLE table1 (
    col1 STRING
)
DEFAULT_DDL_COLLATION='und-ci';

Labels table option

Input Code:

BigQuery

IN -> BigQuery_02.sql

CREATE TABLE table1
(
  col1 INT,
  col2 DATE
)
OPTIONS(
  labels=[("org_unit", "development")]
);

Output Code:

Snowflake

OUT -> BigQuery_02.sql

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:

BigQuery

IN -> BigQuery_03.sql

CREATE TABLE table1
(
  col1 INT,
  col2 DATE
)
OPTIONS(
  description = 'My table comment'
);

Output Code:

Snowflake

OUT -> BigQuery_03.sql

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:

BigQuery

IN -> BigQuery_04.sql

CREATE TABLE table1
(
  col1 INT,
  col2 DATE
)
OPTIONS(
  friendly_name = 'Some_table'
);

Output Code:

Snowflake

OUT -> BigQuery_04.sql

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:

BigQuery

IN -> BigQuery_05.sql

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:

Snowflake

OUT -> BigQuery_05.sql

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:

BigQuery

IN -> BigQuery_06.sql

CREATE TABLE table1(
    transaction_id INT, 
    transaction_date DATE
)
PARTITION BY transaction_date;

Output Code:

Snowflake

OUT -> BigQuery_06.sql

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

1. SSC-EWI-BQ0001: Snowflake does not support the options clause.

2. SSC-EWI-BQ0002: Micro-partitioning is automatically performed on all Snowflake tables.