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

Snowpipe for Event-Driven Loading

📖 Scenario: You work at a company that receives daily sales data files in a cloud storage bucket. You want to automatically load these files into Snowflake as soon as they arrive, without manual intervention.

🎯 Goal: Build a Snowpipe configuration that automatically loads new files from a cloud storage stage into a Snowflake table using event-driven loading.

📋 What You'll Learn

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

Create a named external stage called sales_stage pointing to the cloud storage bucket s3://my_s3_bucket/sales/.

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

Configure the Snowpipe to use event notifications for automatic loading.

💡 Why This Matters

🌍 Real World

Automatically loading data files into Snowflake as soon as they arrive in cloud storage saves time and reduces manual work.

💼 Career

Many data engineering roles require setting up automated data ingestion pipelines using Snowpipe and cloud storage event notifications.

1

Create the sales_data table

Write a SQL statement to create a table called sales_data with columns id as INTEGER, product as STRING, and amount as FLOAT.

Snowflake

-- 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

Write a SQL statement to create or replace an external stage called sales_stage that points to the cloud storage location s3://my_s3_bucket/sales/.

Snowflake

-- Your code here

Need a hint?

Use CREATE OR REPLACE STAGE with the URL parameter for the S3 bucket path.

3

Create the Snowpipe sales_pipe

Write a SQL statement to create or replace a Snowpipe named sales_pipe that loads data from the stage sales_stage into the table sales_data using the COPY INTO command.

Snowflake

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

CREATE OR REPLACE STAGE sales_stage
URL='s3://my_s3_bucket/sales/';
-- Your code here

Need a hint?

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

4

Configure event notifications for automatic loading

Write a SQL statement to alter the pipe sales_pipe to enable event-driven loading by setting AUTO_INGEST = TRUE.

Snowflake

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

Need a hint?

Use ALTER PIPE with SET AUTO_INGEST = TRUE to enable event notifications.