How to CREATE TABLE in Snowflake

How do I use Snowflake CREATE TABLE to build reliable schemas?

CREATE TABLE builds a new, structured table in your Snowflake database, defining columns, data types, defaults, and constraints.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the purpose of CREATE TABLE?

CREATE TABLE initializes a brand-new, permanently stored table inside a specified Snowflake database and schema. It lets you describe columns, data types, defaults, constraints, clustering keys, and file format options for later data loading.

How do I write basic CREATE TABLE syntax?

Start with CREATE TABLE, give the fully qualified name, list columns with data types, then add optional constraints or table options. Add OR REPLACE to rebuild, or IF NOT EXISTS to skip if the table already exists.

How can I copy a structure from another table?

Use CREATE TABLE new_table LIKE existing_table to duplicate column definitions only, or CREATE TABLE new_table CLONE existing_table to copy both structure and data instantly without extra storage.

How do I apply defaults and constraints?

Add DEFAULT expressions after a column definition to pre-populate missing values. Enforce data integrity with NOT NULL, UNIQUE, and PRIMARY KEY constraints. Snowflake accepts but does not enforce FOREIGN KEY constraints for documentation purposes.

What options improve performance?

Specify CLUSTER BY for large fact tables to speed partition pruning. Turn on CHANGE_TRACKING to enable incremental MERGE and stream processing. Use transient tables for scratch data that does not need Fail-safe retention.

Which CREATE TABLE features save time?

Include COMMENT clauses so future analysts understand each column. Add COPY GRANTS when replacing a table to preserve privileges. Use column masking policies to protect PII automatically.

Best practices checklist

Pick precise data types (NUMBER instead of VARCHAR) to shrink storage. Document business logic with COMMENTS. Prefer CLONE for fast environment refreshes. Always version control DDL in Git.

Why How to CREATE TABLE in Snowflake is important

How to CREATE TABLE in Snowflake Example Usage


-- Create a transactional line-item table
CREATE OR REPLACE TABLE OrderItems (
    id          NUMBER          NOT NULL,
    order_id    NUMBER          NOT NULL,
    product_id  NUMBER          NOT NULL,
    quantity    NUMBER          DEFAULT 1,
    CONSTRAINT orderitems_pk PRIMARY KEY(id)
);

-- Verify creation
SHOW TABLES LIKE 'ORDERITEMS';

How to CREATE TABLE in Snowflake Syntax


CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] <db>.<schema>.<table_name> (
    column_name1  data_type  [ DEFAULT expr ] [ NOT NULL | NULL ] [ COMMENT 'text' ],
    column_name2  data_type  [...],
    ... ,
    [ CONSTRAINT pk_name PRIMARY KEY (column_name1) ],
    [ CONSTRAINT uq_name UNIQUE (column_name2) ]
)
[ CLUSTER BY (column_name1, column_name2) ]
[ CHANGE_TRACKING = { TRUE | FALSE } ]
[ COPY GRANTS ];

-- Ecommerce example
CREATE TABLE IF NOT EXISTS Orders (
    id             NUMBER          NOT NULL,
    customer_id    NUMBER          NOT NULL,
    order_date     DATE           DEFAULT CURRENT_DATE,
    total_amount   NUMBER(12,2)    NOT NULL,
    CONSTRAINT orders_pk PRIMARY KEY(id)
);

-- Copy structure only
CREATE TABLE Orders_Staging LIKE Orders;

-- Clone structure + data
CREATE TABLE Orders_Backup CLONE Orders;

Common Mistakes

Forgetting IF NOT EXISTS. Omitting it in deployment pipelines causes errors when the table already exists. Add IF NOT EXISTS to make scripts idempotent.
Using VARCHAR for numeric fields. This inflates storage and breaks math operations. Declare NUMBER(precision, scale) for money and integers.