CREATE TABLE¶

Creates a new table in the current/specified schema, replaces an existing table, or alters an existing table. A table can have multiple columns, with each column definition consisting of a name, data type, and optionally whether the column:

Requires a value (NOT NULL).
Has a default value.
Has any referential integrity constraints (primary key, foreign key, etc.).

In addition, this command supports the following variants:

CREATE OR ALTER TABLE (creates a table if it doesn’t exist, or alters it according to the table definition)
CREATE TABLE … AS SELECT (creates a populated table; also referred to as CTAS)
CREATE TABLE … USING TEMPLATE (creates a table with the column definitions derived from a set of staged files)
CREATE TABLE … LIKE (creates an empty copy of an existing table)
CREATE TABLE … CLONE (creates a clone of an existing table)

See also:: ALTER TABLE , DROP TABLE , SHOW TABLES , DESCRIBE TABLE

Syntax¶

CREATE [ OR REPLACE ]
    [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE | TRANSIENT } ]
  TABLE [ IF NOT EXISTS ] <table_name>

  (
    -- Column definition
    <col_name> <col_type>
      [ inlineConstraint ]
      [ NOT NULL ]
      [ COLLATE '<collation_specification>' ]
      [
        {
          DEFAULT <expr>
          | { AUTOINCREMENT | IDENTITY }
            [
              {
                ( <start_num> , <step_num> )
                | START <num> INCREMENT <num>
              }
            ]
            [ { ORDER | NOORDER } ]
        }
      ]
      [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
      [ [ WITH ] PROJECTION POLICY <policy_name> ]
      [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

    -- Out-of-line constraints
    [ , outoflineConstraint [ ... ] ]
  )

  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE } ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] AGGREGATION POLICY <policy_name> [ ENTITY KEY ( <col_name> [ , <col_name> ... ] ) ] ]
  [ [ WITH ] JOIN POLICY <policy_name> [ ALLOWED JOIN KEYS ( <col_name> [ , ... ] ) ] ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ WITH CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]

Copy

Where:

col_name is an object identifier. It must follow the requirements for Snowflake identifiers.

col_type is one of the Snowflake data types, such as NUMBER or VARCHAR.

inlineConstraint ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE
    | PRIMARY KEY
    | [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> ) ]
  }
  [ <constraint_properties> ]

Copy

For additional inline constraint details, see CREATE | ALTER TABLE … CONSTRAINT.

outoflineConstraint ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE [ ( <col_name> [ , <col_name> , ... ] ) ]
    | PRIMARY KEY [ ( <col_name> [ , <col_name> , ... ] ) ]
    | [ FOREIGN KEY ] [ ( <col_name> [ , <col_name> , ... ] ) ]
      REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ]
  }
  [ <constraint_properties> ]
  [ COMMENT '<string_literal>' ]

Copy

For additional out-of-line constraint details, see CREATE | ALTER TABLE … CONSTRAINT.

Note

Do not specify copy options using the CREATE STAGE, ALTER STAGE, CREATE TABLE, or ALTER TABLE commands. We recommend that you use the COPY INTO <table> command to specify copy options.

Variant syntax¶

CREATE OR ALTER TABLE¶

Creates a table if it doesn’t exist, or alters it according to the table definition. The CREATE OR ALTER TABLE syntax follows the rules of a CREATE TABLE statement and has the same limitations as an ALTER TABLE statement. If the table is transformed, existing data in the table is preserved when possible. If a column must be dropped, data loss might occur.

The following changes are supported when altering a table:

Change table properties and parameters. For example, ENABLE_SCHEMA_EVOLUTION, DATA_RETENTION_TIME_IN_DAYS, or CLUSTER BY.
Change column data type, default value, nullability, comment, or autoincrement.
Add new columns to the end of the column list.
Drop columns.
Add, drop, or modify inline or out-of-line constraints.
Add, drop, or modify clustering keys.

For more information, see CREATE OR ALTER TABLE usage notes.

CREATE OR ALTER
    [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | TRANSIENT } ]
  TABLE <table_name> (
    -- Column definition
    <col_name> <col_type>
      [ inlineConstraint ]
      [ NOT NULL ]
      [ COLLATE '<collation_specification>' ]
      [
        {
          DEFAULT <expr>
          | { AUTOINCREMENT | IDENTITY }
            [
              {
                ( <start_num> , <step_num> )
                | START <num> INCREMENT <num>
              }
            ]
            [ { ORDER | NOORDER } ]
        }
      ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

    -- Out-of-line constraints
    [ , outoflineConstraint [ ... ] ]
  )
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE } ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COMMENT = '<string_literal>' ]

Copy

CREATE TABLE … AS SELECT (also referred to as CTAS)¶

Creates a new table populated with the data returned by a query:

CREATE [ OR REPLACE ] TABLE <table_name> [ ( <col_name> [ <col_type> ] , <col_name> [ <col_type> ] , ... ) ]
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ COPY GRANTS ]
  [ ... ]
  AS <query>

