Snowpipe for continuous loading in Snowflake - Mini Project: Build & Apply

Snowpipe for Continuous Loading

📖 Scenario: You work for a company that collects sales data in CSV files. These files are uploaded continuously to a cloud storage bucket. Your task is to set up Snowpipe in Snowflake to automatically load new files into a table as soon as they arrive.

🎯 Goal: Build a Snowpipe configuration that continuously loads CSV files from a cloud storage stage into a Snowflake table.

📋 What You'll Learn

Create a Snowflake table named sales_data with columns id (integer), product (string), and amount (number).

Create a named external stage called sales_stage pointing to the cloud storage location.

Create a Snowpipe named sales_pipe that loads data from sales_stage into sales_data using a COPY statement.

Configure Snowpipe to auto-ingest files continuously as they arrive.

💡 Why This Matters

🌍 Real World

Companies often collect data files continuously in cloud storage. Snowpipe automates loading these files into Snowflake tables without manual intervention.

💼 Career

Understanding Snowpipe setup is essential for data engineers and cloud architects working with Snowflake to build real-time data pipelines.

1

Create the sales_data table

Create a table called sales_data with columns id as INTEGER, product as STRING, and amount as NUMBER.

Snowflake

# Create the sales_data table with id, product, and amount columns
# Your code here

Need a hint?

Use CREATE OR REPLACE TABLE followed by the table name and column definitions.

2

Create the external stage sales_stage

Create an external stage called sales_stage that points to the cloud storage location @my_s3_bucket/sales/.

Snowflake

CREATE OR REPLACE TABLE sales_data (
    id INTEGER,
    product STRING,
    amount NUMBER
);

# Create the external stage sales_stage pointing to @my_s3_bucket/sales/
# Your code here

Need a hint?

Use CREATE OR REPLACE STAGE with the URL parameter and specify a CSV file format.

3

Create the Snowpipe sales_pipe

Create a Snowpipe named sales_pipe that loads data from sales_stage into the sales_data table using a COPY INTO statement.

Snowflake

CREATE OR REPLACE TABLE sales_data (
    id INTEGER,
    product STRING,
    amount NUMBER
);

CREATE OR REPLACE STAGE sales_stage
URL='s3://my_s3_bucket/sales/'
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');

# Create Snowpipe sales_pipe with COPY INTO sales_data from sales_stage
# Your code here

Need a hint?

Use CREATE OR REPLACE PIPE with a COPY INTO statement referencing the stage and table.

4

Enable auto-ingest for continuous loading

Alter the pipe sales_pipe to enable auto-ingest so Snowpipe loads files automatically as they arrive.

Snowflake

CREATE OR REPLACE TABLE sales_data (
    id INTEGER,
    product STRING,
    amount NUMBER
);

CREATE OR REPLACE STAGE sales_stage
URL='s3://my_s3_bucket/sales/'
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');

CREATE OR REPLACE PIPE sales_pipe AS
COPY INTO sales_data
FROM @sales_stage
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');

# Alter sales_pipe to set AUTO_INGEST = TRUE
# Your code here

Need a hint?

Use ALTER PIPE with SET AUTO_INGEST = TRUE to enable continuous loading.