Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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_INGEST when 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

CommandPurpose
CREATE PIPEDefines a Snowpipe for continuous data loading
AUTO_INGEST = TRUEEnables automatic file ingestion on arrival
COPY INTOSpecifies target table and source stage for loading
CREATE STAGEDefines location where files are stored for loading
CREATE FILE FORMATDefines 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.