19 Aug 2026

Snowflake Data Loading Options: Choosing the Right Method for Your Workload

Quick Summary

Snowflake offers several distinct data loading methods: bulk loading via COPY INTO, Snowpipe for file-based micro-batches, Snowpipe Streaming for direct row-level ingestion, external tables for querying data without loading it, and Dynamic Tables for declarative, SQL-based transformation pipelines. One important, current detail: Snowpipe Streaming's original "classic" architecture is being formally deprecated (announcement planned mid-2026, with an 18-month migration window), the current recommendation is the newer high-performance architecture for any new implementation.

Key Takeaways

The right method depends on data velocity, not preference. Bulk historical loads, scheduled micro-batches, and real-time row-level streams each have a purpose-built Snowflake feature; using the wrong one for your workload creates unnecessary latency or cost.
Snowpipe Streaming's classic architecture is being deprecated. Snowflake plans a formal deprecation announcement in mid-2026, followed by an 18-month migration window. Any new streaming implementation should use the high-performance architecture instead.
Snowpipe and Snowpipe Streaming are complementary, not competing. Snowpipe handles file-based micro-batches; Streaming handles direct, row-level ingestion from sources like Kafka or application events, with no staging files.
You don't always need to load data first. External tables let you query data in cloud storage directly, which is useful when you need only a subset of a much larger dataset.
Dynamic Tables handle transformation, not just loading, letting you define transformations declaratively in SQL or Python while Snowflake automatically manages refresh scheduling.

Bulk Loading with the COPY Command

The COPY INTO command loads batches of data from files already staged in cloud storage, or from files first copied to an internal Snowflake stage. This relies on a user-provisioned virtual warehouse specified in the COPY statement; sizing that warehouse appropriately for the expected load is your responsibility, not Snowflake's.

COPY INTO my_table
FROM @my_stage/data_files/
FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',')
ON_ERROR = 'CONTINUE';

This is the right choice for large, scheduled, or one-time historical loads where a few minutes of latency doesn't matter.

Continuous Loading with Snowpipe

Snowpipe automates file-based loading using event notifications from your cloud provider (AWS SNS/SQS, or equivalents on Azure and GCP). When a new file lands in a staged location, an event notification triggers the pipe's associated COPY command automatically, typically loading within one to two minutes.

CREATE PIPE my_pipe
AUTO_INGEST = TRUE
AS
COPY INTO my_table
FROM @my_stage
FILE_FORMAT = (TYPE = 'CSV');

Snowpipe is serverless; Snowflake manages the compute automatically, and you're billed per second based on actual ingestion workload rather than a provisioned warehouse. Snowflake recommends targeting 100-250MB compressed file sizes for optimal throughput; if your upstream systems produce many small files, aggregating them before ingestion (a common pattern using a service like Kinesis Firehose) avoids inefficient small-file loading.

Real-Time Ingestion with Snowpipe Streaming

Snowpipe Streaming is a fundamentally different mechanism: applications write rows directly into Snowflake tables as they arrive, with no files, staging, or intermediate storage. Data becomes queryable within seconds. This is the right tool for Kafka topics, IoT telemetry, application event streams, and change data capture (CDC) pipelines, not a replacement for file-based Snowpipe, but a complement to it for workloads that arrive as continuous rows rather than discrete files.

Important, current detail: Snowpipe Streaming has two architectures. The original "classic architecture" (using the snowflake-ingest-sdk Java SDK) is planned for deprecation; Snowflake intends to issue a formal deprecation announcement in mid-2026, after which an 18-month migration window begins before end-of-life. The current, recommended path for any new implementation is the high-performance architecture, which offers real, substantial improvements: throughput up to 20 GB/s per table, 5-second latency, flat consumption-based pricing (0.0037 credits per uncompressed GB), roughly 30% lower client-side resource cost than classic, exactly-once delivery guarantees, automatic schema evolution, and support for Snowflake-managed Apache Iceberg tables (v2 and v3).

Querying Without Loading: External Tables

You don't always need to load data before querying it. External tables let you query data sitting in external cloud storage directly, without first loading it into Snowflake. This is particularly useful for organizations with large volumes of data already in cloud storage who only need to query a subset, recent data, for instance, rather than the full historical set. Materialized views can be layered on top of a subset for improved query performance without a full load.

Declarative Transformation with Dynamic Tables

Dynamic Tables let you define data transformations declaratively in SQL or Python, while Snowflake automatically manages the underlying refresh scheduling and dependency tracking. Unlike a manually scheduled task, a Dynamic Table reprocesses only the data that has changed since the last refresh, keeping high-volume, complex pipelines simpler and more cost-efficient than hand-built orchestration. Adjusting a single freshness parameter can turn a batch pipeline into a near-streaming one without rebuilding it.

Comparing the Options

Method	Latency	Best For
COPY INTO (bulk)	Minutes to hours	Large historical loads, scheduled batch jobs
Snowpipe	1-2 minutes	Continuous file-based micro-batches
Snowpipe Streaming	Seconds	Kafka, IoT, CDC, application event streams
External Tables	Query-time, no load	Querying a subset of data already in cloud storage
Dynamic Tables	Configurable	Declarative, automatically-managed transformation pipelines

How DataTerrain Helps

Choosing the right combination of these methods, and migrating off Snowpipe Streaming's classic architecture before its deprecation window closes, is exactly the kind of architecture decision our Data Lake practice handles, having built environments processing billions of records. Our ETL Solutions team designs the ingestion layer feeding Snowflake from source systems, whether that's batch, Snowpipe, or real-time streaming.

Ready to Optimize Your Snowflake Ingestion Strategy?

Talk to a DataTerrain Data Engineering Specialist →

Frequently Asked Questions

What's the difference between Snowpipe and Snowpipe Streaming?

Snowpipe loads files from cloud storage in micro-batches, typically within one to two minutes of arrival. Snowpipe Streaming writes rows directly into tables with no staging files, with data queryable within seconds; it's built for continuous streams like Kafka or CDC rather than discrete files.

Is Snowpipe Streaming's classic architecture being retired?

Yes. Snowflake plans a formal deprecation announcement for the classic architecture in mid-2026, followed by an 18-month migration window before end-of-life. Any new streaming implementation should use the high-performance architecture instead.

Do I always need to load data into Snowflake before querying it?

No. External tables let you query data directly from cloud storage without loading it first, which is useful when you only need to query a subset of a much larger externally stored dataset.

What file size does Snowflake recommend for Snowpipe?

Roughly 100-250MB compressed. Smaller files arriving frequently should generally be aggregated into larger batches before ingestion for efficiency.

What are Dynamic Tables used for?

Declarative, SQL- or Python-defined data transformations where Snowflake automatically manages refresh scheduling and dependency tracking, reprocessing only data that has changed since the last refresh.