Copy

A masking policy can be applied to a column in a CTAS statement. Specify the masking policy after the column data type. Similarly, a row access policy can be applied to the table. For example:

CREATE TABLE <table_name> ( <col1> <data_type> [ WITH ] MASKING POLICY <policy_name> [ , ... ] )
  ...
  [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col1> [ , ... ] )
  [ ... ]
  AS <query>

Copy

Note

In a CTAS statement, the COPY GRANTS clause is valid only when combined with the OR REPLACE clause. COPY GRANTS copies permissions from the table being replaced with CREATE OR REPLACE (if it already exists), not from the source table(s) being queried in the SELECT statement. CTAS with COPY GRANTS allows you to overwrite a table with a new set of data while keeping existing grants on that table.

For more details about COPY GRANTS, see COPY GRANTS in this document.

CREATE TABLE … USING TEMPLATE¶

Creates a new table with the column definitions derived from a set of staged files using the INFER_SCHEMA function. This feature supports Apache Parquet, Apache Avro, ORC, JSON, and CSV files.

CREATE [ OR REPLACE ] TABLE <table_name>
  [ COPY GRANTS ]
  USING TEMPLATE <query>
  [ ... ]

Copy

Note

If the statement is replacing an existing table of the same name, then the grants are copied from the table being replaced. If there is no existing table of that name, then the grants are copied from the source table being cloned.

For more details about COPY GRANTS, see COPY GRANTS in this document.

CREATE TABLE … LIKE¶

Creates a new table with the same column definitions as an existing table, but without copying data from the existing table. Column names, types, defaults, and constraints are copied to the new table:

CREATE [ OR REPLACE ] TABLE <table_name> LIKE <source_table>
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ COPY GRANTS ]
  [ ... ]

Copy

For more details about COPY GRANTS, see COPY GRANTS in this document.

Note

CREATE TABLE … LIKE for a table with an auto-increment sequence accessed through a data share is currently not supported.

CREATE TABLE … CLONE¶

Creates a new table with the same column definitions and containing all the existing data from the source table, without actually copying the data. This variant can also be used to clone a table at a specific time/point in the past (using Time Travel):

