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
| Feature | Description |
|---|---|
| AUTO_INGEST | Enables automatic loading triggered by cloud event notifications |
| COPY INTO | Specifies the command to load data from stage to table |
| FILE_FORMAT | Defines the format of files to load (CSV, JSON, etc.) |
| Stage | Cloud storage location where data files arrive |
| Pipe | Object 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.
