Type of Snowflake Stages - How to Create and Use them?

Snowflake cloud data warehouse architecture is specifically designed to work with the cloud such as Amazon AWS, Google cloud platform and Microsoft Azure. Loading and unloading tables, Snowflake use different methods compared to other relational database. Snowflake uses staging area to store the file that you can in your COPY command. In this article, we will check the types of Snowflake stages, how to create and use them.

Types of Snowflake Stages

A stage in Snowflake is an intermediate space where you can upload the files so that you can use the COPY command to load or unload tables.

By default, each user and table in Snowflake are automatically allocated an internal stage for staging data files to be loaded. However, you can also create the named internal stage for staging files to be loaded and unloaded files.

Snowflake Supports three types of stages

User Stage
Table Stage
Internal Named Stage

Now, let us check these stages in brief.

Snowflake User Stage Area

Each user in Snowflake has a stage allocated to them by default for storing files. The files could be load or unload files. The user stage is a convenient option if your files will only be accessed by a single user, but need to be copied into multiple tables.

If your requirement is to load the file to multiple table, then user stage is your choice.

How to upload file to user stage in Snowflake?

You can use following SnowSQL command to upload files to user stage area.

For example,

vithal#COMPUTE_WH@TEST_DB.PUBLIC>put file://D:\Snowflake\sample_file.csv @~/staged;

The file will be uploaded to user stage and you can verify the same using LIST command.

For example,

vithal#COMPUTE_WH@TEST_DB.PUBLIC>list @~/staged;

Go ahead and try it.

How to identify the user Stage in Snowflake?

The user stages are referenced using @~.

For example, use LIST @~ to list the files in a user stage.

You can provide the name for the user stage. For example, @~/staged in our previous example.

Limitations of User Stage in Snowflake

Following are the limitations of user stage in Snowflake.

Unlike named stages, user stages cannot be altered or dropped.
User stages do not support setting file format options. Instead, specify file format details in your COPY command.

Note that, user stage is not a database object. It is an implicit stage associated with Snowflake user.

Snowflake Table Stage Area

Each table in Snowflake has a stage allocated to it by default for storing files. The table 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. Table stage name is same as your table name.

Note that, this option is not appropriate if your requirement is to load file to multiple tables.

How to upload file to table stage in Snowflake?

You can use following SnowSQL command to upload files to table stage area.

For example,

vithal#COMPUTE_WH@TEST_DB.PUBLIC>put file://D:\Snowflake\sample_file.csv @%test;

You can also specify the subfolder within the table stage where you want to upload files.

For example,

vithal#COMPUTE_WH@TEST_DB.PUBLIC>put file://D:\Snowflake\sample_file.csv @%test/sample_csv/;

The file will be uploaded to user stage and you can verify the same using LIST command.

For example,

vithal#COMPUTE_WH@TEST_DB.PUBLIC>list @%test;

How to identify the Table Stage in Snowflake?

The Table stages are referenced using @%tableName.

For example, use LIST @%testto list the files in a table stage, which is allocated to test table.

Limitations of Table Stage in Snowflake

Following are the limitations of table stage in Snowflake.

Table stages have the same name as the table. For example, @%test is a stage for table test.
The table stages cannot be altered or dropped.
Table stages do not support setting file format options. Instead, specify file format details in your COPY command.

Note that, table stage is not a database object. It is an implicit stage associated with the table.

Snowflake Internal Named Stage

Internal stages are named database objects that you can use in-place of user and table stage. Internal named stages are recommended stage to load the tables.

The Internal named stages can be accessed by multiple user and can be used to load multiple tables. You can also specify the file format.

For example, consider following example to create internal stage.

create or replace stage my_csv_stage
  file_format = (type = 'CSV' field_delimiter = '|' skip_header = 1);

How to identify the Internal Named Stage in Snowflake?

The Table stages are referenced using @stage_name.

For example, use LIST @my_csv_stage to list the files in my_csv_stage.

How to upload file to Internal Named Stage in Snowflake?

You can use following SnowSQL command to upload files to Internal named stage area.

For example,

vithal#COMPUTE_WH@TEST_DB.PUBLIC>put file://D:\Snowflake\sample_file.csv @my_csv_stage;

The file will be uploaded to internal named stage and you can verify the same using LIST command.

For example,

vithal#COMPUTE_WH@TEST_DB.PUBLIC>LIST @my_csv_stage;

How to Use Different Types of Stages with COPY command?

You can specify the different stages such as user, table or named stage in your copy command.

For example,

You can use any of the following commands to use different stage.

Snowflake Copy Command with User Stage

COPY INTO test FROM @~/staged;

Copy Command with Table Stage

COPY INTO test FROM @public.%test;

Snowflake Copy Command with Table Stage

COPY INTO test FROM @stage_path;

Snowflake Stages Best Practices

Following are the some of best practices to use Snowflake stages.

If your requirement is to load the file to multiple table, then user stage is your choice.
Use table stage if your requirement is to load into a single table.
The Internal named stages can be accessed by multiple user and can be used to load multiple tables. The internal named stage is best suited if you share files with multiple users and load into multiple table.
It is always advised to use external named stage for large files.