Bird
Raised Fist0
SnowflakeComparisonBeginner · 4 min read

Copy Into vs Snowpipe: Key Differences and When to Use Each

In Snowflake, COPY INTO is a manual command to load data files into tables, while Snowpipe is an automated, continuous data ingestion service that loads data as soon as files arrive. COPY INTO is best for batch loads, and Snowpipe is ideal for near real-time streaming data.
⚖️

Quick Comparison

This table summarizes the main differences between COPY INTO and Snowpipe in Snowflake.

FeatureCOPY INTOSnowpipe
Loading TypeManual batch loadingAutomated continuous loading
TriggerUser runs commandTriggered by file arrival event
LatencyMinutes to hoursSeconds to minutes
Cost ModelCompute charged per runCompute charged per file processed
Use CaseBulk data loadsReal-time or near real-time ingestion
Setup ComplexitySimple SQL commandRequires pipe and event notification setup
⚖️

Key Differences

COPY INTO is a SQL command you run manually or schedule to load data files from external stages or internal locations into Snowflake tables. It processes files in batches and is suitable when you want control over when data loads happen.

Snowpipe is a serverless, continuous data ingestion service that automatically loads data as soon as new files arrive in a cloud storage location. It uses event notifications or REST API calls to trigger loading, enabling near real-time data availability.

While COPY INTO charges compute resources only when you run the command, Snowpipe charges based on the number of files processed, making it cost-efficient for small, frequent loads. Setup for Snowpipe requires creating a pipe object and configuring cloud event notifications, which is more complex than running COPY INTO directly.

⚖️

Code Comparison

Here is how you manually load data using COPY INTO from an S3 bucket into a Snowflake table.

sql
COPY INTO my_table
FROM @my_s3_stage/path/
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"')
ON_ERROR = 'CONTINUE';
Output
Loads all matching files from the specified S3 path into 'my_table' in batch.
↔️

Snowpipe Equivalent

To achieve continuous loading with Snowpipe, you create a pipe and configure event notifications. Here is the SQL to create a pipe that loads data from the same S3 stage automatically.

sql
CREATE OR REPLACE PIPE my_pipe
AUTO_INGEST = TRUE
AS
COPY INTO my_table
FROM @my_s3_stage/path/
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');
Output
Creates a pipe that automatically loads new files arriving at the S3 path into 'my_table' with near real-time ingestion.
🎯

When to Use Which

Choose COPY INTO when you have large batches of data to load at scheduled times or want full control over the loading process. It is simpler to set up and good for bulk imports.

Choose Snowpipe when you need data loaded continuously and quickly as it arrives, such as streaming logs or event data. It reduces manual effort and latency but requires initial setup of pipes and event notifications.

✅

Key Takeaways

COPY INTO is manual batch loading; Snowpipe is automated continuous loading.
Snowpipe offers near real-time data ingestion with event-driven triggers.
COPY INTO is simpler to use but has higher latency for frequent loads.
Snowpipe charges per file processed, ideal for small frequent data loads.
Use COPY INTO for bulk loads and Snowpipe for streaming or near real-time data.