Skip to main content

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

PropertyDescription
CreationAutomatically created with the table
ScopeBound to a single table
VisibilityNot listed as a named stage
AccessAccessible via SQL and SnowSQL
Best UseOne-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​

OperationCommandPurpose
Upload filesPUTUpload local files to the table stage
List filesLISTView files stored in the table stage
Load dataCOPY INTOLoad staged files into the same table
Unload dataCOPY INTO @%Export table data to the table stage
Remove filesREMOVEDelete 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 SupportedReason
CREATE STAGEAuto-created with the table
DROP STAGEDropped when table is dropped
Sharing filesStage is table-bound
RBAC on stageControlled implicitly via table access
Multi-table usageFiles usable only by that table
SnowpipeNot 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 @%employees at 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​

LimitationExplanation
Table-boundFiles cannot be reused for other tables
No UI uploadLocal file upload requires SnowSQL
Not shareableNo cross-table or team sharing
Not pipeline-friendlyNot ideal for multi-step workflows

βœ… When Should You Use a Table Stage?​

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