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
- Multiple users require access to the files.
- The current user does not have INSERT privileges on the tables the data will be loaded into
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
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