Skip to main content
When you create a new pipeline, Integrate.io ELT & CDC performs an initial sync to load the full historical data from your source tables into the destination. Once the initial sync completes, the pipeline switches to continuous sync mode and begins capturing ongoing changes.
Diagram showing the initial sync process from source table through chunking, Avro streaming, S3 staging, and destination loading

How chunking works

During initial sync, Integrate.io counts the number of records in a table and divides them into chunks of roughly equal size. Each chunk corresponds to a SELECT statement with a primary key range. Multiple chunks are processed in parallel, which allows large tables to sync faster than a single sequential read. Chunking is supported on tables with numeric primary keys (integer, big integer, medium integer). Tables with non-numeric primary keys are synced without chunking. Parallel chunk processing requires Avro as the S3 staging file format. Avro is the default. If you configure your destination with a different S3 file format, such as JSON, initial sync runs single-threaded and processes chunks one at a time. For Oracle sources, the record count that determines the number of chunks comes from table statistics instead of a full COUNT(*) scan. This keeps the pre-sync preparation step fast on very large tables. The estimate affects only chunk sizing, never data coverage, so stale statistics do not cause missing rows.

Oracle tables without a numeric primary key

Oracle tables that have no numeric primary key can still be chunked and synced in parallel. When a table has no numeric primary key and no custom primary key, Integrate.io looks for an indexed numeric column to use as the chunking key. A column qualifies when:
  • It is the leading column of a valid index (partitioned indexes qualify).
  • It is a NUMBER column declared NOT NULL.
  • It holds integer values (scale 0 or an unconstrained NUMBER).
If more than one column qualifies, Integrate.io picks the one whose index order best matches the physical row order. The column does not need to be unique. If no column qualifies, or if the column’s maximum value would produce an excessive number of chunks, the table falls back to a sequential sync without parallel chunking. To make a large primary-key-less Oracle table eligible for parallel chunking, add an index on a NOT NULL integer column:
A custom primary key always takes precedence. If you set one on a table, chunking uses that key instead of an indexed column.

Data flow stages

  1. Source read. Each chunk reads rows from the source database using a range query.
  2. Avro stream. Row data from all active chunks is combined into batches in the Avro format.
  3. S3 staging. When a batch reaches the maximum default size or a sync timeout occurs, the batch is written to S3 as an Avro file.
  4. Destination load. Staged Avro files are loaded into the destination warehouse (Redshift, Snowflake, BigQuery, or S3).

Schema setup phase

Before any data loads, Integrate.io creates or verifies the destination table for every selected table. The destination applies these schema changes one table at a time, so on pipelines with hundreds of tables this phase can take a while before the first rows arrive. Timeouts during this phase measure stalls, not total duration. Each time the destination finishes setting up a table, the timeout resets. Integrate.io never fails a wide pipeline for being slow, only for being stuck. The stall timeout defaults to 15 seconds per selected table, with a minimum of 5 minutes and a maximum of 60 minutes.

Chunk worker settings

Initial sync for database sources (MySQL, PostgreSQL, SQL Server, Oracle, DB2) processes chunks with parallel workers. Four settings control how these workers retry and how long they wait for the schema setup phase: The settings are grouped under a chunkWorker key in the source configuration:
Integrate.io rejects misspelled keys and out-of-range values inside chunkWorker when the pipeline starts. These settings are not configurable from the dashboard. If your pipeline needs different values, contact support@integrate.io.

Resumability

Initial sync is resumable. Once all records in a chunk have been transferred to S3, that chunk is marked as finished and will not be reprocessed if the pipeline is restarted or interrupted. This means that if a sync fails partway through a large table, it picks up from where it left off rather than starting over.

Mid-sync path changes

Initial sync runs either with parallel chunk workers (used for Redshift, Snowflake, and BigQuery destinations) or without them. The two methods record initial-sync progress differently, so switching a table from one method to the other partway through initial sync would silently skip or duplicate rows. To prevent this, Integrate.io records the method each table starts on and halts that table if the method changes before initial sync finishes.
  • If the Source: Keep syncing healthy tables even if others fail toggle is enabled in the pipeline’s Sync Options, the affected table is marked errored and other tables continue.
  • Otherwise the pipeline fails to start.
Recovery is either a resync of the affected table or restoring the previous configuration. Resyncs are never automatic. Tables already in continuous sync are not affected.

What happens after initial sync

Once the initial sync completes for all selected tables, the pipeline transitions to continuous sync. For database sources (PostgreSQL, MySQL, SQL Server, Oracle), continuous sync uses log-based replication to capture inserts, updates, and deletes in near real-time. For SaaS/API sources, continuous sync uses scheduled polling. During continuous sync, the pipeline also handles schema changes automatically.

What is Integrate.io ELT & CDC

Schema Changes

Custom Primary Key

Last modified on October 27, 2026