Snowpipe Streaming Classic best practices¶

Cost optimization¶

As a best practice, we recommend calling the API with fewer Snowpipe Streaming clients that write more data per second. Use a Java or Scala application to aggregate data from multiple sources, such as IoT devices or sensors, and then use the Snowflake Ingest SDK to call the API to load data at higher flow rates. The API efficiently aggregates data across multiple target tables in an account.

A single Snowpipe Streaming client can open multiple channels to send data, but the client cost is only charged per active client. The number of channels does not affect the client cost. Therefore, we recommend using multiple channels per client for performance and cost optimization.

Using the same tables for both batch and streaming ingestion can also result in reduced Snowpipe Streaming compute costs due to pre-empted file migration operations. If Automatic Clustering is also enabled on the same table that Snowpipe Streaming is inserting into, compute costs for file migration might be reduced. The clustering operation optimizes and migrates data in the same transaction.

Performance recommendations¶

For optimal performance in high-throughput deployments, we recommend the following actions:

  • If you are loading multiple rows, using insertRows is more efficient and cost effective than calling insertRow multiple times because less time is spent on locks.

    • Keep the size of each row batch passed to insertRows below 16 MB compressed.

    • The optimal size of row batches is between 10-16 MB.

  • Pass values for the TIME, DATE, and all TIMESTAMP columns as one of the supported types from the java.time package.

  • When you create a channel using OpenChannelRequest.builder, set the OnErrorOption to OnErrorOption.CONTINUE, and manually check the return value from insertRows for potential ingestion errors. This approach currently leads to a better performance than relying on exceptions thrown when OnErrorOption.ABORT is used.

  • When you set the default log level to DEBUG, make sure that the following loggers keep logging on INFO: their DEBUG output is very verbose, which can lead to a significant performance degradation.

    • net.snowflake.ingest.internal.apache.parquet

    • org.apache.parquet

  • Channels should be long lived when a client is actively inserting data and should be reused because offset token information is retained. Don’t close channels after inserting data because data inside the channels is automatically flushed based on the time configured in MAX_CLIENT_LAG.

Latency recommendations¶

When you use Snowpipe Streaming, latency refers to how quickly data written to a channel becomes available for querying in Snowflake. Snowpipe Streaming automatically flushes data within channels every one second, meaning you don’t need to explicitly close a channel for data to be flushed.

Configuring latency with MAX_CLIENT_LAG With Snowflake Ingest SDK versions 2.0.4 and later, you can fine-tune data flush latency by using the MAX_CLIENT_LAG option:

  • Standard Snowflake Tables (non-Iceberg): The default MAX_CLIENT_LAG is 1 second. You can override this to set your desired flush latency anywhere from 1 second up to a maximum of 10 minutes.

  • Snowflake-managed Iceberg Tables: Supported by Snowflake Ingest SDK versions 3.0.0 and later, the default MAX_CLIENT_LAG is 30 seconds. This default helps ensure that optimized Parquet files are created, which is beneficial for query performance. While you can set a lower value, it’s generally not recommended unless you have exceptionally high throughput.

Latency recommendations for optimal performance Setting MAX_CLIENT_LAG effectively can significantly impact query performance and the internal migration process (where Snowflake compacts small partitions).

For low-throughput scenarios, where you might only be sending a small amount of data (for example,e.g., 1 row or 1 KB) every second, frequent flushes can lead to numerous small partitions. This can increase query compilation time as Snowflake has to resolve many tiny partitions, especially if queries run before the migration process can compact them.

Therefore, you should set MAX_CLIENT_LAG as high as your target latency requirements allow. Buffering inserted rows for a longer duration allows Snowpipe Streaming to create better-sized partitions, which improves query performance and reduces migration overhead. For example, if you have a task that runs every minute to merge or transform your streamed data, an optimal MAX_CLIENT_LAG might be between 50 and 55 seconds. This ensures data is flushed in larger chunks just before your downstream process runs.

Kafka connector for Snowpipe Streaming It’s important to note that the Kafka connector for Snowpipe Streaming has its own internal buffer. Whenthe Kafka buffer flush time is reached, data is then sent to Snowflake with the standard one-second latency through Snowpipe Streaming. For more information, see buffer.flush.time setting

Language: English