π₯ Loading Data from Named Internal Stages
In this section, you will learn how to load data using a Named Internal Stage in Snowflake.
Named internal stages are the most flexible and reusable option among Snowflakeβs internal stages and are widely used in team-based and production-grade workflows.
π§ What Is a Named Internal Stage?β
A Named Internal Stage is a user-defined, Snowflake-managed storage location that you explicitly create to store files for loading and unloading data.
Unlike:
- User Stages (
@~) β tied to one user - Table Stages (
@%table) β tied to one table
A Named Stage:
- Is not tied to any single table or user
- Can be shared across roles
- Can be reused by multiple tables and pipelines
π‘ Think of a Named Stage as a shared folder inside Snowflake, governed by roles and permissions.
π Reference Syntaxβ
@stage_name
Example:
@my_stage
β Key Characteristicsβ
| Property | Description |
|---|---|
| Creation | Manually created using CREATE STAGE |
| Scope | Schema-level object |
| Reusability | Can be used by multiple tables |
| Security | Supports RBAC (GRANTs) |
| Visibility | Visible in Snowsight UI |
| Best Use | Team workflows, automation, production pipelines |
π― How Named Internal Stages Workβ
-
Files are stored in Snowflake-managed internal storage
-
Multiple users and roles can access the same stage
-
Files can be reused across:
- Multiple tables
- Multiple COPY commands
- Scheduled pipelines
-
Stage lifecycle is independent of tables
π§ Operations Supported on Named Internal Stages (@stage_name)β
Before performing hands-on loading, it is important to understand what operations are supported on a Named Internal Stage.
β Supported Operationsβ
| Operation | Command | Purpose |
|---|---|---|
| Create stage | CREATE STAGE | Create a reusable internal stage |
| Upload files | PUT | Upload local files to the stage |
| List files | LIST | View files stored in the stage |
| Load data | COPY INTO | Load staged files into one or more tables |
| Unload data | COPY INTO @ | Export table data back to the stage |
| Remove files | REMOVE | Delete files from the stage |
| Secure stage | GRANT | Control access using roles |
π§± Create a Named Internal Stageβ
CREATE OR REPLACE STAGE my_stage;
π€ Upload Files (PUT)β
PUT 'file:///path/to/employees_named.csv' @my_stage AUTO_COMPRESS=TRUE;
Upload multiple files:
PUT 'file:///path/to/*.csv' @my_stage;
π List Filesβ
LIST @my_stage;
With pattern:
LIST @my_stage PATTERN = '.*\.csv.gz';
π₯ Load Data into a Tableβ
COPY INTO employees_named
FROM @my_stage/employees_named.csv.gz
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);
π€ Unload Data from Table to Named Stageβ
COPY INTO @my_stage
FROM employees_named
FILE_FORMAT = (TYPE = 'CSV');
ποΈ Remove Files from Named Stageβ
REMOVE @my_stage;
Remove a specific file:
REMOVE @my_stage/employees_named.csv.gz;
β Operations NOT Supported on Named Internal Stagesβ
| Not Supported | Reason |
|---|---|
| External cloud access | Internal stages are Snowflake-managed |
| Auto file ingestion | Use Snowpipe with external stages instead |
| Infinite storage cleanup | Files must be manually removed |
ποΈ Sample Input File (employees_named.csv)β
id,name,department
1,Varun,Engineering
2,Shreya,Sales
3,Anjali,HR
4,Rohit,Finance
5,Tanvi,Engineering
π§ Step-by-Step: Load Data Using a Named Internal Stageβ
πΉ Step 1: Create the Target Tableβ
CREATE OR REPLACE TABLE employees_named (
id INT,
name STRING,
department STRING
);
πΉ Step 2: Create the Named Internal Stage (One-Time Setup)β
CREATE OR REPLACE STAGE my_stage;
πΉ Step 3: Upload File to the Named Stageβ
PUT 'file:///path/to/employees_named.csv' @my_stage AUTO_COMPRESS=TRUE;
πΉ Step 4: Verify Files in the Stageβ
LIST @my_stage;
πΉ Step 5: Load Data into the Tableβ
COPY INTO employees_named
FROM @my_stage/employees_named.csv.gz
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);
πΉ Step 6: Verify Loaded Dataβ
SELECT * FROM employees_named;
π How Named Stages Compare with Other Internal Stagesβ
| Feature | Table Stage | User Stage | Named Stage |
|---|---|---|---|
| Automatically Created | β Yes | β Yes | β No |
| Tied to Table | β Yes | β No | β No |
| Tied to User | β No | β Yes | β No |
| Reusable | β No | β No | β Yes |
| Role-Based Access | β No | β No | β Yes |
| UI Visibility | β No | β No | β Yes |
| Automation Ready | β No | β No | β Yes |
β οΈ Limitations of Named Internal Stagesβ
| Limitation | Explanation |
|---|---|
| Manual creation | Must be created explicitly |
| Manual cleanup | Old files must be removed |
| Explicit paths | File names/patterns required |
| Internal only | Not suitable for large external ingestion |
π‘οΈ Security & Governance Notesβ
- Supports RBAC
- Example grant:
GRANT USAGE ON STAGE my_stage TO ROLE data_engineer;
- Fully audited via Snowflake access history
- Suitable for regulated environments
π― When Should You Use a Named Internal Stage?β
| Scenario | Recommended? |
|---|---|
| Shared team ingestion | β Yes |
| Reusable staging for multiple tables | β Yes |
| Automated batch pipelines | β Yes |
| Production data loads | β Yes |
| Personal testing only | β Overkill |
| One-off table load | β Use table stage |
π Key Takeawaysβ
- Named internal stages are reusable, secure, and production-ready
- Best choice for team-based ingestion
- Support RBAC, automation, and UI visibility
- Bridge the gap between simple internal stages and external stages
π’ Why Internal Stages Are Still Valuableβ
- β Fully managed by Snowflake
- β Secure by default
- β No cloud credentials required
- β Faster loads within Snowflake-managed storage
---
## β
What you now have (big picture)
| Stage Type | Structure |
| ----------- | -------------------------------------------------- |
| User Stage | Concept β Operations β Steps β Limits β Decision |
| Table Stage | Concept β Operations β Steps β Limits β Decision |
| Named Stage | Concept β Operations β Steps β Security β Decision |
---