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:
- 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:
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.
Create table as select in Snowflake
Create or replace table in Snowflake
Rename Snowflake table
Swap Snowflake tables
Drop table in Snowflake
Delete table in Snowflake
Materialized view vs table
View vs table