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 NULLwhen 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
| Keyword | Description |
|---|---|
| CREATE TABLE | Command to create a new table |
| table_name | Name of the table to create |
| column_name | Name of a column in the table |
| DATA_TYPE | Type of data stored in the column (e.g., INT, STRING, DATE) |
| NOT NULL | Constraint to ensure column cannot have NULL values |
| IF NOT EXISTS | Optional 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.
