Create table in Snowflake, simply explained with examples

Creating a table in Snowflake only requires a simple SQL query. In it’s simplest form, all you need to do is run create table table_name (column_name number). In this post I will show how to write the SQL required to create a table, and also cover some of the most common errors that users experience.

Create table in Snowflake, in it’s simplest form

In this example I will demonstrate how to create a table that contains product data. To store the necessary information about our products we require columns of different data types, so in this very basic example we will create a table with four columns using no constraints, just the most common datatypes.

create table products (
	product_id numeric,
	product_name varchar,
	contains_gluten boolean,
	date_first_sold date
);

Create Snowflake table with constraints

Say we wanted to add some constraints to our table, in order to improve our data quality and decrease the chance for errors. We can do this by adding constraints to our columns which will return an error if they are broken. In this example we will build on our previous query by adding some of the most common constraints.

create table products (
	product_id numeric not null, --No null values can be entered
	product_name varchar (255), --Maximum 255 characters allowed
	contains_gluten boolean default True, --Setting the default value to True
	date_first_sold date default null --Setting the default value to null
);

Column constraints when creating a table in Snowflake

In the example above we saw three common constraints to add to a column definition, in addition to these there are also other possibilities when creating a table in Snowflake:

• Unique
• Primary key
• Foreign key
• Not null
• Maximum length
• Default value

Possible data types when creating a table in Snowflake

We have looked at the most common datatypes in these examples, but there are others that may be useful for different use cases. The data types possible to include in Snowflake table definitions are:

• Number
• Integer
• Float
• Boolean
• Varchar
• Date
• Timestamp
• Array
• Object

Syntax error: unexpected, how to solve this common error

When creating a table like I have shown above it is common to get an error similar to the following:

Syntax error: unexpected
Syntax error: unexpected 'numeric’
Syntax error: unexpected 'varchar’
Syntax error: unexpected 'boolean’

This error is more often than not because you have not wrapped your column definitions in brackets like the examples have shown above.

Syntax error: unexpected ',', how to solve this bug

Another common error faced when creating tables is:

Syntax error: unexpected ','

This error is also simple to solve and is often caused by not providing a datatype for one or multiple of the columns in the table definition.

Related articles

Create tables

Create table as select in Snowflake
Create or replace table in Snowflake

Edit tables

Rename Snowflake table
Swap Snowflake tables
Drop table in Snowflake
Delete table in Snowflake

Views

Materialized view vs table
View vs table

References

Snowflake CREATE TABLE documentation
Snowflake data types documentation