CREATE [ OR REPLACE ]
    [ {
          [ { LOCAL | GLOBAL } ] TEMP [ READ ONLY ] |
          TEMPORARY [ READ ONLY ] |
          VOLATILE |
          TRANSIENT
    } ]
  TABLE <name> CLONE <source_table>
    [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
    [ COPY GRANTS ]
    [ ... ]

Copy

Note

If the statement is replacing an existing table of the same name, then the grants are copied from the table being replaced. If there is no existing table of that name, then the grants are copied from the source table being cloned.
If you directly clone a table, any streams on that table are not cloned.
If you clone a schema including tables with streams, then the streams are also cloned.

For more details about COPY GRANTS, see COPY GRANTS in this document.

For more details about cloning, see CREATE <object> … CLONE.

Required parameters¶

name

Specifies the identifier (i.e. name) for the table; must be unique for the schema in which the table is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

col_name

Specifies the column identifier (i.e. name). All the requirements for table identifiers also apply to column identifiers.

For more details, see Identifier requirements and Reserved & limited keywords.

Note

In addition to the standard reserved keywords, the following keywords cannot be used as column identifiers because they are reserved for ANSI-standard context functions:

CURRENT_DATE
CURRENT_ROLE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER

For the list of reserved keywords, see Reserved & limited keywords.

col_type

Specifies the data type for the column.

For details about the data types that can be specified for table columns, see SQL data types reference.

query

Required for CTAS and USING TEMPLATE.

For CTAS, specifies the SELECT statement that populates the table. This query must be specified last in the CTAS statement, regardless of the other parameters that you include.
For CREATE TABLE … USING TEMPLATE, specifies the subquery that calls the INFER_SCHEMA function and formats the output as an array. Alternatively, USING TEMPLATE accepts the INFER_SCHEMA output as a string literal or variable.

source_table

Required for LIKE and CLONE.

For CREATE TABLE … LIKE, specifies the table from which properties and column definitions are copied.
For CREATE TABLE … CLONE, specifies the table to use as the source for the clone.

Optional parameters¶

{ [ { LOCAL | GLOBAL } ] TEMP [ READ ONLY] | . TEMPORARY [ READ ONLY] | . VOLATILE | . TRANSIENT }

Specifies that the table persists only for the duration of the session that you created it in. A temporary table and all its contents are dropped at the end of the session.

The synonyms and abbreviations for TEMPORARY (e.g. GLOBAL TEMPORARY) are provided for compatibility with other databases (e.g. to prevent errors when migrating CREATE TABLE statements). Tables created with any of these keywords appear and behave identically to a table created with the TEMPORARY keyword.

Default: No value. If a table is not declared as TEMPORARY or TRANSIENT, the table is permanent.

If you want to avoid unexpected conflicts, avoid naming temporary tables after tables that already exist in the schema.

If you created a temporary table with the same name as another table in the schema, all queries and operations used on the table only affect the temporary table in the session, until you drop the temporary table. If you drop the table, you drop the temporary table, and not the table that already exists in the schema.

For information about temporary or transient tables, and how they can affect storage and cost, refer to the following resources:

READ ONLY

Specifies that the table is read-only. READ ONLY is valid only for a temporary table that is being created with the CREATE TABLE … CLONE variant of the CREATE TABLE command.

A read-only table does not allow DML operations and only allows the following subset of DDL operations:

ALTER TABLE … { ALTER | MODIFY } COLUMN … { SET | UNSET } COMMENT
ALTER TABLE … { ALTER | MODIFY } COLUMN … { SET | UNSET } MASKING POLICY
ALTER TABLE … { ALTER | MODIFY } COLUMN … { SET | UNSET } TAG
ALTER TABLE … RENAME COLUMN … TO
ALTER TABLE … RENAME TO
ALTER TABLE … { SET | UNSET } COMMENT
ALTER TABLE … { SET | UNSET } TAG
COMMENT
DESCRIBE
DROP
SHOW
UNDROP

Read-only tables have a METADATA$ROW_POSITION column. This metadata column assigns a row number to each row in the table that is continuous and starts from 0. The row number assigned to each row remains unchanged until the read-only table is dropped.

TRANSIENT

Specifies that the table is transient.

Like a permanent table, a transient table exists until explicitly dropped and is visible to any user with the appropriate privileges. However, transient tables have a lower level of data protection than permanent tables, meaning that data in a transient table might be lost in the event of a system failure. As such, transient tables should only be used for data that can be recreated externally to Snowflake.

Default: No value. If a table is not declared as TRANSIENT or TEMPORARY, the table is permanent.

Note

Transient tables have some storage considerations.

For more information about these and other considerations when deciding whether to create temporary or transient tables, see Working with Temporary and Transient Tables and Storage Costs for Time Travel and Fail-safe.

CONSTRAINT ...

Defines an inline or out-of-line constraint for the specified column(s) in the table.

For syntax details, see CREATE | ALTER TABLE … CONSTRAINT. For more information about constraints, see Constraints.

COLLATE 'collation_specification'

Specifies the collation to use for column operations such as string comparison. This option applies only to text columns (VARCHAR, STRING, TEXT, etc.). For more details, see Collation specifications.

DEFAULT ... or . AUTOINCREMENT ...

Specifies whether a default value is automatically inserted in the column if a value is not explicitly specified via an INSERT or CREATE TABLE AS SELECT statement:

DEFAULT expr
Column default value is defined by the specified expression which can be any of the following:

Constant value.

Sequence reference (seq_name.NEXTVAL).

Simple expression that returns a scalar value.

The simple expression can include a SQL UDF (user-defined function) if the UDF is not a secure UDF.

Note

If a default expression refers to a SQL UDF, then the function is replaced by its definition at table creation time. If the user-defined function is redefined in the future, this does not update the column’s default expression.

The simple expression cannot contain references to:

Subqueries.

Aggregates.

Window functions.

Secure UDFs.

UDFs written in languages other than SQL (e.g. Java, JavaScript).

External functions.

{ AUTOINCREMENT | IDENTITY } . [ { ( start_num , step_num ) | START num INCREMENT num } ] . [ { ORDER | NOORDER } ]
When you specify AUTOINCREMENT or IDENTITY, the default value for the column starts with a specified number and each successive value automatically increments by the specified amount.

AUTOINCREMENT and IDENTITY are synonymous and can be used only for columns with numeric data types, such as NUMBER, INT, FLOAT.

Caution

Snowflake uses a sequence to generate the values for an auto-incremented column. Sequences have limitations; see Sequence Semantics.

The default value for both the start value and the step/increment value is 1.

Note

Manually inserting values into an AUTOINCREMENT or IDENTITY column can result in duplicate values. If you manually insert the value 5 into an AUTOINCREMENT or IDENTITY column, a subsequently inserted row might use the same value 5 as the default value for the column.

Use ORDER or NOORDER to specify whether or not the values are generated for the auto-incremented column in increasing or decreasing order.

ORDER specifies that the values generated for a sequence or auto-incremented column are in increasing order (or, if the interval is a negative value, in decreasing order).

For example, if a sequence or auto-incremented column has START 1 INCREMENT 2, the generated values might be 1, 3, 5, 7, 9, etc.

NOORDER specifies that the values are not guaranteed to be in increasing order.

For example, if a sequence has START 1 INCREMENT 2, the generated values might be 1, 3, 101, 5, 103, etc.

NOORDER can improve performance when multiple INSERT operations are performed concurrently (for example, when multiple clients are executing multiple INSERT statements).

If you do not specify ORDER or NOORDER, the NOORDER_SEQUENCE_AS_DEFAULT parameter determines which property is set.

Note

DEFAULT and AUTOINCREMENT are mutually exclusive; only one can be specified for a column.

MASKING POLICY = policy_name

Specifies the masking policy to set on a column.

This parameter is not supported by the CREATE OR ALTER variant syntax.

PROJECTION POLICY policy_name

Specifies the projection policy to set on a column.

This parameter is not supported by the CREATE OR ALTER variant syntax.

COMMENT 'string_literal'

Specifies a comment for the column.

(Note that comments can be specified at the column level or the table level. The syntax for each is slightly different.)

USING ( col_name , cond_col_1 ... )

Specifies the arguments to pass into the conditional masking policy SQL expression.

The first column in the list specifies the column for the policy conditions to mask or tokenize the data and must match the column to which the masking policy is set.

The additional columns specify the columns to evaluate to determine whether to mask or tokenize the data in each row of the query result when a query is made on the first column.

If the USING clause is omitted, Snowflake treats the conditional masking policy as a normal masking policy.

CLUSTER BY ( expr [ , expr , ... ] )

Specifies one or more columns or column expressions in the table as the clustering key. For more details, see Clustering Keys & Clustered Tables.

Default: No value (no clustering key is defined for the table)

Important

Clustering keys are not intended or recommended for all tables; they typically benefit very large (i.e. multi-terabyte) tables.

Before you specify a clustering key for a table, you should understand micro-partitions. For more information, see Understanding Snowflake Table Structures.

ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE }

