Skip to main content

πŸ“₯ 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​

PropertyDescription
CreationManually created using CREATE STAGE
ScopeSchema-level object
ReusabilityCan be used by multiple tables
SecuritySupports RBAC (GRANTs)
VisibilityVisible in Snowsight UI
Best UseTeam 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​

OperationCommandPurpose
Create stageCREATE STAGECreate a reusable internal stage
Upload filesPUTUpload local files to the stage
List filesLISTView files stored in the stage
Load dataCOPY INTOLoad staged files into one or more tables
Unload dataCOPY INTO @Export table data back to the stage
Remove filesREMOVEDelete files from the stage
Secure stageGRANTControl 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 SupportedReason
External cloud accessInternal stages are Snowflake-managed
Auto file ingestionUse Snowpipe with external stages instead
Infinite storage cleanupFiles 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​

FeatureTable StageUser StageNamed 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​

LimitationExplanation
Manual creationMust be created explicitly
Manual cleanupOld files must be removed
Explicit pathsFile names/patterns required
Internal onlyNot 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?​

ScenarioRecommended?
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 |

---