Skip to main content

Auto-Ingest with Cloud Event Notifications

Snowpipe is fully automated through a feature called Auto-Ingest. Rather than manually calling a REST API to tell Snowpipe to load a file, Auto-Ingest relies on cloud provider event notifications (like AWS SQS) to automatically trigger the ingestion process the moment a file lands in cloud storage.

Topics Covered​

  • Enabling AUTO_INGEST = TRUE
  • Cloud Provider Integrations (AWS, Azure, GCP)
  • End-to-End Walkthrough (AWS S3)

The AUTO_INGEST Parameter​

When you create a pipe in Snowflake, you must explicitly tell it to listen for automated events by setting AUTO_INGEST = TRUE.

CREATE OR REPLACE PIPE MY_DATABASE.MY_SCHEMA.MY_AUTO_PIPE 
AUTO_INGEST = TRUE
AS
COPY INTO MY_TABLE FROM @MY_STAGE;

Once this is created, Snowflake provisions a dedicated message queue endpoint (e.g., an SQS ARN on AWS) exclusively for this pipe. Your cloud provider must be configured to send messages to this specific endpoint.

Cloud Provider Integrations​

Snowflake integrates with the native event notification systems of all three major clouds:

  1. AWS S3: Uses Amazon Simple Queue Service (SQS) or Amazon Simple Notification Service (SNS).
  2. Azure Blob Storage: Uses Azure Event Grid.
  3. Google Cloud Storage (GCS): Uses Google Cloud Pub/Sub.

Hands-On Demonstration: End-to-End AWS S3 Auto-Ingest Setup​

Let's walk through the exact steps a cloud administrator and a Snowflake data engineer would take to set up an automated pipeline for a retail company in Mumbai.

1. Database & Stage Preparation​

(Run this in Snowflake)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_RETAIL_DB;
USE SCHEMA BEEMS_RETAIL_DB.PUBLIC;

CREATE OR REPLACE TABLE MUMBAI_SALES (
TRANSACTION_ID STRING,
STORE_NAME STRING,
AMOUNT NUMBER
);

-- Assume a Storage Integration was already created by the ACCOUNTADMIN
CREATE OR REPLACE STAGE SALES_S3_STAGE
URL = 's3://beems-retail-bucket/mumbai_sales/'
STORAGE_INTEGRATION = S3_ROLE_INTEGRATION;

2. Creating the Pipe​

(Run this in Snowflake)

We create the pipe with AUTO_INGEST = TRUE.

CREATE OR REPLACE PIPE MUMBAI_SALES_PIPE 
AUTO_INGEST = TRUE
AS
COPY INTO MUMBAI_SALES
FROM @SALES_S3_STAGE
FILE_FORMAT = (TYPE = 'CSV');

3. Retrieving the SQS Queue ARN​

(Run this in Snowflake)

Because AUTO_INGEST is enabled, Snowflake has created an SQS queue in its own AWS account to listen for your files. We need to find the ID (ARN) of that queue to give to our AWS Administrator.

-- Retrieve the notification_channel (The SQS ARN)
DESCRIBE PIPE MUMBAI_SALES_PIPE;

-- Example Output: arn:aws:sqs:ap-south-1:123456789012:cyz12345-sqs

(To verify which user you are to ensure you have the DESCRIBE privilege, run SELECT CURRENT_USER();)

4. Configuring AWS S3 (Cloud Admin Step)​

The AWS Administrator (e.g., Rohan from Cloud Ops) takes the ARN retrieved in step 3 and configures the S3 bucket.

In the AWS Management Console:

  1. Navigate to the beems-retail-bucket in S3.
  2. Go to Properties -> Event Notifications -> Create event notification.
  3. Name it TriggerSnowflakePipe.
  4. Select the event type: All object create events (s3:ObjectCreated:*). This ensures that whether a file is uploaded, copied, or multipart-uploaded, the event triggers.
  5. Set the Destination to SQS Queue.
  6. Paste the SQS ARN (arn:aws:sqs:ap-south-1:123456789012:cyz12345-sqs) copied from Snowflake.
  7. Save.

5. Testing the Pipeline​

To test it, an application writes a CSV file named sales_001.csv into s3://beems-retail-bucket/mumbai_sales/.

The moment the file is written, AWS sends an SQS message to Snowflake. Snowpipe reads the message, provisions compute, and runs the COPY INTO command.

Wait 1-2 minutes, then check the table:

SELECT * FROM MUMBAI_SALES;

If the data is there, your automated pipeline is fully functional!

Summary​

  • Auto-Ingest replaces the need to write custom code or Airflow DAGs to trigger Snowpipe.
  • It relies on native cloud event services (SQS, Event Grid, Pub/Sub).
  • You must use DESCRIBE PIPE to retrieve the notification_channel and configure your cloud bucket to send messages to it.