Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How Snowpipe Works: Automated Data Loading in Snowflake

Snowpipe is a Snowflake service that automatically loads data files from cloud storage into tables as soon as files arrive. It uses event notifications to detect new files and serverless compute to process them continuously without manual intervention.
📐

Syntax

Snowpipe uses the CREATE PIPE command to define a pipe that specifies the source stage and the target table for loading data. It listens for new files and loads them automatically.

Key parts:

  • CREATE PIPE pipe_name: Defines the pipe.
  • AUTO_INGEST = TRUE: Enables automatic loading triggered by cloud event notifications.
  • AS COPY INTO target_table FROM @stage: Specifies the copy command to load data from the stage to the table.
sql
CREATE OR REPLACE PIPE my_pipe
  AUTO_INGEST = TRUE
  AS COPY INTO my_table
  FROM @my_stage
  FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);
💻

Example

This example shows how to create a Snowpipe that automatically loads CSV files from a named stage into a target table. It demonstrates setting up the pipe with auto-ingest enabled and the copy command specifying the file format.

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

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

CREATE OR REPLACE PIPE my_pipe
  AUTO_INGEST = TRUE
  AS COPY INTO my_table
  FROM @my_stage
  FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);
Output
Table MY_TABLE created. Stage MY_STAGE created. Pipe MY_PIPE created.
⚠️

Common Pitfalls

Common mistakes when using Snowpipe include:

  • Not configuring cloud event notifications properly, so Snowpipe does not get triggered.
  • Using incorrect file formats or missing file format options causing load failures.
  • Not granting necessary permissions on the stage or storage integration.
  • Expecting immediate data availability; Snowpipe processes files quickly but not instantly.

Always verify event setup and permissions to ensure smooth automatic loading.

sql
/* Wrong: Missing AUTO_INGEST disables automatic loading */
CREATE OR REPLACE PIPE my_pipe_wrong
  AS COPY INTO my_table
  FROM @my_stage;

/* Right: Enable AUTO_INGEST for automatic triggers */
CREATE OR REPLACE PIPE my_pipe_right
  AUTO_INGEST = TRUE
  AS COPY INTO my_table
  FROM @my_stage;
📊

Quick Reference

FeatureDescription
AUTO_INGESTEnables automatic loading triggered by cloud event notifications
COPY INTOSpecifies the command to load data from stage to table
FILE_FORMATDefines the format of files to load (CSV, JSON, etc.)
StageCloud storage location where data files arrive
PipeObject that manages continuous data loading
✅

Key Takeaways

Snowpipe automates data loading by reacting to new files in cloud storage using event notifications.
Define a pipe with AUTO_INGEST = TRUE and a COPY INTO command to set up continuous loading.
Ensure cloud event notifications and permissions are correctly configured for Snowpipe to work.
Snowpipe uses serverless compute to load data quickly without manual intervention.
File format settings must match the incoming data to avoid load errors.