Snowflake Stages and File Formats

Files wait in a stage before their data enters a table. A stage is a storage location that Snowflake reads from and writes to. A file format tells Snowflake how to interpret each file. Stages and file formats together form the doorway for every load and unload operation.

A Stage in Everyday Language

Imagine the loading dock behind a large store. Delivery trucks drop boxes at the dock. Staff check the boxes, open them, and move goods to shelves. A Snowflake stage plays the role of that dock. Files arrive from your laptop, an application, or cloud storage. The COPY INTO command acts as the staff that moves the contents onto shelves, which are tables.

DATA JOURNEY THROUGH A STAGE
============================

 +-----------+     +-----------+     +-----------+     +---------+
 | CSV, JSON |     |   STAGE   |     | COPY INTO |     |  TABLE  |
 | Parquet   |---> | (loading  |---> | (reads    |---> | (rows & |
 | files     |     |   dock)   |     |  files)   |     | columns)|
 +-----------+     +-----------+     +-----------+     +---------+
                         ^
                         |
                  FILE FORMAT explains
                  how to read each file

Internal Stages

Internal stages store files inside Snowflake-managed storage. Three kinds exist.

• User stage: every user owns a private stage named @~. Only that user reads it.
• Table stage: every table owns a stage named @%tablename. It suits quick loads into one table.
• Named internal stage: a stage created with CREATE STAGE. Teams share it and control access through grants.

External Stages

External stages point to files that stay in your own cloud storage: Amazon S3, Azure Blob Storage, or Google Cloud Storage. Snowflake reads the files where they sit. A storage integration lets Snowflake reach the bucket through a trusted cloud role, so no passwords or keys appear in SQL.

CREATE STORAGE INTEGRATION s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake_access'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-company-data/sales/');

CREATE STAGE ext_sales_stage
  URL = 's3://my-company-data/sales/'
  STORAGE_INTEGRATION = s3_int;

Internal and External Stages Compared

FEATURE              INTERNAL STAGE            EXTERNAL STAGE
-------------------  ------------------------  ------------------------
Where files live     Inside Snowflake          Your cloud storage
Storage billing      Snowflake storage rate    Your cloud provider
Upload method        PUT command (SnowSQL)     Cloud upload tools
Best for             Small, quick loads        Large data lakes

File Formats

A file format object stores reading rules once so every load reuses them. Snowflake supports CSV, JSON, Avro, ORC, Parquet, and XML.

CREATE FILE FORMAT csv_format
  TYPE = CSV
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('NULL', '');

CREATE FILE FORMAT json_format
  TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE;

CREATE FILE FORMAT parquet_format
  TYPE = PARQUET;

Everyday Stage Commands

-- Create a named internal stage that carries a default format
CREATE STAGE sales_stage FILE_FORMAT = csv_format;

-- Upload a local file (run from SnowSQL, not from a Snowsight worksheet)
PUT file:///tmp/orders.csv @sales_stage;

-- See which files sit in the stage
LIST @sales_stage;

-- Peek at file contents without loading them
SELECT $1, $2, $3 FROM @sales_stage/orders.csv.gz;

-- Delete a file after a successful load
REMOVE @sales_stage/orders.csv.gz;

The $1, $2, $3 notation refers to the first, second, and third column of each row. Peeking at a file before loading catches wrong delimiters and broken headers early.

Common Mistakes

• Forgetting SKIP_HEADER loads the header line as a data row.
• Using a wrong delimiter squeezes every value into one column.
• Leaving old files in a stage wastes storage and confuses later loads.
• Storing cloud keys in SQL text exposes secrets; storage integrations avoid that risk.

A Practical Scenario

A retail team receives a sales export from each store every morning. Store managers upload the files to a shared cloud bucket. A data engineer creates one external stage that points to the bucket and one file format that matches the export layout. A scheduled load reads the new files and fills the sales table. Nobody retypes a delimiter or a header rule, because the file format object stores every reading rule.

Frequently Asked Questions

Can one stage hold different file types?

A stage holds any mix of files. Each COPY INTO command names the file format that fits the files it reads, and a pattern option selects only matching file names.

Do staged files count toward storage cost?

Files in an internal stage count toward Snowflake storage. Files in an external stage stay in your cloud account and follow that provider's pricing.

Key Points

• A stage is a storage location for files that move into or out of Snowflake.
• Internal stages live inside Snowflake; external stages point to your cloud storage.
• Three internal stage kinds exist: user, table, and named.
• File format objects hold reading rules for CSV, JSON, Parquet, Avro, ORC, and XML.
• LIST, PUT, REMOVE, and direct SELECT help manage and inspect staged files.