Enables or disables automatic changes to the table schema from data loaded into the table from source files, including:

Added columns.

By default, schema evolution is limited to a maximum of 10 added columns per load operation. To request more than 10 added columns per load operation, contact Snowflake Support.

The NOT NULL constraint can be dropped from any number of columns missing in new data files.

Setting it to TRUE enables automatic table schema evolution. The default FALSE disables automatic table schema evolution.

Note

Loading data from files evolves the table columns when all of the following are true:

The COPY INTO <table> statement includes the MATCH_BY_COLUMN_NAME option.
The role used to load the data has the EVOLVE SCHEMA or OWNERSHIP privilege on the table.

Additionally, for schema evolution with CSV, when used with MATCH_BY_COLUMN_NAME and PARSE_HEADER, ERROR_ON_COLUMN_COUNT_MISMATCH must be set to false.

DATA_RETENTION_TIME_IN_DAYS = integer

Specifies the retention period for the table so that Time Travel actions (SELECT, CLONE, UNDROP) can be performed on historical data in the table. For more details, see Understanding & using Time Travel and Working with Temporary and Transient Tables.

For a detailed description of this object-level parameter, as well as more information about object parameters, see Parameters.

Values:

Standard Edition: 0 or 1

Enterprise Edition:

0 to 90 for permanent tables

0 or 1 for temporary and transient tables

Default:

Standard Edition: 1

Enterprise Edition (or higher): 1 (unless a different default value was specified at the schema, database, or account level)

Note

A value of 0 effectively disables Time Travel for the table.

MAX_DATA_EXTENSION_TIME_IN_DAYS = integer

Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for the table to prevent streams on the table from becoming stale.

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

CHANGE_TRACKING = { TRUE | FALSE }

Specifies whether to enable change tracking on the table.

TRUE enables change tracking on the table. This setting adds a pair of hidden columns to the source table and begins storing change-tracking metadata in the columns. These columns consume a small amount of storage.

The change-tracking metadata can be queried using the CHANGES clause for SELECT statements, or by creating and querying one or more streams on the table.
FALSE does not enable change tracking on the table.

Default: FALSE

DEFAULT_DDL_COLLATION = 'collation_specification'

Specifies a default collation specification for the columns in the table, including columns added to the table in the future.

For more details about the parameter, see DEFAULT_DDL_COLLATION.

COPY GRANTS

Specifies to retain the access privileges from the original table when a new table is created using any of the following CREATE TABLE variants:

CREATE OR REPLACE TABLE

CREATE TABLE … LIKE

CREATE TABLE … CLONE

The parameter copies all privileges, except OWNERSHIP, from the existing table to the new table. The new table does not inherit any future grants defined for the object type in the schema. By default, the role that executes the CREATE TABLE statement owns the new table.

If the parameter is not included in the CREATE TABLE statement, then the new table does not inherit any explicit access privileges granted on the original table, but does inherit any future grants defined for the object type in the schema.

Note:

With data sharing:

If the existing table was shared to another account, the replacement table is also shared.

If the existing table was shared with your account as a data consumer, and access was further granted to other roles in the account (using GRANT IMPORTED PRIVILEGES on the parent database), access is also granted to the replacement table.

The SHOW GRANTS output for the replacement table lists the grantee for the copied privileges as the role that executed the CREATE TABLE statement, with the current timestamp when the statement was executed.

The operation to copy grants occurs atomically in the CREATE TABLE command (i.e. within the same transaction).

This parameter is not supported by the CREATE OR ALTER variant syntax.

COMMENT = 'string_literal'

Specifies a comment for the table.

Default: No value

(Note that comments can be specified at the column level, constraint level, or table level. The syntax for each is slightly different.)

