Type of Snowflake Stages: How to Create and Load Data

In this blog, you will learn about Snowflake stages and their types, as well as how to create and load data using them.

Post Contents

What are Snowflake Stages?

Types of Stages in Snowflake

1. Internal Stages in Snowflake
   • User Stage: Creation and Data Loading
   • Table Stage: Creation and Data Loading
   • Named Stage: Creation and Data Loading
2. External Stages in Snowflake

What are Snowflake Stages?

Snowflake stages are storage locations where data files are placed (staged) for the purpose of loading and unloading data out of database tables, and the locations for the stages could be internal or external to the Snowflake environment.

Types of Stages in Snowflake

There are two types of Snowflake stages:

• Internal stages: Internal stages store data files within Snowflake.
• External stages: External stages store data files in a location outside of Snowflake, such as an Amazon S3 bucket, Google Cloud Storage bucket, or Microsoft Azure container.

Internal Stages

By default, each user and table in Snowflake is automatically allocated an internal stage for staging data files to be loaded. In addition, you can create named internal stages.

Snowflake supports the following types of Internal stages:

1- User Stage:

Each user is allocated a default Snowflake stage for file storage. This stage is convenient when files are used by a single user but need to be copied into multiple tables.

Note:

• User Stages are not suitable for files that need to be accessed by multiple users.
• User stages cannot be altered or dropped.

User stages are referenced using: @~ 

For list the files in a user stage: List @~

1.2 Loading data from User stage to a Snowflake Table

Step-1: Open a new terminal window and establish a connection to snowflake. Refer this: SnowSQL

Step-2: Now load file data from your local machine to snowflake user stage.

Execute the following commands-

put file://C:\Work\EmployeeData.txt @~/my_user_stage;

List @~;

• PUT: This is the Snowflake command used to upload files into a stage.
• file://C:\Work\EmployeeData.txt: This part specifies the path to the file you want to upload.
• @~/my_user_stage: This part specifies the destination stage where you want to upload the file. And my_user_stage is a user-specific stage name, and you can choose any name you prefer.

Step-3: Switch to the Snowflake Web UI, add a new SQL worksheet, and execute the following 'CREATE TABLE' statement to create a new table for data loading from user stage.

CREATE or REPLACE TABLE employees_User_Table (
Id INT,
firstname VARCHAR(50),
lastname VARCHAR(50),
salary DECIMAL(10, 2)
);

Step-4: After this create a file format that specifies how your data is formatted in the file.

CREATE or replace FILE FORMAT ff_user_stage
TYPE = 'CSV' 
FIELD_DELIMITER = ','
SKIP_HEADER = 1;

Step-5: Load data into Snowflake table

Copy into employees_User_Table 
from @~/my_user_stage
file_format = ff_user_stage;

Select * from employees_User_Table;

2- Table Stage:

Each table has a Snowflake stage allocated to it by default for storing files. This stage is a convenient option if your files need to be accessible to multiple users and only need to be copied into a single table.

Note:

• Table stages cannot be altered or dropped.
• Table stages have the same name as the table. Example: a table named emptablestage has a stage referenced as @%emptablestage.

2.1 Loading data from Table stage to a Snowflake Table

Step-1: Create a  new table for data loading.

CREATE or replace table emptablestage
(
emp_Id int, 
firstname varchar(50), 
lastname varchar(50), 
salary varchar(50)
);

Step-2: Execute below commands in Snowsql one by one.

put file://c:\work\EmployeeData.txt @%emptablestage;

List @%emptablestage;

Step-3: Switch to Snowflake web UI and create a file format.

Create or replace file format my_ff_Table
Field_Delimiter= ','
Type= 'CSV'
Skip_header =1;

Step-4: Load data into Snowflake table from table stage.

Copy into emptablestage 
from @%emptablestage
file_format = my_ff_Table;

Select * from emptablestage;

3- Named Stage

Named stages are database objects that provide the greatest degree of flexibility for data loading:

1. Users with the appropriate privileges on the stage can load data into any table.
2. Because the stage is a database object, the security/access rules that apply to all objects apply. The privileges to use a stage can be granted or revoked from roles. In addition, ownership of the stage can be transferred to another role.

3.1- Loading data from Named stage to a Snowflake Table

Step-1: Execute the following SQL statements in the Snowflake Web UI.

 CREATE or replace table emp_namedstage_table
(
emp_Id int, 
firstname varchar(50), 
lastname varchar(50), 
salary varchar(50)
);

Step-2: Create a file format and stage, and then execute the given statements one by one.

Create or replace file format my_ff_named
Field_Delimiter= ','
Type= 'CSV'
Skip_header =1;

CREATE OR REPLACE STAGE my_named_stage
FILE_FORMAT = my_ff_named;

Step-3: Execute below commands in Snowsql.

put file://c:\work\EmployeeData.txt @my_named_stage;

list @my_named_stage;

Step-4: Load data from named stage to Snowflake table.

Copy into emp_namedstage_table 
from @my_named_stage/EmployeeData.txt
file_format = my_ff_named;

Select * from emp_namedstage_table;

External Stage in Snowflake

External stages store data files in a location outside of Snowflake, such as an Amazon S3 bucket, Google Cloud Storage bucket, or Microsoft Azure container.

Refer this post for practical of external stage.

Thank you for reading this post, and I hope you enjoyed it!