Stages (internal and external) in Snowflake - Commands & Configuration

Introduction

Stages in Snowflake are places where you store data files temporarily before loading or unloading data. They help move data in and out of Snowflake easily, either inside Snowflake (internal) or outside like cloud storage (external).

When you want to upload CSV files from your computer to Snowflake for analysis.

When you need to export query results to a cloud storage bucket for sharing.

When you want to automate data loading from an Amazon S3 bucket into Snowflake tables.

When you want to keep files temporarily inside Snowflake for quick access during data processing.

When you want to unload large datasets from Snowflake to Azure Blob Storage for backup.

Commands

This command creates a new internal stage named 'my_internal_stage' inside Snowflake to store files temporarily.

Terminal

CREATE OR REPLACE STAGE my_internal_stage;

Expected Output

Statement executed successfully.

Uploads the local file 'data.csv' to the internal stage 'my_internal_stage' and compresses it automatically to save space.

Terminal

PUT file:///home/user/data.csv @my_internal_stage AUTO_COMPRESS=TRUE;

Expected Output

uploading file:///home/user/data.csv to @my_internal_stage/data.csv.gz

100% done. Uploaded 1 file(s) successfully.

→

AUTO_COMPRESS=TRUE - Compresses the file automatically during upload.

Creates an external stage named 'my_external_stage' that points to an Amazon S3 bucket with access credentials.

Terminal

CREATE OR REPLACE STAGE my_external_stage URL='s3://mybucket/data/' CREDENTIALS=(AWS_KEY_ID='AKIAEXAMPLE' AWS_SECRET_KEY='secretkeyexample');

Expected Output

Statement executed successfully.

Lists all files currently stored in the internal stage 'my_internal_stage' so you can see what is available.

Terminal

LIST @my_internal_stage;

Expected Output

name                             size  md5

-------------------------------- ----- --------------------------------

data.csv.gz                      1024  d41d8cd98f00b204e9800998ecf8427e

Downloads the compressed file 'data.csv.gz' from the internal stage to the local folder '/home/user/downloads/'.

Terminal

GET @my_internal_stage/data.csv.gz file:///home/user/downloads/;

Expected Output

downloading @my_internal_stage/data.csv.gz to file:///home/user/downloads/data.csv.gz

100% done. Downloaded 1 file(s) successfully.

Key Concept

If you remember nothing else from this pattern, remember: stages are temporary storage spots inside or outside Snowflake that help move data files in and out smoothly.

Common Mistakes

Trying to PUT a file to an external stage directly.

PUT only works with internal stages; external stages point to external cloud storage and cannot receive files via PUT.

Use PUT only with internal stages; upload files to external storage using cloud provider tools.

Not providing credentials when creating an external stage.

Without credentials, Snowflake cannot access the external storage, so operations will fail.

Always include valid access credentials or use an IAM role when creating external stages.

Forgetting to compress files during PUT for large uploads.

Uncompressed files take longer to upload and use more storage.

Use AUTO_COMPRESS=TRUE flag to save time and space.

Summary

Create internal stages to store files temporarily inside Snowflake.

Use PUT to upload local files to internal stages and GET to download them back.

Create external stages to connect Snowflake to cloud storage like S3 with credentials.

Use LIST to see files in any stage before loading or unloading data.