ROW ACCESS POLICY policy_name ON ( col_name [ , col_name ... ] )

Specifies the row access policy to set on a table.

This parameter is not supported by the CREATE OR ALTER variant syntax.

AGGREGATION POLICY policy_name [ ENTITY KEY ( col_name [ , col_name ... ] ) ]

Specifies an aggregation policy to set on a table. You can apply one or more aggregation policies on a table.

Use the optional ENTITY KEY parameter to define which columns uniquely identity an entity within the table. For more information, see Implementing entity-level privacy with aggregation policies. You can specify one or more entity keys for an aggregation policy.

This parameter is not supported by the CREATE OR ALTER variant syntax.

JOIN POLICY policy_name [ ALLOWED JOIN KEYS ( col_name [ , ... ] ) ]

Specifies the join policy to set on a table.

Use the optional ALLOWED JOIN KEYS parameter to define which columns are allowed to be used as joining columns when this policy is in effect. For more information, see Join policies.

This parameter is not supported by the CREATE OR ALTER variant syntax.

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quota for objects.

This parameter is not supported by the CREATE OR ALTER variant syntax.

WITH CONTACT ( purpose = contact [ , purpose = contact ...] ): Preview Feature — Open

Available to all accounts.

Associate the new object with one or more contacts.

Access control requirements¶

A role used to execute this operation must have the following privileges at a minimum:

Privilege	Object	Notes
CREATE TABLE	Schema	Note that creating a temporary table does not require the CREATE TABLE privilege.
SELECT	Table, external table, view	Required on queried tables and/or views only when cloning a table or executing CTAS statements.
APPLY	Masking policy, row access policy, tag	Required only when applying a masking policy, row access policy, object tags, or any combination of these governance features when creating tables.
USAGE (external stage) or READ (internal stage)	Stage	Required to derive table column definitions from staged files using CREATE TABLE … USING TEMPLATE statements.
OWNERSHIP	Table	A role must be granted or inherit the OWNERSHIP privilege on the object to create a temporary object that has the same name as the object that already exists in the schema. Required to execute a CREATE OR ALTER TABLE statement for an existing table. OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege). Note that in a managed access schema, only the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) or a role with the MANAGE GRANTS privilege can grant or revoke privileges on objects in the schema, including future grants.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

A schema cannot contain tables and/or views with the same name. When creating a table:
- If a view with the same name already exists in the schema, an error is returned and the table is not created.
- If a table with the same name already exists in the schema, an error is returned and the table is not created, unless the optional OR REPLACE keyword is included in the command.
  
  Important
  
  Using OR REPLACE is the equivalent of using DROP TABLE on the existing table and then creating a new table with the same name; however, the dropped table is not permanently removed from the system. Instead, it is retained in Time Travel. This is important to note because dropped tables in Time Travel can be recovered, but they also contribute to data storage for your account. For more information, see Storage Costs for Time Travel and Fail-safe.
  
  CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.
  
  This means that any queries concurrent with the CREATE OR REPLACE TABLE operation use either the old or new table version.
  
  Recreating or swapping a table drops its change data. Any stream on the table becomes stale. In addition, any stream on a view that has this table as an underlying table, becomes stale. A stale stream is unreadable.
The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.
Similar to reserved keywords, ANSI-reserved function names (CURRENT_DATE, CURRENT_TIMESTAMP, etc.) cannot be used as column names.
CREATE OR ALTER TABLE:

For more information, see CREATE OR ALTER TABLE usage notes.
CREATE TABLE … CLONE:

If the source table has clustering keys, then the new table has clustering keys. By default, Automatic Clustering is suspended for the new table – even if Automatic Clustering was not suspended for the source table.
CREATE TABLE … CHANGE_TRACKING = TRUE:

When change tracking is enabled, the table is locked for the duration of the operation. Locks can cause latency with some associated DDL/DML operations. For more information, refer to Resource locking.
CREATE TABLE … LIKE:

If the source table has clustering keys, then the new table has clustering keys. By default, Automatic Clustering is not suspended for the new table – even if Automatic Clustering was suspended for the source table.
CREATE TABLE … AS SELECT (CTAS):
- If the aliases for the column names in the SELECT list are valid columns, then the column definitions are not required in the CTAS statement; if omitted, the column names and types are inferred from the underlying query:
  CREATE TABLE <table_name> AS SELECT ...
  
  Copy
  Alternatively, the names can be explicitly specified using the following syntax:
  CREATE TABLE <table_name> ( <col1_name> , <col2_name> , ... ) AS SELECT ...
  
  Copy
  The number of column names specified must match the number of SELECT list items in the query; the types of the columns are inferred from the types produced by the query.
- When clustering keys are specified in a CTAS statement:
  - Column definitions are required and must be explicitly specified in the statement.
  - By default, Automatic Clustering is not suspended for the new table – even if Automatic Clustering is suspended for the source table.
