COLUMN DEFINITION

The table's schema information

Grammar syntax

column :=
  column_name column_schema

column_schema :=
   {
     simple_type
     | STRUCT<field_list>
     | ARRAY<array_element_schema>
   }
   [PRIMARY KEY NOT ENFORCED | REFERENCES table_name(column_name) NOT ENFORCED]
   [DEFAULT default_expression]
   [NOT NULL]
   [OPTIONS(column_option_list)]

simple_type :=
  { data_type | STRING COLLATE collate_specification }

field_list :=
  field_name column_schema [, ...]

array_element_schema :=
  { simple_type | STRUCT<field_list> }
  [NOT NULL]

For more information, please refer to BigQuery Column Definition.

---

Data types

Click here for more information about the translation spec for data types. Also see more information about data types in BigQuery here.

---

Primary Key Not Enforced

Primary Key Not Enforced is fully supported by Snowflake.

Sample Source

BigQuery (Input Code)

CREATE TABLE table1 (
    col1 integer PRIMARY KEY NOT ENFORCED
);

Snowflake (Output Code)

CREATE TABLE table1 (
    col1 integer PRIMARY KEY NOT ENFORCED
);

---

References Not Enforced

References Not Enforced is fully supported by Snowflake.

Sample Source

BigQuery (Input Code)

CREATE TABLE table1 (
    col1 integer, 
    col2 integer REFERENCES table_name(column_name) NOT ENFORCED
);

Snowflake (Output Code)

CREATE TABLE table1 (
    col1 integer, 
    col2 integer REFERENCES table_name(column_name) NOT ENFORCED
);

---

Default default_expression

The default value assigned to the column. For more information, please refer to specify default column values.

Default constraint is fully supported by Snowflake.

Sample Source

BigQuery (Input Code)

CREATE TABLE table1 (
    col1 integer, 
    col2 integer DEFAULT 10
);

Snowflake (Output Code)

CREATE TABLE table1 (
    col1 integer, 
    col2 integer DEFAULT 10
);

---

Not null

When the NOT NULL constraint is present for a column or field, the column or field is created with REQUIRED mode. Conversely, when the NOT NULL constraint is absent, the column or field is created with NULLABLE mode.

Not null constraint is fully supported by Snowflake.

Sample Source

BigQuery (Input Code)

CREATE TABLE table1 (
    col1 integer NOT NULL
);

Snowflake (Output Code)

CREATE TABLE table1 (
    col1 integer NOT NULL
);

---

Options

For more information, please refer to Column Option List.

Options clause is not supported by Snowflake.

Sample Source

BigQuery (Input Code)

CREATE TABLE table1 (
    col1 VARCHAR(20) OPTIONS(description="A repeated STRING field")
);

Snowflake (Output Code)

CREATE TABLE table1 (
    col1 VARCHAR(20)
--                     --** MSC-ERROR - MSC-BQ0001 - THE CREATE TABLE OPTIONS CLAUSE IS NOT SUPPORTED IN SNOWFLAKE. **
--                     OPTIONS(description="A repeated STRING field")
);

---

String Collate

Supported grammar in Snowflake with a minor change in the separation of the language_tag and the collation_attribute. In Snowflake you must use hyphen (-) as separator instead of the colon (:).

collation_specification: 'language_tag:collation_attribute'

For more information, please refer to Default collate specification.

String collate is fully supported by Snowflake.

Sample Source

BigQuery (Input Code)

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

Snowflake (Output Code)

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

---

Related EWIs

1. MSC-BQ0001: The Create Table OPTIONS clause is not supported in Snowflake.