Hi Rahul,
I recommend a screen sharing with an expert to tune your specific use case. Please let me know if I can help arrange a working session.
Different pipelines can have unique requirements, the suggestions below should be considered as guidelines only.
Increasing concurrency can improve throughput, but I would first address the column mismatch before moving from one to six pipeline instances. Otherwise, additional instances may reproduce the same issue at a larger scale.
For a historical multi-table load, one option StreamSets supports is using a multithreaded JDBC MultiTable Consumer with the Snowflake destination. Each origin thread reads from one table at a time and passes its batches to an available pipeline runner. Because different runners can process batches concurrently, the order in which batches reach the destination is not guaranteed. Also, ensure the JDBC origin's maximum connection pool size is at least equal to the configured number of threads.
A few suggestions:
1.
Investigate the column mismatch firstThe mismatch is not necessarily evidence of shared writer state. It can also occur if records for different target tables are routed incorrectly, if the dynamic file/table expressions resolve inconsistently, or if a table's schema changed after the pipeline started. Confirm that the staging file pattern, target database, schema, and table are derived from
record-specific fields or header attributes, rather than mutable pipeline-level parameters shared across concurrent processing.
2.
Prefer controlled table-level parallelismFor multi-table historical loads, start with multiple threads inside one pipeline and assign one thread per source table, up to the number of tables that can be processed concurrently. Configure enough Snowflake Executor connections to support the active tables, and ensure your Snowflake warehouse has enough resources. StreamSets documentation notes that additional Snowflake connections allow multiple tables to be written concurrently when using
COPY, and the connection pool applies to the pipeline as a whole.
[docs.streamsets.com]
Multiple pipeline instances can improve throughput only if each instance has a clearly defined, non-overlapping work assignment, such as a dedicated list of tables or explicit key/date-range partitions. Running six identical instances against the same historical source selection could create duplicate processing or offset contention, depending on the origin and partitioning design.
A safe model would be:
Instance 1 processes an explicit subset of tables
Instance 2 processes a different subset
Each instance uses a unique staging path or prefix
Each table has one active writer during initial troubleshooting
Source partitions and restart boundaries are deterministic
3. Increase batch size before scaling aggressively
StreamSets recommends increasing the origin's maximum batch size from the typical default of 1,000 records to approximately 20,000 to 50,000 records per batch for Snowflake bulk loading. Heap size should be monitored and increased as needed because larger batches and additional threads consume more memory.
Treat that range as a starting point rather than a universal setting. Test progressively, for example:
10,000 -> 20,000 -> 30,000 -> 50,000 records
Measure batch processing time, staged-file size, Data Collector heap and garbage collection, Redshift query pressure, Snowflake warehouse utilization, and destination errors. Row count is the StreamSets configuration control, but staged-file size and average row width should also be considered when evaluating the result.
4. Validate staging isolation
Because the Snowflake destination writes files to an internal or external stage and then instructs Snowflake to process those files, make sure separate files, tables, pipeline instances, and concurrent writers cannot unintentionally reuse or overlap staging locations or file-selection patterns.
5. Stopping and restarting
StreamSets processes data in batches. The origin records an offset, the batch moves through the pipeline, the destination writes it, and the offset is committed according to the configured delivery guarantee. Exact recovery behavior depends on the origin, destination, delivery guarantee, and pipeline configuration.
Therefore, I would not describe an arbitrary stop as universally loss-free or duplicate-free without reviewing those settings. For a bounded historical load, a clean approach is to let the origin emit a
no-more-data event and route that event to a Pipeline Finisher. This allows expected processing to complete before the pipeline transitions to a finished state.
My recommended sequence would be:
- Reproduce the problem with two threads.
- Confirm the resolved staging, target table and record schema for every failing batch.
- Assign unique stage prefixes where applicable.
- Start with a batch size of 10,000 records.
- Increase Snowflake Executor connections to match the number of concurrently written tables.
- Scale threads gradually while monitoring heap, batch latency, and Snowflake load history.
- Use multiple instances only after dividing the tables or partitions into non-overlapping assignments
If the mismatch continues, please open a support case and include the StreamSets version, pipeline export with credentials removed, origin and destination stage types, exact COPY INTO error, target-table DDL, failing staged-file metadata, thread and connection-pool settings, and whether any schemas are changing during execution. That information will help determine whether the issue is routing, schema discovery, staging isolation, or a product defect.
------------------------------
Eric Greisdorf
------------------------------