Stages (internal and external) in Snowflake - Step-by-Step Execution

Process Flow - Stages (internal and external)

Start: Define Stage

↓

Choose Stage Type

↓

Internal

↓

Upload files

↓

Use COPY INTO

↓

Data loaded

↓

End

This flow shows how you define a stage, choose internal or external, upload or connect storage, then load data using COPY INTO.

Execution Sample

Snowflake

CREATE OR REPLACE STAGE my_int_stage;
PUT file://data.csv @my_int_stage;
COPY INTO my_table FROM @my_int_stage;

CREATE OR REPLACE STAGE my_ext_stage URL='s3://mybucket/data/';
COPY INTO my_table FROM @my_ext_stage;

Defines internal and external stages, uploads data to internal stage, then loads data into a table from both stages.

Process Table

Step	Action	Stage Type	Command	Result
1	Create internal stage	Internal	CREATE OR REPLACE STAGE my_int_stage;	Stage my_int_stage created
2	Upload file to internal stage	Internal	PUT file://data.csv @my_int_stage;	File data.csv uploaded to my_int_stage
3	Load data from internal stage	Internal	COPY INTO my_table FROM @my_int_stage;	Data loaded into my_table
4	Create external stage	External	CREATE OR REPLACE STAGE my_ext_stage URL='s3://mybucket/data/';	Stage my_ext_stage created with S3 URL
5	Load data from external stage	External	COPY INTO my_table FROM @my_ext_stage;	Data loaded into my_table from S3

💡 All steps completed successfully; data loaded from both internal and external stages.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5
my_int_stage	undefined	created	file data.csv present	data loaded into my_table	created	data loaded into my_table
my_ext_stage	undefined	undefined	undefined	undefined	created with S3 URL	data loaded into my_table

Key Moments - 3 Insights

Why do we need to create a stage before uploading or loading data?

A stage acts like a storage location pointer. Without creating it first (see Step 1 and Step 4), Snowflake doesn't know where to find or put files.

What is the difference between internal and external stages?

Internal stages store files inside Snowflake (Step 2), while external stages point to external cloud storage like S3 (Step 4). Both can be used with COPY INTO to load data.

Why do we use COPY INTO after uploading files?

Uploading files to a stage only stores them. COPY INTO actually loads the data into a table (Step 3 and Step 5).

Visual Quiz - 3 Questions

Look at the execution table, what is the result of Step 2?

A File data.csv uploaded to my_int_stage

B Data loaded into my_table

C Stage my_int_stage created

D Stage my_ext_stage created with S3 URL

💡 Hint

Check the 'Result' column for Step 2 in the execution_table.

At which step is the external stage created?

A Step 1

B Step 4

C Step 2

D Step 5

💡 Hint

Look at the 'Action' and 'Stage Type' columns in the execution_table.

If we skip uploading files to the internal stage, what happens at Step 3?

A Stage is created automatically

B Data loads successfully

C COPY INTO fails because no files are found

D External stage is used instead

💡 Hint

Refer to Step 2 and Step 3 in the execution_table and variable_tracker for file presence.

Concept Snapshot

Stages in Snowflake are storage locations for data files.
Internal stages store files inside Snowflake.
External stages point to cloud storage like S3.
Use PUT to upload files to internal stages.
Use COPY INTO to load data from stages into tables.

Full Transcript

This visual execution shows how to use stages in Snowflake. First, you create a stage, which is a storage location. You can choose an internal stage, which stores files inside Snowflake, or an external stage, which points to cloud storage like Amazon S3. For internal stages, you upload files using the PUT command. Then, you load data into a table using COPY INTO from the stage. For external stages, you create the stage with the cloud storage URL and then load data directly using COPY INTO. The execution table traces each step, showing commands and results. Variables track the state of stages and files. Key moments clarify why stages are needed, the difference between internal and external, and the role of COPY INTO. The quiz tests understanding of these steps and outcomes.