- If you want the table to be created with rows in a specific order, then use an ORDER BY sub-clause in the SELECT clause of the CTAS. Specifying CLUSTER BY does not cluster the data at the time that the table is created; instead, CLUSTER BY relies on automatic clustering to recluster the data over time.
  
  The ORDER BY sub-clause in a CREATE TABLE statement does not affect the order of the rows returned by future SELECT statements on that table. To specify the order of rows in future SELECT statements, use an ORDER BY sub-clause in those statements.
Inside a transaction, any DDL statement (including CREATE TEMPORARY/TRANSIENT TABLE) commits the transaction before executing the DDL statement itself. The DDL statement then runs in its own transaction. The next statement after the DDL statement starts a new transaction. Therefore, you can’t create, use, and drop a temporary or transient table within a single transaction. If you want to use a temporary or transient table inside a transaction, then create the table before the transaction, and drop the table after the transaction.
Recreating a table (using the optional OR REPLACE keyword) drops its history, which makes any stream on the table stale. A stale stream is unreadable.
A single masking policy that uses conditional columns can be applied to multiple tables provided that the column structure of the table matches the columns specified in the policy.
When creating a table with a masking policy on one or more table columns, or a row access policy added to the table, use the POLICY_CONTEXT function to simulate a query on the column(s) protected by a masking policy and the table protected by a row access policy.
Regarding metadata:

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

CREATE OR ALTER TABLE usage notes¶

Limitations
- Currently only supports permanent, temporary, and transient tables. Read-only, external, dynamic, Apache Iceberg™, and hybrid tables are not supported.
- All limitations of the ALTER TABLE command apply.
- Currently does not support the following:
  - CREATE TABLE … AS SELECT (CTAS) variant syntax.
  - CREATE TABLE … USING TEMPLATE variant syntax.
  - CREATE TABLE … LIKE variant syntax.
  - CREATE TABLE … CLONE variant syntax.
Table parameters and properties
- The absence of a property or parameter that was previously set in the modified table definition results in unsetting it.
- Unsetting an explicit parameter value results in setting it to the default parameter value. If the parameter is set on the schema or database that contain the table, the table inherits the parameter value set on the schema or database.
Data governance
- Setting or unsetting a tag or policy on a table or column using a CREATE OR ALTER TABLE statement is not supported.
  
  Existing policies or tags are not altered by a CREATE OR ALTER statement and remain unchanged.
Constraints

Setting or unsetting an inline primary key changes the nullability of the column accordingly. This aligns with the behavior of the CREATE TABLE command, but is different from the behavior of the ALTER TABLE command.
Columns
- New columns can only be added to the end of the column list.
- Columns cannot be renamed. If you attempt to rename a column, the column is dropped and a new column is added.
- The default value for a column can only be modified to use a sequence.
- The default sequence for a column (for example, SET DEFAULT seq_name.NEXTVAL) can only be changed if the column already has a sequence.
- For more information about modifying columns, see ALTER TABLE … ALTER COLUMN.
Collation
- Collation specifications cannot be altered.
- Setting the DEFAULT_DDL_COLLATION parameter in the CREATE OR ALTER TABLE command sets the default collation specification for existing columns, which ensures the CREATE OR ALTER TABLE command yields the same results as the CREATE TABLE command. Therefore, you can’t use the CREATE OR ALTER TABLE command to set the DEFAULT_DDL_COLLATION parameter on a table that has existing text columns. You can, however, make collations explicit for existing columns when changing the DEFAULT_DDL_COLLATION parameter for a table.
  
  For example, create a new table my_table and set the default collation specification for the table to ‘fr’:
  CREATE OR ALTER TABLE my_table ( a INT PRIMARY KEY, b VARCHAR(20) ) DEFAULT_DDL_COLLATION = 'fr';
  Copy
  The collation specification for column b is ‘fr’ and cannot be changed. To change the default collation specification for table my_table, you must explicitly set the collation for text column b in the CREATE OR ALTER statement:
  CREATE OR ALTER TABLE my_table ( a INT PRIMARY KEY, b VARCHAR(200) COLLATE 'fr' ) DEFAULT_DDL_COLLATION = 'de';
  Copy

Atomicity

The CREATE OR ALTER TABLE command currently does not guarantee atomicity. This means that if a CREATE OR ALTER TABLE statement fails during execution, it is possible that a subset of changes might have been applied to the table. If there is a possibility of partial changes, the error message, in most cases, includes the following text:
```
CREATE OR ALTER execution failed. Partial updates may have been applied.
```
For example, if the statement is attempting to drop column A and add a new column B to a table, and the statement is aborted, it is possible that column A was dropped but column B was not added.

Note

If changes are partially applied, the resulting table is still in a valid state, and you can use additional ALTER TABLE statements to complete the original set of changes.

To recover from partial updates, Snowflake recommends the following recovery mechanisms:
- Fix forward
  - Re-execute the CREATE OR ALTER TABLE statement. If the statements succeeds on the second attempt, the target state is achieved.
  - Investigate the error message. If possible, fix the error and re-execute the CREATE OR ALTER TABLE statement.
