Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create Table in Snowflake: Syntax and Examples

To create a table in Snowflake, use the CREATE TABLE statement followed by the table name and column definitions. You can specify data types for each column and optional constraints like NOT NULL.
📐

Syntax

The basic syntax to create a table in Snowflake is:

  • CREATE TABLE: Command to create a new table.
  • table_name: The name you want to give your table.
  • (column_name data_type [constraints], ...): List of columns with their data types and optional constraints.
sql
CREATE TABLE table_name (
  column1_name DATA_TYPE [NOT NULL],
  column2_name DATA_TYPE [NOT NULL],
  ...
);
💻

Example

This example creates a table named employees with three columns: id as an integer, name as text, and hire_date as a date.

sql
CREATE TABLE employees (
  id INT NOT NULL,
  name STRING NOT NULL,
  hire_date DATE
);
Output
Table EMPLOYEES successfully created.
⚠️

Common Pitfalls

Common mistakes when creating tables in Snowflake include:

  • Forgetting to specify data types for columns.
  • Using unsupported data types.
  • Not adding NOT NULL when a column must always have a value.
  • Trying to create a table that already exists without using IF NOT EXISTS.

Here is an example of a wrong and right way:

sql
/* Wrong: Missing data type for column */
CREATE TABLE wrong_table (
  id,
  name STRING
);

/* Right: Specify data type for all columns */
CREATE TABLE right_table (
  id INT,
  name STRING
);
📊

Quick Reference

KeywordDescription
CREATE TABLECommand to create a new table
table_nameName of the table to create
column_nameName of a column in the table
DATA_TYPEType of data stored in the column (e.g., INT, STRING, DATE)
NOT NULLConstraint to ensure column cannot have NULL values
IF NOT EXISTSOptional clause to avoid error if table exists
✅

Key Takeaways

Use CREATE TABLE with column names and data types to create tables in Snowflake.
Always specify data types for each column to avoid errors.
Use NOT NULL to enforce required columns.
Add IF NOT EXISTS to prevent errors if the table already exists.
Check Snowflake documentation for supported data types and constraints.