Snowpipe for continuous loading in Snowflake - Step-by-Step Execution

Process Flow - Snowpipe for continuous loading

New file arrives in cloud storage

↓

Cloud storage event triggers Snowpipe

↓

Snowpipe reads new file

↓

Snowpipe loads data into Snowflake table

↓

Data available for queries immediately

↓

Wait for next file

Snowpipe automatically loads new data files from cloud storage into Snowflake as soon as they arrive, making data available continuously.

Execution Sample

Snowflake

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

This code creates a Snowpipe that automatically loads CSV files from a cloud stage into a Snowflake table as files arrive.

Process Table

Step	Event	Snowpipe Action	Data State	Result
1	File uploaded to cloud storage	Snowpipe receives event notification	No new data loaded	Ready to load new file
2	Snowpipe reads new file	Starts loading data into table	Partial data loaded	Loading in progress
3	Loading completes	Data committed to table	New data available	Data ready for queries
4	Wait for next file	Idle until next event	Data stable	Snowpipe ready

💡 Snowpipe waits idle until a new file upload event triggers the next load cycle.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
New File in Storage	No	Yes	Yes	Yes	Yes
Snowpipe Status	Idle	Triggered	Loading	Completed	Idle
Data in Table	Old data only	Old data only	Partial new data	New data fully loaded	New data fully loaded

Key Moments - 3 Insights

Why doesn't Snowpipe load data before a file upload event?

Snowpipe relies on cloud storage event notifications to know when new files arrive, so it stays idle until triggered (see execution_table Step 1).

What happens if a file is partially loaded?

Snowpipe continues loading until the file is fully processed and commits data atomically (see execution_table Step 2 and 3).

Does Snowpipe keep loading data continuously without new files?

No, Snowpipe waits idle after loading completes until a new file upload event triggers it again (see execution_table Step 4).

Visual Quiz - 3 Questions

Look at the execution table, what is Snowpipe status after Step 2?

A Loading

B Triggered

C Idle

D Completed

💡 Hint

Check the 'Snowpipe Action' column in Step 2 of the execution_table.

At which step does new data become fully available in the table?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the 'Data State' column to find when data is fully loaded.

If no new file arrives, what is Snowpipe's state after Step 4?

A Loading

B Triggered

C Idle

D Error

💡 Hint

Refer to the 'Snowpipe Status' variable in variable_tracker after final step.

Concept Snapshot

Snowpipe automatically loads new files from cloud storage into Snowflake tables.
It uses event notifications to trigger loading immediately when files arrive.
Create a pipe with AUTO_INGEST=TRUE and a COPY INTO statement.
Snowpipe loads data continuously but waits idle if no new files arrive.
Data is available for queries as soon as loading completes.

Full Transcript

Snowpipe is a Snowflake feature that continuously loads data from cloud storage into tables. When a new file is uploaded to the storage location, an event triggers Snowpipe to start loading that file. Snowpipe reads the file, loads the data into the target table, and commits it so the data is immediately available for queries. After loading, Snowpipe waits idle until another file upload event occurs. This process allows near real-time data ingestion without manual intervention. The key steps are file arrival, event trigger, data loading, and waiting for the next file. Snowpipe status changes from idle to triggered to loading and back to idle after completion. This automation simplifies continuous data ingestion pipelines.