Skip to main content

2.5 Stages & File Formats

Before you can load large amounts of data into Snowflake tables, the raw data files (CSV, JSON, Parquet) must exist in a Stage.

A Stage is a Snowflake database object that points to a cloud storage location.

Internal vs External Stages​

1. Internal Stages​

Internal stages store data files directly within Snowflake's internal cloud storage infrastructure.

  • You use the PUT command in SnowSQL to upload files from your local machine into an Internal Stage.
  • Snowflake manages the storage, encryption, and lifecycle of the files.

There are three types of Internal Stages:

  • User Stage (@~): Automatically allocated to each user. Good for personal scratch files.
  • Table Stage (@%table_name): Automatically allocated to every table. Good for data that will only ever be loaded into that specific table.
  • Named Internal Stage (@my_internal_stage): A custom, standalone stage object. Highly recommended for production workloads because files can be loaded into multiple tables.

2. External Stages​

External stages point to storage buckets that you own and control outside of Snowflake (e.g., an AWS S3 bucket, an Azure Data Lake Gen2 container, or a GCS bucket).

  • Because you own the bucket, you are responsible for managing its security via IAM roles.
  • Snowflake simply accesses the files in your bucket to read/load them.
-- Creating an External Stage
CREATE STAGE MY_S3_STAGE
URL='s3://my-company-bucket/raw-data/'
STORAGE_INTEGRATION = my_aws_integration;

File Format Objects​

When you load data from a stage into a table using the COPY INTO command, Snowflake needs to know exactly how to parse the file (e.g., Is it a CSV? What is the delimiter? Does it have a header row? Is it compressed?).

Instead of typing out 20 lines of parsing parameters every time you load data, you can create a reusable File Format Object.

-- Create a reusable File Format
CREATE FILE FORMAT my_csv_format
TYPE = 'CSV'
FIELD_DELIMITER = ','
SKIP_HEADER = 1
NULL_IF = ('NULL', 'null')
EMPTY_FIELD_AS_NULL = TRUE;

Once created, you simply attach this file format to your stage or COPY INTO command, making your data pipelines extremely clean and modular!