Skip to main content

πŸ“₯ 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 TypeSyntaxDescription
User Stage@~Personal stage automatically created for each user
Table Stage@%<table_name>Stage automatically tied to a specific table
Named Internal Stage@stage_nameExplicitly 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​

PropertyDescription
CreationAutomatically created by Snowflake
VisibilityNot visible in Snowsight UI
AccessOnly accessible by the owning user
PermissionsNo grants or role-based access
Best UseAd-hoc and development use cases

❗ You do not need to run CREATE STAGE for 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​

OperationCommandPurpose
Upload filesPUTUpload local files to the user stage
List filesLISTView files stored in the user stage
Load dataCOPY INTOLoad staged files into tables
Unload dataCOPY INTO @~Export table data back to the user stage
Remove filesREMOVEDelete 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 SupportedReason
CREATE STAGEAuto-created by Snowflake
DROP STAGEManaged internally
GRANT / REVOKENo RBAC support
Sharing filesStage is private
Snowpipe / automationNot production-ready
UI-based uploadCLI 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​

AttributeValue
Stage TypeInternal
ScopePer user
UI Visibility❌ Not visible
AccessCLI / SQL only
Production Use❌ Not recommended

⚠️ Limitations of User Stage​

LimitationExplanation
No UI accessCannot browse in Snowsight
No sharingPrivate to one user
No role controlNo GRANT support
CLI requiredUploads require SnowSQL
Not production-readyNot suitable for pipelines

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

ScenarioUser StageNamed 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.