Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 38 ยท Loading and Unloading Data

Stages and File Formats

Understand internal and external stages, user and table stages, named file formats for CSV, JSON and Parquet, and how to upload and list files before loading.

Data does not jump straight from your laptop or cloud bucket into a table. It travels through a stage, a location that holds files waiting to be loaded (or that were just unloaded). A file format tells Snowflake how to read those files. Master these two objects and every loading method in this chapter becomes easy.

What is a stage?

A stage is a pointer to a storage location. There are two families:

  • Internal stages: storage managed by Snowflake inside your account. You upload files with PUT.
  • External stages: a pointer to a bucket or container you own in S3, Azure Blob Storage or Google Cloud Storage. Files stay where they are.
flowchart LR
  A["Local files"] -- "PUT" --> B["Internal stage"]
  C["Cloud bucket<br/>S3 / Azure / GCS"] --> D["External stage"]
  B -- "COPY INTO" --> E["Table"]
  D -- "COPY INTO" --> E

Both kinds of stage feed COPY INTO.

Internal stage types

Stage Reference Notes
User stage @~ One per user, private; no setup
Table stage @%orders One per table; convenient for loading just that table
Named internal stage @my_stage Created with CREATE STAGE; shareable with privileges
SQL
-- Snowflake SQL (read-only)
CREATE STAGE shop.raw.landing;                -- named internal stage
LIST @shop.raw.landing;                       -- files in it
LIST @~;                                      -- your user stage
LIST @%orders;                                -- the orders table stage

Uploading with PUT

PUT works from SnowSQL, the Snowflake CLI or a driver, not from the Snowsight worksheet (use the Snowsight "Load data" wizard there). It compresses files with gzip by default and encrypts them.

SQL
-- Snowflake SQL (read-only), run from SnowSQL
PUT file://C:\data\orders_2025_*.csv @shop.raw.landing AUTO_COMPRESS = TRUE;
GET @shop.raw.landing/orders_2025_01.csv.gz file://C:\downloads\;
REMOVE @shop.raw.landing/orders_2025_01.csv.gz;

GET downloads, REMOVE deletes. Stage files cost storage, so remove them after loading (or use PURGE = TRUE on the copy).

External stages

An external stage needs a URL and credentials. The best practice is a storage integration, which lets Snowflake assume a cloud role without you pasting keys into SQL:

SQL
-- Snowflake SQL (read-only)
CREATE STORAGE INTEGRATION s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-reader'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-company-data/exports/');

CREATE STAGE shop.raw.s3_orders
  URL = 's3://my-company-data/exports/orders/'
  STORAGE_INTEGRATION = s3_int
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

LIST @shop.raw.s3_orders;

Never embed AWS_KEY_ID and AWS_SECRET_KEY in statements that get stored in history or in scripts. The integration pattern is safer and survives key rotation.

File formats

A file format describes how to parse files. You can write it inline or create a reusable named one:

SQL
-- Snowflake SQL (read-only)
CREATE FILE FORMAT shop.raw.csv_fmt
  TYPE = CSV
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('', 'NULL', '\\N')
  EMPTY_FIELD_AS_NULL = TRUE
  DATE_FORMAT = 'YYYY-MM-DD'
  ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE;

CREATE FILE FORMAT shop.raw.json_fmt TYPE = JSON STRIP_OUTER_ARRAY = TRUE;
CREATE FILE FORMAT shop.raw.parquet_fmt TYPE = PARQUET;

Supported types: CSV (and delimited variants), JSON, AVRO, ORC, PARQUET and XML. The most important CSV options:

Option Meaning
SKIP_HEADER Number of header lines to ignore
FIELD_OPTIONALLY_ENCLOSED_BY Quote character, usually "
NULL_IF Strings to treat as SQL NULL
ERROR_ON_COLUMN_COUNT_MISMATCH Fail if a row has the wrong number of fields
DATE_FORMAT, TIMESTAMP_FORMAT How to parse date text
ENCODING Character set, default UTF-8

For JSON, STRIP_OUTER_ARRAY = TRUE turns a top-level array into one row per element. Parquet and other binary formats carry their own schema, so there is little to configure.

Looking inside a staged file

Before loading, peek at the content with $1, $2 column references (positional for CSV) or $1 for the whole JSON document:

SQL
-- Snowflake SQL (read-only)
SELECT $1, $2, $3
FROM @shop.raw.landing/orders_2025_01.csv.gz
  (FILE_FORMAT => 'shop.raw.csv_fmt')
LIMIT 5;

This is also how you transform while loading (select, cast, reorder), described in COPY INTO: Loading a Table.

A runnable model of staging

You cannot create a stage here, but you can model the logic: a "stage" table holds raw lines as text, and parsing them with a delimiter (the file format) happens when moving them into the real table. The first stand-in parses CSV text with SKIP_HEADER:

SQL Stand-in: staged text lines and a header to skip

Press Run (or Ctrl + Enter) to see the result here.

The second stand-in splits each line into fields, the job of FIELD_DELIMITER, and casts them to types, like column types in CREATE TABLE:

SQL Stand-in: split fields and cast like a file format would

Press Run (or Ctrl + Enter) to see the result here.

Good habits

  • Create named file formats once and reuse them, so every load parses data the same way.
  • Prefer compressed files (gzip for CSV/JSON, Snappy inside Parquet) of roughly 100-250 MB compressed: large enough to avoid per-file overhead, small enough to load in parallel.
  • Organize stage paths by date or source (orders/2025/06/) so you can load subsets by prefix.
  • Use LIST to confirm what is there, and REMOVE or PURGE = TRUE to clean up.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which reference points to your own personal internal stage?

    @~ is the user stage, @%table is a table stage and @name is a named stage.
  2. 2. What is the recommended way to give Snowflake access to an S3 bucket?

    A storage integration lets Snowflake assume a role without storing keys in SQL.
  3. 3. Where can you NOT run the PUT command?

    PUT needs a client with access to local files; Snowsight worksheets cannot read your disk. Use the Load data wizard there.
  4. 4. What does SKIP_HEADER = 1 do in a CSV file format?

    It tells Snowflake the first line is a header to ignore.

Summary

  • A stage is a location for files; internal stages live in Snowflake, external stages point to your cloud storage.
  • User (@~), table (@%t) and named (@name) stages cover most needs.
  • PUT, GET, LIST and REMOVE manage internal stage files from a client.
  • Named file formats define parsing for CSV, JSON, Avro, ORC, Parquet and XML.
  • Use storage integrations, compressed files of sensible size and organized paths.

Next: COPY INTO: Loading a Table