SnowConvert AI - Vertica - CREATE TABLE¶

Description¶

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

Warning

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

DISK_QUOTA quota
SET USING expression
ENCODING encoding-type 
ACCESSRANK integer
Copy

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 ]
}
Copy

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¶

Vertica¶
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;
Copy
Snowflake¶
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" }}';
Copy

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]
Copy

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.

Warning

This syntax is not supported in Snowflake.

Sample Source¶

Vertica¶
CREATE TABLE metrics 
(
  metric_id INT,
  business_unit VARCHAR(100),
  metric_category VARCHAR(50) NOT NULL,
  measurement_date DATE NOT NULL
)
INCLUDE SCHEMA PRIVILEGES;
Copy
Snowflake¶
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" }}';
Copy

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.

Success

This syntax is fully supported in Snowflake.

Sample Source¶

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

CREATE TABLE customers2 (
  id IDENTITY(1, 2),
  name VARCHAR(50)
);
Copy
Snowflake¶
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" }}';
Copy

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 AI will add an EWI. This will be applied as a CHECK attribute or table constraint in the converted code.

Danger

This syntax is not supported in Snowflake.

Sample Source¶

Vertica¶
CREATE TABLE table1 (
    product_id INT PRIMARY KEY,
    quantity INT CHECK (quantity >= 0)
);
Copy
Snowflake¶
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" }}';
Copy

DEFAULT Constraint¶

Warning

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¶

Vertica¶
CREATE TABLE table1 (
    base_value INT,
    status_code INT DEFAULT 0,
    derived_value INT DEFAULT USING (base_value + 100)
);
Copy
Snowflake¶
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" }}';
Copy

PRIMARY KEY - UNIQUE - FOREIGN KEY¶

SnowConvert AI 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.

Warning

This syntax is partially supported in Snowflake.

Sample Source¶

Vertica¶
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
);
Copy
Snowflake¶
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" }}';
Copy

CREATE TABLE AS¶

Description¶

Creates and loads a table from the results of a query. (Vertica SQL Language Reference Create Table).

Warning

This syntax is partially supported in Snowflake. Translation pending for the following clauses

[ /*+ LABEL */ ]
[ AT epoch ]
[ ENCODED BY column-ref-list ]
[ ENCODING encoding-type ]
[ ACCESSRANK integer ]
[ GROUPED ( column-reference[,...] ) ]
Copy

Grammar Syntax¶

CREATE TABLE [ IF NOT EXISTS ] [[database.]schema.]table
[ ( column-name-list ) ]
[ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
AS  [ /*+ LABEL */ ] [ AT epoch ] query [ ENCODED BY column-ref-list ] [ segmentation-spec ]

<column-name-list> ::=
column-name-list
    [ ENCODING encoding-type ]
    [ ACCESSRANK integer ]
    [ GROUPED ( column-reference[,...] ) ]
Copy

Tables Options¶

Segmentation Clause¶

This syntax isn’t required in Snowflake and is removed from the original code. For more information, please refer to Projections Clauses.

Note

This syntax is not required in Snowflake.

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. For more information please refer to Inherited Schema Privileges Clause.

Warning

This syntax is not supported in Snowflake.

Related EWIs¶

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

CREATE TABLE LIKE¶

Description¶

Creates the table by replicating an existing table. (Vertica SQL Language Reference Create Table).

Warning

This syntax is partially supported in Snowflake. Translation pending for the following clause:

DISK_QUOTA quota
Copy

Grammar Syntax¶

CREATE TABLE [ IF NOT EXISTS ] [[database.]schema.]table
  LIKE [[database.]schema.]existing-table
  [ {INCLUDING | EXCLUDING} PROJECTIONS ]
  [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
  [ DISK_QUOTA quota ]
Copy

Tables Options¶

Projections¶

This syntax isn’t required in Snowflake and is removed from the original code. For more information, please refer to Projections Clauses.

Warning

This syntax is not required in Snowflake.

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. For more information please refer to Inherited Schema Privileges Clause.

Warning

This syntax is not supported in Snowflake.

Related EWIs¶

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

Language: English