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:
- 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:
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.