Stages & File Formats

Stages are one of the most heavily tested topics in the COF-C02 SnowPro Core exam. Understanding the differences between stage types, how to configure external storage integrations, and how to define file format objects is essential for both the exam and real-world Snowflake data engineering work.

What Is a Stage?

A stage in Snowflake is a named location where data files are stored either before being loaded into Snowflake tables or after being exported from them. Stages act as the intermediary between your raw data files and your Snowflake tables.

There are two broad categories:

• Internal stages — storage managed by Snowflake itself
• External stages — references to your own cloud storage (AWS S3, Azure Blob Storage, or Google Cloud Storage)

Snowflake Stage Architecture Overview

Snowflake provides three internal stage types and three external stage targets. Data flows from local files or cloud storage through a stage and into Snowflake tables via COPY INTO. The same pathway works in reverse for unloading.

Internal Stage Types

User Stage (@~)

Every Snowflake user automatically has a private staging area referenced as @~. Key characteristics:

• Cannot be explicitly created — it exists automatically for every user
• Cannot be dropped or altered
• Cannot be shared with other users or roles
• Cannot have a file format assigned at the stage level
• Files are uploaded via SnowSQL PUT and downloaded via GET
• Useful for quick, personal, one-off data loads

1-- Upload a file from local machine (SnowSQL only)
2PUT file:///tmp/my_data.csv @~;
3
4-- List files in your user stage
5LIST @~;
6
7-- Load from user stage into a table
8COPY INTO my_table
9FROM @~/my_data.csv
10FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);
11
12-- Remove a file from user stage
13REMOVE @~/my_data.csv;

Table Stage (@%table_name)

Every Snowflake table automatically has an associated stage. It is referenced using @% followed by the table name.

• Cannot be explicitly created — auto-created with each table
• Cannot be shared across tables
• Accessible to users with INSERT or OWNERSHIP privilege on the table
• Cannot have a file format assigned at the stage level
• Good for simple, single-table loading workflows

1-- Upload a file to the table stage (SnowSQL)
2PUT file:///tmp/sales_data.csv @%sales;
3
4-- List files in the table stage
5LIST @%sales;
6
7-- Load from table stage (file format specified inline)
8COPY INTO sales
9FROM @%sales
10FILE_FORMAT = (
11TYPE = CSV
12FIELD_DELIMITER = ','
13SKIP_HEADER = 1
14NULL_IF = ('NULL', 'null', '')
15);
16
17-- Remove all CSV files from the table stage
18REMOVE @%sales PATTERN='.*\.csv';

Named Internal Stage

The most flexible internal stage type. Created explicitly as a schema-level object and can be shared across roles and used for multiple tables.

• Explicitly created with CREATE STAGE
• Shareable via GRANT
• Supports encryption options (SNOWFLAKE_SSE)
• Can have a default file format assigned
• Can be used by multiple tables and workflows

1-- Create a named internal stage with server-side encryption
2CREATE OR REPLACE STAGE my_internal_stage
3ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
4COMMENT = 'Named internal stage for sales data';
5
6-- Assign a file format at stage creation
7CREATE OR REPLACE STAGE my_csv_stage
8FILE_FORMAT = (
9  TYPE = CSV
10  FIELD_DELIMITER = ','
11  SKIP_HEADER = 1
12  EMPTY_FIELD_AS_NULL = TRUE
13);
14
15-- Grant usage to a role
16GRANT USAGE ON STAGE my_internal_stage TO ROLE data_engineer;
17GRANT READ ON STAGE my_internal_stage TO ROLE data_analyst;
18
19-- List files in named stage
20LIST @my_internal_stage;
21
22-- Load data from named stage
23COPY INTO orders
24FROM @my_internal_stage/orders/2024/
25FILE_FORMAT = (FORMAT_NAME = 'my_csv_format')
26ON_ERROR = 'CONTINUE';

External Stages

External stages point to cloud storage buckets or containers that you own and manage. Snowflake reads files from (or writes files to) these locations without storing the data itself.

External Stage Authentication Methods

External stages can authenticate using inline credentials (AWS key/secret, SAS token for Azure) or via Storage Integrations. Storage Integrations are the recommended approach as they use IAM trust relationships and avoid storing sensitive credentials in Snowflake metadata.

External Stage — AWS S3

1-- Option 1: Using inline credentials (less secure, not recommended for production)
2CREATE OR REPLACE STAGE my_s3_stage
3URL = 's3://my-bucket/data/sales/'
4CREDENTIALS = (
5  AWS_KEY_ID = 'AKIAIOSFODNN7EXAMPLE'
6  AWS_SECRET_KEY = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY'
7)
8FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1);
9
10-- Option 2: Using a Storage Integration (recommended)
11CREATE OR REPLACE STAGE my_s3_stage_secure
12URL = 's3://my-bucket/data/sales/'
13STORAGE_INTEGRATION = my_s3_integration
14FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1)
15COMMENT = 'Secure S3 stage using storage integration';
16
17-- Query the external stage directly (using directory table or INFER_SCHEMA)
18SELECT * FROM @my_s3_stage (FILE_FORMAT => 'my_csv_format') LIMIT 10;

External Stage — Azure Blob Storage

1-- Using a Storage Integration (recommended)
2CREATE OR REPLACE STAGE my_azure_stage
3URL = 'azure://myaccount.blob.core.windows.net/mycontainer/data/'
4STORAGE_INTEGRATION = my_azure_integration
5FILE_FORMAT = (
6  TYPE = JSON
7  STRIP_OUTER_ARRAY = TRUE
8);
9
10-- Using a SAS token (less secure)
11CREATE OR REPLACE STAGE my_azure_stage_sas
12URL = 'azure://myaccount.blob.core.windows.net/mycontainer/data/'
13CREDENTIALS = (AZURE_SAS_TOKEN = '?sv=2020-08-04&ss=bfqt...')
14FILE_FORMAT = (TYPE = JSON);

