Loading from S3, Azure Blob, GCS in Snowflake - Step-by-Step Execution

Process Flow - Loading from S3, Azure Blob, GCS

Start: Define Storage Integration

↓

Create External Stage

↓

Run COPY INTO Command

↓

Data Loaded into Snowflake Table

↓

End

This flow shows how Snowflake loads data from cloud storage by defining access, creating a stage, and copying data into a table.

Execution Sample

Snowflake

CREATE OR REPLACE STAGE my_s3_stage
  URL='s3://mybucket/data/'
  STORAGE_INTEGRATION = my_s3_integration;

COPY INTO my_table FROM @my_s3_stage FILE_FORMAT = (TYPE = 'CSV');

This code sets up access to an S3 bucket and loads CSV files into a Snowflake table.

Process Table

Step	Action	Evaluation	Result
1	Define storage integration for S3	Integration created with credentials	Access to S3 bucket authorized
2	Create external stage pointing to S3 URL	Stage created with URL and integration	Stage 'my_s3_stage' ready to use
3	Run COPY INTO command from stage	Files found in S3 path	Data copied into 'my_table'
4	Verify data load	Query table row count	Rows match files loaded
5	End	No more files or commands	Loading complete

💡 All files from the cloud storage stage are loaded into the Snowflake table successfully.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
storage_integration	undefined	created with S3 credentials	created	created	created
external_stage	undefined	undefined	created with S3 URL	created	created
copy_status	not started	not started	not started	running	completed
table_data_rows	0	0	0	increasing	final row count

Key Moments - 3 Insights

Why do we need a storage integration before creating the stage?

The storage integration securely stores credentials and permissions to access the cloud storage. Without it, Snowflake cannot read data from S3, Azure Blob, or GCS. See execution_table step 1.

What happens if the COPY INTO command runs but no files are found in the stage?

The COPY INTO command completes but loads zero rows because no files exist at the stage path. This is shown in execution_table step 3 where files are checked.

How do we confirm data loaded correctly into the Snowflake table?

By querying the table row count after the COPY INTO command finishes, as shown in execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after step 2?

A Stage 'my_s3_stage' ready to use

B Data copied into 'my_table'

C Integration created with credentials

D Rows match files loaded

💡 Hint

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

At which step does the data start copying into the Snowflake table?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look for the 'Action' describing the COPY INTO command in execution_table.

If the storage integration is missing, what will happen at step 2?

A Table will be created automatically

B Data will load successfully anyway

C Stage creation will fail due to missing credentials

D COPY INTO command will skip files

💡 Hint

Refer to key_moments about why storage integration is needed before stage creation.

Concept Snapshot

Loading from S3, Azure Blob, GCS in Snowflake:
1. Create a storage integration with cloud credentials.
2. Create an external stage pointing to the cloud storage URL using the integration.
3. Use COPY INTO command from the stage to load data into a Snowflake table.
4. Verify data loaded by querying the table.
This process securely and efficiently loads cloud data into Snowflake.

Full Transcript

This visual execution shows how Snowflake loads data from cloud storage services like S3, Azure Blob, and GCS. First, a storage integration is created to securely store access credentials. Next, an external stage is created that points to the cloud storage location using this integration. Then, the COPY INTO command runs to load files from the stage into a Snowflake table. Finally, the data load is verified by checking the table row count. Each step is tracked to show how variables like storage integration, stage, copy status, and table data rows change. Common confusions such as the need for storage integration and what happens if no files are found are addressed. Quiz questions test understanding of the execution steps and outcomes.