Snowflake
COPY INTO & Snowpipe: Batch and Continuous Ingestion
Load data in bulk or continuously as files arrive, without manual intervention.
COPY INTO is Snowflake's primary bulk-loading command, reading files from a stage and inserting them into a target table. It tracks load metadata for each file (via file name and checksum) for 64 days by default, automatically skipping files that were already successfully loaded — making repeated COPY INTO calls against the same stage idempotent and safe to schedule.
COPY INTO is like manually running a delivery truck on a schedule to pick up packages from a depot; Snowpipe is like having packages automatically loaded onto the truck the instant they arrive at the depot, with no dispatcher needed.
Key Concepts
1
Snowpipe extends this to continuous, near-real-time ingestion. Instead of manually or periodically running COPY INTO, Snowpipe automatically loads new files as soon as they land in a stage, triggered either by cloud storage event notifications (e.g., S3 event notifications via SQS) or via the REST API (insertFiles). This is serverless — Snowflake manages the compute for Snowpipe loads and bills per-second based on actual usage, rather than requiring a dedicated virtual warehouse to be running.
continuous, near-real-time ingestionSnowpipeCOPY INTOinsertFiles
2
Snowpipe is designed for high-frequency, small-file ingestion patterns (e.g., IoT events, clickstream data, log files), where latency from file arrival to queryable data is typically under a minute. For very high-throughput or very large file loads, standard COPY INTO on an appropriately-sized warehouse is often more cost-efficient than Snowpipe.
COPY INTO
3
Both mechanisms rely on file format objects and stages, and both provide load history views (COPY_HISTORY, PIPE_USAGE_HISTORY) for monitoring and debugging failed or skipped loads.
COPY_HISTORYPIPE_USAGE_HISTORY