๐ฆ Introduction to Stages in Snowflake
๐ What is a Stage?โ
A stage is a Snowflake object that points to a location where data files live โ either inside Snowflake or in your own cloud storage. Before you can bulk-load CSV, JSON, Parquet or other files into a table, the files must be reachable through a stage.
Think of a stage as the landing zone between your source systems and your tables:
Data Source (CSV, JSON, Logs)
โ ๐ฆ Stage (Internal or External)
โ ๐ฅ COPY INTO table
โ ๐ Analytics / ML / Reporting
Stages are also how data flows the other way: COPY INTO @stage unloads query results to files.
๐ Why Use Stages?โ
- โ One place to land files before loading, so loads can be retried and audited
- โ Any format โ CSV, JSON, Parquet, Avro, ORC, XML
- โ Any cloud โ AWS S3, Azure Blob/ADLS Gen2, Google Cloud Storage
- โ Reusable settings โ attach a file format and credentials once, reuse in every load
- โ Automation-ready โ Snowpipe, Tasks and external tools all read from stages
๐๏ธ Types of Stagesโ
| Stage type | Reference | Who manages storage | Best for |
|---|---|---|---|
| User stage | @~ | Snowflake (auto-created per user) | Personal scratch files |
| Table stage | @%table_name | Snowflake (auto-created per table) | Files loaded into one table only |
| Named internal stage | @stage_name | Snowflake (you create it) | Shared, production internal loads |
| External stage | @stage_name | You (S3 / Azure / GCS bucket) | Large volumes, data lakes, multi-cloud |
The @ prefix tells Snowflake you are referring to a stage.
1. Internal Stages (Managed by Snowflake)โ
Files are stored inside Snowflake's own storage โ encrypted and managed for you. You upload local files with the PUT command (from SnowSQL / Snowflake CLI, or via the Snowsight upload dialog).
-- User stage: every user already has one
PUT file:///tmp/emp.csv @~/emp/;
-- Table stage: every table already has one
PUT file:///tmp/emp.csv @%emp;
-- Named internal stage: create it once, share it across tables and users
CREATE STAGE my_int_stage
COMMENT = 'Landing area for HR files';
PUT file:///tmp/emp.csv @my_int_stage/hr/;
Prefer named internal stages for real pipelines. User and table stages can't be granted to other roles and can't hold a file format, so they don't scale beyond quick tests.
2. External Stages (Bring Your Own Storage)โ
An external stage points at a bucket or container you own. Snowflake reads the files in place; you control the bucket's security. Access is set up with a storage integration (recommended) instead of embedding keys.
-- One-time setup by an admin (covered in "Storage Integration")
-- CREATE STORAGE INTEGRATION my_aws_integration ...;
CREATE STAGE my_s3_stage
URL = 's3://my-company-bucket/raw-data/'
STORAGE_INTEGRATION = my_aws_integration;
๐ฏ Real-world use case: A retail chain lands daily transaction logs in S3. An external stage points at that bucket, and Snowflake loads new files every morning โ no files are copied by hand.
๐๏ธ Working with Files in a Stageโ
-- List files
LIST @my_int_stage;
LIST @~;
LIST @%emp;
-- Query staged CSV files directly, before loading
SELECT $1, $2, $3
FROM @my_int_stage/hr/emp.csv
(FILE_FORMAT => 'my_csv_format');
-- Load into a table
COPY INTO emp
FROM @my_int_stage/hr/
FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');
-- Clean up once loaded
REMOVE @my_int_stage/hr/emp.csv;
๐งพ Stages and File Formatsโ
COPY INTO needs to know how to parse each file: Is it CSV or JSON? What is the delimiter? Is there a header row? Rather than repeating these options in every load, create a reusable file format and attach it to the stage:
CREATE FILE FORMAT my_csv_format
TYPE = 'CSV'
FIELD_DELIMITER = ','
SKIP_HEADER = 1
NULL_IF = ('NULL', 'null')
EMPTY_FIELD_AS_NULL = TRUE;
-- Every load from this stage now uses the format by default
CREATE STAGE my_csv_stage
FILE_FORMAT = my_csv_format;
File format options are covered in depth in the next topic, File Format Management.
โจ Summaryโ
| Question | Answer |
|---|---|
| What is a stage? | A pointer to where data files live, used for loading and unloading |
| Internal vs external? | Internal = Snowflake stores the files; External = your S3 / Azure / GCS bucket |
| How do I upload local files? | PUT into a user, table or named internal stage |
| How do I see staged files? | LIST @stage |
| How do I load them? | COPY INTO table FROM @stage with a file format |