Snowflake file stage area - User Stage vs Table Stage vs Internal Stage

A stage is a temporary storage area, where data files are stored (i.e. “staged”) so that the data in the files can be loaded into a table

There are three types of Stages in Snowflake:

• User Stages
  
• Table Stages
  
• Internal Named Stages
  

There are two steps to load a file in Snowflake

1. Upload the file to an internal stage using PUT command

2. Then use the same stage in the COPY INTO <table> command to load the table from the staged files

The User Stage

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

Limitations:

• Multiple users require access to the files.
  
• The current user does not have INSERT privileges on the tables the data will be loaded into
  

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. To stage files to a table stage, you must have OWNERSHIP of the table itself. Also, This option is not appropriate if you need to copy the data in the files into multiple tables

Internal Named Stage

Example:

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

Internal stages are named database objects that provide the greatest degree of flexibility for data loading. Because they are database objects, the security/access rules that apply to all objects apply:

• Users with the appropriate privileges on the stage can load data into any table.
  
• Ownership of the stage can be transferred to another role, and privileges granted to use the stage can be modified to add or remove roles.
  
• When you create a stage, you must explicitly grant privileges on the stage to one or more roles before users with those roles can use the stage
  

*** All staging areas comes with Unlimited storage and there is no limit on the number of files that you can store in a stage

Atori

posted on 22 Oct 18