CREATE TABLE

Description

Creates a table in the logical schema. (Vertica SQL Language Reference Create Table).

This syntax is partially supported in Snowflake. Translation pending for these clauses:

DISK_QUOTA quota
SET USING expression
ENCODING encoding-type 
ACCESSRANK integer

Grammar Syntax

CREATE TABLE [ IF NOT EXISTS ] [[database.]schema.]table
   ( column-definition[,...] [, table-constraint [,...]] )
   [ ORDER BY column[,...] ]
   [ segmentation-spec ]
   [ KSAFE [safety] ]
   [ partition-clause]
   [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
   [ DISK_QUOTA quota ]
   
<column-definition> ::= 
column-name data-type
    [ column-constraint ][...]
    [ ENCODING encoding-type ]
    [ ACCESSRANK integer ]
    
<column-constraint> ::=
[ { AUTO_INCREMENT | IDENTITY } [ (args) ] ]
[ CONSTRAINT constraint-name ] {
   [ CHECK (expression) [ ENABLED | DISABLED ] ]
   [ [ DEFAULT expression ] [ SET USING expression } | DEFAULT USING expression ]
   [ NULL | NOT NULL ]
   [ { PRIMARY KEY [ ENABLED | DISABLED ] REFERENCES table [( column )] } ]
   [ UNIQUE [ ENABLED | DISABLED ] ]
}
    
<table-constraint>::=
[ CONSTRAINT constraint-name ]
{
... PRIMARY KEY (column[,... ]) [ ENABLED | DISABLED ]
... | FOREIGN KEY (column[,... ] ) REFERENCES table [ (column[,...]) ]
... | UNIQUE (column[,...]) [ ENABLED | DISABLED ]
... | CHECK (expression) [ ENABLED | DISABLED ]
}

Tables Options

Order By

In Vertica, this ORDER BY clause specifies how data is physically sorted within a superprojection, an optimized storage structure for a table. This explicit physical ordering at table creation is not directly supported in Snowflake. For more information please refer to SSC-EWI-VT0002.

Sample Source

Input Code:

Vertica

IN -> Vertica_01.sql

CREATE TABLE metrics 
(
  metric_id INT,
  business_unit VARCHAR(100),
  metric_category VARCHAR(50) NOT NULL,
  measurement_date DATE NOT NULL
)
ORDER BY measurement_date, business_unit, metric_category;

Output Code:

Snowflake

OUT -> Vertica_01.sql

CREATE TABLE metrics
(
  metric_id INT,
  business_unit VARCHAR(100),
  metric_category VARCHAR(50) NOT NULL,
  measurement_date DATE NOT NULL
)
!!!RESOLVE EWI!!! /*** SSC-EWI-VT0002 - ORDER BY TABLE OPTION IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
ORDER BY measurement_date, business_unit, metric_category
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "vertica",  "convertedOn": "06/17/2025",  "domain": "no-domain-provided" }}';

Projections Clauses

Vertica's projections are a mechanism to define and maintain the physical sort order of data on disk, thereby optimizing query performance for specific access patterns. Snowflake, however, utilizes a fundamentally different storage and optimization strategy. Data in Snowflake is automatically broken down into immutable micro-partitions, which are then organized and managed by the cloud service.

While an inherent order might exist within these micro-partitions due to insertion or the application of clustering keys, Snowflake's query optimizer and its underlying architecture are designed to efficiently prune these micro-partitions during query execution, regardless of a pre-defined global sort order. This approach, combined with automatic caching and a columnar storage format, allows Snowflake to achieve high performance without requiring users to manually define and manage physical data structures like Vertica's projections, thus simplifying data management and optimizing for a broader range of query patterns without explicit physical sort definitions.

Due to these reasons, the following clauses aren't necessary in Snowflake and are removed from the original code:

[ segmentation-spec ]
[ KSAFE [safety] ]
[ partition-clause]

Inherited Schema Privileges Clause

INCLUDE SCHEMA PRIVILEGES is a Vertica-specific feature that governs how privileges are inherited, in this case, potentially from the schema level. Snowflake does not have a direct equivalent for this clause within its CREATE TABLE syntax. Privileges in Snowflake are managed explicitly through GRANT statements.

This syntax is not supported in Snowflake.

Sample Source

Input Code:

Vertica

IN -> Vertica_02.sql

CREATE TABLE metrics 
(
  metric_id INT,
  business_unit VARCHAR(100),
  metric_category VARCHAR(50) NOT NULL,
  measurement_date DATE NOT NULL
)
INCLUDE SCHEMA PRIVILEGES;

Output Code:

Snowflake

OUT -> Vertica_02.sql

CREATE TABLE metrics
(
  metric_id INT,
  business_unit VARCHAR(100),
  metric_category VARCHAR(50) NOT NULL,
  measurement_date DATE NOT NULL
)
!!!RESOLVE EWI!!! /*** SSC-EWI-VT0001 - INHERITED PRIVILEGES CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
INCLUDE SCHEMA PRIVILEGES
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "vertica",  "convertedOn": "06/17/2025",  "domain": "no-domain-provided" }}';

Constraints

IDENTITY - AUTO_INCREMENT

Creates a table column whose values are automatically generated by and managed by the database. You cannot change or load values in this column. You can set this constraint on only one table column.

This syntax is fully supported in Snowflake.

Sample Source

Input Code:

Vertica

IN -> Vertica_03.sql

CREATE TABLE customers (
  id AUTO_INCREMENT(1, 2),
  name VARCHAR(50)
);

CREATE TABLE customers2 (
  id IDENTITY(1, 2),
  name VARCHAR(50)
);

Output Code:

Snowflake

OUT -> Vertica_03.sql

CREATE TABLE customers (
  id INT AUTOINCREMENT(1, 2) ORDER,
  name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "vertica",  "convertedOn": "06/17/2025",  "domain": "no-domain-provided" }}';

CREATE TABLE customers2 (
  id INT IDENTITY(1, 2) ORDER,
  name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "vertica",  "convertedOn": "06/17/2025",  "domain": "no-domain-provided" }}';

CHECK Constraint

The CHECK clause in Vertica requires new or updated rows to satisfy a Boolean expression. Snowflake doesn't have an equivalent to this clause; therefore, SnowConvert will add an EWI. This will be applied as a CHECK attribute or table constraint in the converted code.

This syntax is not supported in Snowflake.

Sample Source

Input Code:

Vertica

IN -> Vertica_04.sql

CREATE TABLE table1 (
    product_id INT PRIMARY KEY,
    quantity INT CHECK (quantity >= 0)
);

Output Code:

Snowflake

OUT -> Vertica_04.sql

CREATE TABLE table1 (
    product_id INT PRIMARY KEY,
    quantity INT
                 !!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!! CHECK (quantity >= 0)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "vertica",  "convertedOn": "06/17/2025",  "domain": "no-domain-provided" }}';

DEFAULT Constraint

This syntax is partially supported in Snowflake.

The basic DEFAULT clause from Vertica is fully supported and translates directly to Snowflake. For Vertica's DEFAULT USING clause, however, the translation is partial. Snowflake will correctly apply the DEFAULT value when new rows are inserted, but the deferred refresh capability from the USING portion has no direct equivalent and some expressions might not be supported in Snowflake. Therefore, a warning is added to highlight this functional difference.

Sample Source

Input Code:

Vertica

IN -> Vertica_05.sql

CREATE TABLE table1 (
    base_value INT,
    status_code INT DEFAULT 0,
    derived_value INT DEFAULT USING (base_value + 100)
);

Output Code:

Snowflake

OUT -> Vertica_05.sql

CREATE TABLE table1 (
    base_value INT,
    status_code INT DEFAULT 0,
    derived_value INT DEFAULT (base_value + 100) /*** SSC-FDM-VT0001 - EXPRESSION IN USING CONSTRAINT MIGHT NOT BE SUPPORTED IN SNOWFLAKE ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "vertica",  "convertedOn": "06/17/2025",  "domain": "no-domain-provided" }}';

PRIMARY KEY - UNIQUE - FOREIGN KEY

SnowConvert keeps the constraint definitions; however, in Snowflake, these properties are provided to facilitate migrating from other databases. They are not enforced or maintained by Snowflake. This means that the defaults can be changed for these properties, but changing the defaults results in Snowflake not creating the constraint.

This syntax is partially supported in Snowflake.

Sample Source

Input Code:

Vertica

IN -> Vertica_06.sql

CREATE OR REPLACE TABLE employees (
    emp_id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    CONSTRAINT pk_employees_enabled PRIMARY KEY (emp_id) ENABLED
);

Output Code:

Snowflake

OUT -> Vertica_06.sql

CREATE OR REPLACE TABLE employees (
    emp_id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    CONSTRAINT pk_employees_enabled PRIMARY KEY (emp_id) ENABLE
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "vertica",  "convertedOn": "06/17/2025",  "domain": "no-domain-provided" }}';

Related EWIs

1. SSC-EWI-0035: Check statement not supported.

2. SSC-EWI-VT0001: Inherited privileges clause is not supported in Snowflake.

3. SSC-EWI-VT0002: Order by table option is not supported in Snowflake.

4. SSC-FDM-VT0001: Expression in USING constraint might not be supported in Snowflake.