Bird
Raised Fist0
SnowflakeConceptBeginner · 3 min read

What is Snowpipe in Snowflake: Continuous Data Loading Explained

Snowpipe in Snowflake is a service that automatically loads data into tables as soon as new files arrive in a cloud storage location. It enables continuous, near real-time data ingestion without manual intervention.
⚙️

How It Works

Imagine you have a mailbox that automatically sorts letters into folders as soon as they arrive. Snowpipe works similarly for data files. When new data files land in a cloud storage location like Amazon S3 or Azure Blob Storage, Snowpipe detects them and loads their contents into Snowflake tables right away.

This process happens continuously and automatically, so you don’t have to run manual commands or wait for scheduled batch jobs. Snowpipe uses event notifications or polling to know when new files appear, then processes them quickly and efficiently.

💻

Example

This example shows how to create a Snowpipe that loads data from an S3 bucket into a Snowflake table automatically.

sql
CREATE OR REPLACE TABLE my_table (
  id INT,
  name STRING,
  created_at TIMESTAMP
);

CREATE OR REPLACE FILE FORMAT my_csv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1;

CREATE OR REPLACE STAGE my_stage
  URL = 's3://my-bucket/data/'
  FILE_FORMAT = my_csv_format;

CREATE OR REPLACE PIPE my_pipe AS
  COPY INTO my_table
  FROM @my_stage
  FILE_FORMAT = (FORMAT_NAME => 'my_csv_format')
  ON_ERROR = 'CONTINUE';

-- To start Snowpipe, you can use event notifications from S3 to trigger it automatically.
Output
Table MY_TABLE created. File format MY_CSV_FORMAT created. Stage MY_STAGE created. Pipe MY_PIPE created.
🎯

When to Use

Use Snowpipe when you need to load data continuously and quickly as it arrives, such as streaming logs, IoT device data, or real-time analytics. It is ideal for scenarios where waiting for batch loads would cause delays.

For example, a company tracking website clicks can use Snowpipe to load click data immediately for up-to-date dashboards. Or a sensor network can stream data into Snowflake for instant monitoring.

✅

Key Points

  • Snowpipe automates continuous data loading from cloud storage to Snowflake tables.
  • It uses event notifications or polling to detect new files.
  • Snowpipe supports near real-time data ingestion without manual steps.
  • It is best for streaming or frequently arriving data sources.
✅

Key Takeaways

Snowpipe automatically loads new data files into Snowflake as they arrive.
It enables near real-time data ingestion without manual intervention.
Use Snowpipe for streaming data or frequent file arrivals to keep data fresh.
Snowpipe integrates with cloud storage event notifications for instant loading.