- Roll back
  
  If it is not possible to fix forward, Snowflake recommends manually rolling back partial changes:
  - Investigate the state of the table using the DESCRIBE TABLE and SHOW TABLES commands. Determine which partial changes were applied, if any.
  - If any partial changes were applied, execute the appropriate ALTER TABLE statements to transform the table back to its original state.
    
    Note
    
    In some cases, you might not be able to undo partial changes. For more information, see the supported and unsupported actions for modifying column properties in the ALTER TABLE … ALTER COLUMN topic.
- If you need help recovering from a partial update, contact Snowflake Support.

Examples¶

Basic examples¶

Create a simple table in the current database and insert a row in the table:

CREATE TABLE mytable (amount NUMBER);

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table MYTABLE successfully created. |
+-------------------------------------+

INSERT INTO mytable VALUES(1);

SHOW TABLES like 'mytable';

+---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:32:28 -0700 | MYTABLE | TESTDB        | PUBLIC      | TABLE |         |            |    1 |  1024 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+

DESC TABLE mytable;

+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name   | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| AMOUNT | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Copy

Create a simple table and specify comments for both the table and the column in the table:

CREATE TABLE example (col1 NUMBER COMMENT 'a column comment') COMMENT='a table comment';

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table EXAMPLE successfully created. |
+-------------------------------------+

SHOW TABLES LIKE 'example';

+---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment         | cluster_by | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:35:59 -0700 | EXAMPLE | TESTDB        | PUBLIC      | TABLE | a table comment |            |    0 |     0 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------+

DESC TABLE example;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment          |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------|
| COL1 | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | a column comment |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------+

Copy

CTAS examples¶

Create a table by selecting from an existing table:

CREATE TABLE mytable_copy (b) AS SELECT * FROM mytable;

DESC TABLE mytable_copy;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| B    | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

CREATE TABLE mytable_copy2 AS SELECT b+1 AS c FROM mytable_copy;

DESC TABLE mytable_copy2;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| C    | NUMBER(39,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

SELECT * FROM mytable_copy2;

+---+
| C |
|---|
| 2 |
+---+

Copy

More advanced example of creating a table by selecting from an existing table; in this example, the values in the summary_amount column in the new table are derived from two columns in the source table:

CREATE TABLE testtable_summary (name, summary_amount) AS SELECT name, amount1 + amount2 FROM testtable;

Copy

Create a table by selecting columns from a staged Parquet data file:

CREATE OR REPLACE TABLE parquet_col (
  custKey NUMBER DEFAULT NULL,
  orderDate DATE DEFAULT NULL,
  orderStatus VARCHAR(100) DEFAULT NULL,
  price VARCHAR(255)
)
AS SELECT
  $1:o_custkey::number,
  $1:o_orderdate::date,
  $1:o_orderstatus::text,
  $1:o_totalprice::text
FROM @my_stage;

+-----------------------------------------+
| status                                  |
|-----------------------------------------|
| Table PARQUET_COL successfully created. |
+-----------------------------------------+

DESC TABLE parquet_col;

+-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name        | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| CUSTKEY     | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| ORDERDATE   | DATE         | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| ORDERSTATUS | VARCHAR(100) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| PRICE       | VARCHAR(255) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Copy

CREATE TABLE … LIKE examples¶

Create a table with the same column definitions as another table, but with no rows:

CREATE TABLE mytable (amount NUMBER);

INSERT INTO mytable VALUES(1);

SELECT * FROM mytable;

+--------+
| AMOUNT |
|--------|
|      1 |
+--------+

CREATE TABLE mytable_2 LIKE mytable;

DESC TABLE mytable_2;

+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name   | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| AMOUNT | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

SELECT * FROM mytable_2;

+--------+
| AMOUNT |
|--------|
+--------+

Copy

CREATE TABLE examples that set parameters and properties¶

Create a table with a multi-column clustering key:

CREATE TABLE mytable (date TIMESTAMP_NTZ, id NUMBER, content VARIANT) CLUSTER BY (date, id);

SHOW TABLES LIKE 'mytable';

+---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment | cluster_by       | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:20:41 -0700 | MYTABLE | TESTDB        | PUBLIC      | TABLE |         | LINEAR(DATE, ID) |    0 |     0 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------+

Copy

Specify collation for columns in a table:

CREATE OR REPLACE TABLE collation_demo (
  uncollated_phrase VARCHAR, 
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en',
  spanish_phrase VARCHAR COLLATE 'es');

INSERT INTO collation_demo (
      uncollated_phrase, 
      utf8_phrase, 
      english_phrase, 
      spanish_phrase) 
   VALUES (
     'pinata', 
     'pinata', 
     'pinata', 
     'piñata');

Copy

CREATE TABLE … USING TEMPLATE examples¶

Create a table where the column definitions are derived from a set of staged files that contain Avro, Parquet, or ORC data.

Note that the mystage stage and my_parquet_format file format referenced in the statement must already exist. A set of files must already be staged in the cloud storage location referenced in the stage definition.

The following example creates a table using the detected schema from staged files and sorts the columns by order_id. It builds on an example in the INFER_SCHEMA topic.

CREATE TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    WITHIN GROUP (ORDER BY order_id)
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage',
          FILE_FORMAT=>'my_parquet_format'
        )
      ));

