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

CREATE SCHEMA IF NOT EXISTS SCHEMA1;

Collation Case

BigQuery

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

Snowflake

CREATE OR REPLACE SCHEMA SCHEMA1
DEFAULT_DDL_COLLATION = 'und-ci';

Description Option Case

BigQuery

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

Snowflake

CREATE SCHEMA IF NOT EXISTS SCHEMA1
COMMENT = 'Create Schema translation sample';

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

CREATE TAG IF NOT EXISTS "label1";
CREATE TAG IF NOT EXISTS "label2";

CREATE SCHEMA IF NOT EXISTS SCHEMA1
WITH TAG ("label1" = 'value1', "label2" = 'value2');

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

CREATE SCHEMA IF NOT EXISTS SCHEMA1
MAX_DATA_EXTENSION_TIME_IN_DAYS = 2;

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