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
PUTcommand 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!