How to Use Snowpipe in Snowflake: Quick Guide
To use
Snowpipe in Snowflake, create a pipe object that defines the source stage and target table, then load data continuously by automatically ingesting files as they arrive. You can trigger Snowpipe using cloud event notifications or manual commands to automate data loading.Syntax
The basic syntax to create a Snowpipe involves defining a pipe with a name, specifying the source stage where files arrive, and the target table where data will be loaded. You can also specify a file format for parsing the data.
- CREATE PIPE: Command to create the pipe.
- PIPE NAME: Unique identifier for the pipe.
- AUTO_INGEST: Enables automatic loading when files arrive.
- AS COPY INTO: Defines the target table and source stage for data loading.
sql
CREATE OR REPLACE PIPE my_pipe AUTO_INGEST = TRUE AS COPY INTO my_table FROM @my_stage FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');
Example
This example shows how to create a Snowpipe that automatically loads CSV files from a named stage into a target table. It demonstrates continuous data ingestion without manual intervention.
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 FILE_FORMAT = my_csv_format; CREATE OR REPLACE PIPE my_pipe AUTO_INGEST = TRUE AS COPY INTO my_table FROM @my_stage FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');
Output
Table MY_TABLE created.
File format MY_CSV_FORMAT created.
Stage MY_STAGE created.
Pipe MY_PIPE created with auto ingest enabled.
Common Pitfalls
Common mistakes when using Snowpipe include:
- Not enabling
AUTO_INGESTwhen expecting automatic loading. - Incorrect file format settings causing load failures.
- Missing or misconfigured cloud event notifications for auto ingest.
- Trying to load files not placed in the specified stage.
Always verify your stage, file format, and pipe settings match your data source and target table.
sql
/* Wrong: Missing AUTO_INGEST disables automatic loading */ CREATE OR REPLACE PIPE wrong_pipe AS COPY INTO my_table FROM @my_stage FILE_FORMAT = (FORMAT_NAME = 'my_csv_format'); /* Right: Enable AUTO_INGEST for automatic loading */ CREATE OR REPLACE PIPE correct_pipe AUTO_INGEST = TRUE AS COPY INTO my_table FROM @my_stage FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');
Quick Reference
| Command | Purpose |
|---|---|
| CREATE PIPE | Defines a Snowpipe for continuous data loading |
| AUTO_INGEST = TRUE | Enables automatic file ingestion on arrival |
| COPY INTO | Specifies target table and source stage for loading |
| CREATE STAGE | Defines location where files are stored for loading |
| CREATE FILE FORMAT | Defines how to parse incoming files |
Key Takeaways
Create a pipe with AUTO_INGEST = TRUE to enable continuous loading.
Define a stage and file format that match your data source.
Use COPY INTO in the pipe to specify the target table and source.
Ensure cloud event notifications are configured for automatic triggers.
Test your pipe with sample files to confirm correct loading.
