Snowpipe for event-driven loading in Snowflake - Step-by-Step Execution

Process Flow - Snowpipe for event-driven loading

New file arrives in cloud storage

↓

Cloud event triggers notification

↓

Snowpipe receives event

↓

Snowpipe loads new data into table

↓

Data available for queries

↓

END

Snowpipe listens for new files in cloud storage via events, then automatically loads data into Snowflake tables.

Execution Sample

Snowflake

CREATE PIPE my_pipe
  AUTO_INGEST = TRUE
  INTEGRATION = my_notification_integration
  AS COPY INTO my_table
  FROM @my_stage
  FILE_FORMAT = (TYPE = 'CSV');

This code creates a Snowpipe that automatically loads CSV files from a stage into a table when new files arrive.

Process Table

Step	Event	Snowpipe Action	Data State	Notes
1	File uploaded to cloud storage	No action yet	No new data in table	Waiting for event
2	Cloud event sent to Snowpipe	Snowpipe receives event	No new data in table	Trigger received
3	Snowpipe starts loading file	COPY INTO runs	Data loading in progress	File being processed
4	Load completes successfully	Data loaded into table	New data available	Ready for queries
5	No new files	No action	Data unchanged	Idle state

💡 No new files to load, Snowpipe waits for next event

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
File in storage	Absent	Present	Present	Present	Present
Snowpipe status	Idle	Triggered	Loading	Completed	Idle
Table data	Old data only	Old data only	Loading data	New data added	New data present

Key Moments - 3 Insights

Why doesn't Snowpipe load data immediately when a file is uploaded?

Snowpipe waits for a cloud event notification (see Step 2 in execution_table) before starting to load data.

What happens if the file load fails during Snowpipe processing?

Snowpipe retries loading the file until success or manual intervention; this is not shown in the simple flow but is part of Snowpipe's reliability.

Can Snowpipe load multiple files at once?

Yes, Snowpipe can process multiple events and load files concurrently, but each event triggers a separate load operation.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does Snowpipe start loading the file?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Check the 'Snowpipe Action' column for when 'COPY INTO runs'

According to variable_tracker, what is the Snowpipe status after Step 4?

A Loading

B Triggered

C Completed

D Idle

💡 Hint

Look at 'Snowpipe status' value under 'After Step 4'

If no new files arrive, what does Snowpipe do according to the execution_table?

A Starts loading old files

B Waits idle with no action

C Deletes old data

D Sends error notification

💡 Hint

See Step 5 'Snowpipe Action' and 'Notes' columns

Concept Snapshot

Snowpipe automatically loads data into Snowflake tables when new files arrive in cloud storage.
It listens for cloud event notifications to trigger loading.
Use CREATE PIPE with AUTO_INGEST=TRUE to enable event-driven loading.
Snowpipe runs COPY INTO commands behind the scenes.
Data becomes available for queries immediately after loading.
Snowpipe handles retries and scales with file arrivals.

Full Transcript

Snowpipe is a Snowflake feature that loads data automatically when new files arrive in cloud storage. The process starts when a file is uploaded, which triggers a cloud event notification. Snowpipe listens for this event and then runs a COPY INTO command to load the data into a Snowflake table. After loading completes, the new data is available for queries. If no new files arrive, Snowpipe remains idle, waiting for the next event. This event-driven loading helps keep data fresh without manual intervention.