π₯ Loading Data from Table Stages
In this section, you will learn how to load data using a Table Stage, which is an internal stage automatically created for every table in Snowflake.
Table stages are best suited for table-specific, isolated data loading workflows.
π‘ What Is a Table Stage?β
A table stage is a Snowflake-managed internal stage that is automatically associated with a table.
π Reference Syntaxβ
@%<table_name>
You do not need to create it manually. Snowflake creates and manages it for you.
β Key Characteristicsβ
| Property | Description |
|---|---|
| Creation | Automatically created with the table |
| Scope | Bound to a single table |
| Visibility | Not listed as a named stage |
| Access | Accessible via SQL and SnowSQL |
| Best Use | One-table, controlled data loads |
π― How the Table Stage Worksβ
- Every table in Snowflake automatically has its own stage
- Files uploaded to the table stage belong only to that table
- The stage lifecycle is tied to the table lifecycle
- Access to the stage is implicitly controlled by table access
π§ Operations Supported on Table Stage (@%<table_name>)β
Before performing a hands-on load, itβs important to understand what operations are supported on a table stage.
β Supported Operationsβ
| Operation | Command | Purpose |
|---|---|---|
| Upload files | PUT | Upload local files to the table stage |
| List files | LIST | View files stored in the table stage |
| Load data | COPY INTO | Load staged files into the same table |
| Unload data | COPY INTO @% | Export table data to the table stage |
| Remove files | REMOVE | Delete files from the table stage |
π€ Upload Files (PUT)β
PUT 'file:///Users/gopikrishnan/Desktop/employees.csv' @%employees AUTO_COMPRESS=TRUE;
π List Filesβ
LIST @%employees;
π₯ Load Data into the Tableβ
COPY INTO employees
FROM @%employees/employees.csv.gz
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);
π€ Unload Data from Table to Table Stageβ
COPY INTO @%employees
FROM employees
FILE_FORMAT = (TYPE = 'CSV');
ποΈ Remove Files from Table Stageβ
REMOVE @%employees;
Remove a specific file:
REMOVE @%employees/employees.csv.gz;
β Operations NOT Supported on Table Stageβ
| Not Supported | Reason |
|---|---|
CREATE STAGE | Auto-created with the table |
DROP STAGE | Dropped when table is dropped |
| Sharing files | Stage is table-bound |
| RBAC on stage | Controlled implicitly via table access |
| Multi-table usage | Files usable only by that table |
| Snowpipe | Not intended for automation |
ποΈ Sample Input File (employees.csv)β
id,name,department
1,Sahana,Engineering
2,Raj,Sales
3,Rekha,HR
4,Neeraj,Finance
5,Ananya,Engineering
π§ Step-by-Step: Load Data Using Table Stageβ
πΉ Step 1: Create the Target Tableβ
CREATE OR REPLACE TABLE employees (
id INT,
name STRING,
department STRING
);
π‘ Snowflake automatically creates the table stage
@%employeesat this point.
πΉ Step 2: Upload File to the Table Stage (SnowSQL)β
PUT 'file:///Users/gopikrishnan/Desktop/employees.csv' @%employees AUTO_COMPRESS=TRUE;
πΉ Step 3: Verify the File in the Table Stageβ
LIST @%employees;
πΉ Step 4: Load Data into the Tableβ
COPY INTO employees
FROM @%employees/employees.csv.gz
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);
πΉ Step 5: Verify the Loaded Dataβ
SELECT * FROM employees;
β οΈ Limitations of Table Stagesβ
| Limitation | Explanation |
|---|---|
| Table-bound | Files cannot be reused for other tables |
| No UI upload | Local file upload requires SnowSQL |
| Not shareable | No cross-table or team sharing |
| Not pipeline-friendly | Not ideal for multi-step workflows |
β When Should You Use a Table Stage?β
| Scenario | Recommended? |
|---|---|
| Load data for a single table | β Yes |
| One-off or ad-hoc loads | β Yes |
| Team-wide shared ingestion | β No |
| Production ETL pipelines | β No |
| Multiple tables from same files | β No |
π οΈ Better Alternative for Reuse & Pipelinesβ
If you need:
- Reusability
- Role-based access
- UI visibility
- Scheduled or automated loads
β‘οΈ Use a Named Internal Stage instead.
CREATE OR REPLACE STAGE hr_shared_stage;
π Key Takeawaysβ
- Every Snowflake table has an automatic internal stage
- Table stages are simple, secure, and isolated
- Best suited for single-table loading scenarios
- Not designed for collaboration or production pipelines