Skip to main content

๐Ÿ“ฆ 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 typeReferenceWho manages storageBest for
User stage@~Snowflake (auto-created per user)Personal scratch files
Table stage@%table_nameSnowflake (auto-created per table)Files loaded into one table only
Named internal stage@stage_nameSnowflake (you create it)Shared, production internal loads
External stage@stage_nameYou (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/;
tip

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โ€‹

QuestionAnswer
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