π₯ Loading Data from Internal Stages (User Stage)
In this section, you will learn how to load data from Snowflake internal stages into tables using the COPY INTO command.
This workflow is commonly used when:
- Files are already uploaded to Snowflake-managed storage
- You want quick, ad-hoc data loading without external cloud setup
- You are testing or experimenting locally
π‘ What Are Internal Stages?β
Internal stages are Snowflake-managed storage locations used to temporarily store data files for loading and unloading operations.
Types of Internal Stagesβ
| Stage Type | Syntax | Description |
|---|---|---|
| User Stage | @~ | Personal stage automatically created for each user |
| Table Stage | @%<table_name> | Stage automatically tied to a specific table |
| Named Internal Stage | @stage_name | Explicitly created, reusable internal stage |
In this lesson, we focus on the User Stage (@~).
π€ User Stage (@~)β
π What Is the User Stage?β
Every Snowflake user is automatically provided with a personal internal stage, known as the User Stage.
It is ideal for:
- Developer testing
- One-time or ad-hoc file uploads
- Learning and experimentation
β οΈ The user stage is private and cannot be shared with other users.
π Reference Syntaxβ
@~
This syntax is used with commands like PUT, LIST, COPY INTO, and REMOVE.
β Key Characteristicsβ
| Property | Description |
|---|---|
| Creation | Automatically created by Snowflake |
| Visibility | Not visible in Snowsight UI |
| Access | Only accessible by the owning user |
| Permissions | No grants or role-based access |
| Best Use | Ad-hoc and development use cases |
β You do not need to run
CREATE STAGEfor a user stage.
π§ How the User Stage Worksβ
- Snowflake creates the stage when the user account is created
- Files uploaded are automatically compressed (
.gz) - Access is restricted to the logged-in user
- Designed for simplicity, not collaboration
π§ Operations Supported on User Stage (@~)β
Before performing a hands-on load, it is important to understand what operations are possible on a User Stage.
The User Stage supports a small, well-defined set of operations focused on personal and ad-hoc data loading.
β Supported Operationsβ
| Operation | Command | Purpose |
|---|---|---|
| Upload files | PUT | Upload local files to the user stage |
| List files | LIST | View files stored in the user stage |
| Load data | COPY INTO | Load staged files into tables |
| Unload data | COPY INTO @~ | Export table data back to the user stage |
| Remove files | REMOVE | Delete files from the user stage |
π€ Upload Files (PUT)β
PUT 'file:///Users/gopi/Desktop/employee_india.csv' @~;
Upload multiple files:
PUT 'file:///Users/gopi/Desktop/*.csv' @~;
π List Filesβ
LIST @~;
With pattern:
LIST @~ PATTERN = '.*\.csv.gz';
π₯ Load Data into a Tableβ
COPY INTO employee_india
FROM @~
FILES = ('employee_india.csv.gz')
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);
π€ Unload Data from Table to User Stageβ
COPY INTO @~
FROM employee_india
FILE_FORMAT = (TYPE = 'CSV');
ποΈ Remove Files from User Stageβ
REMOVE @~;
Remove a specific file:
REMOVE @~/employee_india.csv.gz;
β Operations NOT Supported on User Stageβ
| Not Supported | Reason |
|---|---|
CREATE STAGE | Auto-created by Snowflake |
DROP STAGE | Managed internally |
GRANT / REVOKE | No RBAC support |
| Sharing files | Stage is private |
| Snowpipe / automation | Not production-ready |
| UI-based upload | CLI required |
π§ Step-by-Step: Loading Data Using User Stageβ
Now that you understand the supported operations, letβs walk through a complete end-to-end example.
π§° Step 1: Prerequisitesβ
1. Install SnowSQLβ
π https://docs.snowflake.com/en/user-guide/snowsql-install-config
2. Prepare a Sample CSV File (employee_india.csv)β
id,name,department,location,salary
1,Aarav,Engineering,Bengaluru,850000
2,Ishita,Sales,Mumbai,600000
3,Rajesh,HR,Delhi,550000
4,Divya,Finance,Chennai,700000
5,Vikram,Engineering,Hyderabad,870000
π₯οΈ Step 2: Login Using SnowSQLβ
snowsql -a <account_identifier> -u <username> -r <role> -w <warehouse>
ποΈ Step 3: Set Contextβ
USE WAREHOUSE COMPUTE_WH;
USE DATABASE EMPLOYEE_DEMO_DB;
USE SCHEMA HR;
π€ Step 4: Upload File to User Stageβ
PUT 'file:///Users/gopikrishnan/Desktop/employee_india.csv' @~;
π¦ Step 5: Verify Uploaded Filesβ
LIST @~;
π₯ Step 6: Create Target Table (Optional)β
CREATE OR REPLACE TABLE employee_india (
id INT,
name STRING,
department STRING,
location STRING,
salary NUMBER(10,2)
);
π₯ Step 7: Load Data into Tableβ
COPY INTO employee_india
FROM @~
FILES = ('employee_india.csv.gz')
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);
Verify:
SELECT * FROM employee_india;
π― Deep Dive: Understanding the User Stage (@~)β
π User Stage Propertiesβ
| Attribute | Value |
|---|---|
| Stage Type | Internal |
| Scope | Per user |
| UI Visibility | β Not visible |
| Access | CLI / SQL only |
| Production Use | β Not recommended |
β οΈ Limitations of User Stageβ
| Limitation | Explanation |
|---|---|
| No UI access | Cannot browse in Snowsight |
| No sharing | Private to one user |
| No role control | No GRANT support |
| CLI required | Uploads require SnowSQL |
| Not production-ready | Not suitable for pipelines |
π οΈ Recommended Alternative: Named Internal Stageβ
For collaboration, automation, or UI visibility:
CREATE OR REPLACE STAGE hr_file_stage;
PUT 'file:///Users/gopi/Desktop/employee.csv' @hr_file_stage;
LIST @hr_file_stage;
β When to Use What?β
| Scenario | User Stage | Named Stage |
|---|---|---|
| Quick local testing | β | β |
| Developer sandbox | β | β |
| Team collaboration | β | β |
| Scheduled pipelines | β | β |
| UI-based loading | β | β |
π Key Takeawayβ
User stages are perfect for personal, ad-hoc data loadsβbut not for shared or production pipelines.
π― Quick Takeaway (One-Liner)β
The User Stage (
@~) supports basic file operationsβupload, list, load, unload, and removeβbut intentionally excludes sharing, RBAC, and automation.