Snowflake Stages: The Complete Guide

A Stage is the intermediate file location Snowflake uses for loading (ingest) and unloading (export) data. Bulk loads via COPY INTO, automated ingestion through Snowpipe, and data exports almost always go through a stage. Stages are a required Data Loading domain topic on the SnowPro Core exam.

Stage Types and Comparison

Snowflake offers 4 types of stages, each with its own use case, management model, and security profile.

Creating and Using a Named Internal Stage

A Named Internal Stage stores files inside Snowflake's internal storage. Because it supports GRANT-based permission control, it is preferred over User and Table Stages for team operations.

-- Create a Named Internal Stage
CREATE OR REPLACE STAGE hr_load_stage
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"')
  COMMENT = 'CSV load stage for the HR team';

-- Upload files (SnowSQL / PUT)
PUT file:///data/employees.csv @hr_load_stage AUTO_COMPRESS = TRUE;

-- List files on the stage
LIST @hr_load_stage;

-- Load into the target table
COPY INTO hr.employees
FROM @hr_load_stage
PATTERN = '.*employees.*\.csv\.gz'
ON_ERROR = 'CONTINUE';

External Stage + Storage Integration

An External Stage references external storage on S3, Azure Blob Storage, or GCS directly. Because it does not copy data into Snowflake's internal storage, it is the ideal choice for integrating with an existing data lake.

Designing the Storage Integration

A Storage Integration is the object that securely manages access between Snowflake and cloud storage using IAM role-based delegation. It removes the need to embed long-lived keys and centralizes management under the ACCOUNTADMIN role.

-- Create the Storage Integration (ACCOUNTADMIN)
CREATE OR REPLACE STORAGE INTEGRATION s3_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-access'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('s3://data-lake-prod/raw/', 's3://data-lake-prod/staging/');

-- Fetch the values needed for the IAM trust relationship
DESC INTEGRATION s3_integration;

-- Create the External Stage
CREATE OR REPLACE STAGE data_lake_stage
  STORAGE_INTEGRATION = s3_integration
  URL = 's3://data-lake-prod/raw/'
  FILE_FORMAT = (TYPE = PARQUET);

COPY INTO Patterns with Stages

COPY INTO works in both directions: loading from a stage into a table, and unloading from a table to a stage.

-- Load: stage -> table
COPY INTO analytics.orders
FROM @data_lake_stage/orders/2026/03/
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

-- Unload: table -> stage
COPY INTO @export_stage/monthly_report/
FROM (
  SELECT region, SUM(amount) AS total_amount
  FROM analytics.orders
  WHERE order_date >= '2026-03-01'
  GROUP BY region
)
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP)
HEADER = TRUE
OVERWRITE = TRUE;

Key COPY INTO Options

Defining and Reusing FILE FORMAT

Defining a Named File Format up front lets you reuse a consistent format across multiple stages and COPY INTO statements.

-- Create a Named File Format
CREATE OR REPLACE FILE FORMAT csv_standard
  TYPE = CSV
  SKIP_HEADER = 1
  FIELD_DELIMITER = ','
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('NULL', 'null', '')
  EMPTY_FIELD_AS_NULL = TRUE;

-- Reference the file format from a stage
CREATE OR REPLACE STAGE shared_csv_stage
  FILE_FORMAT = csv_standard;

Directory Tables

Directory Tables let you query file metadata on a stage (file path, size, last modified, etc.) as if it were a table. Enable the feature by specifying DIRECTORY = (ENABLE = TRUE) when you create the stage.

-- Enable the Directory Table
ALTER STAGE data_lake_stage SET DIRECTORY = (ENABLE = TRUE);

-- Manually refresh the metadata
ALTER STAGE data_lake_stage REFRESH;

-- Query the Directory Table
SELECT *
FROM DIRECTORY(@data_lake_stage)
WHERE FILE_URL LIKE '%2026/03%'
ORDER BY LAST_MODIFIED DESC;

Security Best Practices

• Always use a Storage Integration for External Stages: avoid embedding long-lived keys and delegate access via an IAM role
• Scope access with STORAGE_ALLOWED_LOCATIONS: restrict the allowed buckets/paths on the Storage Integration to the minimum required
• Apply appropriate GRANTs to Named Stages: for team operations, control USAGE / READ / WRITE privileges at the role level
• Internal Stage encryption: Snowflake automatically applies AES-256 encryption with no additional configuration required

Check Your Understanding

Frequently Asked Questions