Snowflake Internal Stages
Snowflake · Staging & SnowSQL
Theory
A stage is a named location holding files that Snowflake loads from or unloads to. Internal stages live in Snowflake-managed storage and come in three kinds: the user stage (@~), private to each user; the table stage (@%orders), tied to one table; and named stages (@my_stage), created with CREATE STAGE and shareable through grants. External stages point at your own S3, Azure or GCS location.
The workflow is the same for all of them: upload files with PUT (internal only), check them with LIST, load with COPY INTO and clean up with REMOVE. For example, PUT file:///data/orders.csv @my_stage, then COPY INTO orders FROM @my_stage.
Why we use it
Snowflake loads data from files in bulk, and those files need a place to sit first. Internal stages give you that place without owning any cloud storage account or setting up credentials.
When to use it
Use a named internal stage for files uploaded from laptops, scripts or apps, and for shared loads. Use user or table stages for quick personal work. Use an external stage when the files already live in your cloud bucket.
What a Stage Is
A stage is simply a named storage location Snowflake reads from or writes to when loading and unloading files — think of it as a waiting room for data before it becomes table rows.
Types of Internal Stages
Every user automatically gets a personal stage (@~), every table gets a table stage (@%table_name), and you can also create a named stage shared across a schema — pick based on whether the files belong to one user, one table, or a whole pipeline.
Typical Workflow
Files are PUT onto a stage, optionally validated, then loaded into a table with COPY INTO — the same three-step flow whether the stage is internal or points at an external cloud bucket.
How it works
- Local file ready to load
- Do several users or tables need this stage? Yes: Create a named stage with CREATE STAGE. No: Use the user stage @~ or table stage @%table.
- PUT the file to the stage (compressed by default)
- LIST the stage to confirm the file is there
- COPY INTO the target table
- REMOVE or PURGE loaded files
Example
CREATE OR REPLACE STAGE my_stage COMMENT = 'Stage for loading CSV files';
CREATE OR REPLACE STAGE my_dir_stage DIRECTORY = (ENABLE = TRUE);
LIST @~;
LIST @my_stage;
LIST @%orders;
REMOVE @my_stage/old_file.csv;Key points
- Three internal kinds: user @~, table @%table and named @stage
- Table stages cannot be altered or dropped, and do not support COPY transformations
- Named stages are database objects with grants, the most flexible choice
- PUT and GET work only with internal stages
- Directory tables (DIRECTORY = (ENABLE = TRUE)) list staged files as a table