Loading from S3, Azure Blob, GCS in Snowflake - Mini Project: Build & Apply

Loading Data from S3, Azure Blob, and GCS into Snowflake

📖 Scenario: You work as a data engineer. Your task is to load data files stored in cloud storage services into Snowflake tables. The files are stored in Amazon S3, Azure Blob Storage, and Google Cloud Storage (GCS). You will create external stages for each cloud storage, configure access, and load data into Snowflake tables.

🎯 Goal: Build Snowflake external stages for S3, Azure Blob, and GCS with proper credentials and load data from these stages into Snowflake tables.

📋 What You'll Learn

Create an external stage for Amazon S3 with the given bucket and credentials

Create an external stage for Azure Blob Storage with the given container and credentials

Create an external stage for Google Cloud Storage with the given bucket and credentials

Load data from each external stage into a Snowflake table

💡 Why This Matters

🌍 Real World

Data engineers often need to load data from various cloud storage services into Snowflake for analytics and reporting.

💼 Career

Knowing how to configure external stages and load data securely is essential for cloud data platform roles and data engineering jobs.

1

Create an external stage for Amazon S3

Write a Snowflake SQL command to create an external stage called s3_stage that points to the S3 bucket my-s3-bucket/data/. Use the storage integration named my_s3_integration for authentication.

Snowflake

# Write the CREATE STAGE command for S3 here

Need a hint?

Use CREATE OR REPLACE STAGE with the URL set to the S3 bucket path and specify the STORAGE_INTEGRATION for credentials.

2

Create an external stage for Azure Blob Storage

Write a Snowflake SQL command to create an external stage called azure_stage that points to the Azure Blob container mycontainer/data/ in the storage account mystorageaccount. Use the storage integration named my_azure_integration for authentication.

Snowflake

CREATE OR REPLACE STAGE s3_stage
  URL='s3://my-s3-bucket/data/'
  STORAGE_INTEGRATION = my_s3_integration;

# Write the CREATE STAGE command for Azure Blob Storage here

Need a hint?

Use CREATE OR REPLACE STAGE with the URL set to the Azure Blob container path and specify the STORAGE_INTEGRATION.

3

Create an external stage for Google Cloud Storage (GCS)

Write a Snowflake SQL command to create an external stage called gcs_stage that points to the GCS bucket my-gcs-bucket/data/. Use the storage integration named my_gcs_integration for authentication.

Snowflake

CREATE OR REPLACE STAGE s3_stage
  URL='s3://my-s3-bucket/data/'
  STORAGE_INTEGRATION = my_s3_integration;

CREATE OR REPLACE STAGE azure_stage
  URL='azure://mystorageaccount.blob.core.windows.net/mycontainer/data/'
  STORAGE_INTEGRATION = my_azure_integration;

# Write the CREATE STAGE command for GCS here

Need a hint?

Use CREATE OR REPLACE STAGE with the URL set to the GCS bucket path and specify the STORAGE_INTEGRATION.

4

Load data from all external stages into Snowflake tables

Write Snowflake SQL commands to load data from s3_stage into table sales_s3, from azure_stage into table sales_azure, and from gcs_stage into table sales_gcs. Use the COPY INTO command with file format csv_format.

Snowflake

CREATE OR REPLACE STAGE s3_stage
  URL='s3://my-s3-bucket/data/'
  STORAGE_INTEGRATION = my_s3_integration;

CREATE OR REPLACE STAGE azure_stage
  URL='azure://mystorageaccount.blob.core.windows.net/mycontainer/data/'
  STORAGE_INTEGRATION = my_azure_integration;

CREATE OR REPLACE STAGE gcs_stage
  URL='gcs://my-gcs-bucket/data/'
  STORAGE_INTEGRATION = my_gcs_integration;

# Write COPY INTO commands to load data from each stage into respective tables here

Need a hint?

Use COPY INTO with the table name, FROM the stage name prefixed by @, and specify the FILE_FORMAT.