Stages (internal and external) in Snowflake - Mini Project: Build & Apply

Working with Stages (internal and external) in Snowflake

📖 Scenario: You are managing data files for a company that uses Snowflake as their cloud data platform. You need to organize files for loading and unloading data using Snowflake stages. You will create an internal stage to hold files inside Snowflake and an external stage that points to an Amazon S3 bucket.

🎯 Goal: Build Snowflake stages: first create an internal stage named my_internal_stage and then create an external stage named my_external_stage that points to a specific S3 bucket with credentials.

📋 What You'll Learn

Create an internal stage named my_internal_stage with no additional options.

Create a variable s3_bucket_url with the exact value 's3://mycompany-data-bucket/'.

Create an external stage named my_external_stage that uses the s3_bucket_url variable and includes the storage integration my_s3_integration.

Add the file_format option to the external stage with the value csv_format.

💡 Why This Matters

🌍 Real World

Companies use Snowflake stages to organize and manage data files for loading and unloading data efficiently and securely.

💼 Career

Understanding how to create and configure internal and external stages is essential for data engineers and cloud architects working with Snowflake.

1

Create an internal stage

Write a Snowflake SQL command to create an internal stage named my_internal_stage with no additional options.

Snowflake

-- Your code here to create internal stage my_internal_stage

Need a hint?

Use the CREATE STAGE command followed by the stage name.

2

Set the S3 bucket URL variable

Create a variable called s3_bucket_url and set it to the string 's3://mycompany-data-bucket/'.

Snowflake

CREATE STAGE my_internal_stage;
-- Your code here to create variable s3_bucket_url

Need a hint?

Use the SET command to create a session variable.

3

Create an external stage using the S3 bucket URL

Write a Snowflake SQL command to create an external stage named my_external_stage that uses the variable s3_bucket_url for the URL and the storage integration my_s3_integration.

Snowflake

CREATE STAGE my_internal_stage;
SET s3_bucket_url = 's3://mycompany-data-bucket/';
-- Your code here to create external stage my_external_stage

Need a hint?

Use CREATE STAGE with URL and STORAGE_INTEGRATION options.

4

Add file format option to the external stage

Modify the external stage my_external_stage to add the option FILE_FORMAT = csv_format.

Snowflake

CREATE STAGE my_internal_stage;
SET s3_bucket_url = 's3://mycompany-data-bucket/';
CREATE STAGE my_external_stage URL = '${s3_bucket_url}' STORAGE_INTEGRATION = my_s3_integration
-- Your code here to add FILE_FORMAT option

Need a hint?

Add FILE_FORMAT = csv_format at the end of the CREATE STAGE command.