External Stage — Google Cloud Storage

-- GCS requires a Storage Integration (no credential-based option)
CREATE OR REPLACE STAGE my_gcs_stage
  URL = 'gcs://my-gcs-bucket/data/'
  STORAGE_INTEGRATION = my_gcs_integration
  FILE_FORMAT = (TYPE = PARQUET);

Storage Integrations

A Storage Integration is an account-level Snowflake object that establishes a trust relationship between Snowflake and your cloud storage provider. This means Snowflake can access your storage without you needing to store cloud provider credentials.

The trust model works as follows:

1. You create the Storage Integration in Snowflake
2. Snowflake generates an IAM principal (AWS IAM role ARN, Azure Service Principal, GCS Service Account)
3. You grant that principal access to your cloud storage
4. Snowflake assumes the identity to read/write files

CREATE OR REPLACE STORAGE INTEGRATION my_s3_integration
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-s3-role'
STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/data/', 's3://my-bucket/exports/')
COMMENT = 'S3 integration for data loading';

DESC INTEGRATION my_s3_integration;
-- Note these output values:
-- STORAGE_AWS_IAM_USER_ARN (e.g., arn:aws:iam::snowflake-account:user/...)
-- STORAGE_AWS_EXTERNAL_ID  (e.g., ABC12345_SFCRole=...)

{
"Version": "2012-10-17",
"Statement": [
  {
    "Effect": "Allow",
    "Principal": {
      "AWS": "arn:aws:iam::snowflake-account:user/sfcuser"
    },
    "Action": "sts:AssumeRole",
    "Condition": {
      "StringEquals": {
        "sts:ExternalId": "ABC12345_SFCRole=..."
      }
    }
  }
]
}

CREATE OR REPLACE STAGE my_s3_stage
URL = 's3://my-bucket/data/'
STORAGE_INTEGRATION = my_s3_integration
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

-- List files in the external stage
LIST @my_s3_stage;

-- Attempt to load a sample file
COPY INTO my_table
FROM @my_s3_stage/sample.csv
FILE_FORMAT = (FORMAT_NAME = 'my_csv_format')
VALIDATION_MODE = 'RETURN_ERRORS';

LIST and REMOVE Commands

-- List all files in a named stage
LIST @my_internal_stage;

-- List files in a subfolder path
LIST @my_s3_stage/data/2024/;

-- List only CSV files using PATTERN
LIST @my_internal_stage PATTERN='.*\\.csv';

-- Remove a specific file
REMOVE @my_internal_stage/old_data.csv;

-- Remove all Parquet files using PATTERN
REMOVE @my_s3_stage PATTERN='.*\\.parquet';

File Format Objects

A file format object is a schema-level object that captures all the parsing configuration for a specific file type. Using file format objects promotes reuse and consistency across multiple COPY INTO statements.

1-- CSV file format with common options
2CREATE OR REPLACE FILE FORMAT my_csv_format
3TYPE = CSV
4FIELD_DELIMITER = ','
5RECORD_DELIMITER = '\n'
6SKIP_HEADER = 1
7NULL_IF = ('NULL', 'null', '\\N', '')
8EMPTY_FIELD_AS_NULL = TRUE
9FIELD_OPTIONALLY_ENCLOSED_BY = '"'
10DATE_FORMAT = 'YYYY-MM-DD'
11TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
12ESCAPE = '\\'
13TRIM_SPACE = TRUE
14ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE;
15
16-- JSON file format
17CREATE OR REPLACE FILE FORMAT my_json_format
18TYPE = JSON
19STRIP_OUTER_ARRAY = TRUE
20ALLOW_DUPLICATE = FALSE
21IGNORE_UTF8_ERRORS = FALSE;
22
23-- Parquet file format
24CREATE OR REPLACE FILE FORMAT my_parquet_format
25TYPE = PARQUET
26SNAPPY_COMPRESSION = TRUE;
27
28-- Avro file format
29CREATE OR REPLACE FILE FORMAT my_avro_format
30TYPE = AVRO;
31
32-- ORC file format
33CREATE OR REPLACE FILE FORMAT my_orc_format
34TYPE = ORC;

CSV File Format Key Options

JSON File Format Key Options

Columnar Formats (Parquet, Avro, ORC)

Directory Tables

Directory tables are a special feature available on named internal and external stages. When enabled, they expose file metadata as a queryable virtual table.

-- Enable directory table on a named stage
CREATE OR REPLACE STAGE my_stage_with_dir
  URL = 's3://my-bucket/files/'
  STORAGE_INTEGRATION = my_s3_integration
  DIRECTORY = (ENABLE = TRUE);

-- Refresh the directory table (sync with underlying storage)
ALTER STAGE my_stage_with_dir REFRESH;

-- Query the directory table
SELECT *
FROM DIRECTORY(@my_stage_with_dir);

-- Query with filters
SELECT
  RELATIVE_PATH,
  SIZE,
  LAST_MODIFIED,
  MD5,
  ETAG
FROM DIRECTORY(@my_stage_with_dir)
WHERE RELATIVE_PATH LIKE '%.parquet'
ORDER BY LAST_MODIFIED DESC;

Stage URL Reference Patterns

Snowflake uses consistent URL prefixes to reference different stage types. Understanding the @ prefix syntax is critical for writing COPY INTO, LIST, REMOVE, and GET commands correctly.

Stage URL Quick Reference

Cheat Sheet

Practice Quizzes

Flashcards