Skip to main content

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:

  1. File Lands: An application or data engineer drops a file (e.g., transactions_001.csv) into the AWS S3 Bucket.
  2. S3 Event Notification: S3 detects the new object. It immediately generates an event payload containing the filename and path.
  3. SQS Queue: S3 publishes that event payload directly to an Amazon Simple Queue Service (SQS) queue managed by Snowflake.
  4. 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.
  5. Ingestion: Snowpipe provisions serverless compute, runs the COPY INTO command 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):

  1. Log into the AWS Console and go to the beems-ecommerce-prod S3 bucket.
  2. Go to Properties -> Event Notifications -> Create Event Notification.
  3. Event Types: Select All object create events.
  4. Destination: Select SQS Queue, choose "Enter SQS queue ARN", and paste the ARN from Snowflake.
  5. 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:

  1. 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.
  2. 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.
  3. Data Type / CSV Format: The file loaded successfully, but COPY_HISTORY shows LOAD_FAILED. Did the CSV have a header row? If so, ensure your pipe specifies SKIP_HEADER = 1 in 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 = TRUE to generate the SQS ARN.
  • Use DESCRIBE PIPE to get the ARN to configure in the AWS Console.
  • Always use SYSTEM$PIPE_STATUS to verify the connection is healthy.