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.

Practice

1. What is the main difference between an internal stage and an external stage in Snowflake?

easy

A. Internal stages store files inside Snowflake, external stages link to cloud storage.

B. Internal stages are only for unloading data, external stages are only for loading data.

C. Internal stages require a file format, external stages do not.

D. Internal stages are free, external stages always cost extra.

Solution

1. Step 1: Understand internal stage storage

   Internal stages keep files physically inside Snowflake's managed storage.

2. Step 2: Understand external stage storage

   External stages point to external cloud storage like AWS S3 or Azure Blob.

3. Final Answer:

   Internal stages store files inside Snowflake, external stages link to cloud storage. -> Option A

4. Quick Check:

   Internal vs external storage location = A [OK]

Hint: Remember: internal = inside Snowflake, external = outside Snowflake [OK]

Common Mistakes:

• Thinking internal stages can link to external cloud storage
• Confusing loading and unloading roles of stages
• Assuming file format is only needed for internal stages

2. Which of the following is the correct syntax to create an internal stage named mystage in Snowflake?

easy

A. CREATE STAGE mystage URL='s3://mybucket/data/';

B. CREATE STAGE mystage FILE_FORMAT = (TYPE = 'CSV');

C. CREATE EXTERNAL STAGE mystage FILE_FORMAT = (TYPE = 'CSV');

D. CREATE STAGE mystage STORAGE_INTEGRATION = my_integration;

Solution

1. Step 1: Identify internal stage syntax

   Internal stages do not require URL or STORAGE_INTEGRATION parameters.

2. Step 2: Check file format usage

   Specifying FILE_FORMAT is valid and common for internal stages.

3. Final Answer:

   CREATE STAGE mystage FILE_FORMAT = (TYPE = 'CSV'); -> Option B

4. Quick Check:

   Internal stage creation syntax = B [OK]

Hint: Internal stage needs FILE_FORMAT, no URL or integration [OK]

Common Mistakes:

• Using URL parameter for internal stages
• Confusing external stage syntax with internal
• Omitting FILE_FORMAT when needed

3. Given this Snowflake SQL snippet:

CREATE OR REPLACE STAGE ext_stage
URL='s3://mybucket/data/'
STORAGE_INTEGRATION = my_int
FILE_FORMAT = (TYPE = 'JSON');

LIST @ext_stage;

What will the LIST @ext_stage; command do?

medium

A. List files stored inside Snowflake internal stage named ext_stage.

B. Return an error because FILE_FORMAT is not allowed in stage creation.

C. Show the contents of the JSON files in the stage.

D. List files in the external S3 bucket linked by ext_stage.

Solution

1. Step 1: Identify stage type from syntax

   URL and STORAGE_INTEGRATION indicate an external stage linked to S3.

2. Step 2: Understand LIST command behavior

   LIST @stage lists files in the stage's storage location, here the S3 bucket.

3. Final Answer:

   List files in the external S3 bucket linked by ext_stage. -> Option D

4. Quick Check:

   LIST on external stage lists external files = C [OK]

Hint: LIST @stage shows files where stage points, internal or external [OK]

Common Mistakes:

• Thinking LIST shows file contents, not file names
• Assuming FILE_FORMAT is invalid in stage creation
• Confusing internal and external stage storage

4. You try to create an external stage with this command:

CREATE STAGE mystage
URL='s3://mybucket/data/';

But get an error. What is the most likely cause?

medium

A. Missing STORAGE_INTEGRATION for external stage access.

B. FILE_FORMAT is required for external stages.

C. Internal stages cannot use URL parameter.

D. Stage name mystage is reserved.

Solution

1. Step 1: Check external stage requirements

   External stages need STORAGE_INTEGRATION to access cloud storage securely.

2. Step 2: Identify missing parameter

   The command lacks STORAGE_INTEGRATION, causing access error.

3. Final Answer:

   Missing STORAGE_INTEGRATION for external stage access. -> Option A

4. Quick Check:

   External stage needs integration = D [OK]

Hint: External stage always needs STORAGE_INTEGRATION for cloud access [OK]

Common Mistakes:

• Assuming FILE_FORMAT is mandatory for external stage creation
• Confusing internal stage syntax with external
• Thinking stage name causes error

5. You want to unload query results to a stage and then copy them to an external S3 bucket. Which setup is best practice?

hard

A. Unload to local machine, then upload manually to S3 external stage.

B. Unload directly to an external stage linked to S3, then copy from there.

C. Unload to an internal stage, then use Snowflake commands to copy to external stage.

D. Unload to internal stage and keep data only there without copying.

Solution

1. Step 1: Understand unloading to stages

   Unloading query results to internal stage is fast and secure inside Snowflake.

2. Step 2: Copying to external storage

   Use Snowflake COPY INTO command to move data from internal to external stage.

3. Step 3: Evaluate other options

   Direct unload to external stage is possible but less controlled; manual upload is inefficient.

4. Final Answer:

   Unload to an internal stage, then use Snowflake commands to copy to external stage. -> Option C

5. Quick Check:

   Unload internal then copy external = A [OK]

Hint: Unload inside Snowflake first, then copy out [OK]

Common Mistakes:

• Unloading directly to external stage without integration setup
• Manual upload instead of automated copy
• Not copying data out after unloading