Snowflake
Create Tables

Snowflake CREATE TABLE: How to Create Tables

The CREATE TABLE command in Snowflake allows you to define and create a new table in your database. It supports various options to customize table properties and behavior, such as column definitions, constraints, and more. This flexibility supports a range of use cases from basic table creation to more complex configurations like cloning, templating, or creating tables with specific data retention policies.

Key Features

  • Column Definitions: Define each column with its data type and additional attributes like default values or constraints.
  • Variants: Use variants like CREATE TABLE AS SELECT for creating a table and populating it with query results, or CREATE TABLE ... CLONE to copy an existing table along with its data.
  • Constraints: Define primary keys, foreign keys, or unique constraints to maintain data integrity.
  • Table Properties: Set properties like data retention period, schema evolution, and collation specifications.

Examples

Create a Basic Table

Snowflake SQL Query


_10
CREATE TABLE employees (
_10
id INT PRIMARY KEY,
_10
name STRING NOT NULL,
_10
email STRING,
_10
department STRING DEFAULT 'General'
_10
);

Explanation

The command above creates a simple table named employees with four columns. It specifies:

  1. id as a primary key which uniquely identifies each record.
  2. name as mandatory (NOT NULL).
  3. email is optional.
  4. The department column has a default value of General if not specified.

Crete a Table with a Complex Definition

Snowflake SQL Query


_10
CREATE TABLE sales_data (
_10
sale_id INT AUTOINCREMENT START 100 INCREMENT 1,
_10
product_id INT NOT NULL,
_10
quantity INT DEFAULT 1,
_10
sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
_10
CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products(id)
_10
)
_10
COMMENT = 'Stores sales transactions';

Explanation

This command establishes a more complex table named sales_data. It includes:

  1. sale_id with an AUTOINCREMENT setting starting at 100 and incrementing by 1 for each new record.
  2. product_id as mandatory (NOT NULL).
  3. quantity with a default of 1 if not specified.
  4. sale_date with a default of the current timestamp when not provided.
  5. A foreign key constraint (fk_product_id) that references the product_id column to the id in the products table to ensure referential integrity.
  6. A comment describing the table as storing sales transactions.

Clone a table

Snowflake SQL Query


_10
CREATE OR REPLACE TABLE sales_data_clone
_10
CLONE sales_data;

Explanation

This command duplicates an existing table named employee_backup using the CLONE operation. This is a zero-copy operation, meaning it doesn’t physically store data again but creates a reference to the original data. You can modify the clone without affecting the original table. For a detailed explanation of cloning, you can read about it here.

Create a Table from a Query (CTAS)

Snowflake SQL Query


_10
CREATE TABLE marketing_leads AS
_10
SELECT name, email FROM customers
_10
WHERE subscription_date > '2021-01-01';

Explanation

This command creates a new table named marketing_leads populated by the results of a query executed against the customers table. It extracts name and email of customers who subscribed after January 1, 2021.

Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started