Classic Snowpipe: stage files to an internal or external stage, then call the REST API ingest endpoint or use auto-ingest via a cloud storage event notification to trigger a COPY INTO; suitable for micro-batch file-based workflows with latency in the seconds-to-minutes range.
Snowpipe Streaming: use the Snowflake Ingest SDK (Java client) to open a channel per table partition and call insertRows() with row maps; rows appear in the target table in near-real-time (sub-second to low seconds) without staging files.
For Snowpipe Streaming, configure the channel with an offset token that your application manages; on restart, resume from the last committed offset token to avoid data loss or duplication.
Classic Snowpipe charges per file load based on compute credits; Snowpipe Streaming charges based on rows ingested and compute; compare cost at your expected row rate before choosing.
Use Snowpipe Streaming with Kafka Connect (Snowflake Kafka connector) by setting ingestion.method=SNOWPIPE_STREAMING in the connector config to route Kafka records directly into Snowflake without staging.
For both modes, monitor credit consumption and error queues (REST status endpoint for classic Snowpipe; channel status via SDK for Streaming) to detect stalled or failed ingestion.
Known gotchas
Snowpipe Streaming rows are initially written to a micro-partition buffer; they are not immediately visible to queries until the buffer is flushed, which typically takes a few seconds but can be longer under load.
Classic Snowpipe has a minimum file size recommendation; loading many tiny files increases per-file overhead and cost; batch files to at least the recommended size before triggering ingestion.
Snowpipe Streaming channel names must be unique per table; reusing a channel name after a crash without resetting the offset token can cause duplicate inserts.
Give your agent this knowledge — and 200+ more routes
One MCP install gives any agent live access to the full route map, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp