Snowflake Solutions Expertise and
Community Trusted By

Enter Your Email Address Here To Join Our Snowflake Solutions Community For Free

Snowflake Solutions Community

How do you create a new table in Snowflake and what different tables types are available?

559 viewsData Modeling

How do you create a new table in Snowflake and what different tables types are available?

Daniel Steinhold Answered question August 2, 2023

Creating a new table in Snowflake involves defining the table's structure and specifying its columns, data types, and other properties. Snowflake supports various table types, each serving different purposes. Here's a step-by-step process to create a new table in Snowflake and an overview of the different table types:

**Step-by-Step Process to Create a New Table in Snowflake:**

1. **Connect to Snowflake:** Use a SQL client or Snowflake web interface to connect to your Snowflake account.
2. **Choose or Create a Database:** Decide which database you want the table to be created in. You can use an existing database or create a new one using the **`CREATE DATABASE`** statement.
3. **Choose a Schema:** Choose an existing schema within the selected database or create a new schema using the **`CREATE SCHEMA`** statement.
4. **Define the Table Structure:** Use the **`CREATE TABLE`** statement to define the table structure. Specify the column names, data types, constraints (e.g., primary key, foreign key), and other optional properties.
5. **Execute the Query:** Execute the **`CREATE TABLE`** query to create the table in Snowflake.

**Example of Creating a Simple Table:**

sqlCopy code
-- Assuming we are connected to the Snowflake account and a database
-- and schema are selected or created.

-- Create a simple table called "employees".
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR,
last_name VARCHAR,
hire_date DATE,
salary DECIMAL(10, 2)


**Different Table Types in Snowflake:**

1. **Standard Tables:** Standard tables are the most common type in Snowflake and are used to store data in a structured format. They can be loaded, queried, and modified like traditional tables in a relational database.
2. **Temporary Tables:** Temporary tables are created for temporary storage and are automatically dropped at the end of the session or transaction. They are useful for intermediate data processing steps.
3. **External Tables:** External tables allow you to query data stored in external locations, such as files in cloud storage, without loading the data into Snowflake. They provide a way to access data in its native format.
4. **Secure Views:** Secure views are used to hide sensitive data by restricting access to certain columns of a table. They allow you to control what data users can see based on their privileges.
5. **Materialized Views:** As mentioned earlier, materialized views store pre-computed query results as physical tables. They are used to improve query performance for complex or frequently executed queries.
6. **Transient Tables:** Transient tables are used to store non-essential data temporarily. They are suitable for workloads where data can be regenerated or reloaded if needed.
7. **Zero-Copy Cloning (ZCC) Tables:** ZCC tables are special clones of a base table that share the same data blocks, reducing the storage cost. Changes to the clone don't affect the base table until modifications are made.

Remember that the availability of certain table types may depend on the specific edition and features enabled in your Snowflake account. The appropriate table type for your use case will depend on factors like data access patterns, query performance requirements, data security, and cost considerations.

Daniel Steinhold Answered question August 2, 2023

Maximize Your Data Potential With ITS

Feedback on Q&A