Create or replace table in Snowflake, simple examples!

Using create or replace table in Snowflake only requires a simple SQL query. In it’s simplest form, all you need to do is run create or replace table table_name (column_name number). In this post I will show how to write the SnowSQL required to use create or replace table, and how to fix some of the common errors that users face.

Create or replace table in Snowflake, in it’s simplest form

In this example I will demonstrate how to create or replace 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 use create or replace table with four columns using no constraints, just the most common datatypes.

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

Create or replace 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 or replace 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 using create or replace 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 using create or replace table in Snowflake, they are:

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

Possible data types when using create or replace 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 using create or replace 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 using create or replace table 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 table in Snowflake
Create temp 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