Copy

Note that sorting the columns by order_id only applies if all staged files share a single schema. If the set of staged data files includes multiple schemas with shared column names, the order represented in the order_id column might not match any single file.

Note

Using * for ARRAY_AGG(OBJECT_CONSTRUCT()) may result in an error if the returned result is larger than 16MB. It is recommended to avoid using * for larger result sets, and only use the required columns, COLUMN NAME, TYPE, and NULLABLE, for the query. Optional column ORDER_ID can be included when using WITHIN GROUP (ORDER BY order_id).

Temporary table examples¶

Create a temporary table that is dropped automatically at the end of the session:

CREATE TEMPORARY TABLE demo_temporary (i INTEGER);
CREATE TEMP TABLE demo_temp (i INTEGER);

Copy

For compatibility with other vendors, Snowflake also supports using the keywords below as synonyms for TEMPORARY:

CREATE LOCAL TEMPORARY TABLE demo_local_temporary (i INTEGER);
CREATE LOCAL TEMP TABLE demo_local_temp (i INTEGER);

CREATE GLOBAL TEMPORARY TABLE demo_global_temporary (i INTEGER);
CREATE GLOBAL TEMP TABLE demo_global_temp (i INTEGER);

CREATE VOLATILE TABLE demo_volatile (i INTEGER);

Copy

CREATE OR ALTER TABLE examples¶

Create a table my_table using the CREATE OR ALTER TABLE command:

CREATE OR ALTER TABLE my_table(a INT);

Copy

Note

CREATE OR ALTER TABLE statements for existing tables can only be executed by a role with the OWNERSHIP privilege on table my_table.

Alter table my_table to add and modify columns and set the DATA_RETENTION_TIME_IN_DAYS and DEFAULT_DDL_COLLATION parameters:

CREATE OR ALTER TABLE my_table(
    a INT PRIMARY KEY,
    b VARCHAR(200)
  )
  DATA_RETENTION_TIME_IN_DAYS = 5
  DEFAULT_DDL_COLLATION = 'de';

Copy

Unset the DATA_RETENTION_TIME_IN_DAYS parameter. The absence of a parameter in the modified table definition results in unsetting it. In this case, unsetting the DATA_RETENTION_TIME_IN_DAYS parameter for the table resets it to the default value of 1:

CREATE OR ALTER TABLE my_table(
    a INT PRIMARY KEY,
    c VARCHAR(200)
  )
  DEFAULT_DDL_COLLATION = 'de';

Copy

The CREATE OR ALTER TABLE command supports adding columns at the end of the column list. If you attempt to rename an existing column, the existing column is dropped, and a new column with the new column name is added. This might result in data loss if data exists in the original column.

The following example illustrates this behavior.

Create a table:

CREATE OR ALTER TABLE my_table(
    a INT PRIMARY KEY,
    b INT
  );

Copy

Insert data into table my_table:

INSERT INTO my_table VALUES (1, 2), (2, 3);

SELECT * FROM my_table;

Copy

Returns:

+---+---+
| A | B |
|---+---|
| 1 | 2 |
| 2 | 3 |
+---+---+

Attempt to rename column b:

CREATE OR ALTER TABLE my_table(
    a INT PRIMARY KEY,
    c INT
  );

Copy

Column b is dropped and column c is added:

SELECT * FROM my_table;

Copy

Returns:

+---+------+
| A | C    |
|---+------|
| 1 | NULL |
| 2 | NULL |
+---+------+

Note

You can recover dropped columns using Time Travel.

Setting or unsetting an inline primary key changes the nullability of the column in a way that aligns with the behavior of the CREATE TABLE command, but is different from the behavior of the ALTER TABLE command. For example, adding a primary key constraint on a column using an ALTER TABLE statement does not change column nullability.

The following example illustrates this behavior.

Create a table:
```
CREATE TABLE t(a INT);
```
Copy

Alter the table to add a PRIMARY KEY constraint:

CREATE OR ALTER TABLE t(a INT PRIMARY KEY);

Copy

Column a is now the primary key and is set to NOT NULL:

DESC TABLE t;

Copy

Returns:

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| A    | NUMBER(38,0) | COLUMN | N     | NULL    | Y           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

Replace table t:
```
CREATE OR REPLACE TABLE t(a INT);
```
Copy
Insert a NULL value:
```
INSERT INTO t VALUES (null);
```
Copy

Add primary key constraint to column a.

The NULL value in column a causes the following statement to fail:

CREATE OR ALTER TABLE t(a INT PRIMARY KEY);

Copy

Returns:

001471 (42601): SQL compilation error:
Column 'A' contains null values. Not null constraint cannot be added.