CREATE SCHEMA

A SCHEMA is used to refer to a logical collection of tables, views, and other resources. This statement is supported in Snowflake.

Grammar Syntax

CREATE SCHEMA [ IF NOT EXISTS ]
[project_name.]dataset_name
[DEFAULT COLLATE collate_specification]
[OPTIONS(schema_option_list)]

Click here to go to the BigQuery specification for this syntax.

Schema Options Translation

BigQuery	Snowflake
default_kms_key_name	N/A
default_partition_expiration_days	N/A
default_rounding_mode	N/A
default_table_expiration_days	N/A
description	COMMENT
friendly_name	N/A
is_case_insensitive	N/A
labels	WITH TAG
location	N/A
max_time_travel_hours	MAX_DATA_EXTENSION_TIME_IN_DAYS
primary_replica	N/A
storage_billing_model	N/A

Not supported options are removed from the output code.

Basic Case

BigQuery

CREATE SCHEMA IF NOT EXISTS SCHEMA1;

Snowflake

Collation Case

BigQuery

CREATE SCHEMA IF NOT EXISTS SCHEMA1
DEFAULT COLLATE 'und:ci';

Snowflake

Description Option Case

BigQuery

CREATE SCHEMA IF NOT EXISTS SCHEMA1
OPTIONS(
  description = 'Create Schema translation sample'
);

Snowflake

Labels Option Case

For labels option translation, a CREATE TAG statement should be executed before the SCHEMA definition.

BigQuery

CREATE SCHEMA IF NOT EXISTS SCHEMA1
OPTIONS(
    labels=[("label1","value1"),("label2","value2")]
);

Snowflake

MAX_TIME_TRAVEL_HOURS Option Case

The equivalent for max_time_travel_hours option in Snowflake is MAX_DATA_EXTENSION_TIME_IN_DAYS, however, the value should be cast to days since the original value is in hours.

BigQuery

CREATE SCHEMA IF NOT EXISTS SCHEMA1
OPTIONS(
  max_time_travel_hours = 48
);

Snowflake

Hours may be lost if the value is not divisible by 24. The final value would be rounded to the closer integer value.