Snowpipe for continuous loading in Snowflake - Commands & Configuration

Introduction

Loading data into a database can be slow and manual. Snowpipe helps by automatically loading new data files as soon as they arrive, so your data is fresh without waiting.

When you want new data files to be loaded into Snowflake automatically without manual steps.

When you have data arriving continuously, like logs or sensor data, and need it ready fast.

When you want to avoid running batch jobs to load data every few hours.

When you want to reduce delays between data arrival and availability for queries.

When you want a simple way to keep your tables updated with new files in cloud storage.

Config File - pipe.sql

pipe.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);

This SQL file creates a Snowpipe named my_pipe that automatically loads CSV files from the stage my_stage into the table my_table.

AUTO_INGEST = TRUE tells Snowflake to load files as soon as they arrive.

The COPY INTO command defines how to load the data, including file format details.

Commands

This command creates a storage stage in Snowflake that points to your S3 bucket where new data files arrive. The storage integration securely connects Snowflake to your cloud storage.

Terminal

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

Expected Output

Successfully executed the query.

This runs the SQL file to create the Snowpipe that will automatically load new files from the stage into your table.

Terminal

snowsql -f pipe.sql

Expected Output

Successfully executed the query.

This command refreshes the pipe to make sure it is ready to detect new files and start loading them immediately.

Terminal

snowsql -q "ALTER PIPE my_pipe REFRESH"

Expected Output

Successfully executed the query.

This command checks the data in your table to confirm that Snowpipe has loaded the new files successfully.

Terminal

snowsql -q "SELECT * FROM my_table"

Expected Output

ID | NAME | VALUE

1  | Alice | 100

2  | Bob   | 200

Key Concept

If you remember nothing else from this pattern, remember: Snowpipe automatically loads new data files as soon as they arrive, keeping your data fresh without manual work.

Common Mistakes

Not creating or configuring the storage integration properly.

Snowpipe cannot access your cloud storage to detect new files without the integration, so no data loads.

Create and test the storage integration before creating the stage and pipe.

Forgetting to set AUTO_INGEST = TRUE when creating the pipe.

Without AUTO_INGEST, Snowpipe will not load files automatically and requires manual triggers.

Always include AUTO_INGEST = TRUE in the pipe definition for continuous loading.

Not refreshing the pipe after creation or changes.

The pipe may not detect new files immediately until refreshed.

Run ALTER PIPE my_pipe REFRESH after creating or modifying the pipe.

Summary

Create a storage stage pointing to your cloud storage bucket with proper integration.

Create a Snowpipe with AUTO_INGEST = TRUE to load new files automatically.

Refresh the pipe to activate it and start loading new data immediately.

Query your table to verify that data is loaded as files arrive.