S3 Event → Snowpipe → Table Flow
The most common implementation of Snowpipe is using Amazon Web Services (AWS) S3 as the data lake storage. This module provides a detailed, step-by-step walkthrough of exactly how a file moves from an S3 bucket into a Snowflake table without any manual intervention.
Topics Covered
- The End-to-End Architectural Flow
- Setting Up the Snowflake Infrastructure
- Setting Up the AWS Infrastructure
- Verification and Troubleshooting
The End-to-End Architectural Flow
Here is the exact sequence of events that happens behind the scenes:
- File Lands: An application or data engineer drops a file (e.g.,
transactions_001.csv) into the AWS S3 Bucket. - S3 Event Notification: S3 detects the new object. It immediately generates an event payload containing the filename and path.
- SQS Queue: S3 publishes that event payload directly to an Amazon Simple Queue Service (SQS) queue managed by Snowflake.
- Snowpipe Picks Up: A background Snowflake process continuously polls that SQS queue. It reads the message, grabs the filename, and puts it in the pipe's internal ingestion queue.
- Ingestion: Snowpipe provisions serverless compute, runs the
COPY INTOcommand defined in the pipe, reads the file from S3, and loads the data into the target table.
Hands-On Demonstration: Building the Flow
Let's configure this flow for a fictitious e-commerce company in India that processes daily transactions.
1. Setting up the Snowflake Infrastructure
(Run these commands in your Snowflake worksheet)
First, we set up the destination table and the external stage that points to our AWS S3 bucket.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_ECOMMERCE_DB;
USE SCHEMA BEEMS_ECOMMERCE_DB.PUBLIC;
-- 1. Create Target Table
CREATE OR REPLACE TABLE DAILY_TRANSACTIONS (
TXN_ID STRING,
CUSTOMER_NAME STRING,
STATE STRING,
AMOUNT NUMBER(10,2)
);
-- 2. Create the External Stage
-- (Assumes an ACCOUNTADMIN has already created an integration named AWS_S3_INT)
CREATE OR REPLACE STAGE TXN_S3_STAGE
URL = 's3://beems-ecommerce-prod/transactions/'
STORAGE_INTEGRATION = AWS_S3_INT;
-- 3. Create the Pipe with AUTO_INGEST
CREATE OR REPLACE PIPE TXN_SNOWPIPE
AUTO_INGEST = TRUE
AS
COPY INTO DAILY_TRANSACTIONS
FROM @TXN_S3_STAGE
FILE_FORMAT = (TYPE = 'CSV', SKIP_HEADER = 1);
2. Linking the SQS Queue to the S3 Bucket
Once the pipe is created, Snowflake generates a unique SQS queue. We need the ARN (Amazon Resource Name) of this queue to tell AWS where to send the event notifications.
(To ensure you have the correct privileges, verify your active role. Run SELECT CURRENT_ROLE();)
-- Retrieve the SQS Queue ARN
DESCRIBE PIPE TXN_SNOWPIPE;
Look for the notification_channel column in the output. It will look like: arn:aws:sqs:ap-south-1:123456789012:cyz12345-sqs.
AWS Configuration (Conceptual Steps for your Cloud Admin):
- Log into the AWS Console and go to the
beems-ecommerce-prodS3 bucket. - Go to Properties -> Event Notifications -> Create Event Notification.
- Event Types: Select All object create events.
- Destination: Select SQS Queue, choose "Enter SQS queue ARN", and paste the ARN from Snowflake.
- Save changes.
3. Verifying the Flow
Let's simulate a file dropping into the S3 bucket.
-- Simulate what Snowpipe loads into the table
INSERT INTO DAILY_TRANSACTIONS VALUES
('T1', 'Ananya Sharma', 'Delhi', 1500.50),
('T2', 'Vikram Singh', 'Punjab', 450.00),
('T3', 'Meera Menon', 'Kerala', 3200.75);
To verify that the pipe is actively listening to the SQS queue, use SYSTEM$PIPE_STATUS.
-- Check the health of the pipe
SELECT SYSTEM$PIPE_STATUS('BEEMS_ECOMMERCE_DB.PUBLIC.TXN_SNOWPIPE');
You should see "executionState":"RUNNING" and "pendingFileCount":0 (meaning it has caught up on all files).
Common Setup Errors & Debugging
If the data isn't loading, check these common failure points:
- IAM Permissions: S3 will refuse to send the notification to the SQS queue if the IAM Trust Policy isn't correct. Check the AWS S3 Event Notification page for permission errors when saving the SQS ARN.
- Bucket Prefix Mismatch: If you set the S3 Event Notification to trigger on the whole bucket, but your Snowflake Stage only points to
/transactions/, Snowpipe will get confused by files arriving in/backups/and fail. - Data Type / CSV Format: The file loaded successfully, but
COPY_HISTORYshowsLOAD_FAILED. Did the CSV have a header row? If so, ensure your pipe specifiesSKIP_HEADER = 1in the file format.
Summary
- The automated flow relies on S3 Event Notifications sending payloads to a Snowflake-managed SQS Queue.
- You must create the pipe with
AUTO_INGEST = TRUEto generate the SQS ARN. - Use
DESCRIBE PIPEto get the ARN to configure in the AWS Console. - Always use
SYSTEM$PIPE_STATUSto verify the connection is healthy.